Azure Log Analytics Query with WHERE clause produc

2019-08-27 01:27发布

问题:

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"

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

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":

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:

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

Thank you for any help!

回答1:

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!! :)