Need a bit of assistance.

Microsoft

Hi, I am new to using AIQL and I have been tasked to create a query to look for missing phrases. Here is my query to look for the initial phrase.

 

traces
| where message contains "Received 'job valid' notification"
| extend backendRequest = customDimensions['jsonMessage']
| extend customerId = customDimensions['customerId']
| extend jobId = customDimensions['jobId']
| extend requestAsJson = tostring(backendRequest)
| extend request = substring(requestAsJson,6, indexof(requestAsJson,'"',6,40)-6)
| order by tostring(jobId) desc
| project message, request, severityLevel, timestamp, backendRequest, customerId, jobId
 
This will produce a list of items that match the phrase that I'm looking for.
I then need to look in each Jobid and check to see if a phrase in the message does not exist. 
Any help would be greatly appreciated. 
2 Replies

Hi Jim,

The example you shared work fine, after adjusting it to the data in the demo environment I created this query:

traces
| where customDimensions != ""
| where message contains "StartProfiler" // "Received 'job valid' notification"
| extend agentId = customDimensions['AgentSession']
| extend Source = customDimensions["Source"]
| order by tostring(agentId) desc
| project message, severityLevel, timestamp, Source, agentId
| where message !contains "triggered" 

and as you can see I've added the !contains on the last line, to exclude results with a message that contains a certain string. Is that what you need?

you don't need to use bracket notation if your custom dimensions are valid kusto names,

and you should convert types from dynamic as you go, and you should parse your json vs using substring, and you can combine extends and where's.

 

traces
| where message contains "Received 'job valid' notification"
and message !contains "some other value"
| extend backendRequest = tostring(customDimensions.jsonMessage),
customerId = tostring(customDimensions.customerId),
jobId = toint(customDimensions.jobId)
| extend request = tostring(parse_json(backendRequest).request)
| project message, request, severityLevel, timestamp, backendRequest, customerId, jobId
| order by jobId desc

i can help further with sample data...