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)?
One idea
let a = datatable(Hostname:string, Events:int)
[
'GGPC-01',5,
'GGPC-02',12,
'JEPC-01',50,
'JEPC-02',20
];
a
| summarize JerseyPCs = sumif(Events, Hostname startswith "JE"),
GuernseyPCs = sumif(Events, Hostname startswith "GG")
| evaluate narrow()
| project-away Row
| project-rename GroupedHostname = Column, Events = Value - 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?- 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")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?
