Forum Discussion

danielmasters's avatar
danielmasters
Brass Contributor
May 18, 2022

How can I feed a Sentinel watchlist into a union?

Hey all,

 

So, I'm building out a query that will alert me if any of our listed critical Log Analytic tables "goes quiet" which has happened recently. 

 

My first version had a static list of LA tables within the union and this works fine.

 

My second version I want to use a Sentinel watchlist which I'm now a fan of. In Log Analytics I can use "union * | where _TableName has_any "watchlist"" for example and that works.

 

The problem: I can't use "union all" in a Sentinel analytic.

 

So, I'm trying to feed a watchlist into the union but this isn't working.

 

Anyone tried this before in a Sentinel analytic?

 

I don't want a static list in the query but it's not so much a problem. Just wanting to make it more usable for the SOC analysts by using  a Watchlist

 

Some of the query which is broken on the union...

 

 

 

let watchlist = (_GetWatchlist('security-log-quiet-tables') | project Tables);
let timeFrame = 7d;
//Cannot union * in a Sentinel analytic...
union withsource="_TableName" watchlist
| where TimeGenerated >= ago(timeFrame)
| project _BilledSize, _IsBillable, TimeGenerated, _TableName

 

 

 

 

 

I've tried feeding the watchlist directly into the union without using a let statement but that doesn't work.

 

Tried without projecting Tables in the let statement but also doesn't work.

 

Also tried "view ()" in the let statement but couldn't get that to work.

 

Any ideas appreciated!!

 

 

Dan 

 

KQL
kusto
siem

3 Replies

    • Clive_Watson's avatar
      Clive_Watson
      Bronze Contributor
      May 18, 2022
      Use the Usage Table as the source rather than a Union * and list the Distinct DataTypes (which are the table names)

      Usage
      | distinct DataType

      or

      let watchlist = dynamic(["Operation","AuditLogs"]);
      Usage
      | where DataType in (watchlist)
      | distinct DataType
      • danielmasters's avatar
        danielmasters
        Brass Contributor
        May 23, 2022

        Fantastic Clive_Watson! 

         

        I knew I had the Usage table but never actually used or looked at it.

         

        I now have this which I think will do the job, alerting me of any tables in my Watchlist that have gone quiet.

         

         

        This uses very little processing compared to my initial idea.

         

        Many thanks!!

         

        Dan 

         

         

         

Resources