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

Question

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.

Answer 1

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

Answer 2

you could try something like this:

datatable(uniqueID:long, trackedproperty1:long, trackedproperty2:string)
[
    1, 24, "",
    1, long(null),  'word1',
    2, 35, "",
    2, long(null),  'word1.2',
    3, 10, "",  
    3, long(null),  'word1.3',
    4, 100, "",
    4, long(null),  'word1.4',
]
| summarize take_any(trackedproperty1), take_any(trackedproperty2) by uniqueID
| where trackedproperty1 > 25

uniqueID	trackedproperty1	trackedproperty2
2	35	word1.2
4	100	word1.4