May 18 2022 03:15 AM
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
May 18 2022 03:46 AM
Returning a "union_arg0" when running this KQL
May 18 2022 10:13 AM
May 23 2022 10:19 AM
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