KQL - merging field values

[James_McLaren]

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!

KQL

Cheers

James 

[#2]

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 

@James_McLaren 

[#3]

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?

[#4]

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")

[#5]

@Clive_Watson 

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? 

[#6 · Accepted solution]

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)?

[#7]

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.