Forum Discussion

Copper Contributor

Oct 29, 2020

Solved

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 que...

GaryBushey
Oct 30, 2020
roadruner This is the starting query for something like that.

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

roadruner

Copper Contributor

Oct 30, 2020

GaryBushey 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

GaryBushey

Bronze Contributor

Oct 30, 2020

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.

roadruner
Copper Contributor
Oct 30, 2020
GaryBushey 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
- GaryBushey
  Bronze Contributor
  Oct 30, 2020
  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.
  - roadruner
    Copper Contributor
    Oct 30, 2020
    GaryBushey 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