SOLVED

Alternative to using 'top' operator with a calculated variable

Occasional Contributor

I was wondering of anyone knows a workaround to using the 'top' operator along with a variable calculated at runtime. As can be seen by running the sample query below, Sentinel doesn't seem to like a calculated value being passed as an argument to top and returns a "'top' operator: Failed to resolve scalar expression named 'a'" error . Commenting out the last line indicates the value of 'a' is being displayed in the query result. 

 

let x=5; let y =2; 
SecurityAlert 
| summarize count() by AlertName 
| extend a = x-y 
| top a by count_ asc

 

I need this functionality for playbook automation to isolate hosts. I want to be able to cap the isolation to a limit(for e.g. 5) per day and every time the rule query is run(i.e. if the query returns 10 hosts only isolate the first 5 if there were no hosts isolated in the past day). I think the 'top' operator would fit this requirement however I am seeing the error mentioned above. I am trying to get number of hosts that can be isolated based on the number previously isolated in the past day and the number of results returned by current query run. This is calculated by subtracting the number of hosts isolated in the past day from the limit and returning only those number of hosts to be isolated(for e.g. if  2 hosts have been isolated in the past 24 hours, and current query run returns 5 hosts only the first 3 should get isolated since the limit is 5). I was wondering if there is another way of accomplishing this ? 

Thanks,
Princely Dmello  

2 Replies
best response confirmed by Princely (Occasional Contributor)
Solution

@Princely Try this

let x=5; let y =2; 
SecurityAlert 
| summarize count() by AlertName 
| extend a = x-y 
| top toint(x-y) by count_ asc