データ集計の効率化 <ルータのステータス情報の集計>

昨日は区切り文字を使用して、ルータの経路情報を抜き出す作業を紹介しましたが

データ区切りを複数回実行することで、データを集計する方法を紹介したいと思います。

今回は、ルータに直接接続している機器がどれくらい存在するかという情報を

１０分間隔で取得し、そのデータを集計する作業を効率的に行いたいと思います。

取得するデータは下記のようなデータで、１０分間隔で異なるファイルに出力されます。

下記のようなデータでは、ルータに２台の機器が接続していることを示しています。

Router#show arp

Protocol  Address          Age (min)  Hardware Addr   Type   Interface

Internet  192.168.1.10            0   001e.8cc8.0235  ARPA   FastEthernet0

Internet  192.168.1.1             -   000c.ce4b.b7a6  ARPA   FastEthernet0

下記のように同じフォルダにファイルが生成されます。

ファイル名の末尾の数字が、取得時刻を表しています。

最終的に下記の集計結果を作成します。

各時刻ごとに何台の機器がルータと接続しているかを表しています。　　　　　　

それでは、データを加工していきます。

grepという抽出ツールを使用して、上記フォルダ内のすべてのデータから

IPアドレスが記載された部分だけを抽出し、エクセルに貼り付けます。

（grepについては本投稿では省略します。）

下記が抽出したものをエクセルに貼り付けた状態となります。

昨日使用した「スペース区切り」のマクロを実行します。

これでデータが各々のセルに格納されます。

下記が、スペース区切り実行後の状態です。

次にA列の「RouterA」「show」「arp」「時刻」を分離します。この時に新たに作成したマクロを実行します。

区切り文字は「_(アンダーバー)」「.(ピリオド)」になりますが、エクセルの機能だけでは二度手間になるため

マクロを作成し、同時に実行できるようにします。

A列からH列にカット＆ペーストし、マクロを実行します。

マクロについては、[開発]タブ> マクロ> ウィザードから「ピリオドとアンダーバーで区切」を選択して実行です。

文字列を分割した状態が以下になります。

「1707240910」「1707240920」「1707240930」を抜き出すことができました。

この後は、ピポットテーブルを使用して集計します。

[挿入]タブ> ピポットテーブル> ウィザードでOKを押下すれば、別シートにピポットテーブルが作成されます。

行ラベルに「時刻」、Σ値に「Internet」等を選択することで、集計結果が完成します。

今回、新たに使用した「ピリオドとアンダーバーで区切」マクロは以下のプログラムになります。

エクセルの「マクロの記録」で作成しただけのものですが、

２回で実行する作業を１回で実行できるので便利です。

Sub ピリオドとアンダーバーで区切()

    

    Selection.TextToColumns Destination:=Range("H1"), DataType:=xlDelimited, _

        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _

        Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _

        :=".", FieldInfo:=Array(Array(1, 1), Array(2, 1)), TrailingMinusNumbers:=True

        

    Selection.TextToColumns Destination:=Range("H1"), DataType:=xlDelimited, _

        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _

        Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _

        :="_", FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1)), _

        TrailingMinusNumbers:=True

        

End Sub