No.1 抽取RequestBody，ResponseBody，Vin（未Base64加密的情况）
⏭ log中请求和响应未Base64加密的情况，直接使用正则表达式抽取内容
| extend requestBody = extract('\\[RequestBody](.*?)\\[ResponseBody]', 1, message, typeof(string))
| extend responseBody = extract('[[]ResponseBody[]]({.*})', 1, message, typeof(string))
| extend Vin = extractjson('$.Vin', requestBody)
//| extend Vin = parse_json(Request).vin
No.2 抽取RequestBody，ResponseBody，Vin（Base64加密的情况）
⏭ log中请求和响应被Base64加密的情况，先正则表达式抽出加密的内容，再解密
| extend base64_RequestBody = extract('RequestBody[]](.*)\n', 1, message, typeof(string))
| extend RequestBody = coalesce(base64_decode_tostring(base64_RequestBody),gzip_decompress_from_base64_string(base64_RequestBody))
| extend base64_ResponseBody = extract('ResponseBody[]](.*)\n', 1, message, typeof(string))
| extend ResponseBody = coalesce(base64_decode_tostring(base64_ResponseBody),gzip_decompress_from_base64_string(base64_ResponseBody))
| extend Vin = extractjson('$.Vin', RequestBody)
No.3 抽取RequestBody，ResponseBody，Sql（Base64加密的情况2）
⏭ log中请求和响应被Base64加密的情况，先正则表达式抽出加密的内容，再使用多种方式解析Body
let GetRequest = (message: string) {
let base64_body = extract('[[]RequestBody[]](.*)', 1, message);
let body = coalesce(base64_decode_tostring(base64_body)
, gzip_decompress_from_base64_string(base64_body)
, extract('[[]RequestBody[]]:(.*?),[[]ResponseHeader[]]', 1, message)
, extract('[[]RequestBody[]](.*?),[[]ResponseBody[]]', 1, message)
, extract('[[]RequestBody[]](.*?)[[]ResponseBody[]]', 1, message)
, extract('[[]RequestBody[]](.*?),[[]Message[]]', 1, message)
, extract('[[]RequestBody[]](.*?)[[]Message[]]', 1, message)
, extract('[[]RequestBody[]](.*?),[[]ResponseHeaders[]]', 1, message)
, extract('[[]RequestBody[]](.*?)[[]ResponseHeaders[]]', 1, message)
);
iif(message has 'Start' and isempty(body), extract('[Bb]ody:(.*)', 1, message), body);
};
let GetResponse = (message: string) {
let base64_body = extract('[[]ResponseBody[]](.*)', 1, message);
let body = coalesce(base64_decode_tostring(base64_body)
, gzip_decompress_from_base64_string(base64_body)
, extract('[[]ResponseBody[]]:(.*?),[[]Method[]]', 1, message)
, extract('[[]ResponseBody[]](.*?),[[]Message[]]', 1, message)
, extract('[[]ResponseBody[]](.*?)[[]Message[]]', 1, message)
, extract('[[]ResponseBody[]](.*?),[[]ResponseHeaders[]]', 1, message)
, extract('[[]ResponseBody[]](.*?)[[]ResponseHeaders[]]', 1, message)
);
iif(message has 'End' and isempty(body), extract('[Bb]ody:(.*)', 1, message), body);
};
let GetSql = (message: string) {
extract('[[]SQL[]](.*)', 1, message)
};
//提取多个信息
| extend Request = GetRequest(message)
| extend Response = GetResponse(message)
| extend errorID = customDimensions.errorID
| extend resultCode = extractjson('$.resultCode', GetResponse(message))
| extend MethodName = extract('[[]MethodName[]](.*)' , 1, message)
| extend serviceName = customDimensions.serviceName
| extend msg = extract('[[]Message[]](.*)', 1, message)
| extend strSql = GetSql(message)
No.4 TransactionID结合
⏭ 部分语句，根据TransactionID进行结合，查询多个操作的log
let RecieveResultTranId = traces
| where operation_Name == 'POST ReceiveCooperationResult/RecieveCooperationResult'
| where message has _vin
| extend transactionID = tostring(customDimensions.transactionID)
| distinct transactionID;
let CooperationBatchTranId = traces
| where customDimensions.serviceName == 'CooperationBatch'
| where message has _vin
| extend transactionID = tostring(customDimensions.transactionID)
| distinct transactionID;
//.......
| where customDimensions.transactionID in ((union RecieveResultTranId, CooperationBatchTranId | project transactionID))
No.5 サービス遅延状況まとめ
⏭ 完全query，可直接使用，注意更改region和时间
let startDatetime = datetime(2024-08-02T02:04:14Z);
let endDatetime = datetime(2024-08-02T04:04:14Z);
let collectionPeriod = 1h;// 収集するログの終端時刻。endDateTimeよりも多めに取りつつ、負荷がかからない程度の期間を設定すること。
let delayTime_short = 120s;
let delayTime_long = 120m; // 遅延時間
let count_Alert = 0; // ロスト件数閾値
let OtherAppiLog = (union withsource=SourceApp
app('SI-JPP-APPLOG-20PF-APPI02').traces, // XXX: JPP,USO,EUP //転送先にログがあるか確認
app('SI-JPP-APPLOG-20PF-APPI03').traces
| where timestamp between (startDatetime .. (endDatetime + collectionPeriod))
| where customDimensions .errorID == "000C01"
| extend TransactionID = tostring(customDimensions.transactionID)
| summarize max(timestamp) by TransactionID
);
//let NoFileConverter = pack_array(''); // 変換処理がない機能を除外しない
//let NoFileConverter = pack_array('/v1/accident','/v1/ffd','/v1/last','/v1/mcr','/v1/ofn','/v1/oneTimePass/request/','/v1/dre/rDiagResult','/v1/all-diag/errornotification','/v1/dre/HelpNet','/v2/ffd','/v2/last','/v2/mcr','/v2/ofn','/v2/dre/rDiagResult','/v2/all-diag/errornotification','/v2/oneTimePass/request/'); // (20PFMNAO)変換処理がない機能を除外
let NoFileConverter = pack_array('/v1/accident','/v1/ffd','/v1/last','/v1/mcr','/v1/ofn','/v1/oneTimePass/request/','/v1/dre/rDiagResult','/v1/all-diag/errornotification','/v1/dre/HelpNet','/v2/ffd','/v2/last','/v2/ofn','/v2/dre/rDiagResult','/v2/all-diag/errornotification','/v2/oneTimePass/request/'); // (20PFMJO)変換処理がない機能を除外
//let NoFileConverter = pack_array('/v2/ffd','/v2/last','/v2/dre/rDiagResult','/v2/ofn','/v2/mcr','/v2/all-diag/errornotification','/v2/oneTimePass/request/'); // (20PFMME)変換処理がない機能を除外
traces
| where timestamp between (startDatetime .. endDatetime)
| where customDimensions .serviceName == "AsyncReceiver"
| where not( customDimensions .URL has_any (NoFileConverter))
| extend TransactionID = tostring(customDimensions.transactionID)
| extend URL = extract('[0-9]+\\.[0-9]+\\.[0-9]+\\.[0-9]+(:[0-9]+)?(.*)', 2 , tostring(customDimensions.URL), typeof(string))
| summarize min(timestamp), max(timestamp), arg_min(timestamp, URL) by TransactionID
| join kind=leftouter (traces
| where timestamp between (startDatetime .. (endDatetime + collectionPeriod))
| where customDimensions .serviceName == "FileConverter"
| extend TransactionID = tostring(customDimensions.transactionID)
| summarize min(timestamp), max(timestamp) by TransactionID
) on TransactionID
| join kind=leftouter (traces
| where timestamp between (startDatetime .. (endDatetime + collectionPeriod))
| where customDimensions .serviceName == "Transfer"
| extend TransactionID = tostring(customDimensions.transactionID)
| summarize max(timestamp) by TransactionID
) on TransactionID
| join kind=leftouter (OtherAppiLog) on TransactionID
| extend executionTime = iif(isnotempty( min_timestamp1), min_timestamp1 - max_timestamp, totimespan("00:00"))
| summarize ["リクエスト全体数 [件]"] = count(), ["遅延件数(2分以上) [件]"] = countif(executionTime >= delayTime_short), ["遅延件数(120分以上) [件]"] = countif(executionTime >= delayTime_long), ["最大遅延時間"] = max(executionTime), ["失敗数 [件]"] = countif(isempty( TransactionID1) and isempty( TransactionID2) and isempty( TransactionID3))
, ["遅延開始時刻(2分以上)"] = minif( timestamp, executionTime >= delayTime_short), ["遅延終了時刻(2分以上)"] = maxif( timestamp, executionTime >= delayTime_short)
, ["遅延開始時刻(120分以上)"] = minif( timestamp, executionTime >= delayTime_long), ["遅延終了時刻(120分以上)"] = maxif( timestamp, executionTime >= delayTime_long)
, ["ロスト開始時刻"] = minif( timestamp, isempty( TransactionID1) and isempty( TransactionID2) and isempty( TransactionID3)), ["ロスト終了時刻"] = maxif( timestamp, isempty( TransactionID1) and isempty( TransactionID2) and isempty( TransactionID3)) by URL
| sort by URL asc
No.6 抽VinCounts
⏭ 部分语句，通过分别的summarize，再结合起来，抽出Vin数
_Base
| summarize RequestCounts = count() by ServiceRank, Region, ServiceName, RequestResult
| join kind = inner (
_Base
| summarize count() by ServiceRank, Region, ServiceName, RequestResult, Vin
| summarize VinCounts = count() by ServiceRank, Region, ServiceName, RequestResult
)
on ServiceRank, Region, ServiceName, RequestResult