CODEWITHSUNDEEP
kqlazure-sentinel
Rakim
Rakim

Reputation: 177

Group similar column results into 1 row - KQL - Azure

I can't figure out how to turn this:

20.20.5.4   vuxml.freebsd.org   
20.20.5.20  vuxml.freebsd.org   
20.20.8.7   edgedl.me.gvt1.com  
20.20.8.7   dl.google.com   
20.20.8.7   redirector.gvt1.com 
20.20.32.8  armmf.adobe.com

into this:

20.20.5.4   vuxml.freebsd.org   
20.20.5.20  vuxml.freebsd.org   
20.20.8.7   edgedl.me.gvt1.com  
            dl.google.com   
            redirector.gvt1.com 
20.20.32.8  armmf.adobe.com

Essentially, I am trying to summarize the destinations per IP address (group similar IP addresses, instead of showing 20.20.8.7 3 times per each result) I can't figure out what aggregation to pass to "summarize", any ideas or links to documentation would be helpful.

Upvotes: 1

Views: 1198

Answers (1)

Yoni L.
Yoni L.

Reputation: 25895

you could try using the make_list() aggregation function: https://learn.microsoft.com/en-us/azure/data-explorer/kusto/query/makelist-aggfunction

datatable(ip_address:string, destination:string)
[
    '20.20.5.4',  'vuxml.freebsd.org',
    '20.20.5.20', 'vuxml.freebsd.org',   
    '20.20.8.7',  'edgedl.me.gvt1.com', 
    '20.20.8.7',  'dl.google.com',
    '20.20.8.7',  'redirector.gvt1.com',
    '20.20.32.8', 'armmf.adobe.com',
]
| summarize make_list(destination) by ip_address
ip_address list_destination
20.20.5.4 [
"vuxml.freebsd.org"
]
20.20.5.20 [
"vuxml.freebsd.org"
]
20.20.8.7 [
"edgedl.me.gvt1.com",
"dl.google.com",
"redirector.gvt1.com"
]
20.20.32.8 [
"armmf.adobe.com"
]

Upvotes: 1

Related Questions