Microsoft Secure Tech Accelerator
Apr 03 2024, 07:00 AM - 11:00 AM (PDT)
Microsoft Tech Community
SOLVED

KQL - merging field values

Copper Contributor

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 

6 Replies

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 

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

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

best response confirmed by James_McLaren (Copper Contributor)
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)?
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.
1 best response

Accepted Solutions
best response confirmed by James_McLaren (Copper Contributor)
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)?

View solution in original post