2015年6月3日水曜日

AWSのリソース使用状況レポートを自動生成しインスタンスタイプの最適化を図る【Excelレポート生成編】

こんにちは、井下です。

先日父の日に何を贈ろうかと考えていたところ、父からの直接のオーダーがありました。
内容は「肩たたき1時間」。…小型マッサージ機なら去年贈ったはずなんですが。

はじめに

前回はメトリクスデータの収集を行うバッチ2、バッチのデータを整形するバッチ3について説明しました。
今回はバッチ3で整形されたファイルから、Excelレポート生成を行うバッチ4について説明します。

おさらいになりますが、用意する全バッチは下表の通りです。

バッチ名 処理内容 実行周期
バッチ1インスタンス内でデータを取得し、カスタムメトリクスとしてCloudWatchへ送信します。1分ごと
シェル1バッチ1のシェル版です。Linuxのカスタムメトリクスを取得・送信したい場合は、こちらを利用します。1分ごと
バッチ2 前日分の標準メトリクス・カスタムメトリクスのデータを取得します。 日次
バッチ3 バッチ2で取得したデータをまとめ、OSSのEmbulkを利用して整形します。 月次
バッチ4 バッチ3で整形されたデータから、レポートとしてExcelのグラフを作成します。 月次

バッチ4(Excelレポート生成)


処理概要

バッチ3で整形されたファイル群を元に、Excelレポートを出力します。

パラメータ

第1パラメータ（必須） ：バッチ3で整形されたファイル群の保存先パス（※）
第2パラメータ（必須） ：バッチ3で整形されたファイル群に付与されているインスタンスID
第3パラメータ（必須） ：ファイルの出力先パス（※）

※ ブランクパスを指定する場合、パスの前後を「"」で括ってください。

リターンコード

0 ： 正常終了しました
4 ： パラメータの指定が不正
8 ： 第1パラメータで指定したパスのファイル群が不足しているか、ファイルオープンに失敗しました
12： 第3パラメータで指定したパスが存在しないか、Excelレポートの出力に失敗しました
16： Excelがインストールされていません

サンプルバッチ


@echo off
set vbsdir=C:\BSP\AUW\BIN
echo ### %date% %time% report batch started. inportPath=%1,instace id=%2,exportPath=%3.

 if not "%~1"=="" if not "%2"=="" if not "%3"=="" set PARAM_VALID=TRUE
if not "%PARAM_VALID%"=="TRUE" goto PARAM_INVALID

 for /F "usebackq" %%d in (`"cscript //nologo %vbsdir%\GetDate.vbs %date% m -1"`) do set TAGRGET_MONTH=%%d
set TAGRGET_MONTH=%TAGRGET_MONTH:~0,6%

 cscript //nologo %vbsdir%\report.vbs %1 %2 %TAGRGET_MONTH% %3

 rem 64bit version
rem C:\Windows\SysWOW64\cscript //nologo %vbsdir%\report.vbs %1 %2 %TAGRGET_MONTH% %3

 set RC=%ERRORLEVEL%

 IF "%RC%"=="0" (
  echo ### %date% %time% report create successful.
) ELSE (
  echo ### %date% %time% report create failed.
)
goto END

 :PARAM_INVALID
echo ### %date% %time% parameter invalid.
set RC=4
goto END

 :END
echo ### %date% %time% report batch ended(RC=%RC%).
exit %RC%set vbsdir=C:\BSP\AUW\BIN

サンプルバッチから呼び出すVBSファイル

' excel const
Const xlDown = -4121
Const xlNone = -4142
Const xlXYScatterLinesNoMarkers = 75
Const xlCategory = 1
Const xlValue = 2

 ' report vbs const
Const percentValueMinimum = 50

 Const Sheet1 = "Sheet1"
Const masterTemplateSheetName = "template"

 ' sheet names
Const cpuSheetName = "CpuUsage"
Const avMemSheetName = "AvailableMemory"
Const memUsageSheetName = "MemoryUsage"
Const loadAveSheetName = "LoadAverage"
Const swapMemSheetName = "SwapMemory"
Const netTrfInSheetName = "NetworkTrafficIn"
Const netTrfOutSheetName = "NetworkTrafficOut"

 ' metrics prefixs
Const cpuPrefix = "cpu"
Const availableMemoryPrefix = "availablemem"
Const memoryUsagePrefix = "memusage"
Const loadAveragePrefix = "loadave"
Const swapMemoryPrefix = "swap"
Const networkTrafficInPrefix = "network_in"
Const networkTrafficOutPrefix = "network_out"

 ' export file prefix
Const exportFilePrefix= "report"
    
Set inputParams = Wscript.Arguments

 ' get params
strInputPath = inputParams(0)
instanceId = inputParams(1)
yearMonth = inputParams(2)
strExportPath = inputParams(3)

 logEcho "report vbs started."

 ' set file path, sheet names
strExportFile = strExportPath & "\report_" & instanceId & "_" & yearMonth & ".xlsx"

 cpuDataSheetName = cpuPrefix & "_" & instanceId & "_" & yearMonth
avMemDataSheetName = availableMemoryPrefix & "_" & instanceId & "_" & yearMonth
memUsageDataSheetName = memoryUsagePrefix & "_" & instanceId & "_" & yearMonth
loadAveDataSheetName = loadAveragePrefix & "_" & instanceId & "_" & yearMonth
swapDataSheetName = swapMemoryPrefix & "_" & instanceId & "_" & yearMonth
netTrfInDataSheetName = networkTrafficInPrefix & "_" & instanceId & "_" & yearMonth
netTrfOutDataSheetName = networkTrafficOutPrefix & "_" & instanceId & "_" & yearMonth

 Set objFileSys = Nothing
Set excel = Nothing

 On Error Resume Next

 ' check input file path exist
Set objFileSys = CreateObject("Scripting.FileSystemObject")

 Set cpuFile = objFileSys.GetFile(strInputPath & "\" & cpuDataSheetName & ".csv")
Set avMemFile = objFileSys.GetFile(strInputPath & "\" & avMemDataSheetName & ".csv")
Set memUsageFile = objFileSys.GetFile(strInputPath & "\" & memUsageDataSheetName & ".csv")
Set loadAveFile = objFileSys.GetFile(strInputPath & "\" & loadAveDataSheetName & ".csv")
Set swapFile = objFileSys.GetFile(strInputPath & "\" & swapDataSheetName & ".csv")
Set netTrfInFile = objFileSys.GetFile(strInputPath & "\" & netTrfInDataSheetName & ".csv")
Set netTrfOutFile = objFileSys.GetFile(strInputPath & "\" & netTrfOutDataSheetName & ".csv")

 If Err <> 0 Then
    inputFilesPathNotExist
End If

 ' check export folder path exist
objFileSys.GetFolder(strExportPath)

 If Err <> 0 Then
    exportFolderNotExist
End If

 ' set excel use object

 Set excel = CreateObject("Excel.Application")

 If Err <> 0 Then
    excelNotFound
End If

 On Error Goto 0

 excel.Visible = False
excel.DisplayAlerts = False
oriSheetsInNewWorkbook = excel.Application.SheetsInNewWorkbook
excel.Application.SheetsInNewWorkbook = 1

 ' create workbook
Set reportBook = excel.Workbooks.Add

 On Error Resume Next

 ' open metrics data files
Set cpuWorkBook = excel.Workbooks.Open(cpuFile)
Set networkTrfInBook = excel.Workbooks.Open(netTrfInFile)
Set networkTrfOutBook = excel.Workbooks.Open(netTrfOutFile)
Set avMemBook = excel.Workbooks.Open(avMemFile)
Set memUsageBook = excel.Workbooks.Open(memUsageFile)
Set swapMemBook = excel.Workbooks.Open(loadAveFile)
Set loadAveBook = excel.Workbooks.Open(swapFile)

 If Err <> 0 Then
    inputFilesPathNotExist
End If

 On Error Goto 0

 ' copy metrics data files
cpuWorkBook.Sheets(1).Copy(reportBook.Sheets(Sheet1))
networkTrfInBook.Sheets(1).Copy(reportBook.Sheets(Sheet1))
networkTrfOutBook.Sheets(1).Copy(reportBook.Sheets(Sheet1))
avMemBook.Sheets(1).Copy(reportBook.Sheets(Sheet1))
memUsageBook.Sheets(1).Copy(reportBook.Sheets(Sheet1))
swapMemBook.Sheets(1).Copy(reportBook.Sheets(Sheet1))
loadAveBook.Sheets(1).Copy(reportBook.Sheets(Sheet1))

 ' close metrics data files
cpuWorkBook.Close
networkTrfInBook.Close
networkTrfOutBook.Close
avMemBook.Close
memUsageBook.Close
swapMemBook.Close
loadAveBook.Close

 ' delete tmp sheet
reportBook.Sheets(Sheet1).Delete

 ' create master template sheet
createMasterTemplate(yearMonth)

 ' create metrics sheet
Call reportBook.Sheets(masterTemplateSheetName).Copy(,reportBook.Sheets(reportBook.Sheets.Count))
reportBook.Sheets(reportBook.Sheets.Count).name = cpuSheetName
insertData cpuSheetName, cpuDataSheetName
reportBook.Sheets(cpuDataSheetName).Delete
Call createGraph(cpuSheetName, True)

 Call reportBook.Sheets(masterTemplateSheetName).Copy(,reportBook.Sheets(reportBook.Sheets.Count))
reportBook.Sheets(reportBook.Sheets.Count).name = avMemSheetName
insertData  avMemSheetName, avMemDataSheetName
reportBook.Sheets(avMemDataSheetName).Delete
Call createGraph(avMemSheetName, False)

 Call reportBook.Sheets(masterTemplateSheetName).Copy(,reportBook.Sheets(reportBook.Sheets.Count))
reportBook.Sheets(reportBook.Sheets.Count).name = memUsageSheetName
insertData memUsageSheetName, memUsageDataSheetName
reportBook.Sheets(memUsageDataSheetName).Delete
Call createGraph(memUsageSheetName, True)

 Call reportBook.Sheets(masterTemplateSheetName).Copy(,reportBook.Sheets(reportBook.Sheets.Count))
reportBook.Sheets(reportBook.Sheets.Count).name = loadAveSheetName
insertData loadAveSheetName, loadAveDataSheetName
reportBook.Sheets(loadAveDataSheetName).Delete
Call createGraph(loadAveSheetName, False)

 Call reportBook.Sheets(masterTemplateSheetName).Copy(,reportBook.Sheets(reportBook.Sheets.Count))
reportBook.Sheets(reportBook.Sheets.Count).name = swapMemSheetName
insertData swapMemSheetName, swapDataSheetName
reportBook.Sheets(swapDataSheetName).Delete
Call createGraph(swapMemSheetName, True)

 Call reportBook.Sheets(masterTemplateSheetName).Copy(,reportBook.Sheets(reportBook.Sheets.Count))
reportBook.Sheets(reportBook.Sheets.Count).name = netTrfInSheetName
insertData netTrfInSheetName, netTrfInDataSheetName
reportBook.Sheets(netTrfInDataSheetName).Delete
Call createGraph(netTrfInSheetName, False)

 Call reportBook.Sheets(masterTemplateSheetName).Copy(,reportBook.Sheets(reportBook.Sheets.Count))
reportBook.Sheets(reportBook.Sheets.Count).name = netTrfOutSheetName
insertData netTrfOutSheetName, netTrfOutDataSheetName
reportBook.Sheets(netTrfOutDataSheetName).Delete
Call createGraph(netTrfOutSheetName, False)

 ' delete master template sheet
reportBook.Sheets(masterTemplateSheetName).Delete

 ' save report file
On Error Resume Next

 reportBook.SaveAs(strExportFile)
If Err <> 0 Then
    exportFolderNotExist
End If

 On Error Goto 0

 logEcho "report vbs ended."
reportEnd 0

 Sub insertData(masterSheetName, dataSheetName)
    mIndex = 1
    dIndex = 1
    mEndIndex = reportBook.Sheets(masterSheetName).Range("A1").End(xlDown).Row
    
    While mIndex < mEndIndex
        If reportBook.Sheets(masterSheetName).Range("A" & mIndex).Value = reportBook.Sheets(dataSheetName).Range("A" & dIndex).Value Then
            reportBook.Sheets(masterSheetName).Range("B" & mIndex).Value = reportBook.Sheets(dataSheetName).Range("B" & dIndex).Value
            dIndex = dIndex + 1
        End If
        mIndex = mIndex + 1
    Wend
    
End Sub

 Sub createMasterTemplate(yearMonth)
    index = 1
    startYear = Left(yearMonth, 4)
    startMonth = Right(yearMonth, 2)
    
    startDate = CDate(startYear & "/" & startMonth & "/1 00:00:00")
    endDate = DateAdd("m", 1, startDate)
    
    tmpDate = startDate
    Set sheet = excel.Worksheets.Add
    sheet.name = masterTemplateSheetName
    
    While tmpDate <= endDate
        sheet.Range("A" & index).Value = tmpDate
        sheet.Range("B" & index).Value = 0
        tmpDate = DateAdd("n", 5, tmpDate)
        index = index + 1
    Wend
End Sub

 Sub createGraph(sheetName, isPercentValue)
    Set sheet = excel.Worksheets(sheetName)
    lastRow = sheet.Range("A1").End(xlDown).Row
    Set chart = sheet.ChartObjects.Add(50, 20, 850, 350)
    chart.Chart.ChartType = xlXYScatterLinesNoMarkers
    chart.Chart.SetSourceData sheet.Range("A1:A" & lastRow & ",B1:B" & lastRow)
    
    chart.Chart.Axes(xlCategory).MinimumScale = sheet.Range("A1").Value
    chart.Chart.Axes(xlCategory).MaximumScale = sheet.Range("A" & lastRow).Value
    chart.Chart.Axes(xlCategory).TickLabelPosition = xlNone
    chart.Chart.SeriesCollection(1).Name = sheetName
    
    If isPercentValue = True Then
        chart.Chart.Axes(xlValue).MinimumScale = percentValueMinimum    
    End If
    
End Sub

 Sub inputFilesPathNotExist() 
    logEcho "Not exist input files path, or input file open failed."
    reportEnd 8
End Sub

 Sub exportFolderNotExist() 
    logEcho "Not exist export path, or export file failed."
    reportEnd 12
End Sub

 Sub excelNotFound()
    logEcho "Excel file use faild."
    reportEnd 16
End Sub

 Sub logEcho(message)
    WScript.Echo "### " & Now & " " & message
End Sub

 Sub reportEnd(returnCode)
    If Not excel Is Nothing Then
        excel.Application.SheetsInNewWorkbook = oriSheetsInNewWorkbook
        excel.Quit
        Set excel = Nothing
    End If
    If Not objFileSys Is Nothing Then
        Set objFileSys = Nothing
    End If
    WScript.Quit returnCode
End Sub


バッチ補足


1.出力するExcelレポート内容

バッチ4では、下記のようなExcelレポートを出力します。

  • シートごとにメトリクスのデータが入っている
  • グラフは1月分全てのデータを表示
  • 単位が%のメトリクスのグラフに関しては、縦軸の最低値を50に設定
  • 横軸は全メトリクスのグラフ共通で、値を表示しない
  • データの存在しない時刻に対しては、値を0とする

シートは下記の表のように設定しています。
シート名 メトリクス 単位
CpuUsageCPU使用率%
AvailableMemory空メモリー容量MByte
MemoryUsageメモリー使用率%
LoadAverageロードアベレージ個
SwapMemory仮想メモリー使用率%
NetworkTrafficInネットワーク送信バイト数MByte
NetworkTrafficOutネットワーク受信バイト数MByte

なお、グラフで表示されている値がいつ時点のデータかを知りたい場合は、グラフをマウスオンすることで確認することができます。

2.出力ファイル

出力先：第3パラメータの指定先

出力ファイル（リターンコードが0の場合のみ出力します）
ファイル名：report_インスタンスID_年月.xlsx

3.実行方法とタイミング

A-AUTO 50でバッチ3終了後に実行するか、手動でコマンドプロンプトから実行してください。
なお、バッチ4の実行には、前回のブログで作成した「GetDate.vbs」が「A-AUTO 50のダウンロード先」\AUW\BINに配置してあることが前提となります。
※実行する方法に関係なく必須の前提条件です

注意事項として、A-AUTO 50から本バッチを自動で実行する場合、予め下記のフォルダを作成しておく必要があります。
※作成しておかないと、Excelの操作時にエラーが発生します

32bitOS：C:\Windows\System32\config\systemprofile\Desktop
64bitOS：C:\Windows\SysWOW64\config\systemprofile\Desktop

なお、MicrosoftはOffice製品が不安定な動作をしたり、デッドロックが発生する可能性があるとし、Office製品が非対話の無人実行をサポート・推奨していません。

Microsoftが保証しないと提示しているので、基本的には手動実行をお勧めします。
コマンドプロンプトからバッチ4を手動実行すれば、上記のリスクなくExcelレポートを生成できます。


A-AUTO 50から実行する場合は、下記の手順を踏んでください。
※前提として、前回のブログで作成手順を説明したジョブネットワーク、スケジュールが作成済みであることとします

1.ジョブネットワーク情報の登録
登録方法に関しては、過去のブログをご参照ください。

前回のブログで作成したバッチ3のジョブネットワークを編集し、バッチ3の実行後、バッチ4が実
行されるように設定します。

  • ジョブ
    • ジョブ番号：バッチ3のジョブよりも後ろの数字を指定します。例えば、バッチ3のジョブ番号を「010」にしている場合、バッチ4のジョブ番号に「020」を指定します。
    • ジョブコード：バッチ4のジョブ名を指定します。なお、8桁まで入力が可能です。例として、バッチ4を「report.bat」とした場合、「REPORT」と入力します。
    • マックスリターンコード：「1」を指定します。ここで指定した数値未満の値であれば、正常終了と見なします。
    • ノーマル実行時のジョブ引渡しパラメータ：バッチ4の実行時のパラメータを指定します。必要なパラメータは前述の「パラメータ」をご参照ください。
    • リラン実行時のパラメータ ：ノーマル実行時の引渡しパラメータと同様の入力をしてください。こちらは異常終了時からの再実行時の入力パラメータとなります。
    • 先行ジョブ番号：バッチ3のジョブ番号を指定します。




バッチ1～バッチ4までを順次実行することで、Excelレポートの生成まで行えるようになりました！

これでExcelレポートの7つのシートから、インスタンスタイプが適正であるかを視覚的に判断することができるようになりました。


投稿者 時刻:
ラベル: , , ,

0 件のコメント:

コメントを投稿

登録: コメントの投稿 (Atom)