Linux/sarをグラフ化その4(Excelで読み込む

マクロ苦手ですけど、必要に迫られて作ったことがあります(2018-03-16)


どうやら、うちのサイトに
「sar excel マクロ」で検索されている方がいらっしゃるようなので、
もし、他で良い感じのマクロが見つからずに、藁をもつかむ思いの方は、
以下の藁をご覧ください。
(およそ10年くらい前に、この件で苦戦していた自分自身を助けるつもりでメモを作ります・・・)

・・・とはいえ、むかしの話なので内容は覚えておらず、
(マクロで「やれる」ことだけは覚えていた)
バグとかあったらごめんなさい。
以下、CPU使用率を拾う例を挙げます。


sarでログを取る

まずは sar の -o オプションでログを拾っておきましょう。
Linux/sarをグラフ化その1(sarでデータ取得

ログをshellで CSV にする

ログをsar -f で読み込んで、
それを awk でCSVファイル(カンマ区切りのファイル)に整形します。
変数部分を変更して、実行してください。

#!/bin/sh
export LANG=C
#
# sar -o で取得したログをCSVにする例
# 必要に応じて変更&バグ修正 (^^; して使ってください
#
# この例はLinux(CentOS) で作成したので、必要に応じて、
# awk を gawk にしたり、egrep を grep -e にしたり環境に合わせて下さい
#
# 出力ファイルは、別途掲載している(予定)のマクロと連動するので
# ファイル名を変える際は、マクロの方も変更して使ってください
#

#--- ここの変数を設定する ------------
SAR_INPUT=./sar_log          # sar -o で出力したログ
SAR_DATE=`date +%y%m%d`      # ログの出力日付

# sarデータからログ開始時刻を取得します
SAR_TIME=`sar -f $SAR_INPUT |\
          awk '{
            if($1~/[0-9:]+/){
               gsub(":", "", $1);
               print $1;
               exit;}
          } '`

SAR_OUTPUT_DIR=./$SAR_DATE # 出力先ディレクトリ
SAR_OUTPUT_COMMENT="one_fine_day"  # コメント欄(スペース不可)
#--- ここの変数を設定(ここまで) -----

get_output_path(){
  # sarYYMMDD-HHMMSS-type-comment.csv
  printf ${SAR_OUTPUT_DIR}/sar${SAR_DATE}-${SAR_TIME}-$1-${SAR_OUTPUT_COMMENT}.csv
}

if [ ! -d $SAR_OUTPUT_DIR ]; then mkdir $SAR_OUTPUT_DIR; fi

#
# CPU summary (sar -u)
#   00:00:01        CPU     %user     %nice   %system   %iowait    %steal     %idle
#   00:10:01        all      0.08      0.00      0.06      0.02      0.17     99.68
#   00:20:01        all      0.07      0.00      0.06      0.02      0.18     99.67
#
output_file=`get_output_path CpuSummary`
sar -f $SAR_INPUT -u |\
  egrep -vi '^$|average|Linux|CPU' |\
  awk '
    BEGIN{
      printf("%s,%s,%s,%s,%s,%s,%s\n", "time", "total", "user", "nice", "sys", "io", "steal");
    }
    {
      if( $2 ~ /all/){
          printf("%s,%0.2f,%0.2f,%0.2f,%0.2f,%0.2f,%0.2f\n", $1, 100-$8, $3, $4, $5, $6, $7);
      }
    }
  ' \
  > $output_file

#
# CPU per-processor (sar -P ALL)
#   *** if 1 only, you don't need this log. ***
#
#  00:00:01        CPU     %user     %nice   %system   %iowait    %steal     %idle
#  00:10:01        all      0.08      0.00      0.06      0.02      0.17     99.68
#  00:10:01          0      0.07      0.00      0.07      0.01      0.09     99.76
#  00:10:01          1      0.08      0.00      0.04      0.04      0.25     99.59
#
output_file=`get_output_path CpuAll`
sar -f $SAR_INPUT -P ALL |\
  egrep -vi '^$|average|Linux|CPU' |\
  awk '
    BEGIN{
      last_time = "-";
      count = 0;
    }
    {
      if($1 != last_time){
          if(last_time == "-"){ last_time=$1; }
          else {
              if(count <= 0){
                  # 初回はヘッダ行を出力する
                  printf("time");
                  for(i in cpu){
                      printf(",cpu-%d", i);
                  }
                  printf("\n");
              }
              printf("%s", last_time);
              for(i in cpu){
                  printf(",%0.2f", 100-value[i]);
                  delete velue[i]
              }
              printf("\n");
              count++
          }
          last_time = $1;
      }
      if( $2 !~ /all/){
          if(count <= 0){
              # 初回はCPUデータの行数を記録する
              cpu[$2] = $2
          }
          value[$2] = $8;
      }
    }
    END{
        printf("%s", last_time);
        for(i in cpu){
            printf(",%0.2f", 100-value[i]);
        }
        printf("\n");
    }
  ' \
  > $output_file

sarの出力はログの内容によって異なるので、
その都度整形する必要があります。
今回は「-P ALL」(プロセッサ単位のCPU使用率)を例に挙げましたが
これは、実際に有用なログというほかに、
環境によってプロセッサ数が変わるという厄介なログの例になります。

一度グラフにした後で、
Excelを使って不要な列を削除したりデータを平均化したりするといいでしょう。
でも、データ量が多過ぎるとExcelが固まるので、
CSV化する時に量を絞る必要があります。
やってみながら、調整が必要ですね。

Excelで読み込む

上記のShellで出力したCSVをディレクトリごと持ってきたら、
そのフォルダごと丸々取り込むExcelマクロの例です。

そもそも、どうやってマクロをONにするかとかは、
良い感じに解説してくれるサイトが見つかるはずです。
ついさっき自分がそうでした(そもそもマクロをどうやってry)

※Excel2016でしか動作確認できていません

' indexシート以外を全削除して、
' 指定フォルダのCSVを全て読み込んで、
' グラフを描画します
'
' index という名前のシートを作って
' 「Run」を呼ぶボタンとかを置いて使ってください

Sub Run()
    DeleteAllSheets
    ReadAllCsv
    makeLink
    Sheets("index").Activate
End Sub

Sub DeleteAllSheets()
    ' "index" という目次用シートを除いて、すべてのシートを削除します
    Dim is_index As Boolean
    is_index = False
    For Each s In ThisWorkbook.Sheets
        If s.Name = "index" Then
            is_index = True
        End If
    Next
    If is_index Then
        Worksheets("index").Cells.Clear
    Else
        Worksheets.Add
        ActiveSheet.Name = "index"
    End If
    
    Application.DisplayAlerts = False
    For Each s In ThisWorkbook.Sheets
        If s.Name <> "index" Then
            s.Delete
        End If
    Next
    Application.DisplayAlerts = True
End Sub

Sub ReadAllCsv()
    ' 指定フォルダにある、すべてのCSVファイルを読み込みます
    Dim path As String, buf As String
    With Application.FileDialog(msoFileDialogFolderPicker)
        If .Show = False Then
            Exit Sub
        End If
        path = .SelectedItems(1)
    End With
    buf = dir(path & "\*.csv")
    Do While buf <> ""
        Call OpenCsv(path, buf)
        buf = dir()
    Loop
End Sub

Sub OpenCsv(dir As String, file As String)
    ' CSVファイルを開いて、グラフを作成します
    Workbooks.Open dir & "\" & file
    ActiveSheet.Move After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
    
    ' file から情報を拾う
    ' 書式は「sarYYMMDD-HHMMSS-データ種類-コメント.csv」を前提としています
    Dim val As Variant
    val = Split(file, "-")
    Dim c_time As String, c_type  As String
    c_time = val(1)
    c_type = val(2)
    
    ' シート名を変更(半角で31文字までが上限?)
    ActiveSheet.Name = c_time & "-" & c_type
    
    ' グラフを作成
    Call MakeNewChart(ActiveSheet.Name, c_time, c_type)
End Sub

Sub MakeNewChart(s_name As String, s_time As String, s_type As String)
    ' グラフを作ります
    ActiveSheet.Shapes.AddChart2(227, xlLine).Select
    ActiveChart.SetSourceData Source:=Sheets(s_name).Range("A1").CurrentRegion
    ActiveChart.Location Where:=xlLocationAsNewSheet
    ActiveChart.Name = "グラフ" & s_name
    ActiveChart.ChartTitle.Text = s_type
    
    ' タイプ毎にグラフの描画方法を変えます
    If s_type = "CpuSummary" Or s_type = "CpuAll" Then
        ' 使用率グラフの場合は、範囲を0~100で固定する
        ActiveChart.Axes(xlValue).MaximumScale = 100
        ActiveChart.Axes(xlValue).MinimumScale = 0
    End If
    
End Sub

Sub makeLink()
    ' CSV表の一覧と、そのリンクをつくります
    ' グラフへのリンクは作れないので、何か別の方法で対応しましょう

    ' 開始位置は、よしなに
    ' マクロの開始ボタンとか置くと思ったので、5行目から開始にしました
    Sheets("index").Select
    Range("A5").Select
    For Each s In ThisWorkbook.Worksheets
        If s.Name <> "index" Then
            ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", _
            SubAddress:="'" & s.Name & "'!A1", _
            TextToDisplay:="'" & s.Name & "'"
            Selection.Offset(1, 0).Select
        End If
    Next
End Sub

先ほどのCSVログの作成以上に、
マクロの内容は自力でカスタマイズする必要が出てくると思います。
ログの追加はもちろんですが、
平均値を入れたり時間幅を調整したり凡例を変えたり色々・・・
わたしはVBAはさっぱりなんですが、
それでも「マクロの記録」を駆使すると、このくらいまでは頑張れました。
おなじくVBAが苦手な方は、記録機能を使ってみることをオススメします。


いかがでしょうか・・・
とりあえず、たたき台くらいにはなったでしょうか?
ここから先は、みなさんの健闘を祈ります!


  最終更新のRSS