CODEWITHSUNDEEP
azureazure-application-insightsazure-log-analyticsazure-data-explorer
Markus S.
Markus S.

Reputation: 2812

Azure Log Analytics Query with WHERE clause produces no results

I'm querying log entries in Azure Application Insights originating from AppCenter Diagnostics using Azure Log Analytics. In some log entries i use custom propertys. Now i'm trying to write a query to show values only with certain properties having a given value.

My original query looks like this and produces the expected result:

customEvents
| where (timestamp >= datetime(2019-02-20T09:04:00.000Z) and timestamp <= datetime(2019-02-21T09:04:00.000Z)) 
| top 101 by timestamp desc
| project timestamp, name, customDimensions.Properties
| where name == "Navigated to details view"

Query result

Hovering over the "productId" property shows a plus-sign which allows to add a filter criteria:

Filter for property

Choosing this options extends my query:

customEvents
| where (timestamp >= datetime(2019-02-20T09:04:00.000Z) and timestamp <= datetime(2019-02-21T09:04:00.000Z)) 
| top 101 by timestamp desc
| project timestamp, name, customDimensions.Properties
| where name == "Navigated to details view"
| where customDimensions_Properties.productId == 4711

So far, so good. If i now try to run this query i get the message "NO RESULTS FOUND":

No results found

Edit: I also tried adding the where clause on the bottom to the first where clause

customEvents
| where (timestamp >= datetime(2019-02-20T09:04:00.000Z) and timestamp <= datetime(2019-02-21T09:04:00.000Z)) 
and name == "Navigated to details view" 
and customDimensions.Properties.productId == 4711
| top 101 by timestamp desc
| project timestamp, name, customDimensions

Unfortunately no result either.

Edit 2: I also tried this query to see if i can project the productId property in my query without including it in the where clause:

customEvents
| where (timestamp >= datetime(2019-02-20T09:04:00.000Z) and timestamp <= datetime(2019-02-21T09:04:00.000Z)) 
and name == "Navigated to details view" 
| top 101 by timestamp desc
| project timestamp, name, customDimensions, customDimensions.Properties.productId

But this column is empty: Project productId

Is there anything i am missing? Is the tooling a problem and producing a wrong query?

Thank you for any help!

Upvotes: 2

Views: 3933

Answers (1)

KrishnaG
KrishnaG

Reputation: 3484

You would have to use various operators like mvexpand and extend to accomplish your requirement. Please find below sample query. Note that the below one is just a sample query which you may have to tweak a bit to make it work as expected and get the expected output (say if you are expecting output with all the columns of the customEvent at a particular timestamp which has particular productId, etc.)

customEvents
| where (timestamp >= datetime(2019-02-20T09:04:00.000Z) and timestamp <= datetime(2019-02-21T09:04:00.000Z)) 
| top 101 by timestamp desc
| project timestamp, name, customDimensions_Properties
| where name == "Navigated to details view"
| extend CDP_toString=parsejson(tostring(customDimensions_Properties))
| mvexpand CDP_toString
| project CDP_toString
| where CDP_toString.['productId'] == "4711";

Hope this helps!! Cheers!! :)

Upvotes: 1

Related Questions