2

Given the following Log analytics KQL query :

SigninLogs 
| where ResultType == 0 
| summarize max(TimeGenerated) by UserPrincipalName

I need to display other columns from those selected rows in the SigninLogs table. I've tried different approaches with no success. Joining back to the same table again seems unfeasible as joins appear to only be available using a single column. Other approaches using in failed because the needed columns weren't available in the above source query.

AdamC
  • 23
  • 1
  • 3

1 Answers1

1

You can use the arg_max() aggregation function: https://learn.microsoft.com/en-us/azure/data-explorer/kusto/query/arg-max-aggfunction

Yoni L.
  • 22,627
  • 2
  • 29
  • 48
  • Thanks, this was the solution I was looking for. The query I needed was: `"SigninLogs | where TimeGenerated > ago(90d) | where Location != 'GB' and Location != '' and ResultType == 0 | extend countryOrRegion = tostring(LocationDetails['countryOrRegion']) | extend geoCoordinates = tostring(LocationDetails['geoCoordinates']) | extend state = tostring(LocationDetails['state']) | extend city = tostring(LocationDetails['city']) | summarize arg_max(TimeGenerated, * ) by UserPrincipalName | project UserDisplayName, UserPrincipalName, Location, countryOrRegion, state, city, geoCoordinates"` – AdamC Aug 20 '21 at 17:59