Forum Discussion
Watchlist and query
new to kql here, is it possible to build a query that search's across logs looking for machines that connected to any of ip addresses in the watchlist? Any examples ? Plan would be to turn that query into a log analytic rule to create events and eventually a playbook.
thanks
roadruner This is the starting query for something like that.
let ClearedIPAddresses=_GetWatchlist('test1');
CommonSecurityLog
| join ClearedIPAddresses on $left.SourceIP== $right.IPAddress
roadruner Here is a simple example of how to do this. I created a CSV file that has all the IPAddresses I have cleared and uploaded that into the Watchlist using "ClearedIPAddreses" as the alias.
let ClearedIPAddresses=_GetWatchlist('ClearedIPAddresses');Heartbeat| join ClearedIPAddresses on $left.ComputerIP == $right.IPAddressGaryBushey Hi, i tried that query with alias of test1 which is alias of watchlist and received an error,
let ClearedIPAddresses=_GetWatchlist('test1');Heartbeat| join ClearedIPAddresses on $left.ComputerIP == $right.IPAddresserror is 'join' operator: failed to resolve Column named "IPAddress"my csv file has the name IP Addresses in first cell then next cells below the actual ip addresses.What do you mean by cleared? The ip's I would have in my list would be IOC's, thus checking to see if any machines were hitting them.thanks againroadruner You CSV file would need to have the column headers in the first row. One of mine was "IPAddresses", you would need to substitute whatever you called your columns for that.
I used the term "cleared" only because my watchlist contained those IP Addresses that I want to allow. You can call you watchlist whatever makes sense to you.
