CODEWITHSUNDEEP
azureazure-log-analyticsazure-data-explorer
Bombbe
Bombbe

Reputation: 165

Filter out ip addresses from Kusto query

I am using following query to review inbound connections of VMs:

// the machines of interest
let ips=materialize(ServiceMapComputer_CL
| summarize ips=makeset(todynamic(Ipv4Addresses_s)) by MonitoredMachine=ResourceName_s
| mvexpand ips to typeof(string));
let StartDateTime = datetime(2020-07-01T00:00:00Z);
let EndDateTime = datetime(2021-01-01T01:00:00Z);
VMConnection
| where Direction == 'inbound'
| where TimeGenerated > StartDateTime and TimeGenerated  < EndDateTime
| join kind=inner (ips) on $left.DestinationIp == $right.ips
| summarize sum(LinksEstablished) by Computer, Direction, SourceIp, DestinationIp, DestinationPort, RemoteDnsCanonicalNames, Protocol

There are few ip addresses that I would like to filter out because they are useless and could confuse. Any tips how I could filter out from result ip addresses e.g 10.30.0.0/20 and 10.40.0.0/25?

Upvotes: 0

Views: 3508

Answers (1)

Alexander Sloutsky
Alexander Sloutsky

Reputation: 3017

It is not quite clear how your input data looks and how you define IPs to filter out. Therefore, the answer below is to get you started:

let ServiceMapComputer_CL = datatable(Ipv4Addresses_s:string, ResourceName_s:string)
[
 '10.0.30.0/20', 'a',
 '10.40.0.0/25', 'a',
 '11.1.30.0/20', 'b', // only record that will be left
];
ServiceMapComputer_CL
| where not(ipv4_is_match(Ipv4Addresses_s, '10.0.30.0') or ipv4_is_match(Ipv4Addresses_s, '10.40.0.0'))
| distinct Ipv4Addresses_s, ResourceName_s

Please, also note that 'mvexpand' operator should be replaced with 'mv-expand' : the semantics of two are different ('mvexpand' is a deprecated version - and it also has inner limitation of expanding by default only 128 values, which can cause incorrect results to be returned).

Upvotes: 1

Related Questions