監査や月次の報告で紙一枚ぺらでレポートが要るみたいなことでタイトルどおりテスト環境で試してみた備忘録です。
ちょっとAzureMonitorのダッシュボードをpdf出力しようとしてもまともにできないので、MS製品的な親和性からPowerBIにとりこんでpdfにするのが手っ取り早かったわけです。
（書式のためだけにPBIつかうのどうなのというのはさておき）
はじめてのKQL（Kusto）でした。
####・AzureMonitorからLogAnaliticsにデータを投げ込む
AzureMonitorはデフォだと7日らしいのでデータ保持するにはLogAnaliticsにワークスペース作って入れます。
お値段、検証時には無料枠で試すことができそう（継続的に保持したいなら有料枠にする）
https://azure.microsoft.com/ja-jp/pricing/details/monitor/
設定については、いくつかの場所から可能で、MonitorかVMなどリソース毎の診断設定かLogAnaliticsのAzureリソースから検索して出てきたやつを選んで作ったLogAnaliticsのワークスペースに接続していくといいようです。Monitorの診断設定からだとリージョン違うとワークスペース出てこなかったりするけどVMやLogAnaliticsからはそんなことはなかったですね。
Monitorの診断設定で出てくるEventHubはAWSでいうとKinesisのようなものらしくリアルタイムなIoTや多数のデータを扱うDWHにつなぐ時用っぽいのであんまり要らないらしい。
https://qiita.com/okuda_h/items/2047bf884fd6a7fcfa9d
https://azure.microsoft.com/ja-jp/pricing/details/event-hubs/
ストレージに置かれたログを取り込むやつについては独自ログだとLogicAppで成型するパイプライン組むなど要るのかも。
https://docs.microsoft.com/ja-jp/azure/azure-monitor/platform/create-pipeline-datacollector-api
https://docs.microsoft.com/ja-jp/azure/virtual-machines/extensions/diagnostics-linux#metrics-supported-by-the-builtin-provider
https://docs.microsoft.com/ja-jp/azure/app-service/troubleshoot-diagnostic-logs
https://docs.microsoft.com/ja-jp/azure/azure-monitor/platform/data-collector-api
https://blog.engineer-memo.com/2018/02/25/log-analytics-%E3%81%AE-http-%E3%83%87%E3%83%BC%E3%82%BF-%E3%82%B3%E3%83%AC%E3%82%AF%E3%82%BF%E3%83%BC-api-%E3%82%92%E4%BD%BF%E7%94%A8%E3%81%97%E3%81%9F-sql-database-%E3%81%AE%E6%83%85%E5%A0%B1/
あと、ストレージ自体の使用量などのメトリクスはMonitorのダッシュボードでは見られるけどデータとってきてPowerbiに入れようという話だとAPIでとってくる話になるので自動的にどうにかするにはサービスプリンシパルが要りそうでした。（armclientでとってきてcsvにしてストレージに投げ込んでwebURLでpbiにとりこむかんじで）スクリプト作ったけど対話じゃアレだなと思ったりしました。
https://docs.microsoft.com/ja-jp/azure/storage/common/storage-metrics-in-azure-monitor
今回はInsight（APM(アプリケーションパフォーマンス監視)にあたるやつ）はつかわず。
使う場合の参考↓。
https://docs.microsoft.com/ja-jp/azure/architecture/reference-architectures/app-service-web-app/app-monitoring
VMの拡張モニタリングなど
https://docs.microsoft.com/ja-jp/azure/virtual-machines/extensions/diagnostics-linux#metrics-supported-by-the-builtin-provider
結論としてはLogAnaliticsの画面＞Azureリソース＞リソースグループ選ぶ＞出てきたリソースを必要なものから順にコネクト設定する
というのが簡単そうにみえました。たぶん。リソースによって違ったりはするかもしれないのでマニュアルあればみたほうがよさそう。
azureリソースごとのLogAnaliticsのワークスペースにつなげる手段のリスト↓
https://docs.microsoft.com/ja-jp/azure/azure-monitor/platform/collect-azure-metrics-logs
WebAppsに関してはLogAnaliticsのワークスペースのAzureリソースから探して接続できるけどそのときにAllMetrics以外のアプリのログっぽいのをチェックすると接続失敗しました。特殊なメトリクスとりたい場合はInsightつかうなどの手段もあるようです。
このあたりみるとテンプレが検索できるみたいですね。
https://www.slideshare.net/tetsuyaodashima/log-analytics-94570686
https://azure.microsoft.com/ja-jp/resources/templates/?term=OMS
####・Kusto関連
LogAnaliticsからクエリ打つやつ（KQLというらしい）
https://docs.microsoft.com/ja-jp/azure/azure-monitor/learn/tutorial-viewdata
https://docs.microsoft.com/ja-jp/sharepoint/dev/general-development/keyword-query-language-kql-syntax-reference
LogAnaliticsのワークスペースの左のメニューの「ログ」を選ぶとログから検索するクエリエディタっぽい画面が出てきます。
右上のタブにサンプルクエリが出てるので見てみるといろいろあります。
クエリエクスプローラというのでみたソリューションクエリなども参考になります。
クエリはお気に入り登録も可能。
・ログクエリのマニュアル
https://docs.microsoft.com/ja-jp/azure/azure-monitor/log-query/log-query-overview
https://docs.microsoft.com/ja-jp/azure/azure-monitor/log-query/query-language
・SQLとMonitorのクエリの比較チートシート
https://docs.microsoft.com/ja-jp/azure/azure-monitor/log-query/sql-cheatsheet
・サンプル
https://github.com/MicrosoftDocs/LogAnalyticsExamples/blob/master/log-analytics.md
・Kustoクエリ言語のマニュアル（サンプルにはないけどSQLでできるこれはできんのか？を調べる場合に）
https://docs.microsoft.com/en-us/azure/kusto/query/
https://qiita.com/YoshiakiOi/items/9f649c9b9c97dc6e8e3d
結構クエリ入力中に勝手にサジェストされたりする。
時間の範囲は指定しなければプルダウンからも選べるがクエリで指定も可能。
VMのデータならPerfと打つだけで一通りとってこれるようです。
Azureリソースのメトリクスは、AzureMetricsというテーブルのMetricName指定すると取れる模様。今回はタイトルどおりWebAppsとSQLServerのデータを。
これ↓ピボットしてる例が載っててわかりやすかったりなど。(PBI側でやったほうがいんじゃね説も）
https://www.systemcenterautomation.com/2018/09/azure-log-analytics-summarize-operator/
時間長くするとランキング処理は重い様子でした。
| where CounterName == "% Processor Time" and InstanceName == "_Total"
| summarize arg_max(TimeGenerated, *) by Computer
| top 10 by "% Processor Time"
| order by "CounterValue" desc
ソリューションクエリから
すべてのコンピュータ一覧（LogAnaliticsにはいってるインスタンスがでてくるだけ）
search not(ObjectName == "Advisor Metrics" or ObjectName == "ManagedSpace") | summarize AggregatedValue = max(TimeGenerated) by Computer | limit 500000 | sort by Computer asc
// Oql: NOT(ObjectName="Advisor Metrics" OR ObjectName=ManagedSpace) | measure max(TimeGenerated) by Computer | top 500000 | Sort Computer // Args: {OQ: True; WorkspaceId: 00000000-0000-0000-0000-000000000000} // Settings: {PTT: True; SortI: True; SortF: True} // Version: 0.1.122
取れるメトリクスのカテゴリタイプ一覧
（冒頭に指定する指標のカテゴリというか テーブルにあたるタイプがでてくる
search * | extend Type = $table | summarize AggregatedValue = count() by Type
// Oql: * | Measure count() by Type // Args: {OQ: True; WorkspaceId: 00000000-0000-0000-0000-000000000000} // Settings: {PTT: True; SortI: True; SortF: True} // Version: 0.1.122
・VMのパフォーマンスデータとるクエリ例
//CPU,memory,io使用率
AzureMetrics
| where Resource contains "KOMI"
| where TimeGenerated > ago(30d)
| where ((MetricName == "cpu_percent") or MetricName == "memory_percent" ) or MetricName == "io_consumption_percent"
| project TimeGenerated, Resource, MetricName, Average
| summarize avg(Average) by MetricName, bin(TimeGenerated, 5m)
| evaluate pivot(MetricName, avg(avg_Average))
//network
AzureMetrics
| where Resource contains "KOMI"
| where TimeGenerated > ago(30d)
| where (MetricName == "network_bytes_egress") or MetricName == "network_bytes_ingress"
| project TimeGenerated, Resource, MetricName, Average
| summarize avg(Average) by MetricName, bin(TimeGenerated, 5m)
| evaluate pivot(MetricName, avg(avg_Average))
//接続数
AzureMetrics
| where Resource contains "KOMI"
| where TimeGenerated > ago(30d)
| where (MetricName == "active_connections") or MetricName == "connections_failed"
| project TimeGenerated, Resource, MetricName, Count
| summarize avg(Count) by MetricName, bin(TimeGenerated, 5m)
| evaluate pivot(MetricName, avg(avg_Count))
//ストレージ使用率
AzureMetrics
| where Resource contains "KOMI"
| where TimeGenerated > ago(30d)
| where (MetricName == "storage_percent") or MetricName == "serverlog_storage_percent"
| project TimeGenerated, Resource, MetricName, Average
| summarize avg(Average) by MetricName, bin(TimeGenerated, 5m)
| evaluate pivot(MetricName, avg(avg_Average))
binは範囲を時間単位に丸めて出すかんじで、すでに範囲が指定されててその範囲で一つのデータでいいなら指定不要そうですね。時系列グラフにしたいので日毎や時間ごとに出す場合によさそう。
https://docs.microsoft.com/en-us/azure/kusto/query/binfunction
・期間と月末月初
https://docs.microsoft.com/ja-jp/azure/azure-monitor/log-query/datetime-operations
期間をだすやつ
| where TimeGenerated between(datetime("2018-06-30 22:46:42") .. datetime("2018-07-01 00:57:27"))
月末
range offset from -1 to 1 step 1
| project monthEnd = endofmonth(datetime(2017-01-01 10:10:17), offset)
月初
range offset from -1 to 1 step 1
| project monthStart = startofmonth(datetime(2017-01-01 10:10:17), offset)
・WebApp
let requireMonth = todatetime(now());
let startDate = startofmonth(requireMonth);
let endDate = endofmonth(requireMonth);
AzureMetrics
| where ResourceProvider == "MICROSOFT.WEB"
| where TimeGenerated between(startDate .. endDate)
| where ((((((MetricName == "CpuTime") or MetricName == "MemoryWorkingSet")
or MetricName == "BytesSent") or MetricName == "BytesReceived")
or MetricName == "Requests") or MetricName contains "5xx") or MetricName contains "4xx"
| project TimeGenerated, Resource, MetricName, Average, Minimum, Maximum, Total
| summarize Avg = avg(Average)
, Min = min(Minimum)
, Max = max(Maximum)
, Sum = sum(Total) by Resource, MetricName
| order by Resource, MetricName asc
now()になってるとこ、このままだと今月が出る(startofmonth/endofmonthの範囲がでる)が
前月を出したい場合now()をago(30d)などおおざっぱに前月の日付になるようにするとひと月前を丸っと出せる。日付が属する月をもとにしたその月の開始日と終了日をstartofmonth/endofmonth関数で求めてその範囲指定で検索しているため。（見ればわかるかもですが一応）
BIツール側のクエリエディタの詳細エディタで取り直したりなども可能。
https://docs.microsoft.com/ja-jp/azure/azure-monitor/log-query/datetime-operations
・SQLサーバ
//MetricName一覧をとってくる
AzureMetrics
| where ResourceProvider == "MICROSOFT.SQL"
| summarize by MetricName
//各種使用率
let requireMonth = todatetime(now());
let startDate = startofmonth(requireMonth);
let endDate = endofmonth(requireMonth);
AzureMetrics
| where ResourceProvider == "MICROSOFT.SQL"
| where TimeGenerated between(startDate .. endDate)
| where MetricName in ("cpu_percent","dtu_consumption_percent","sessions_percent","workers_percent","xtp_storage_percent","log_write_percent","physical_data_read_percent")
| project TimeGenerated, Resource, ResourceGroup, MetricName, Maximum
| summarize avg(Maximum ) by MetricName, bin(TimeGenerated, 5m), Resource, ResourceGroup
| evaluate pivot(MetricName, avg(avg_Maximum ))
//接続情報
let requireMonth = todatetime(now());
let startDate = startofmonth(requireMonth);
let endDate = endofmonth(requireMonth);
AzureMetrics
| where ResourceProvider == "MICROSOFT.SQL"
| where TimeGenerated between(startDate .. endDate)
| where MetricName contains "connection"
| project TimeGenerated, Resource, ResourceGroup, MetricName, Maximum
| summarize avg(Maximum ) by MetricName, bin(TimeGenerated, 5m), Resource, ResourceGroup
| evaluate pivot(MetricName, avg(avg_Maximum ))
以下URLに、過去 1 時間の平均が 95% を超える Azure SQL Database ストレージを探すクエリがあった。範囲を一か月にしても対象が見つからないとレポートはできなさそうではある。
https://docs.microsoft.com/ja-jp/azure/azure-monitor/insights/azure-sql#azure-sql-database-storage-in-average-above-95-in-the-last-1-hr
このクエリ実行後の状態でPOWERBIへの変換方法
https://docs.microsoft.com/ja-jp/azure/azure-monitor/platform/powerbi
画面はこんな
実行ボタン押さなくてもKusto系全般)は、Shift + Enter でクエリが実行できるようです。
####・LogAnaliticsのKQLをPowerBIのMクエリにして取り込んだあと
軽く備忘録しときます。
視覚化の例↓など。用途別のソリューションとできることなどが載ってる。
https://docs.microsoft.com/ja-jp/azure/azure-monitor/visualizations
pdf出力の件
https://docs.microsoft.com/ja-jp/power-bi/desktop-export-to-pdf
ちなみにMicrosoftEdgeを開いてる状態でないとpdf出力うまくいかずなにも起こらなかったです。
MicrosoftEdgeで出力されたpdfを名前をつけて保存するとよいようです。
視覚化の書式のページサイズから、カスタム、幅826・高さ1169ピクセル程度に変更すると自動調整でA4縦におさまるかんじでした。
とりあえずどういう絵面にするのか軽くスケッチしてから寄せてくのがよいらしい。
https://docs.microsoft.com/ja-jp/power-bi/visuals/power-bi-visualization-best-practices
クエリ実行したあとのデータ出てる状態でエクスポートでPowerBIを選ぶとMクエリというのに変換されテキストに出力される。
それをPowerBI側でデータ取り込み、空のクエリ選択で上の真ん中の詳細エディタクリックして画面にテキストを丸ごと貼って取り込むとテーブルが作られる。
そのあと閉じて適用という左上のボタンを押すととりこみが完了するがその前にNullは平均に変換など必要。
取り込んだ直後のクエリエディタの画面ですること
右のあたりのプロパティの名前でテーブル名をつける（初期はクエリ１と書いてある）
型の変更、数字からパーセンテージ
エラーの置換、CTL+Aで全選択してから変換タブで値の置換でエラーの置換後の値を0や平均などにする。
で、適用または閉じて適用
クエリエディタはあとからでも開いて編集可能で、値の置換も可能。列名の変更はエディタ開けなくても可能。
https://lab.pasona.co.jp/687/
まずデータを扱えるように日付のテーブルを作って関連付ける→おそらくドリルダウンとスライサーなどしない場合は不要(日付のカラムがあれば裏で良いようにしてくれるらしく複雑なことしたい場合は意図どおりになるように日付のテーブルを作って調整が要る模様）
https://qiita.com/y-komuro/items/de3e2cb3dea129260079
一緒くたにとってきた場合CPUとメモリをテーブル複製して分ける→ピボットを事前にLogAnalitics側でやるなら不要のようでした。
時系列データとの付き合い方というかカレンダーテーブルかなにか加えたりなどが要る例
https://qiita.com/h_kobayashi1125/items/0db6efe91db7df821429
https://blogs.technet.microsoft.com/mssvrpmj/2018/10/19/dax2/
DAXという関数で色々できるらしい（エクセル関数のように列増やして計算式入れるなど）
https://docs.microsoft.com/ja-jp/power-bi/desktop-quickstart-learn-dax-basics
https://docs.microsoft.com/ja-jp/dax/dax-function-reference
列を増やすのか集計したメジャーを増やすのか
https://qiita.com/h_kobayashi1125/items/a7533832183ac086b12d
単にテーブル内の集計月だけほしいなあと思ったときにはメジャーがいいっぽいですね。
https://blogs.msdn.microsoft.com/dataplatjp/2016/07/25/dax3/
https://docs.microsoft.com/en-us/power-bi/desktop-conditional-table-formatting
メジャーを以下の様に追加して複数カードのフィールドにいれる。
（列追加ではなく集計？したメジャーを追加）
表の月(MM) = FORMAT(FIRSTDATE('webapp-1m-summary'[TimeGenerated]),"YYYY/MM")
複数帰らないようにMAXとかを挟むなどするとよさそう（FIRSTDATEだとエラー出るかも）
YYYYMM = FORMAT(EDATE(MAX('webapp-1m-summary'[TimeGenerated]),-1),"YYYY/MM")
初心者向け情報と自習書をダウンロードなど
https://www.microsoft.com/ja-jp/cloud-platform/Solutions-BI-Data-Analytics.aspx
https://docs.microsoft.com/ja-jp/power-bi/desktop-getting-started
データの関連性が要りそうならテーブル同士のリレーション設定する（日付やidなど一意になるデータをつかう）
https://docs.microsoft.com/ja-jp/power-bi/desktop-relationship-view
デザイン画面で好きなグラフ形式を選んで軸とデータを関連づけてく。
時系列データならx軸に日付でy軸に実データチェックいれたりドラッグアンドドロップなど。
https://docs.microsoft.com/ja-jp/power-bi/visuals/power-bi-visualization-basic-area-chart
デザインやらフィルタの効く範囲が3段階くらい選べるっぽいですが今回あんまり気にしてなかった。
デザインの調整は視覚化のローラーのアイコンで切り替えてポチポチしたりすると大体なんかできてくようでした。
https://docs.microsoft.com/ja-jp/power-bi/visuals/power-bi-visualization-combo-chart
データが作ったばかりの適当な環境からなので微妙ですがいちおう以下のようなことに。ちょっと色数多すぎるのは気ままにテストしてた関係で実際には差し色の色相にまとめとモノトーンくらいがよさそう。レポートテーマとかつかうといいのかな。
https://docs.microsoft.com/ja-jp/power-bi/desktop-report-themes
カスタムビジュアル↓
https://docs.microsoft.com/ja-jp/power-bi/power-bi-custom-visuals
####ほかのクラウドのBIツール
検索してたら似たのがあるのを見たのでなんとなく貼っておきます。
・AWS quicksight
https://aws.amazon.com/jp/quicksight/
https://www.slideshare.net/AmazonWebServicesJapan/aws-black-belt-online-seminar-2016-amazon-quicksight
https://akiyoko.hatenablog.jp/entry/2017/03/15/090200
https://techblog.nhn-techorus.com/archives/6202
・GCP data-studio
https://marketingplatform.google.com/about/data-studio/
https://cloudplatform-jp.googleblog.com/2017/06/how-to-build-a-bi-dashboard-using-google-data-studio-and-bigquery.html
####感想
SQLは大昔とったオラクルブロンズレベル(にISP時代の登録調査オペで若干毛が生えた程度とmysqlイベント関連での耳年魔的な関係)でKustoも触りだけでよくわかっていないですが、例やらマニュアルが豊富でどうにかこうにか。まともに結果返ってくると嬉しいなと思いました。
Powerbiはハンズオンうけてから長い間放置してた宿題を大したことはできてないけど一応提出したような感覚。
以上