【ネットワークエンジニアのプログラミング】アクセススイッチの空きポート確認（材料：Tera Termマクロ＋VBA ）

#ネットワークエンジニアのプログラミング　ほっぷ・すってぷ・Tera Term

今回書いてあるTera TermマクロとexcelVBAをつかうと、アクセススイッチの空きポートが一覧できますので、良かったら使ってください。
例えば
###「３００台のアクセススイッチの空きポートを一覧にしろ！」と言われたとしても、秒でできます。

では、本文です。

ネットワークエンジニアはプログラミングが出来ません。
（もちろん出来る人はいるよ！！！）

ただ、前回のブログでも言いましたが、プログラミングができないとIoTの世界で生きていけないので、
ネットワークエンジニアもIoTに興味のある人も、
第一歩目としてお勧めなのがTera Termマクロです。
（嘘です、ネットワークエンジニアにはお勧めですが、WEB界隈の人はマイコン制御した方がいいです。ラズパイです）

今回は、こんな感じでやってみます。

###料理の目的
ゴール：自動でアクセススイッチの空きポート確認をする
目的　：スキルの付かない反復単純作業の自動化
理由　：浮いた時間でTera Termマクロを覚え、更に自動化 → IoTエンジニアへ
時間　：2時間クッキング
###クッキングの流れ
①Tera Termマクロを作成
　・ASW（Access SWitchにログイン、コマンドを実行）
　・ログ取得
②ログをexcelに貼り付ける
　・excel上で少し成形する
　・excel VBAで空きポートなのか判断する

###材料

teratermマクロ.ttl

; 接続情報
JumpSwitch = 'ホスト名：訳合って踏み台SWにログインが必要'
HOSTADDR01 = 'ASWのホスト名'
HOSTADDR02 = 'ASWのホスト名'
HOSTADDR03 = 'ASWのホスト名'
SSH        = 'ssh '
EXECOMMAND1 = 'ter len 0'
EXECOMMAND2 = 'sh ip int bri'
EXECOMMAND3 = 'sh int status'
EXECOMMAND4 = 'sh int counters'
EXECOMMAND5 = 'sh ver'
USERNAME = 'ログイン時のユーザー名'
PASSWORD = 'パスワード'
;=====================================================================

;--- 踏み台スイッチへログイン
COMMAND = 'xxx.xxx.xxx.xxx' ;--- IP address
strconcat COMMAND ':22 /ssh /2 /auth=password /user='
strconcat COMMAND USERNAME
strconcat COMMAND ' /passwd='
strconcat COMMAND PASSWORD

; 接続
connect COMMAND
strconcat JumpSwitch '>'
strconcat SSH HOSTADDR01
wait JumpSwitch
sendln SSH
wait 'Password:'
sendln PASSWORD

; 接続
strconcat HOSTADDR01 '>'
wait HOSTADDR01
sendln EXECOMMAND1
wait HOSTADDR01
sendln EXECOMMAND2
wait HOSTADDR01
sendln EXECOMMAND3
wait HOSTADDR01
sendln EXECOMMAND4
wait HOSTADDR01
sendln EXECOMMAND5
wait HOSTADDR01
sendln 'exit'
 

;--------------------------------------------------

wait JumpSwitch
SSH = 'ssh '
strconcat SSH HOSTADDR02
sendln SSH
wait 'Password:'
sendln PASSWORD

; 接続
strconcat HOSTADDR02 '>'
wait HOSTADDR02
sendln EXECOMMAND1
wait HOSTADDR02
sendln EXECOMMAND2
wait HOSTADDR02
sendln EXECOMMAND3
wait HOSTADDR02
sendln EXECOMMAND4
wait HOSTADDR02
sendln EXECOMMAND5
wait HOSTADDR02
sendln 'exit'

;--------------------------------------------------

wait JumpSwitch
SSH = 'ssh '
strconcat SSH HOSTADDR03
sendln SSH
wait 'Password:'
sendln PASSWORD

; 接続
strconcat HOSTADDR03 '>'
wait HOSTADDR03
sendln EXECOMMAND1
wait HOSTADDR03
sendln EXECOMMAND2
wait HOSTADDR03
sendln EXECOMMAND3
wait HOSTADDR03
sendln EXECOMMAND4
wait HOSTADDR03
sendln EXECOMMAND5
wait HOSTADDR03
sendln 'exit'

;--- という感じで、ログインしたいスイッチを永遠に繋いでいきます。

closett
end

さぁ、これでエンドレスに入りたいスイッチにログインし、ポートステータスが取得できるようになりましたね。

因みに企業環境下では以下の制約があるケースが多いので、上記のような書き方をしています。
・スイッチへの直接ログインができない
　→踏み台サーバーやスイッチを経由してる書き方をしている。
・スイッチへのenableモードでのログインが制限されている
　→userモードで実行できるコマンドで記述している

--- 実行コマンドの説明 ---
"sh int status"や"sh ip int bri"
　→　ポートのdown/upが判断します。
"sh int counters"
　→　そのポートの累積通信量が表示される

上記２つで以下が分かります。（マトリックスを書きたいが文章で・・・）
ポートがup且つ、通信量があるポート
　→　利用しているポートと判断。
ポートがup且つ、通信量の無いポート
　→　デバイス接続があるが、通信はしてないポート。
ポートがdown且つ、通信量のあるポート
　→　今は接続がない（もしくは結線ありだがエンド端末が電源off）が、
　　　過去に通信していたポート
　　　（このポートはケーブル結線があれば、
　　　　利用しているポートにカテゴライズされるべき）
ポートがdown且つ、通信量の無いポート
　→　利用していないポートと判断。

今回は空きポートを知りたいので、
最後のパターン、「ポートがdown且つ、通信量の無いポート」をリストアップするようなマクロを書きます。

最後に"sh ver"についてですが、
このコマンドで機器のuptime（連続稼働時間）を見ます。
理由は、データの信頼性の確認です。

"sh int counters"の表示される通信量は、スイッチの電源が切れるとリセットされます。
よって、uptimeが1週間未満である場合は、sh int countersで取得した値はただの参考値とします。
（1週間未満だと、通信量が０であっても、たまたまこの一週間使ってなかっただけ。という可能性があるからです）

実際の利用状況は、（しょうがないので）、実地（オンサイト）で確認する事。としておきます。

さて、Tera Termマクロはこれで完成したので、今度はexcel側の成型作業とexcel　VBAを作ります。
こんな感じで書きました。

---

②ログをexcelに貼り付ける
　・excel上で少し成形する
　・excel VBAで空きポートなのか判断する

このフローを実行するマクロを作成します。
１ uptimeの確認
　　対象端末が20台くらいの場合：
　　　excelにログを貼り付けた時点で「uptime」で検索します。
　　　マクロ書きません。ただの検索です。その方が早いです。
　　　そこで、uptimeが1 week未満だったスイッチは今回の処理から除外しましょう。
　　
　　対象端末が300台を超える場合：
　　　マクロ化しましょう。
　　　マクロにより「やっぱり、1 week未満だったよ印」を付ける事にします。

２　ポートのup/downの確認
　　やってみて分かったんですが、"sh int status"の方の結果を使いましょう。
　　今回は、"sh ip int bri"は使いません。
　　理由はポート名の表記です。
　　"sh ip int bri" = GigabitEthernet1/0/1
　　"sh int status" = Gi1/0/1
　　"sh int counters" = Gi1/0/1
　　この3つの中だと、sh ip int briだけ、フルでポート名が表示されるんですね。
　　表示名が合ってる"sh int status"の方を使ってあげましょう。

３　ポートの通信量のチェック
　　これは、ポートのup/down確認の時に、downだったポートのみ確認するように
　　書いてみたいと思います。

４　ホスト名と、空きポートの書き出し
　　ポートがdownステータス且つ、通信量が0だったもののみ書き出し（リストアップ）します。
　　１の処理でuptimeが1 week未満だったものは、1 week未満だったよ印をつけます。

　　こんな感じのVBAにしました。
　　基本の骨組みはこれです。
　　どんな言語でも似てるともいますが、
　　機能別にモジュール分けしておくと、
　　トラシューの時や再利用時に楽です。

Private Sub main()
　　'処理①
　　checkUPtime
　'処理②と③
　　checkNonUsedPorts
　　'処理④
　　listNonUsedPorts
End Sub

Function checkUPtime() As String
　　　処理①
End Function

Function checkNonUsedPorts() As String
　　　処理②と③
End Function

Function listNonUsedPorts() As String
　　　処理④
End Function

では、中身書いてみます。
（注意点：
　　①シートには列名を入れておく。
　　　Ｂ列：ホスト名
　　　Ｃ列：uptimeが1週間未満だったかどうか
　　　Ｄ列：未使用ポート
　　　Ｅ列：notconnectだったポート
　　　Ｆ列：流量０ポート
　　　Ｇ列：uptime
　　②取り急ぎ作成。そのため、アクティブセルに対して処理がいくようになってる。
　　　あとでシート指定に直す
　　③取り急ぎ作成。そのため、ファイルパスがハードコーディングになってる
　　　あとで選択できるように直す
　　④上記で計画したsubやfunctionの設計をCodingでは、いきなり度外視している。
　　　理由は、VBAではテキストファイルを一行ずつ読み込む技が通常らしく（office TANAKA先生）
　　　それに着想を得て、「一度excelに貼り付けて成型作業する必要ないじゃん」って思ったから。
　　⑤空きポートと判断して記載するのが上下逆になっているので、そこは後で直す。

　　因みにQiitaを始めてから、VBAを１０年ぶりに書いてみてます。
　　動いたソースコードではあるので、利用はできるはずですが、何かあればぜひコメントください！
　　

Option Explicit
Private Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long) 'APIを宣言

    '---------------------------------------------------
    '　基本情報
    '　シートは１つ
    '　　シート名「outPut」: まぁシート名は何でもいいです。今はactive cellに対して処理してしまっているので。
    '　大前提
    '　①「uptime」という文字が、sh verの結果以外に含まれない。(基本 port descriptionに記載がなければOK）
    '　②「notconnect」という文字が、sh int statusの結果以外に含まれない。(基本 port descriptionに記載がなければOK）
    '　③ログがずれていたり、表記がバグって無い事。（よく時差が生まれちゃうからね）
    '　④commands は右記の順に実行すること　ter len 0, sh int status, sh int counters, sh ver
    '---------------------------------------------------

Private Sub main2()

    '①uptimeの確認：機器起動時間が1週間未満だった機器を分かるようにする
    writeINFOfromLOG
        
End Sub


'①uptimeの確認：機器起動時間が1週間未満だった機器を分かるようにする
Sub writeINFOfromLOG()

'ログファイルを読み込みながら、以下実行する

    'ログファイル選択
    '我らがオフィス田中
    'http://officetanaka.net/excel/vba/file/file08b.htm
    Dim buf As String, n As Long
    n = 1
    Dim startPointRow As Long
    Dim FlagOfcheckTraffic As Long 'start at "sh int couunters" as 1. end at "sh ver" as 0
    FlagOfcheckTraffic = 0
    Dim columnB As Long, columnE As Long, columnF As Long '
    Dim highestRow As Long
    
    
    Open "ファイル名込みのフルパス" For Input As #1
        Do Until EOF(1)
            Line Input #1, buf
        '●notconnectだったポートを記載する
            If InStr(buf, "notconnect") <> 0 Then
                startPointRow = Cells(1048576, 5).End(xlUp).Row + 1
                Cells(startPointRow, 5) = Mid(buf, InStr(buf, "Gi") - 1, 9)
            End If
        
        '●流量が0のみだった（1-9がなかった）ポートを記載する
            'sh int countersまでいく。
            If InStr(buf, "sh int counters") <> 0 Then
            '    MsgBox "detected sh int coutners"
                FlagOfcheckTraffic = 1
            End If
            
            'sh verが来たら終わりにする
            If InStr(buf, "sh ver") <> 0 Then
            '    MsgBox "detected sh ver"
                FlagOfcheckTraffic = 0
            End If
            
            If FlagOfcheckTraffic = 1 Then
            'MsgBox "この処理は入るよね"
            'MsgBox Mid(buf, 35)
            'Debug.Print Mid(buf, 35)
                If Mid(buf, 35) = "               0              0              0              0 " Then
                    startPointRow = Cells(1048576, 6).End(xlUp).Row + 1
            '        MsgBox buf
            '        MsgBox startPointRow
                    Cells(startPointRow, 6) = Mid(buf, InStr(buf, "Gi") - 1, 9)
                End If
            End If
            
        '●uptime＆ホスト名の記載と1週間未満だったかどうか判断
            'uptime の文字列がある行の情報を取得
            If InStr(buf, "uptime") <> 0 Then
                
                'uptimeを記載する
                startPointRow = Cells(1048576, 7).End(xlUp).Row + 1
                Cells(startPointRow, 7) = Mid(buf, InStr(buf, "uptime") + 9, 50)
                
                'uptimeが1週間未満だったかどうか記載する
                If InStr(buf, "week") = 0 Then
                    startPointRow = Cells(1048576, 3).End(xlUp).Row + 1
                    Cells(startPointRow, 3) = "Yes"
                End If
                
                'ホスト名記載する
                Cells(startPointRow, 2) = Mid(buf, 27, 20)
            
    
                'ホスト毎の区切り文字「---」を列B～Gへ記載する
                    'まず、Ｂ・Ｅ・Ｆ列で一番行数の多い列を判定
                    
                columnB = Cells(1048576, 2).End(xlUp).Row
                columnE = Cells(1048576, 5).End(xlUp).Row
                columnF = Cells(1048576, 6).End(xlUp).Row
                
                If columnB > columnE Then
                    If columnB > columnF Then
                        highestRow = columnB
                    Else
                        highestRow = columnF
                    End If
                ElseIf columnE > columnF Then
                        highestRow = columnE
                Else
                        highestRow = columnF
                End If
                
                    '実際にホスト毎の区切り文字「---」を列B～Gへ記載する
                Cells(highestRow + 1, 2) = "'---"
                Cells(highestRow + 1, 3) = "'---"
                Cells(highestRow + 1, 4) = "'---"
                Cells(highestRow + 1, 5) = "'---"
                Cells(highestRow + 1, 6) = "'---"
                Cells(highestRow + 1, 7) = "'---"
                
                '②空きポート（down/down且つ流量0ポート）を記載する（D列）
                checkNonUsedPorts
                
            End If
            
            
            
        Loop
    Close #1

End Sub

'②空きポート（down/down且つ流量0ポート）を記載する（D列）
Sub checkNonUsedPorts()
    Dim ddPort() As Variant 'E列の配列 dd = down/down ports
    Dim ffPort() As Variant 'F列の配列 ff = final fantasy
    
    Dim RowPoint As Long
    Dim startRowPoint As Long 'E列のスタートポイント　下から処理するので、行数は　start > end
    Dim endRowPoint As Long   'E列のスタートポイント　下から処理するので、行数は　start > end
    Dim FstartRowPoint As Long 'F列のスタートポイント　下から処理するので、行数は　start > end
    Dim FendRowPoint As Long   'F列のスタートポイント　下から処理するので、行数は　start > end
    Dim i As Long
    i = 0
    Dim r As Long
    r = 0
    Dim f As Long
    f = 0
    Dim numberOfPorts As Long
    Dim count As Long
    count = 0
    
    
    '●E列（down/down）を全部配列に入れる
        '★start地点を確認して取得する処理 = startRowPoint
        
    RowPoint = Cells(1048576, 5).End(xlUp).Row - 1
        '区切り文字の直ぐ上がデータなのか、空白なのかの確認
    If Cells(RowPoint, 5) = "" Then
        startRowPoint = Cells(RowPoint, 5).End(xlUp).Row
    Else
        startRowPoint = RowPoint
    End If
        
        '★end地点を確認して取得 = endRowPoint
    endRowPoint = Cells(startRowPoint, 5).End(xlUp).Row + 1
        '配列の数をポートの数と合わせる
        
    numberOfPorts = startRowPoint - endRowPoint
    ReDim ddPort(numberOfPorts)
        
        '配列の最後が来るまで繰り返す。
    r = startRowPoint
    For i = LBound(ddPort) To UBound(ddPort)
        ddPort(i) = Cells(r - i, 5).Value
    Next i
    
    '●F列と比べる。もしあれば空きポートと判断しちゃう
    '　理由：本当はoutgoingとincomingの両方が０かどうか。が判断ポイントなんだけど
    '　片方が０で、片方にデータがあるポートなんて、ほぼないだろ。
        '★F列のスタートとエンドを取って、F列も配列へ
    RowPoint = Cells(1048576, 6).End(xlUp).Row - 1
            '区切り文字の直ぐ上がデータなのか、空白なのかの確認
    If Cells(RowPoint, 6) = "" Then
        FstartRowPoint = Cells(RowPoint, 6).End(xlUp).Row
    Else
        FstartRowPoint = RowPoint
    End If
        
            '★end地点を確認して取得 = endRowPoint
    FendRowPoint = Cells(startRowPoint, 6).End(xlUp).Row + 1
        
        '配列の数をポートの数と合わせる
        
    numberOfPorts = FstartRowPoint - FendRowPoint
    ReDim ffPort(numberOfPorts)
        
        '配列の最後が来るまで繰り返す。
    r = FstartRowPoint
    For i = LBound(ffPort) To UBound(ffPort)
        ffPort(i) = Cells(r - i, 6).Value
    Next i
        
    i = 0
    f = 0
        
        '★★★比べましょう★★★
    For i = LBound(ddPort) To UBound(ddPort)
        For f = LBound(ffPort) To UBound(ffPort)
            If ddPort(i) = ffPort(f) Then
                Cells(endRowPoint + count, 4) = ddPort(i)
                count = count + 1
                Exit For
            End If
        Next f
    Next i



End Sub