joinkqlazure-data-explorerazure-diagnostics

Kusto query combining multiple rows into a single row based on Unique ID and where clause


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.


Solution

  • 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