Sentinel Crashing when Running Lookup Search. Trying to find stale Firewall rules.

Question

Hello, I'm trying to implement a lookup search that takes a lookup of all of our firewall rules and correlates it with our firewall data to then output what firewall rules are NOT present in the firewall logs. This is to trim down on any stale firewall rules we have. Although, when I run the below search my Sentinel crashes(freezes) and I need to reload it. I have yet to finish the complete search because of this, so any input on how I should be implementing this would be greatly appreciated. I am trying to store all the firewall rules from the lookup in to the allRules variable and if I could get that working then do a make_list of the firewall rules used in our logs. Then do a comparison of allRules !in usedRules to find the rules that are not being utilized. The lookup itself works fine. The current search will crash my Sentinel. And if I move the make_list command before the join I get an error in the join command. Not sure how to pivot from this so any help would be appreciated. SAS url is obfuscated.

externaldata (rulelabel: string) [h@"https://.file.core.windows.net/"] with (format='txt', ignoreFirstRecord=true)
//| summarize allRules = make_list(rulelabel)
| join (CommonSecurityLog | where DeviceProduct == "NSSFWlog") on $left.rulelabel == $right.Activity
| summarize allRules = make_list(rulelabel)

Answer 1

Hi @Christian Lozach ,

The error in line 4 is due to a missing "=" symbol: use "==" instead of "="

Please try this: (replace the external data url with the correct one)

externaldata (rulelabel: string) [h@"<deprecaped>"] with (format='txt', ignoreFirstRecord=true)  
| distinct rulelabel  
| join kind=leftanti  
(CommonSecurityLog | where DeviceProduct == "NSSFWog" | distinct Activity) on $left.rulelabel==$right.Activity  

I hope this works for you!

Answer 2

Hey @Christian Lozach ,

I am not clear what doesn't work for you, but the above query is expected tp only return the "useless" rules: rules that exist on the DB (external data) but are never triggered (not seen on CommonSecurityLog). Is that not the case?