【VBA】フローを可視化して、自動化の悟りを開く【TeraTermMacro】

前置き

本日12月8日は、釈迦が悟りを開いたとされる日で、それを祝う成道会(じょうどうえ)という法会が行われている。
自己の無明を認識して智慧の光で照らすことが「悟り」の道であるならば、
データや処理のフローを可視化して明らかにすることが「自動化」の道だと考えた。
(仏教タグへのこじつけ)

概要

IPアドレスの確認コマンドを自動で打つマクロを作るために、フローを可視化してみた。

現状

• 案件ごとに使用するIPアドレスが未使用であることを確認するために、踏み台サーバとルータ2台でコマンドを打っている。
• pingやtracerouteの結果待ち時間があるため、コマンドを連続で流し込めない。結果が返ってきたことを確認してから手動で次のコマンドを打つため、その間は人手が拘束されてしまう。

要件

• 結果が返ってきたら自動的に次のコマンドを打つことで、すべての処理が終わるまで手作業不要にしたい。
• 誤ってルータの設定変更コマンドを打たないようにしたい。

前提

• 使用予定のIPアドレスから確認コマンドを作成する工程は、すでにExcel関数で自動出力できるようになっている。
• 踏み台サーバに自動ログインする工程は、別チーム内で作成していたTeraTermマクロを流用できる。
• 踏み台サーバとルータ2台のログイン名・パスワードは同じものとする。

実現方法

全体の流れ

• Excelファイルに記載したユーザ情報から、踏み台サーバへの自動ログインコマンドをExcel関数で出力。
• IPアドレスを記載して自動出力された確認コマンドを、TeraTermマクロのコマンドにVBAで変換する。
• 上記2つのコマンド群を順番に実行するTeraTermマクロ実行ファイル(.ttl)を作成し、任意のパスに出力する。

その他

• 自動的に次のコマンドを打つために、TeraTermマクロのwaitを利用。
• conf～、sy～で始まるコマンドが書かれていた場合、処理を中断する。

処理の流れ

IPアドレス確認処理フロー

• 踏み台サーバにログインする
• 確認コマンドをいくつか打つ
• 踏み台サーバからルータAにログインする
• 確認コマンドをいくつか打つ
• ルータAからログアウトする
• 踏み台サーバからルータBにログインする
• 確認コマンドをいくつか打つ
• ルータBからログアウトする

Excelファイルでの処理フロー

• ユーザ情報から、踏み台サーバにログインするTeraTermマクロのコマンドを作成する
• 入力したIPアドレスから、ルータでの確認コマンドを作成する
  ※この辺の処理の仕方は、下記投稿を参照。
  　https://qiita.com/AnPanda/items/93390f43b7115873c4e5
• ルータでの確認コマンドから、TeraTermマクロのコマンドを作成する
• TeraTermマクロのコマンドから、TTLファイルを出力する

Excel画面イメージ

• 薄い黄色のセル：ユーザごとの可変値。一度入れたら変更は不要。
• 濃い黄色のセル：案件ごとに入力する可変値。

VBAマクロでの処理フロー

VBAマクロのコード

※プログラミングの専門家ではないので、効率が悪いところがあるのはご愛敬。配列の要素数を1個ずつ増やすところとか。。。

Sub shutsuryoku()
Worksheets("macro").Select
Dim WaitTriggerDefault As String
    '踏み台サーバのプロンプト。機器からログアウトしたらこれに戻す。
Dim WaitTrigger As String
    '初期値は踏み台サーバのプロンプト。telnetした場合は機器名に変更する。
Dim HeaderRange As Range
    '踏み台サーバにログインするコマンド群のセル範囲。他の用途に流用できそうなので、後続のコマンドとは分ける。
Dim ShoriRange As Range
    'Pingやtracerouteなどのコマンド群のセル範囲。
Dim SavePath As String
    'TTLファイルの保存先
Dim Output() As Variant
    '最終的なアウトプット。この配列にTTLコマンドを記載していく。

WaitTriggerDefault = "'~]$'"
WaitTrigger = WaitTriggerDefault
    '初期値は、踏み台サーバのプロンプトを記述｡
    'TeraTermマクロの書式に合わせて、シングルクオートで囲む。
Set HeaderRange = Range(Cells(2, 3), Cells(Cells(Rows.Count, 3).End(xlUp).Row, 3))
Set ShoriRange = Range(Cells(2, 4), Cells(Cells(Rows.Count, 4).End(xlUp).Row, 4))
'マクロにする対象範囲をRangeで取得。
'今回のファイルでは行数は変わらないので固定値でもよいが、可変値にして他に流用しやすくする
SavePath = Range("B6").Value
ReDim Preserve Output(1)
    Output(UBound(Output)) = ";;;アドレス確認Teratermマクロ for " & Cells(8, 2)
    '配列の長さを定義しつつ、1行目にコメントを記載。

For Each cell In HeaderRange
    ReDim Preserve Output(UBound(Output) + 1)
        Output(UBound(Output)) = cell
Next
'踏み台サーバへの自動ログインコマンドを、Output配列に入れる。
'すでにTeraTermマクロの書式になっているので、ただ入れていくだけ。
'配列の要素を1つ増やして、その増やしたところにセルの内容を代入。

'-------確認コマンドをTeraTermマクロの書式にする処理ここから
For Each cell In ShoriRange
If cell.Value Like "conf*" Or cell.Value Like "sy*" Then
'confモードに入るコマンドだった場合、処理を中止する。
    cell.Select
    MsgBox "configモードに入るコマンドを投入しないでください。", vbCritical, "中断"
    'confモードに入るコマンドのセルに移動し、メッセージを表示する。
    Exit Sub
End If

ReDim Preserve Output(UBound(Output) + 1)
    Output(UBound(Output)) = "sendln '" & cell & "'"
    'confモードのコマンドでなかったら、とりあえず確認コマンドをsendlnで入力する分。

If cell.Value Like "telnet *" Then
    WaitTrigger = "'" & Replace(cell.Value, "telnet ", "") & "'"
    'telnetの場合は、Waitのトリガーを機器名に変更
    ReDim Preserve Output(UBound(Output) + 1)
        Output(UBound(Output)) = "Wait ':'"
    ReDim Preserve Output(UBound(Output) + 1)
        Output(UBound(Output)) = "sendln username"
    ReDim Preserve Output(UBound(Output) + 1)
        Output(UBound(Output)) = "Wait ':'"
    ReDim Preserve Output(UBound(Output) + 1)
        Output(UBound(Output)) = "sendln passwd"
    ReDim Preserve Output(UBound(Output) + 1)
        Output(UBound(Output)) = "Wait ':'"
    ReDim Preserve Output(UBound(Output) + 1)
        Output(UBound(Output)) = "sendln ''"
    'ユーザー名とパスワードを入力してログインする。
    '半角コロンが出てきたら入力可能状態と判断。
ElseIf cell.Value Like "exi*" Or cell.Value Like "q*" Then
    WaitTrigger = WaitTriggerDefault
    'exit,quitの場合、Waitトリガーを初期値に変更
    ReDim Preserve Output(UBound(Output) + 1)
        Output(UBound(Output)) = "Wait " & WaitTrigger
    ReDim Preserve Output(UBound(Output) + 1)
        Output(UBound(Output)) = "sendln ''"
    ReDim Preserve Output(UBound(Output) + 1)
        Output(UBound(Output)) = "Wait " & WaitTrigger
    ReDim Preserve Output(UBound(Output) + 1)
        Output(UBound(Output)) = "sendln ''"
    '機器の区切りが見やすいように、空行を2行分入れる。
'ElseIf cell.Value Like "en*" Then
    'ReDim Preserve Output(UBound(Output) + 1)
        'Output(UBound(Output)) = "Wait ':'"
    'ReDim Preserve Output(UBound(Output) + 1)
        'Output(UBound(Output)) = "sendln 'enablepassword'"
    'enebleコマンドの処理だが、Cisco機を使っていないので封印。
    '使うなら、eneblaパスワードも認証鍵と同じようにパス指定させる。
Else
'telnet・exit・quit・enableでない場合、特に処理をせずに次へ。
End If

ReDim Preserve Output(UBound(Output) + 1)
    Output(UBound(Output)) = "Wait " & WaitTrigger
ReDim Preserve Output(UBound(Output) + 1)
    Output(UBound(Output)) = "sendln ''"
ReDim Preserve Output(UBound(Output) + 1)
    Output(UBound(Output)) = "Wait " & WaitTrigger
ReDim Preserve Output(UBound(Output) + 1)
    Output(UBound(Output)) = "sendln ''"
ReDim Preserve Output(UBound(Output) + 1)
    Output(UBound(Output)) = "Wait " & WaitTrigger
'コマンドの区切りとして、空行を2行分入れる

Next
'-------確認コマンドをTeraTermマクロの書式にする処理ここまで

Sheets.Add After:=Sheets(Sheets.Count)
'TTL出力用に新規シートを作成する。

Range("A1", Cells(UBound(Output), 1)) = WorksheetFunction.Transpose(Output)
'Output配列の長さを確認し、それと同じセル範囲をRangeとして指定して、貼り付ける。
'配列は横方向にしか晴れないので、縦方向に出力するためにTranspose。

ActiveSheet.Copy
ActiveWorkbook.SaveAs FileFormat:=xlCurrentPlatformText, _
Filename:=SavePath & Format(Now, "yyyymmdd_hhmmss") & ".ttl"
ActiveWorkbook.Close SaveChanges:=False
'作成したシートをTTLファイルとして保存する。
'ファイル名はタイムスタンプを利用。

Application.DisplayAlerts = False
ActiveWindow.SelectedSheets.Delete
Application.DisplayAlerts = True
'削除メッセージを一時的に非表示にして、作成したシートを削除する。

MsgBox (SavePath & "にTTLファイルを作成しました。")
’完了報告

End Sub

効果

実環境でTeraTermマクロを実行したところ、2分半で処理が終了した。もちろんその間は一切操作なし。
人力で作業する場合、この作業に張り付いて順次コマンドを投入する(2分半は拘束される)か、待ち時間に別の作業をしながら各処理が終わったタイミングで次のコマンドを投入する(マルチタスクで誤操作リスクあり)か。その時間×案件数と誤操作リスクを軽減できることになる。

教訓

• フローが可視化されていれば、マクロを組むのは比較的簡単。フェーズを分けずに同時にやろうとするから、進まない。
• 業務を熟知した現場担当者がフローの可視化を行い、自動化やプログラミングのプロフェッショナルがそれを技術言語に落とし込むと効率がよさそう。
• 他者からもわかりやすくして保守性を高めるために、可視化とコメント記載を習慣にしたい。
• Qiitaには「仏教」タグがある。苦行してる場合じゃねぇ！