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)?
Hi Clive,
That's a helpful starting point. The real thing is a Sentinel table and is rather bigger and more complex than the example: can I pull the data into the summarization from that?
That's a helpful starting point. The real thing is a Sentinel table and is rather bigger and more complex than the example: can I pull the data into the summarization from that?
Just trim it down, and add your Table name
<enter name of table here>
| summarize JerseyPCs = sumif(Events, Hostname startswith "JE"),
GuernseyPCs = sumif(Events, Hostname startswith "GG")
<enter name of table here>
| summarize JerseyPCs = sumif(Events, Hostname startswith "JE"),
GuernseyPCs = sumif(Events, Hostname startswith "GG")
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)?- 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.