I have an Azure Logic App that logs to a Log Analytics Workspace.
There are 2 tracked properties that are written as custom values. These are written on different Rows as they are part of different steps.
If this was SQL, I'd do a conditional join to itself and then base my Where clause on said conditional join.
Sample Data:
uniqueID | trackedproperty1 | trackedproperty2 |
---|---|---|
1 | 24 | |
1 | word1 | |
2 | 35 | |
2 | word1.2 | |
3 | 10 | |
3 | word1.3 | |
4 | 100 | |
4 | word1.4 |
What I want to to do is get an output that shows all the uniqueIDs where trackedproperty1 is greater than a number - for the sake of argument - 25. e.g. the output would look like:
uniqueID | trackedproperty1 | trackedproperty2 |
---|---|---|
2 | 35 | word1.2 |
4 | 100 | word1.4 |
I got as far as:
table
| summarize any(trackedproperty1), any(trackedproperty2) by uniqueID
| where trackedproperty1 >= 25
However, that causes the trackedproperty2 column to be blank.
As an additional consideration, eventually, I would like to be able to expand the contents of trackedproperty2 with a single drill-down (so the solution should bear that in mind) - currently it's in JSON.
I managed to solve this myself - ironically by trying to do something else - I kept getting an error when trying to extend a column - which was the column didn't exist.
when using the any() function, a new column is created with the name any_columnName - by moving my where clause in the Kusto query to after the summarize step and referencing the new column name - it then filtered as I expected.
table
| summarize any(trackedproperty1), any(trackedproperty2) by uniqueID
| where any_trackedproperty1 >= 25
This generated the results that I was wanting/expecting