jqコマンドを利用してmongodbのlogからslow queryを抽出して集計するスクリプト

mongodbのslow query集計.txt

# slow query のみ抽出 & 整形
head -500 /var/log/mongodb/mongod.log |\
  jq -c 'select(.msg == "Slow query" and .attr.type == "command") | { collection: (.attr.command.find // .attr.command.aggregate // .attr.command.mapReduce), durationMillis: .attr.durationMillis, count: 1 } | select(.collection != null)' \
  > work-0

# slow query の集計
cat work-0 |\
  jq -s -c 'group_by(.collection) | .[] | reduce .[] as {$collection,$durationMillis,$count} (null; .collection = $collection | .durationMillis += $durationMillis | .count += $count)' \
  > work-1

# 遅い順 Top 5
cat work-1 | jq -s -c 'sort_by(.durationMillis) | reverse | .[0:5] | .[]'

# 件数の多い順 Top 5
cat work-1 | jq -s -c 'sort_by(.count) | reverse | .[0:5] | .[]'