Example complex KQL

complex.kql

AppRequests
| project TimeGenerated, Id, invocationId=Properties['InvocationId']
| where TimeGenerated > ago(30d)
//| where cloud_RoleName =~ 'func01spz5txz3vpspbmcg' and operation_Name =~ 'stream_analytics_output_handler'
| order by TimeGenerated desc
| take 10


AppRequests
| project TimeGenerated, Id, OperationName, Success, ResultCode, DurationMs, OperationId, AppRoleName, Properties
| where TimeGenerated > ago(30min)
| order by TimeGenerated desc
| take 10

AppTraces
| project TimeGenerated, Message, AppRoleName, Properties, shortMessage=substring(Message,0,20) //, isHeartBeat=Properties['is_heartbeat'],
| where AppRoleName == "__main__.py"
| order by TimeGenerated desc 
| take 20

AppTraces
|where Message contains "heartbeat:success"
| take 50
| order by TimeGenerated desc

---

let recentHearbeats = StorageBlobLogs
| where OperationName == "PutBlob" and Uri contains "/heartbeat/"
| extend CorrelationID = extract("/heartbeat/(.*/.*)",1,Uri)
| extend Area = extract("(.*)/",1,CorrelationID)
| join kind=leftouter (
   // get functions success events
   AppTraces 
    | extend CorrelationID = tostring(Properties["correlation_id"])
    | where CorrelationID != '' and Message contains "heartbeat:success"
    | project SuccessTimeGenerated = TimeGenerated, CorrelationID
    // if we get multiple confirmations for the same file, use the first
    | summarize SuccessTimeGenerated = min(SuccessTimeGenerated) by CorrelationID
    ) on CorrelationID
| project AccountName, Area, BlobPutTime = TimeGenerated, SuccessTimeGenerated, Success = not(isnull(SuccessTimeGenerated)), CorrelationID
| extend ProcessingTime = SuccessTimeGenerated - BlobPutTime
// don't alert on a blob that just landed -- we may still be processing it
| where BlobPutTime < ago(1m) or Success == true
| order by BlobPutTime;

recentHearbeats
| summarize Successes = countif(Success), Failures = countif(not(Success)), LastFailureTime = maxif(BlobPutTime,not(Success)), Last10MinFailures = countif(not(Success) and BlobPutTime > ago(10m)) by AccountName, Area
| extend LastFailureAgo = iif(isnull(LastFailureTime),int(null),datetime_diff('Minute',now(),LastFailureTime))


--

let recentHearbeats = StorageBlobLogs
| where OperationName == "PutBlob" and Uri contains "/heartbeat/"
| extend CorrelationID = extract("/heartbeat/(.*/.*)",1,Uri)
| extend ResourceGroup = tostring(split(_ResourceId, '/')[4])
| extend Area = extract("(.*)/",1,CorrelationID)
| join kind=leftouter (
// get functions success events
AppTraces
| extend CorrelationID = tostring(Properties["correlation_id"])
| where CorrelationID != '' and Message contains "heartbeat:success"
| extend ResourceGroup = tostring(split(_ResourceId, '/')[4])
| project SuccessTimeGenerated = TimeGenerated, CorrelationID, ResourceGroup
// if we get multiple confirmations for the same file, use the first
| summarize SuccessTimeGenerated = min(SuccessTimeGenerated) by ResourceGroup, CorrelationID
) on ResourceGroup, CorrelationID
| project AccountName, Area, BlobPutTime = TimeGenerated, SuccessTimeGenerated, Success = not(isnull(SuccessTimeGenerated)), CorrelationID, ResourceGroup
| extend ProcessingTime = SuccessTimeGenerated - BlobPutTime
// don't alert on a blob that just landed -- we may still be processing it
| where BlobPutTime < ago(1m) or Success == true
| order by BlobPutTime;
recentHearbeats




---
let recentHearbeats = StorageBlobLogs
    | where OperationName == "PutBlob" and Uri has "/heartbeat/"
    | extend CorrelationID = extract("/heartbeat/(.*/.*)", 1, Uri)
    | extend ResourceGroup = tostring(split(_ResourceId, '/')[4])
    | extend Area = extract("(.*)/", 1, CorrelationID)
    | join kind=leftouter (
        // get functions success events
        AppTraces
        | extend CorrelationID = tostring(Properties["correlation_id"])
        | where CorrelationID != '' and Message has "heartbeat:success"
        | extend ResourceGroup = tostring(split(_ResourceId, '/')[4])
        | project SuccessTimeGenerated = TimeGenerated, CorrelationID, ResourceGroup
        // if we get multiple confirmations for the same file, use the first
        | summarize SuccessTimeGenerated = min(SuccessTimeGenerated) by ResourceGroup, CorrelationID
        )
        on ResourceGroup, CorrelationID
    | project AccountName, Area, BlobPutTime = TimeGenerated, SuccessTimeGenerated, Success = not(isnull(SuccessTimeGenerated)), CorrelationID, ResourceGroup
    | extend ProcessingTime = SuccessTimeGenerated - BlobPutTime
    // don't alert on a blob that just landed -- we may still be processing it
    | where BlobPutTime < ago(1m) or Success == true
    | order by BlobPutTime; 
recentHearbeats | order by BlobPutTime desc | where BlobPutTime > ago(11m) | where Success == true | take 1 | summarize count()


---

let recentHearbeats = StorageBlobLogs
    | where OperationName == "PutBlob" and Uri has "/heartbeat/"
    | extend CorrelationID = extract("/heartbeat/(.*/.*)", 1, Uri)
    | extend ResourceGroup = tostring(split(_ResourceId, '/')[4])
    | extend Area = extract("(.*)/", 1, CorrelationID)
    | join kind=leftouter (
        // get functions success events
        AppTraces
        | extend CorrelationID = tostring(Properties["correlation_id"])
        | where CorrelationID != '' and Message has "heartbeat:success"
        | extend ResourceGroup = tostring(split(_ResourceId, '/')[4])
        | project SuccessTimeGenerated = TimeGenerated, CorrelationID, ResourceGroup
        // if we get multiple confirmations for the same file, use the first
        | summarize SuccessTimeGenerated = min(SuccessTimeGenerated) by ResourceGroup, CorrelationID
        )
        on ResourceGroup, CorrelationID
    | project AccountName, Area, BlobPutTime = TimeGenerated, SuccessTimeGenerated, Success = not(isnull(SuccessTimeGenerated)), CorrelationID, ResourceGroup
    | extend ProcessingTime = SuccessTimeGenerated - BlobPutTime
    // don't alert on a blob that just landed -- we may still be processing it
    | where BlobPutTime < ago(1m) or Success == true
    | order by BlobPutTime; 
recentHearbeats
| where BlobPutTime > ago(11m)
| order by BlobPutTime desc
| where Success == true
| take 1
| summarize count()

----
StorageBlobLogs
| where OperationName == "PutBlob" and Uri has "/heartbeat/"
| extend CorrelationID = substring(Uri, indexof(Uri, '/heartbeat/') + strlen('/heartbeat/'))
| extend ResourceGroup = tostring(split(_ResourceId, '/')[4])
| extend Area = split(CorrelationID, '/')[0]
| join kind=leftouter (
    // get functions success events
    AppTraces
    | extend CorrelationID = tostring(Properties["correlation_id"])
    | where CorrelationID != '' and Message has "heartbeat:success"
    | extend ResourceGroup = tostring(split(_ResourceId, '/')[4])
    | project SuccessTimeGenerated = TimeGenerated, CorrelationID, ResourceGroup
    // if we get multiple confirmations for the same file, use the first
    | summarize SuccessTimeGenerated = min(SuccessTimeGenerated) by ResourceGroup, CorrelationID
    )
    on ResourceGroup, CorrelationID
| project AccountName, Area, BlobPutTime = TimeGenerated, SuccessTimeGenerated, Success = not(isnull(SuccessTimeGenerated)), CorrelationID, ResourceGroup
| extend ProcessingTime = SuccessTimeGenerated - BlobPutTime
// don't alert on a blob that just landed -- we may still be processing it
| where BlobPutTime < ago(1m) or Success == true
| order by BlobPutTime
| where BlobPutTime > ago(11m)
| order by BlobPutTime desc
| where Success == false
| take 1