ネットワークエンジニアのプログラミング ほっぷ・すってぷ・Tera Term
今回書いてあるTera TermマクロとexcelVBAをつかうと、アクセススイッチの空きポートが一覧できますので、良かったら使ってください。
例えば
「３００台のアクセススイッチの空きポートを一覧にしろ！」と言われたとしても、秒でできます。
では、本文です。
ネットワークエンジニアはプログラミングが出来ません。
（もちろん出来る人はいるよ！！！）
ただ、前回のブログでも言いましたが、プログラミングができないとIoTの世界で生きていけないので、
ネットワークエンジニアもIoTに興味のある人も、
第一歩目としてお勧めなのがTera Termマクロです。
（嘘です、ネットワークエンジニアにはお勧めですが、WEB界隈の人はマイコン制御した方がいいです。ラズパイです）
今回は、こんな感じでやってみます。
料理の目的
ゴール：自動でアクセススイッチの空きポート確認をする
目的 ：スキルの付かない反復単純作業の自動化
理由 ：浮いた時間でTera Termマクロを覚え、更に自動化 → IoTエンジニアへ
時間 ：2時間クッキング
クッキングの流れ
①Tera Termマクロを作成
・ASW（Access SWitchにログイン、コマンドを実行）
・ログ取得
②ログをexcelに貼り付ける
・excel上で少し成形する
・excel VBAで空きポートなのか判断する
材料
; 接続情報
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