Forum Discussion
Solved
KQL - merging field values
Morning all, I have data where there are 20-30 distinct values, and I want to group the data into a smaller number of groups. As an example: Hostname Events GGPC-01 5 GGPC-02 12 JEP...
- Sorry, this is quite hard to visualize, without seeing your example. Are you using, something like this KQL (not sure where the hostname is coming from in this data though).
AADServicePrincipalSignInLogs
| summarize Events=count() by ServicePrincipalName, bin(TimeGenerated, 1d)
Is this something you can mock up in a datatable (like in my first example)?
Much as I wish I could say it worked - it doesn't.
Working with the real data now, I've created a sort of intermediate table from Service Principal logins that breaks the data up to three columns:
- the SP name
- the date
- the number of events
using | summarize Events=count() by ServicePrincipalName, bin(TimeGenerated, 1d)
I've then put a second summarize in plus my grouping statements, but the result is a table with 5 columns, one row, and a value of false under each column.
Does it need some way of creating a column for the grouped values?
Sorry, this is quite hard to visualize, without seeing your example. Are you using, something like this KQL (not sure where the hostname is coming from in this data though).
AADServicePrincipalSignInLogs
| summarize Events=count() by ServicePrincipalName, bin(TimeGenerated, 1d)
Is this something you can mock up in a datatable (like in my first example)?
AADServicePrincipalSignInLogs
| summarize Events=count() by ServicePrincipalName, bin(TimeGenerated, 1d)
Is this something you can mock up in a datatable (like in my first example)?
- I finally cracked it!
The answer involves using split(), thus:
GroupedHostname = split(Hostname,"-",0)
Next problem is a different extension of the same thing. Thanks for your help.