Forum Discussion
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
JEPC-01 50
JEPC-02 20
I want to associate all hostnames starting with GG to the value "Guernsey PCs" and all starting with JE to the value Jersey PCs, so I can produce a table like this:
Grouped Hostname Events
Guernsey PCs 17
Jersey PCs 70
I'm sure this must have been done before (the reason I want to do this is for readability when the query's exported into PowerBI), but can't find a solution. Any pointers appreciated!
Cheers
James
- 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")
