CODEWITHSUNDEEP
kqlazure-sentinel
Miguel Cuba
Miguel Cuba

Reputation: 61

Group data using list KQL

I am quite new using Kusto so I want to make list grouping differents network connections by the same source ip in differents ports. For example, i got a table like this:

Source IP Destination IP Destination Port
192.168.1.1 10.0.0.1 80
192.168.1.1 10.0.0.2 8080
192.168.1.1 10.0.0.1 443
192.168.1.1 10.0.0.1 443
192.168.1.2 10.0.0.1 80
192.168.1.2 10.0.0.1 80

I need to show when this table gots more than 2 matches. This list will contain only unique registers, for example:

Source IP Destination IP Destination Port
192.168.1.1 10.0.0.1 80
192.168.1.1 10.0.0.2 8080
192.168.1.1 10.0.0.1 443

So, I tried to use this query but it returns duplicated registers:

datatable(SrcIp:string, DstIP:string, DstPort:string)[         
'192.168.1.1', '10.0.0.1','80',
'192.168.1.1', '10.0.0.2','8080',
'192.168.1.1', '10.0.0.1','443',
'192.168.1.1', '10.0.0.1','443',
'192.168.1.2', '10.0.0.1','80',
'192.168.1.2', '10.0.0.1','80',]    | summarize
recurrence  = count(),
Details = make_list(pack("Source IP: ",SrcIp,"Destination Ip: ",DstIP,"Destination Port: ",DstPort))
by SrcIp| where recurrence >2

enter image description here

Upvotes: 1

Views: 835

Answers (1)

Hauke Mallow
Hauke Mallow

Reputation: 3212

You could make the input dataset unique in the first step, by adding a summarize over all columns (see also the Kusto documentation):

datatable(SrcIp:string, DstIP:string, DstPort:string)[         
'192.168.1.1', '10.0.0.1','80',
'192.168.1.1', '10.0.0.2','8080',
'192.168.1.1', '10.0.0.1','443',
'192.168.1.1', '10.0.0.1','443',
'192.168.1.2', '10.0.0.1','80',
'192.168.1.2', '10.0.0.1','80',] 
| summarize by SrcIp, DstIP, DstPort   
| summarize recurrence  = count(), Details = make_list(pack("Source IP: ",SrcIp,"Destination Ip: ",DstIP,"Destination Port: ",DstPort))
by SrcIp| where recurrence >2

Upvotes: 2

Related Questions