Watchlist and query

[roadruner]

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

[#2]

@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.IPAddress

[#3]

@Gary Bushey  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.IPAddress

error 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 again

[#4]

@roadruner 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.

[#5]

@Gary Bushey Thanks I tracked the error I had, which was the columns. It runs with no errors now. I did run a quick test and hit one of the ip's  in watchlist and then ran the query and no results found. Does the query search out all of sentinel? I tried just putting CommonSecurityLog to see if it would just search through those logs, since that's where the hit should be.

Here is what i tried, didn't work.

CommonSecurityLog

let ClearedIPAddresses=_GetWatchlist('test1');
Heartbeat
| join ClearedIPAddresses on $left.ComputerIP == $right.IPAddresses

[#6]

@roadruner It will only search the one table. 

There really is no way to search all tables for multiple values.  There is the "search" command, https://docs.microsoft.com/en-us/azure/data-explorer/kusto/query/searchoperator, that allows you to search for a single term although I do not know if this can be used in a Analytic rule or not.

[#7]

@Gary Bushey Hmm ok, thanks.  How can i search one table? say CommonSecurityLog

I tried this but no dice.

CommonSecurityLog

let ClearedIPAddresses=_GetWatchlist('test1');
Heartbeat
| join ClearedIPAddresses on $left.ComputerIP == $right.IPAddress

[#8 · Accepted solution]

@roadruner This is the starting query for something like that.

let ClearedIPAddresses=_GetWatchlist('test1');
CommonSecurityLog
| join ClearedIPAddresses on $left.SourceIP== $right.IPAddress

[#9]

@Gary Bushey Thanks! This worked. Just replaced sourceip to destip. and .found the test hits to the list. either way works.

[#10]

@Gary Bushey thank you very much for that.  I will try that also and feedback

Kind regards 

Caitlin