【ExcelVBA】IPアドレスから接続スイッチを特定する。MACテーブルとARPテーブルを自動読み込み

スポンサーリンク




作業を自動化する

ネットワークの運用でたまにあるのが、このIPアドレスのサーバってどのスイッチに接続されているんだっけ?

サーバ撤去や通信不具合の調査などでこの疑問が発生します。

ケーブル結線図や詳細なサーバ管理表があればすぐ分かるかも知れませんが、ネットワークの情報しかない場合は実機を見る必要があります。

この記事は、

  • そもそも何から調べたらいいか分からない
  • 情報を取得するコマンドを知りたい
  • ARPテーブルとMACアドレステーブルの目視チェックが大変

こんな方に読んでもらいたいです。

スポンサーリンク

IPアドレスから接続スイッチを特定する方法

探しているIPアドレスのサーバが接続されていると想定されるネットワーク機器から2つの情報を取得します。

  • ARPテーブル
  • MACアドレステーブル

 

ネットワーク機器はL2スイッチ、L3スイッチ、ルータ、ファイアウォール、ロードバランサーなどです。

ARPテーブルは、ルーティング可能な装置(L3スイッチ、ルータ、ファイアウォール、ロードバランサー)がIPアドレスとMACアドレスを紐付ける情報です。
表示するコマンドはこちらです。

show arp

MACアドレステーブルは、スイッチング可能な装置(L2スイッチ、L3スイッチ、ルータ、ファイアウォール、ロードバランサー)MACアドレスとインタフェース(接続スイッチ名)を紐付ける情報です。
表示するコマンドはこちらです。

show mac address-table

 

 

もう気づいているかもしれませんが、どちらのテーブルにもMACアドレスがあります

 

 

この2つの情報を・・

IPアドレスMACアドレス

MACアドレスインタフェース(接続スイッチ名)

MACアドレスで組み合わせると・・

IPアドレスインタフェース(接続スイッチ名)

IPアドレスから接続スイッチ名が特定できます。

 

 

これでIPアドレスから接続スイッチを見つける方法がわかりました。

次は2つのテーブルを読み込ませて組み合わせるExcelVBAを準備しますが、読み込ませる際の注意点としてファイル名の規則を決めておきます。

ホスト名_機種名_テーブル情報.txt

例えばホスト名が「tokyo-sw01」のARPテーブルの場合

tokyo-sw01_Catalyst_arp.txt

という感じにします。

例えばホスト名が「tokyo-sw01」のMACテーブルの場合

tokyo-sw01_Catalyst_mac.txt

にします。

 

ネットワーク機器からARPテーブルとMACアドレステーブルの情報を取得したらファイル名の規則に従って名前を付けてください。

取得するコマンドはこちらです。

show arp
show mac address-table

マクロを動かして自動で特定する

ファイルが準備できたらマクロをコピペします。
ツールの[開発]から[Visual Basic]を選択して「Microsoft Visual Basic for Applications」を開く

ツールの[挿入]から[標準モジュール]を選択

真っ白な画面が出るので下のコードをコピーして貼り付けます。

※画像は別のコードを貼り付けたものなので、貼り付け位置の参考にしてください。

このコードをコピーして貼り付けます。
Sub MACARPテーブル紐づけ()
    'シート文字を全削除
    Cells.Clear
    
    '基本情報設定
    検索パス = ThisWorkbook.Path
    MACファイル検索文字 = "*mac.txt"
    ARPファイル検索文字 = "*arp.txt"
    
    'ヘッダを作成
    ヘッダ = Array("ホスト名", "インタフェース", "MACアドレス", "紐づくIPアドレス", "", "MACアドレス", "IPアドレス")
    Range("A1:G1") = ヘッダ
    
    Dim ファイルパスリスト()
    Dim ファイル名リスト()
    
    'パスからファイル名とファイルパスを取得
    Call ファイル情報取得(検索パス, ファイルパスリスト, ファイル名リスト)
    
    'ファイル数だけ繰り返し
    For i = LBound(ファイル名リスト) To UBound(ファイル名リスト)
        Select Case True
            'MACファイルだった場合の処理値を設定
            Case ファイル名リスト(i) Like MACファイル検索文字
                セレクト列1 = 3
                セレクト列2 = 1
                貼り付け開始列 = 2
                貼り付け終了列 = 3
                
            'ARPファイルだった場合の処理値を設定
            Case ファイル名リスト(i) Like ARPファイル検索文字
                セレクト列1 = 3
                セレクト列2 = 1
                貼り付け開始列 = 6
                貼り付け終了列 = 7
            Case Else
                GoTo 次へ
        End Select
        
        'ファイルの内容をリストに格納
        リスト = ファイルの内容を配列へ格納(ファイルパスリスト(i))
        
        'リストの内容から必要なデータのみ抽出
        テーブル = セレクト(リスト, セレクト列1, セレクト列2)
        
        'セルの貼り付け範囲を特定
        開始行 = Cells(Rows.Count, 貼り付け開始列).End(xlUp).Row + 1
        終了行 = Cells(Rows.Count, 貼り付け開始列).End(xlUp).Row + UBound(テーブル)
        
        'データを貼り付け
        Range(Cells(開始行, 貼り付け開始列), Cells(終了行, 貼り付け終了列)) = テーブル
        
        'MACアドレスの場合のみファイル名を貼り付け
        If ファイル名リスト(i) Like MACファイル検索文字 Then
            Range(Cells(開始行, 1), Cells(終了行, 1)) = ファイル名リスト(i)
        End If
次へ:
    Next
    
    'VLOOKUP関数貼り付け用のスペースを取得
    全テーブル = Range(Cells(1, 1), Cells(Cells(Rows.Count, 1).End(xlUp).Row, 4))
    
    'スペースにVLOOKUP関数を準備
    For i = 2 To UBound(全テーブル)
        全テーブル(i, 4) = "=VLOOKUP(C" & i & ",$F$2:$G$100001,2,0)"
    Next i
    
    'VLOOKUP関数を貼り付け
    Range(Cells(1, 1), Cells(Cells(Rows.Count, 1).End(xlUp).Row, 4)) = 全テーブル

End Sub

Function ファイル情報取得(パス, ByRef ファイルパスリスト, ByRef ファイル名リスト)
    Dim FSO, ファイルオブジェクト As Object
    Set FSO = CreateObject("Scripting.FileSystemObject")
     
    Dim 仮ファイルパスリスト()
    Dim 仮ファイル名リスト()
    Dim ループカウンタ As Long
    ループカウンタ = 0
    For Each ファイルオブジェクト In FSO.GetFolder(パス).Files
        ReDim Preserve 仮ファイルパスリスト(ループカウンタ)
        ReDim Preserve 仮ファイル名リスト(ループカウンタ)
        仮ファイルパスリスト(ループカウンタ) = ファイルオブジェクト.Path
        仮ファイル名リスト(ループカウンタ) = ファイルオブジェクト.Name
        ループカウンタ = ループカウンタ + 1
    Next
    ファイルパスリスト = 仮ファイルパスリスト
    ファイル名リスト = 仮ファイル名リスト
End Function
Function ファイルの内容を配列へ格納(ファイルパス)
    Set FSO = CreateObject("Scripting.FileSystemObject")
    Set テキストファイル = FSO.OpenTextFile(ファイルパス)
    ファイルの内容を配列へ格納 = Split(テキストファイル.ReadAll, vbLf)
    テキストファイル.Close
End Function
Function セレクト(リスト, 列1, 列2)
    i = 1
    Dim テーブル()
    For Each ライン In リスト
        ReDim Preserve テーブル(1 To 2, 1 To i)
        スペース削除 = WorksheetFunction.Trim(ライン)
        On Error Resume Next
            テーブル(1, i) = Split(スペース削除)(列1)
            テーブル(2, i) = Split(スペース削除)(列2)
            i = i + 1
    Next
    セレクト = WorksheetFunction.Transpose(テーブル)
End Function

ちなみに調査するファイルはテスト用に作成した下記情報を使いました。
L2スイッチを想定した1台目

Switch# show mac-address-table
All xxxx.xxxx.xxx1 Dynamic Gi1/0/1
All xxxx.xxxx.xxx2 Dynamic Gi1/0/2

L2スイッチを想定した2台目

Switch# show mac-address-table
All xxxx.xxxx.xxx3 Dynamic Gi1/0/3
All xxxx.xxxx.xxx4 Dynamic Gi1/0/4

L3スイッチを想定した3台目

Switch# show arp
Internet 10.0.0.1 - xxxx.xxxx.xxx1 ARPA Vlan1
Internet 10.0.0.2 - xxxx.xxxx.xxx2 ARPA Vlan2
Internet 10.0.0.3 - xxxx.xxxx.xxx3 ARPA Vlan3
Internet 10.0.0.4 - xxxx.xxxx.xxx4 ARPA Vlan4
フォルダ内の全てのファイルを調査するので、ARPテーブルとMACアドレステーブル以外のファイルは置かなようにして下さい。

 

ALT+8を押して「MACARPテーブル紐づけ」を選択して実行します。
するとファイルの読み込みと組み合わせが行われて・・

結果が表示されます。

赤枠はMACアドレステーブルを読み込み部分で、
青枠はARPテーブルを読み込み部分です。
黄色はARPテーブルを参照してMACアドレスに紐づくIPアドレスを表示させています。

後はCtrl+Fの検索機能やフィルタ機能を使って目的のIPアドレスを探します。

見つからなかったらどうすればいいのか。
下記3点を試してください。

  • 探しているIPアドレスはPing応答している事を確認
  • トレースルートを実行して到達直前のIPアドレスが存在しているか探す
  • 取得したARPテーブル、MACアドレステーブルに漏れがないか見直す

 

今回のマクロは機種をCatalystスイッチに限定して作成しています。
他の機種でも使う場合はマクロの修正が必要です。

VBAはどうやって覚える?

プログラムのコードが読めないと、参考にしているコードの理解や自分でプログラムを書くときに時間がかかってしまいます。
そんな時は基礎から学ぶと応用もできるようになり自分の作りたいプログラムがスムーズに作れるようになります。

無料でやるならネット上の学習サイトを参考にするといいです。
こちらはVBAの基礎から説明している無料のWEBサイトになります。

>>基礎から学べるWEBサイトを見てみる


過去にネットでの学習でつまずいた方には専門書をオススメします。
本なら基礎から学べる事はもちろん、読者視点で分かりやすい解説になっています。
一人で集中してコツコツ進めたい方は書籍を試してください。



本を読んでもわからない所が多すぎたり、誰かに質問したい場合はオンラインスクールを見てみましょう。
カウンセリングから目的に応じた学習プランと教材を提供してくれるので、プログラミングの敷居がとても低くなります。
誰かに相談できるのは心強いです。

>>オンラインスクールを無料体験で始める


スポンサーリンク

まとめ

IPアドレスから接続スイッチを特定するには、ARPテーブルとMACアドレステーブルを取得して組み合わせる事で調査が出来ることが分かりました。

今回は、その組み合わせからIPアドレスを検索してスイッチを特定する部分をExcelVBAのマクロで自動化しています。

調査対象が数台であれば目視でもチェック可能ですが、数十台になるとマクロを駆使した方が楽になるはずなのでぜひ試してみてください。

タイトルとURLをコピーしました