【ExcelVBAで自動化】スイッチのコンフィグからルーティング図を作成する

ルーティング図はそのネットワークの理解を高めることができます。

送信元IPと送信先IPの情報があればネットワークの経路が一目瞭然になるので、ネットワークの変更・トラブル調査に活用できます。

とはいえ他の資料と違って、あれば便利だけど無くてもいいという物です。

今回はそんなルーティング図を１から作ってみます。

 

作成したルーティング図

白い枠

上から順に

• インターネット
• switch1（L3スイッチ）
• switch2（L3スイッチ）
• 他拠点

それぞれIPアドレスを記載しています。

 

青い枠

上２つの青はswitch1のルーティング情報で、それぞれインターネット側へ転送するネットワークとswitch2へ転送するネットワークを分けて入力しています。

下２つの青はswitch2のルーティング情報で、それぞれswitch1へ転送するネットワークと他拠点へ転送するネットワークを分けて入力しています。

 

赤い枠

ここにはルーティング図作成中にルーティング先不明になったルーティング情報を入力します。

 

必要な情報を取得

作る方法はIPアドレスとルーティング情報があれば作れるのでかなり簡単です。

ネットワーク機器へ接続して２つのコマンドをうつだけです。

• show running-config
• show ip route

show running-configのファイル名はホスト名.txt

show ip routeのフォルダ名はホスト名_shiproute.txt

にしてください。

テスト用に作ったファイルだとこんな感じです。

show running-config

switch1#show run
interface gigabitethernet 1/0/1
    ip address 192.168.1.1 255.255.255.0
!
interface gigabitethernet 1/0/2
    ip address 192.168.10.2 255.255.255.0
!

 

switch2#show run
interface gigabitethernet 1/0/1
    ip address 192.168.1.2 255.255.255.0
!
interface gigabitethernet 1/0/2
    ip address 10.0.0.2 255.255.255.0
!

show ip route

switch1#show ip route
S 0.0.0.0/0 via 192.168.10.1
S 10.0.0.0/8 via 192.168.1.2

switch1#show ip route
S 0.0.0.0/0 via 192.168.1.1
S 10.0.0.0/8 via 10.0.0.1

 

 

作り方

Excelのオートシェイプを使って図を作ります。

 

装置の枠を作り、線で結ぶ

　枠の名前は【ホスト名】で記載します。

 

ルーティングを書く枠を作る

　枠の名前はホスト名からホスト名で記載します。

 

Excelと同じフォルダに取得したファイルを保存する

今回は先ほど取得した（ことにした）４つのファイルを読み込んでいきます。

• switch1.txt
• switch2.txt
• switch1_shiproute.txt
• switch2_shiproute.txt

ExcelVBAでIPアドレスを取得するマクロを実行

実行するマクロはこれです。

 

Option Explicit
    Dim 検索結果 As String
    Dim ファイルパス As String
    Dim ホスト名 As String
    Dim 読み込み行 As String
    Dim IPアドレス As String
    Dim 機能 As String
    Dim ゾーン As String

Sub コンフィグからIPを抽出して入力する()
'ファイルを検索する
    Dim ファイル名 As String
    ファイルパス = ThisWorkbook.Path
    ファイル名 = ".txt"

    検索結果 = Dir(ファイルパス & "\*" & ファイル名 & "*")
    
    Dim ファイル名検索文字列1, ファイル名検索文字列2 As String
    ファイル名検索文字列1 = ""
    ファイル名検索文字列2 = ""
    
    Do
        If 検索結果 = "" Then
            MsgBox "ファイルが見つかりません。終了します。"
            Exit Sub
        End If
        If 検索結果 Like "*" & ファイル名検索文字列1 & "*" And _
           検索結果 Like "*" & ファイル名検索文字列2 & "*" Then
'ファイル名からホスト名を取得する
            ホスト名 = Split(検索結果, ".")(0)
'コンフィグを読み込んでIPアドレスを図に入力
            Call コンフィグ読み込み
        End If
        検索結果 = Dir()
    Loop While 検索結果 <> ""

    MsgBox "おわり"
End Sub


Function コンフィグ読み込み()
    Open ファイルパス & "\" & 検索結果 For Input As #1
    Do Until EOF(1)
'ファイルの内容を1行ずつ読み込む
        Line Input #1, 読み込み行
'1行の中にIPアドレスが含まれているか確認して図に入力
        Call IPアドレス判定
    Loop
    Close #1
End Function

Function IPアドレス判定()
Dim 行分割() As String
Dim IP候補 As Variant
    IPアドレス = ""

'IPアドレスの書かれている行の情報
    If 読み込み行 Like "*ip address*" Or _
       読み込み行 Like "* virtual-server *" Or _
       読み込み行 Like "* standby *" Then
    Else
        Exit Function
    End If

    行分割 = Split(読み込み行, " ")
    For Each IP候補 In 行分割()
'「.」が3つある文字列をIPアドレスとして認識する
        If 3 = UBound(Split(IP候補, ".")) Then
            IPアドレス = IP候補
'図形からホスト名を探してそこへ読み込んだ行を入力する
            Call オートシェイプへ追記(読み込み行, ホスト名)
            Exit Function
        End If
    Next
End Function
Function オートシェイプへ追記(記入文字 As String, 選択条件 As String)
    Dim i As Long
    With ActiveSheet.Shapes
        For i = 1 To .Count
            If .Item(i).Type = 9 Then
            Else
'ホスト名と一致する図を探す
                If .Item(i).TextFrame.Characters.Text Like "【*" & 選択条件 & "*" Then
'図にIPアドレスの情報を入力する
                    .Item(i).TextFrame.Characters.Text = .Item(i).TextFrame.Characters.Text & Chr(10) & 記入文字
                    Exit Function
                    End If
            End If
        Next
    End With
End Function

switch1とswitch2はshow running-configの情報からIPアドレスを取得できています。

インターネットと別拠点はコンフィグがないのでIPアドレスがわかりませんが、この後で確認するので今はIPアドレスは空欄のままにします。

ExcelVBAでルーティングを取得するマクロを実行

実行するマクロはこれです。

Option Explicit
    Dim 検索結果 As String
    Dim ファイルパス As String
    Dim ホスト名 As String
    Dim 読み込み行 As String
    Dim IPアドレス As String
    Dim 機能 As String
    Dim NEXTHOPホスト名 As String
    Dim ゾーン As String
Sub ルート情報から宛先を確認して入力()
    Dim ファイル名 As String
    
    ファイルパス = ThisWorkbook.Path
    ファイル名 = ".txt"
    
    検索結果 = Dir(ファイルパス & "\*" & ファイル名 & "*")
    
    Dim ファイル名検索文字列1, ファイル名検索文字列2 As String
    ファイル名検索文字列1 = "shiproute"
    ファイル名検索文字列2 = ""
    
    Do
        If 検索結果 = "" Then
            MsgBox "ファイルが見つかりません。終了します。"
            Exit Sub
        End If
        If 検索結果 Like "*" & ファイル名検索文字列1 & "*" And _
           検索結果 Like "*" & ファイル名検索文字列2 & "*" Then
            ホスト名 = Split(Split(検索結果, ".")(0), "_")(0)
            Call ルーティング読み込み
        End If
        検索結果 = Dir()
    Loop While 検索結果 <> ""

    MsgBox "おわり"
End Sub


Function ルーティング読み込み()
    Open ファイルパス & "\" & 検索結果 For Input As #1
    Do Until EOF(1)
        Line Input #1, 読み込み行
        Call コネクト以外のルート特定
    Loop
    Close #1
End Function

Function コネクト以外のルート特定()
Dim 行分割() As String
Dim IP候補 As Variant
Dim i As Long
    IPアドレス = ""

    Dim NEXTHOPアドレス As String
    Select Case True
        Case Left(読み込み行, 1) = "S"
            行分割 = Split(読み込み行, "via ")
            NEXTHOPアドレス = 行分割(1)
        Case Left(読み込み行, 2) = "*S"
            行分割 = Split(読み込み行, " ")
            For i = 0 To UBound(行分割)
                If UBound(Split(行分割(i), ".")) = 3 Then
                    If 行分割(i) Like "*/*" Then
                    Else
                        NEXTHOPアドレス = 行分割(i)
                        Exit For
                    End If
                End If
            Next i
    End Select
    If Left(読み込み行, 1) = "S" Or _
       Left(読み込み行, 2) = "*S" Then
        Call IPアドレスからNEXTHOPホスト名の特定(NEXTHOPアドレス)
        Call オートシェイプへ追記(読み込み行, Replace(Replace(ホスト名, "】", ""), "【", "") & "から" & NEXTHOPホスト名)
    End If
End Function

Function IPアドレスからNEXTHOPホスト名の特定(調査アドレス As String)
    Dim i As Long
    With ActiveSheet.Shapes
        For i = 1 To .Count
            If .Item(i).Type = 9 Then
            Else
                If .Item(i).TextFrame.Characters.Text Like "【*" & 調査アドレス & "*" Then
                    NEXTHOPホスト名 = Mid(.Item(i).TextFrame.Characters.Text, 2, InStr(.Item(i).TextFrame.Characters.Text, "】") - 2)
                    Exit Function
                End If
            End If
        Next
        NEXTHOPホスト名 = "宛先不明"
    End With
End Function

Function オートシェイプへ追記(記入文字 As String, 選択条件 As String)
    Dim i As Long
    With ActiveSheet.Shapes
        For i = 1 To .Count
            If .Item(i).Type = 9 Then
            Else
                If .Item(i).TextFrame.Characters.Text Like "*" & 選択条件 & "*" Then
                    .Item(i).TextFrame.Characters.Text = .Item(i).TextFrame.Characters.Text & Chr(10) & 記入文字
                    If NEXTHOPホスト名 = "宛先不明" Then
                        .Item(i).Fill.ForeColor.RGB = RGB(255, 100, 100)
                    End If
                    Exit Function
                End If
            End If
        Next
    End With
End Function

 

 

実行した結果ですが、インターネット側のIPアドレスと他拠点のIPアドレスが書かれていないので、ルーティング先がわからず赤い枠に書かれています。

ここでルーティング情報を一旦削除してマクロ実行前の状態に戻します。

さっき赤い枠に書かれていたIPアドレスをインターネットと他拠点に書いて再実行します。

 

これで完成です。

 

VBAはどうやって覚える？

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

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

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


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



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

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

活用できるタイミング

ほとんどのネットワークではルーティング図は作られていません。

理由はshow runやshow ip routeを見れば事足りるからです。

ではいつ使うのかというと誰かに説明する時です。

 

３つの装置を経由した通信を追っていく場合は３つのshow runと３つのshow ip routeを見る事になります。

一人ならじっくり見て理解していけばいいですが、複数人が同じように理解できるとは限りません。

そんな時に全ての情報が１つの画面に収まって図になっていると理解は各段に早まります。

 

あとは、一人で使う場合でも複数の送信元と送信先の組み合わせについてルートを確認したいときはかなりスピーディーにできるようになります。