Microsoft Entra Suite Tech Accelerator
Aug 14 2024, 07:00 AM - 09:30 AM (PDT)
Microsoft Tech Community

How can I feed a Sentinel watchlist into a union?

Brass 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!!





3 Replies

Returning a "union_arg0" when running this KQL




Use the Usage Table as the source rather than a Union * and list the Distinct DataTypes (which are the table names)

| distinct DataType


let watchlist = dynamic(["Operation","AuditLogs"]);
| 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.




This uses very little processing compared to my initial idea.


Many thanks!!