LoginSignup
9
7

More than 1 year has passed since last update.

@smallpalace(Komiya)

kusto(LogAnalitycs)備忘録

Last updated at Posted at 2021-05-06

・これはなにか

kustoクエリ書きたいけど思い出せないときの逆引きとなんか違うことしたときに追記する用の備忘録です

・経緯

すぐ忘れて調べなおすのがいい加減面倒すぎたので楽になりたかった

・暗黙知のようなもの

リソース毎のログは検索できるスコープがそのリソースのみに限られている
サンプルクエリがけっこう使えるので困ったら見たほうがいい
InsightのログはLogAnalyticsのワークスペースに出せてないのは古いやつ
Insight画面のメトリクスグラフの上のほうにログっぽいボタンついてる場合はクエリ確認可能
カーソルがポイントされてる個所のクエリが実行される
改行で空いてるものは処理されない
自分でつくってない長いクエリは短くして途中の出力結果を追っていくと何してるかわかることが多い（シェル芸でパイプを減らして出力を確認するのと同じかんじ）
野生の中の人の記事や公式マニュアルを見たほうが情報量が多い

ベストプラクティス

最初に時間を限定するとか効率よくするための色々が書かれておりcontainsはやめろというくだりが多いです
https://docs.microsoft.com/ja-jp/azure/data-explorer/kusto/query/best-practices

※基本的にはSQLやシェルのパイプ処理なんかと大差なくて結果件数が少なくなる処理を上のほうに先に書くと先に処理されるのでI/O（出し入れするデータ）が減りパフォーマンスが上がる。公式↑にも先に日付で絞れとしつこく書いてある。（JOINするときは元と先のテーブル両方の時間の範囲を絞るといい）

Kusto関係ないがデータ出力関連

ちゃんと設定しないと出ないデータが多いがリソース毎の診断設定で大体はなんとかなる気がする。
https://docs.microsoft.com/ja-jp/azure/azure-monitor/essentials/diagnostic-settings
保存先を保存期間で分けるのがセオリーで、監査用は長く2年で、アラートとかの通常運用用は3か月など。
https://docs.microsoft.com/ja-jp/azure/cloud-adoption-framework/overview

・コメント

//を書くとコメントが書ける。条件切り替えるのにもよくつかう
クエリ書く場所の上にある＋でタブを増やすと結果を出しなおさなくて済む

AzureDiagnostics
| where Category == "SQLSecurityAuditEvents" //カテゴリがセキュリティ監査イベントで絞り込む
| where ResourceType == "MANAGEDINSTANCES" //MIの場合
//| where ResourceType == "SERVERS/DATABASES" //Synapseの場合

・データがどこにあるかわからんが探したい値はわかる場合

search "＜検索したい値＞"

で当てはまるものがあれば出てくる
（部分一致全文検索風というか一本釣りではなく投網風というか）
わりと重い処理なので乱発しないほうがいい

・件数を制限したい

単に結果行数を限定できればいいという場合はlimitをつかう

perf
| limit 10

日付でサマライズしつつ丸める時間の範囲を増やしてデータポイントを減らしたいという場合、bin(TimeGenerated,1d)の1dの部分が一定の期間でデータを丸めるトレンドビンサイズというもので、それを5分なら5m、1時間なら1h、1日なら1dというように変える。すると集計時にとれるデータポイント数を調整できる。

AzureDiagnostics
| summarize count() by bin(TimeGenerated,1d),server_principal_name_s,database_principal_name_s,session_server_principal_name_s,client_ip_s,LogicalServerName_s,database_name_s,action_name_s,succeeded_s,application_name_s

binfunction

・期間を指定したい

クエリにかかなくても上のほうの「時間の範囲」から期間指定は可能。

月末月初を指定してきっちり出すとか現在を軸に4か月前から出すとかに使える。月末にしたい場合はendofmonth関数を使う。

let now = now();
let requireMonth = datetime_add('month', -4, todatetime(now));
let startDateTime = startofmonth(requireMonth);
let endDateTime = todatetime(now);
Tablename
| where TimeGenerated between(startDateTime .. endDateTime)

昨日一日分を出したい場合

let now = now();
let requireDay = datetime_add('Day', -1, todatetime(now));
let startDateTime = startofday(requireDay);
let endDateTime = startDateTime + 24h;
Tablename
| where TimeGenerated between(startDateTime .. endDateTime)

リテラルの場合

| where TimeGenerated between(datetime("2020-06-11 01:00:00.000") .. datetime("2020-06-11 13:05:59.999"))

何日前とか何分前（日はd,時はh,分はm)

| where TimeGenerated > ago(30d)

Timezone調整というか日本時間にするには、以下のサイトによると9時間引く。
https://cloudsteady.jp/post/4275/

let startDateTime = startofday(requireDay) - 9h;
let endDateTime = endofday(requireDay) - 9h;

タイムゾーンをUTCからJSTに変換したい場合、kustoクエリで指定はできないがLogAnalyticsのUIから変更することができ、変更した見た目のデータをCSVダウンロードすることは可能。

日付の一部をフォーマットして取り出す

| extend month = format_datetime(TimeGenerated,'yyyy-MM')

https://docs.microsoft.com/ja-jp/azure/data-explorer/kusto/query/format-datetimefunction

前月の半ばから今月の半ばなどの変則的な場合クエリの日付変数を自動的にするには以下の方法が。

let requireMonth_start = format_datetime(datetime_add('month', -1, todatetime(now())),'yyyy-MM');
let requireMonth_end = format_datetime(datetime_add('month', 0, todatetime(now())),'yyyy-MM');
let startdt = todatetime(strcat(requireMonth_start, "-15T15:00:00"));
let enddt = todatetime(strcat(requireMonth_end, "-15T14:59:59"));
print startdt,enddt

datetime_addで期間を調整、format_datetimeで月の部分だけ出す、strcatで文字列連結、todatetimeで型変換します。

・日付の差分を特定

findや削除ツールの指定日数を特定したい等これ何日前かを計算して出す

print
day = datetime_diff('day',datetime(2022-05-26 00:00),datetime(2020-10-29 00:00))

https://docs.microsoft.com/ja-jp/azure/data-explorer/kusto/query/datetime-difffunction

・データのばらつきを確認

| distinct action_name_s

をつけると一意の値のばらつきがみられる

値のばらつき＝データの種類がどれくらいあるか＝カーディナリティー
（値の種類が多い＝カーディナリティーが高い列にインデックスはるとかあるけどこの場合は短に雑学）
絞り込む条件を考える前段階で特定のカラムにどんなデータが入ってるかをざっくり見たり、
出力されているはずのデータが出てるかどうか確認するのに使うことがある
このとき確認する時間範囲が短すぎると有為なデータが出ないので注意

・カウントしたい

件数をカウントしたい場合は以下のようにする

| summarize count(action_name_s) by action_name_s

byのあとに複数のカラムを書ける。時間のカラムをbinで指定してトレンドデータを出すのもよくある
件数がわかると、特定のログが異様に出てるし使わないしコスト掛かるから出さなくしようなどの判断指標になる

・デフォルト値を指定してカウントしたい

| make-series count() default=0 on TimeGenerated in range(startDateTime,endDateTime,30d) by SubscriptionId
| mvexpand TimeGenerated, count_
| project todatetime(TimeGenerated), SubscriptionId, toint(count_)
| order by TimeGenerated asc

データがないとこを0としたいときにつかいたいことが多い
縦配列を横に直してるかんじ

・特定の値を絞り込みたい

where句のパイプを追加していくと絞り込めます。

AzureDiagnostics
| where Category == "SQLSecurityAuditEvents" //カテゴリがセキュリティ監査イベントで絞り込む
| where ResourceType == "MANAGEDINSTANCES" //MIの場合
//| where ResourceType == "SERVERS/DATABASES" //Synapseの場合

where句でcontainsを使うとlike検索のようなことが可能。

| where action_name_s !contains "SELECT"
| where action_name_s !contains "DATABASE AUTHENTICATION"

※エクスクラメーションマーク！がwhere句の演算子の先頭にきているときは否定になる
in もSQLと同じように使える（複数の値のどれかを含むとか）

否定のinだとどれにも当てはまらないとなるので、以下の例だとErrorが出るという話に。

| where EventLevelName !in ("Success","Information","Warning")

どっちか出したい

| where (RenderedDescription contains "World Wide Web 発行サービス サービスは 停止 状態に移行しました") or RenderedDescription contains "World Wide Web 発行サービス サービスは予期せぬ原因により終了しました"

・出力カラムを限定したい

| project todatetime(TimeGenerated), SubscriptionId, toint(count_)

このカラムだけ出さないとかも可能らしい

| project-away price, quantity, zz*

カラム名を変えたい

| project-rename IntegrationRuntimeAvailableNodeNumber = avg_Count

・正規表現などで加工したデータで列を追加

値を変換したい

| extend env = replace('*******hoge', 'stg',replace('**********fuga', 'prod', SubscriptionId))

文字列の一部を取り出す

| extend sourceIP=extract("^([^ ]*) ", 1, LogEntry)
| extend requestMethodPath = extract("([^ ]* [^ ]*) HTTP/", 1, LogEntry)
| extend returnCode = extract("([0-9]{3}$)",1,extract("([^ ]* [^ ]* HTTP/[^ ]* [^ ]*)", 0, LogEntry))

※LogEntryということは、送信元のログフォーマットをJsonにするだけで正規表現使わないですむ

・URLのパース

AzureDiagnostics
| where ResourceProvider == "MICROSOFT.NETWORK" and Category == "FrontdoorAccessLog"
| where isReceivedFromClient_b == true
| extend ParsedUrl = parseurl(requestUri_s)
| extend Path = tostring(ParsedUrl.Path)

・区切り文字で区切って列追加

AddonAzureBackupJobs 
| where TimeGenerated > ago(7d)
| where JobOperation == "Backup"
| extend BackupItem = todynamic(split(BackupItemUniqueId,";"))
| extend instanceName = tostring(BackupItem[2]),deviceName = tostring(BackupItem[3])

https://docs.microsoft.com/ja-jp/azure/data-explorer/kusto/query/splitfunction
https://docs.microsoft.com/ja-jp/azure/data-explorer/kusto/query/scalar-data-types/dynamic

・空またはNullを置換したい

//文字列の場合
| project shortpath = case(isempty(shortpath), "/", shortpath),TimeGenerated,timeTaken_d
//数値
| project s = case(isnull(i), -99999, i)

https://stackoverflow.com/questions/64288616/kusto-query-how-to-replace-empty-or-blank-valuesrows-under-a-column-with-a-s

・pivotしたい

|evaluate pivot(env,avg(count_env))

値が違うけど他のカラムは同じなので同じ行に出したいみたいなのをカラムを追加して行をまとめてくれるやつ
引数の2つ目は統計値しか受け付けてくれない
月次グラフ集計以外で使ったことがない

・複数のテーブルを見たい

joinを書くのが面倒でデータ量大したことない場合

AlertLog_CL | union AlertLog_customsearch_CL
| where TimeGenerated between(startDateTime .. endDateTime)

とりあえずinner joinで数珠繋ぎみたいなことも可能ではあるが負荷は高くなる
のでトレンドデータ出すとかだと丸める間隔（binsize）を広げてデータポイント減らしたり、
where句で時間のカラムをjoinする元と先のテーブル両方で絞るなどの途中の工程で出るデータ量が減るような工夫が要る
onのあたりでつなげるテーブル同士で同じになるカラムをキーとして指定する

let capacityCounterName = 'cpuLimitNanoCores';
let usageCounterName = 'cpuUsageNanoCores';
let memcapacityCounterName = 'memoryLimitBytes';
let memusageCounterName = 'memoryRssBytes';
//let trendBinSize = 15m;
KubePodInventory
| extend InstanceName = strcat(ClusterId, '/', ContainerName),
         ClusterName = extract("managedClusters/([^ /]*)$",1,ClusterId),
         ContainerName = strcat(tostring(split(ContainerName, '/')[1])),
         PodName=Name
| distinct Computer, InstanceName, ContainerName, Namespace
| join hint.strategy=shuffle (
    Perf
    | where ObjectName == 'K8SContainer'
    | where CounterName == capacityCounterName
    | extend ClusterName = extract("managedClusters/([^ /]*)/",1,InstanceName)
    | summarize LimitValue = max(CounterValue) by Computer, InstanceName,ClusterName
    | project Computer, InstanceName, ClusterName, LimitValue
) on Computer, InstanceName
| join hint.strategy=shuffle (
    Perf
    | where ObjectName == 'K8SContainer'
    | where CounterName == usageCounterName
    | extend ClusterName = extract("managedClusters/([^ /]*)/",1,InstanceName)
    | project Computer, InstanceName, UsageValue = CounterValue,ClusterName
) on Computer, InstanceName
| project Computer, ContainerName, UsagePercent = UsageValue * 100.0 / LimitValue, ClusterName, Namespace
| summarize CpuUsage = round(avg(UsagePercent),2) by ContainerName, ClusterName, Namespace
| project ContainerName, ClusterName, Namespace, CpuUsage
|join  kind = inner(
KubePodInventory
| extend InstanceName = strcat(ClusterId, '/', ContainerName),
         ClusterName = extract("managedClusters/([^ /]*)$",1,ClusterId),
         ContainerName = strcat(tostring(split(ContainerName, '/')[1])),
         PodName=Name
| distinct Computer, InstanceName, ContainerName, Namespace
| join hint.strategy=shuffle (
    Perf
    | where ObjectName == 'K8SContainer'
    | where CounterName == memcapacityCounterName
    | extend ClusterName = extract("managedClusters/([^ /]*)/",1,InstanceName)
    | summarize LimitValue = max(CounterValue) by Computer, InstanceName,ClusterName
    | project Computer, InstanceName, ClusterName, LimitValue
) on Computer, InstanceName
| join hint.strategy=shuffle (
    Perf
    | where ObjectName == 'K8SContainer'
    | where CounterName == memusageCounterName
    | extend ClusterName = extract("managedClusters/([^ /]*)/",1,InstanceName)
    | project Computer, InstanceName, UsageValue = CounterValue,ClusterName
) on Computer, InstanceName
| project Computer, ContainerName, UsagePercent = UsageValue * 100.0 / LimitValue, ClusterName, Namespace
| summarize MemoryUsage = round(avg(UsagePercent),2) by ContainerName, ClusterName, Namespace
)on ContainerName, ClusterName
| project ContainerName, ClusterName, Namespace, MemoryUsage,CpuUsage;

・チャート出したい

棒グラフ

| render columnchart with (kind=unstacked)

結果をグラフで見たいときに使う
データラベルとかは出せない。Workbookならいけると思う

・階層が下のデータ出す

Jsonできてて奥まったとこにある場合の皮むきが要るケースはtodynamicでカラム切り出してドットでつないでstringに変換して出せる
（中身のカラム名に記号が多いと結構難しい）

InsightsMetrics
| where Origin == 'container.azm.ms/telegraf'
| where Namespace == 'disk' or Namespace =~ 'container.azm.ms/disk'
| where Name == 'used_percent'
| extend Tags = todynamic(Tags)
| extend HostName = tostring(Tags.hostName), Device = strcat('/dev/', tostring(Tags.device)), PATH = tostring(Tags.path)
| extend NodeDisk = strcat(HostName, Device)
| distinct PATH

並べ替える

| order by TimeGenerated asc

ascが昇順でdescが降順など普通のSQLとまるで同じ

iff関数で分岐処理して列を新しくつくる

もしCounterNameが%FreeSpaceだった場合に100から引いた値に変える列を新しく作る。けどそうじゃない場合の値は元のままで出力する、といったことができる。

Perf 
//|distinct CounterName
| project TimeGenerated, Computer, CounterName, CounterValue ,InstanceName
| where (CounterName == "% Committed Bytes In Use"
   or CounterName == "% Free Space" and InstanceName == "_Total")
   or CounterName == "% Processor Time" and InstanceName == "_Total"
| extend Usages = iff(CounterName == "% Free Space", todouble(int(100) - round(CounterValue,3)), todouble(CounterValue))
| summarize avg(Usages) by Computer, CounterName, bin(TimeGenerated, 5m)
| order by TimeGenerated,CounterName,Computer asc

https://blog.csdn.net/dpengwang/article/details/89327752
https://docs.microsoft.com/ja-jp/azure/data-explorer/kusto/query/ifffunction

日毎、時間毎、曜日ごとなどのアクセス傾向を出す

  • Insightから。１日のうち時間別に平均を取った、１時間あたりの合計アクセス数推移をロール毎に出す例。
let timeGrain=1h;
requests
| where client_Type != "Browser"
| where (isnotempty(cloud_RoleName))// すべての要求をカウントする
| where cloud_RoleName !contains 'slot'
| extend time1 = format_datetime((timestamp+9h),'HH')
| summarize count_=sum(itemCount) by bin(timestamp, timeGrain),time1,cloud_RoleName
| summarize avg(count_) by time1,cloud_RoleName
| extend cloud_role_name1 = replace('hoge-app-','rc-',replace('piyo-app-','bs_',replace('fuga-app-','sv_',cloud_RoleName)))
| project-away cloud_RoleName
| order by time1 asc
//| render timechart
  • Insightから。日合計の曜日とCloudRoleName毎のアクセス数推移。
let timeGrain=1d;
let Saturday= time(6.00:00:00); //条件定義には使えるが結果の表示には利用できない変数
let Sunday= time(0.00:00:00);
let Monday= time(1.00:00:00);
let Tuesday= time(2.00:00:00);
let Wednesday= time(3.00:00:00);
let Thursday= time(4.00:00:00);
let Friday= time(5.00:00:00);
requests
| where client_Type != "Browser"
| where (isnotempty(cloud_RoleName))// すべての要求をカウントする
| extend cloud_role_name1 = replace('hoge-app-','rc-',replace('piyo-app-','bs_',replace('fuga-app-','sv_',cloud_RoleName)))
| extend week1 = dayofweek(timestamp+9h)
| summarize count_=sum(itemCount) by bin(timestamp, timeGrain),week1,cloud_role_name1
| summarize avg(count_) by week1,cloud_role_name1
| order by week1 asc
| extend week0 = replace('00:00:00','Sunday',replace('5.00:00:00','Friday',replace('4.00:00:00','Thursday',replace('3.00:00:00','Wednesday',replace('2.00:00:00','Tuesday',replace('1.00:00:00','Monday',replace('6.00:00:00','Saturday',tostring(week1))))))))
|project-away week1
//| render timechart
  • Insightから。日合計のCloudRoleName毎のアクセス数推移(過去3か月)
let now = now();
let requireMonth = datetime_add('month', -4, todatetime(now));
let endMonth = datetime_add('month', -1, todatetime(now));
let startDateTime = startofmonth(requireMonth);
let endDateTime = endofmonth(endMonth);
let timeGrain1=5m;
let timeGrain2=1d;
requests
| where timestamp between(startDateTime .. endDateTime)
| where client_Type != "Browser"
| where (isnotempty(cloud_RoleName))// すべての要求をカウントする
| where cloud_RoleName !contains 'slot'
| extend cloud_role_name1 = replace('hoge-app-','rc-',replace('piyo-app-','bs_',replace('fuga-app-','sv_',cloud_RoleName)))
//|extend timestamp1 = (timestamp+9h)
| summarize count_=sum(itemCount) by bin(timestamp, timeGrain2),cloud_role_name1
//| summarize avg(count_) by bin(timestamp, timeGrain2),cloud_RoleName
//| project-away cloud_RoleName
| order by timestamp asc
//| render timechart

PrivateIPを判定する関数をつかう

配列をin句につかうクエリ

https://cloudblogs.microsoft.com/industry-blog/en-gb/cross-industry/2017/10/02/azure-log-analytics-dynamic-arrays/
https://docs.microsoft.com/ja-jp/azure/data-explorer/kusto/query/scalar-data-types/dynamic
配列を自作する場合は以下のようになる。

let mytesttxt = dynamic ([‘DC01.fabrikamltd.co.uk’, ‘DC02.fabrikamltd.co.uk’, ‘DC03.fabrikamltd.co.uk’]);

Heartbeat
| where Computer in (mytesttxt)
| project Computer

検索結果のリスト配列変数から検索したい場合、
例えばコンテナのサービスの名前からコンテナIDを検索して配列に入れた結果を単純に取得するには以下のようにする。
joinだとデータポイント多すぎますというような重い処理になってしまう。joinしなくていいので結果が早く帰ってくる。
https://docs.microsoft.com/ja-jp/azure/data-explorer/kusto/query/makelist-aggfunction

//配列をつくってin句でつかう
let Cids = KubePodInventory 
  | where ServiceName == "＜サービス名＞"
  | distinct ContainerID 
  | summarize Cids = make_list(ContainerID);
//Cids
ContainerLog 
| where ContainerID in (Cids)

in句だと、それぞれの文字列は完全一致だけども、以下リンク先に例があるがhas_anyだと配列で複数のlike検索的なフィルタができる模様。
selectの結果というよりはdatatableで配列を雑に自分で作るケースで使うとよさそう。
https://docs.microsoft.com/ja-jp/azure/data-explorer/kusto/query/has-anyoperator
https://docs.microsoft.com/ja-jp/azure/data-explorer/kusto/query/datatableoperator?pivots=azuredataexplorer

おきにいりとWorkbook

良く実行するクエリを保存しておいて他の人に共有することが可能
https://docs.microsoft.com/ja-jp/azure/azure-monitor/logs/queries#favorites

Wookbookだと違うとこに出てるログから横断的にグラフ作って見やすくまとめたり書式弄り回すなどが可能
https://docs.microsoft.com/ja-jp/azure/azure-monitor/visualize/workbooks-overview

データソースが色々選べるので、ResouceGraphでアラートとってきて一覧にしたりが可能
https://docs.microsoft.com/ja-jp/azure/azure-monitor/vm/monitor-virtual-machine-alerts#target-resource-and-impacted-resource

alertsmanagementresources| where type =~ 'microsoft.alertsmanagement/alerts'
| where todatetime(properties.essentials.startDateTime) {timeRange}  
| extend SignalType=tostring(properties.essentials.signalType)
//|distinct SignalType
//| extend ResultCount = tostring(properties.context.ResultCount)
| where SignalType == "Metric"
| extend dimention0 = properties.context.context.condition.allOf[0]
| extend metricValue = toint(dimention0.metricValue)
| extend threshold = tostring(dimention0.threshold)
| extend metricName = tostring(dimention0.metricName)
| extend TargetResource = tostring(properties.essentials.targetResource)
| summarize count() by Alert=name , threshold, metricName,TargetResource
| join (alertsmanagementresources
        | where todatetime(properties.essentials.startDateTime) {timeRange}  
        | where type =~ 'microsoft.alertsmanagement/alerts'
        | extend SignalType=tostring(properties.essentials.signalType)
        | where SignalType == "Metric"
        | extend dimention0 = properties.context.context.condition.allOf[0]
        | extend metricValue = todouble(dimention0.metricValue)
        | summarize avg(metricValue) by Alert=name)
    on Alert
    | join (alertsmanagementresources
            | where todatetime(properties.essentials.startDateTime) {timeRange}  
            | where type =~ 'microsoft.alertsmanagement/alerts'
            | extend SignalType=tostring(properties.essentials.signalType)
            | where SignalType == "Metric"
            | extend dimention0 = properties.context.context.condition.allOf[0]
            | extend metricValue = todouble(dimention0.metricValue)
            | summarize max(metricValue) by Alert=name)
        on Alert
| project-rename  counts = count_, average_metricValue = avg_metricValue
| project Alert, counts, metricName,average_metricValue,max_metricValue,threshold,TargetResource
| order by counts desc

↑これは既存のアラートブックを改造すると時間パラメータをパクれる、アラート毎の一覧に平均メトリック値などを出すクエリ。

・参考

文字列演算子　https://docs.microsoft.com/ja-jp/azure/data-explorer/kusto/query/datatypes-string-operators
数値演算子　https://docs.microsoft.com/ja-jp/azure/data-explorer/kusto/query/numoperators
型変換はスカラー関数のマニュアルに載っており整数への変換はtoint(),文字列への変換はtostring()など　
https://docs.microsoft.com/ja-jp/azure/data-explorer/kusto/query/scalarfunctions
正規表現　https://docs.microsoft.com/ja-jp/azure/data-explorer/kusto/query/re2

そのた個人が書いてる参考
https://qiita.com/TsuyoshiUshio@github/items/08a1ee8ca3e1c87ee2dc
https://blog.shibayan.jp/entry/20190414/1555229387

9
7
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
9
7