【AWS】EC2のリソース情報をExcelでまとめるハンズオン

1.はじめに

先日上司からある一言を言われました。
上司「EC2インスタンスの情報をEXCELで見たいから落としてきてくんない？」
何を言ってるんだと思った方が多いと思いますが、この上司、なんとEXCELしか使えないんです。。
コンソール入るだけで済むやんと言いたくはなりますが、こういう上司に限って社内では権力を持っているためとても言い返せないのです。
技術は極める、上司には媚びへつらう、両方やらなくちゃいけないのがエンジニアの辛いとこです。
ということでコンソールに入れば済む話ですが、上司のご希望通りわざわざEXCELに落としてフォーマット整えて提出してやりました。
今回はそのやり方をハンズオン形式で記事にまとめてみようと思います。
やり方としては非常に簡単なので是非最後までお付き合い頂けたらと思います。

2.ハンズオンの流れ&目標

今回のハンズオンの流れと目標を以下に記載します。
　
<ハンズオン手順>
①特定の条件を持つEC2のリソース情報をCSVファイルにまとめる。
②zipコマンドで圧縮
③SCPコマンドでコピー | zip解凍
④VBAでExcelに取り込み&フォーマット整え
※ローカルでCLIを使用すれば②③は必要ありませんが、それだと味気ないので今回はあえてEC2コンソールからSCPで落とすという遠回りをします。
※本環境はAmazon Linux2の使用を想定しています。他のインスタンスをご使用の方は別途AWS CLiをインストールする必要があります。
　
<ハンズオン目標>
EC2インスタンスのリソース情報をローカルのExcelに取り込む

3.リソース情報をCSV化

まずはEC2コンソールでの作業です。
それでは何でもいいのでお手持ちのEC2にSSH接続して下さい。
まずはAWS CLIを使用できるように設定します。
以下のコマンドを順に実行して下さい。

# rootにスイッチ
[ec2-user@ip-#-#-#-# ~]$ sudo su -
# jqインストール（既にインストール済みかも..）
[root@ip-#-#-#-# ~]# yum install -y jq
# AWSCLIプロファイルを生成
[root@ip-#-#-#-# ~]# aws configure
AWS Access Key ID [None]:<アクセスキーID>
AWS Secret Access Key [None]:<シークレットキーID>
Default region name [None]:<リージョン名>
Default output format [None]:json

では次にAWSコマンドを使用してEC2のリソース情報を取得 & CSV化します。
ここでは私の好みの項目を取得をしていますが、取得する情報は選べますので皆様お好きな項目を追加・削除して下さい。

aws ec2 describe-instances | jq -r '.Reservations[].Instances[] | select(.State.Name == "running") | 
  [
    ( .Tags[]| select(.Key == "Name") | .Value ) // "",
    .InstanceId,
    .InstanceType,
    .Placement.AvailabilityZone,
    .State.Name,
    .Platform,
    .VpcId,
    .VirtualizationType,
    .PublicIpAddress,
    .PrivateIpAddress
  ] | @csv' > EC2.csv

4.取得したリソースファイルをローカルへ移動する

安全性を考え、CSVファイルをZip化します。
引き続きプロンプト上で下記コマンドを入力して下さい。

$ zip -r EC2.zip EC2.csv 

先程のCSVファイルがzip圧縮されました。

ではSCPコマンドを使用して、リソースファイルをローカルへ落としてみましょう。
MACの方はターミナルを、Windowsの方はコマンドプロンプトを開いて下さい。
そこで下記コマンドを実行してください。

# SCPコマンド使い方
C:\USERS\user> scp ユーザ名@リモートのホスト名:コピーしたいリモートのファイル ローカルのコピー先
# コマンド例
C:\USERS\user> scp -i C:\Users\***\.ssh\***.pem -r ec2-user@*.*.*.*:/home/ec2-user/rougai_jousi_ec2.csv .

■ SCPコマンドの説明
SCPコマンドはsshを使用してローカルとリモートで通信のやり取りを行うコマンドです。
違う環境同士のファイルのやり取りをセキュアに行ってくれるため非常に便利なコマンドになります。
オプションを以下に記載します。

オプション	説明
-r	ディレクトリごと再帰的にコピーする
-p	コピー元のタイムスタンプやパーミッションを保持する
-i <キーファイル>	ssh接続に使用する鍵ファイルを指定する
-p <ポート番号>	接続に使用するポートを指定する

これでローカルへファイルを落とせた事かと思います。
では次のフェーズでExcelに取り込んでみましょう。

5.Excelへの取り込み

最後にExcelからVBAキックして落としてきたzipファイルを選択、EXCELに取り込んでフォーマット修正を行いましょう。
EXCELを開いたら下記コードをVBEに貼り付けて下さい。

Excel取り込みマクロ

'----- マクロ -----

Sub EC2_Tenkai()

'zip解凍
'*****************************************************************************

    'マクロ高速化処理
    '===============================================
    '画面の描画更新を停止する
    '自動計算をOFFにする
    'イベントの発生を無効にする
    'マウスポインタを砂時計にする
    '================================================
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    Application.EnableEvents = False
    Application.Cursor = xlWait

    Dim myFile As Variant
    
    '// ダイアログボックスを開き、落としたzipを選択する
    '※ カレントディレクトリを環境に合わせて変更して下さい。
    ChDir "C:\USERS\chibi"
    myFile = Application.GetOpenFilename("ZIPファイル(*.zip),*.zip")

    If VarType(myFile) = vbBoolean Then
        MsgBox "キャンセルされました"
        End
    Else
        MsgBox myFile & " が選択されました"
    End If
    
    '// 解凍するZIPファイル
    File_Name = myFile
    '// 解凍先
    '※ 環境に合わせて変更して下さい
    File_Path = "C:\Users\chibi"

    '// オブジェクト生成
    Set objShell = CreateObject("Shell.Application")
    
    '// 変数代入
    Set FilesInZip = objShell.Namespace(File_Name).items
    Set objFolder = objShell.Namespace(File_Path)

    '// 解凍
    If (Not objFolder Is Nothing) Then
        objFolder.CopyHere FilesInZip, FOF_NOCONFIRMATION + FOF_SILENT
    End If
    
    '// マクロ実行元
    Dim macroWb As Workbook
    
    '//インポート先
    Dim importWb As Workbook
    Dim importPath As String
    
    '// マクロを実行元のWorkbook
    Set macroWb = ThisWorkbook
    
    '// インポート先の文字列を取得
    '※ 環境に合わせて変更して下さい
    importPath = "C:\Users\chibi\ec2.csv"
    
    '// データをインポート(シートの内容をコピー)
    '※ "ec2","Shhet1"は環境に合わせて変更して下さい
    Set importWb = Workbooks.Open(importPath)
    importWb.Worksheets("ec2").Copy After:=macroWb.Worksheets("Sheet1") 
    importWb.Close

    MsgBox "解凍が終わりました"
    

'*****************************************************************************
'zip解凍ここまで

'フォーマット
'*****************************************************************************
    
　　MsgBox "表を作成します"
    
   '// 横に1つづつずらす
    Dim z As Long
    no = 1
    
    For z = 2 To Cells(Rows.Count, 1).End(xlUp).Row
            Range("A" & z, "N" & z).Cut _
            Destination:=Range("A" & z).Offset(0, no)
            no = no + 1
    Next z
    
    '// 縦から横にデータの向きを変更する
    Dim z2 As Long
    Dim io As Long
    io = 0
    
    For z2 = 1 To z
        Range("A" & z2, "N" & z2).Offset(0, io).Select
        Dim itm As Range
        Dim n As Long
        For Each itm In Selection
          itm.Cut Destination:=ActiveCell.Offset(n, 0)
          n = n + 1
        Next itm
        io = io + 1
     Next z2
        
    '// 空白行を無くして上にずらす
    Dim ios As Long
    For ios = 1 To z
        Columns(ios).Select
        Selection.SpecialCells(xlCellTypeBlanks).Select
        Selection.Delete Shift:=xlUp
    Next ios
    
    
    '// 列と行の挿入
    Rows("1:3").Select
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Columns("A:B").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    
    '// 項目(タイトル)値の入力
    Range("B4").Value = "インスタンス名"
    Range("B5").Value = "インスタンスID"
    Range("B6").Value = "インスタンスタイプ"
    Range("B7").Value = "リージョン名"
    Range("B8").Value = "ステータス"
    Range("B9").Value = "プラットフォーム"
    Range("B10").Value = "VPC_ID"
    Range("B11").Value = "仮想化タイプ"
    Range("B12").Value = "パブリックIPv4 アドレス"
    Range("B13").Value = "プライベートIPv4 アドレス"
    Range("B14").Value = "Key_Name"
    
    '// 項目(タイトル)値の入力　＆　背景色の変更
    Rows("5:5").Select
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("B5").Value = "項目"
    Range("C5").Value = "ステータス値"
    Range("B5").Interior.Color = RGB(0, 0, 255)
    Range("B4").Select
    Selection.End(xlToRight).Select
    c = Selection.Column
    Dim f As Long
    For f = 2 To c
        Cells(4, f).Offset(1).Interior.Color = RGB(153, 255, 153)
        Cells(4, f).Offset(1).Value = "ステータス値"
        Cells(4, f).Select
        Range(Selection, Selection.End(xlDown)).Rows.Select
        Selection.Borders.LineStyle = True
    Next f
    Range("B6").Select
    Range(Selection, Selection.End(xlDown)).Rows.Select
    Selection.Interior.Color = RGB(204, 255, 204)
    
    '// B列を太字化と中央揃い化(記録から取得)
    Columns("B:B").Select
    Selection.Font.Bold = True
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    
    '// 列の自動調節(取得するインスタンス数に応じて設定)
    Columns("A:Z").AutoFit
　　msgbox "表の作成が完了しました"
    msgbox "ハンズオンは以上となります。お疲れ様でした。"
    range("A1").select
    
    'マクロ高速化処理
    '==================================================
    'マウスポインタをデフォルトにする
    'イベントの発生を有効にする
    '自動計算をONにする
    '画面の描画更新を有効にする
    '==================================================
    Application.Cursor = xlDefault
    Application.EnableEvents = True
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
'*****************************************************************************
'フォーマットここまで

End Sub

※取得する情報によって中身のコードを変えて下さい。
貼り付けたらマクロを実行して下さい。
後は自動でEXCELにEC2リソース状況が記載されます。

以上でハンズオンは終了となります。

6.まとめ

しかし改めて見るとほんとにニッチなハンズオンだなと思います。だってコンソール入れば済む話ですもん。。
しかしいつ｢1.はじめに｣で話したようなケースに遭遇するかも分かりませんので使う機会の無いようなことかも知れませんが是非、頭の片隅に入れて置いてくれたらと思います。
後、今回はEC2でCLIを実行しましたが、ローカルでCLIを使用すれば「3.｣は必要ない工程になります。※scp使いたいがためにわざわざEC2からローカルへ落とす作業を挟みました。

最後に、CLI、非常に便利なので真っ黒い画面が嫌いだという方も毛嫌いせずに是非使って見て下さい。
以上で失礼します。