Jul 12 2018
10:59 AM
- last edited on
Apr 07 2022
05:14 PM
by
TechCommunityAP
Jul 12 2018
10:59 AM
- last edited on
Apr 07 2022
05:14 PM
by
TechCommunityAP
Jul 12 2018 01:26 PM
Mohsen,
You're very close in your query. There are two things you need to fix: 1) the way you define your IP set, and 2) using the !in operator instead of !contains. The updated query will look as follows (I've commented two of the lines out as I don't have any data with the "streamname_s" or "identity_s" columns - you should uncomment them):
let ips = datatable(IP:string)[ "10.0.0.2", "11.0.0.3" ]; search * //| where ( StreamName_s !contains "clusters" ) | order by TimeGenerated //| project TimeGenerated, identity_s, CallerIPAddress | where CallerIPAddress !in (ips)
Using search *, however, is potentially misleading here: it will search across all your data and return any row where the CallerIPAddress column does not a value matching those two IPs. Practically, that means that any rows with an empty CallerIPAddress will also be returned, likely not the behaviour you're looking for. This means you'll likely want to add one further where statement:
let ips = datatable(IP:string)[ "10.0.0.2", "11.0.0.3" ]; search * //| where ( StreamName_s !contains "clusters" ) | order by TimeGenerated //| project TimeGenerated, identity_s, CallerIPAddress | where isnotempty(CallerIPAddress) | where CallerIPAddress !in (ips)
I'd also urge you, for efficiency's sake, to use a particular table name, or a union of table names as your data source, rather than a search *. This'll ensure your queries run much quicker, and the results you see are much more focused.
Finally, check out the module on the let command and the in & !in operators on our Pluralsight course (free to watch after a free account registration) if you want to learn a bit more!