How can I feed a Sentinel watchlist into a union?

Occasional Contributor

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 

 

3 Replies

Returning a "union_arg0" when running this KQL

 

danielmasters_0-1652870773181.png

 

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

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.

 

danielmasters_0-1653326341755.png

 

This uses very little processing compared to my initial idea.

 

Many thanks!!

 

Dan 

 

 

 

www.000webhost.com