Azure Application Insights Query customDimensions

Question

I have a Java application that sends log4j2 logs to App Insights. When logging an exception I basically pass a JSON in string format and the exception something like this

JSONObject json=new JSONObject();
json.put(...)
....
log.error(json.toString(), exception)

On the App Insights side; the message is stored under customDimensions with the key Logger Message in a JSON format.

From this stored information, I would like to make a query that would fetch all the exception messages where the statusCode > 200 and statusCode < 300. I've tried couple of queries but I was not able to extract those specific exception messages.

One of the queries I was trying was

exceptions 
| limit 50 
| where toint(customDimensions["Logger Message"].statusCode) > 200 
  and toint(customDimensions["Logger Message"].statusCode) < 300

Any help would be appreciated

Update: I've running the query

exceptions
| limit 50 
| project s1 = customDimensions["Logger Message"]
| extend  s2 = s1.statusCode
| extend  s3 = toint(s2)
| extend  s4 = s3 >= 200 and s3 < 300

Yoni L. Yoni L. · Accepted Answer · 2020-05-28T22:58:45

from a query language perspective, you're doing the right thing (assuming you actually want > 200 and not >= 200, so that the query in your example will actually return the record you've pasted a snapshot of)

print customDimensions = dynamic({
    "Logger Message":{
        "message":"Test 1 and Test 2",
        "statusCode": 200
    }
})
| project toint(customDimensions['Logger Message'].statusCode)

This returns a single table, with a single row, with a single column, with the value 200.

It's hard to fully understand how the actual data in your case looks like based on the snapshot - you could try 'debugging' it step by step (s1,...,s4 below) and seeing where it 'breaks'.

(just a guess) you may need to invoke parse_json() on a string payload so that you can actually access dynamic properties in it.

exceptions
| limit 50 
| project s1 = customDimensions["Logger Message"]
| extend  s2 = s1.statusCode
| extend  s3 = toint(s2)
| extend  s4 = s3 > 200 and s3 < 300

Update:

try using parse_json() as the previous 'guess' suggested:

(plus, see Notes section here: https://docs.microsoft.com/en-us/azure/data-explorer/kusto/query/parsejsonfunction)

exceptions
| limit 50 
| project s1 = parse_json(tostring(customDimensions["Logger Message"]))
| extend  s2 = s1.statusCode
| extend  s3 = toint(s2)
| extend  s4 = s3 > 200 and s3 < 300

Azure Application Insights Query customDimensions

1 Answers