get SecurityEvents across all workspaces

Question

Hi All,

I’m running the following KQL to get a breakdown of Security Events across my workspaces for the last 31 days. The question I have, is the way I’m doing it a) optimal b) best practice, or would you recommend a different approach?

let totalSecEventRows = toscalar (SecurityEvent | where TimeGenerated > ago(31d) | count);  
SecurityEvent  
| where TimeGenerated > ago(31d)  
| summarize Count = count() by EventID, Activity  
| extend Percent = round(todecimal(Count) / totalSecEventRows * 100, 0)  
| sort by Count desc   
| top 5 by Count  
| project Activity, Count  
| render piechart  

The second question, I have, is how could I get the same data but for the entire data set in the Security Event table across all my Log Analytics workspaces? I can get ALL SecurityEvents across all workspace and assign the value to the variable totalSecEventRows, however how can I get the count by Event ID, Activity for the entire values as well, instead of defaulting to last 24h? Or by following your advice, how could I get the result for ALL security events without having to manually set the time window (so that basically the query itself will pull all events and based on that count perform the calculation)?

Answer 1

This reduced query will do the same thing (the Pie chart render adds the % calculation anyway, so you dont need to do that)

 SecurityEvent  
 | where TimeGenerated > ago(31d)  
 | summarize Count = count() by EventID, Activity  
 | sort by Count desc   
 | top 5 by Count  
 | project Activity, Count  
 | render piechart  

If you have a few Workspaces, then you could do a simple cross workspace query https://learn.microsoft.com/en-us/azure/azure-monitor/logs/cross-workspace-query

e.g. Just add more workspaces to row 1 (or see the docs above to create a function to do this especially if you have a lot)

union  SecurityEvent, workspace("workspace 1"). SecurityEvent  
 | where TimeGenerated > ago(31d)  
 | summarize Count = count() by EventID, Activity  
 | sort by Count desc   
 | top 5 by Count  
 | project Activity, Count  
 | render piechart  

If this helps please accept the answer?

Answer 2

For this and if this is ad-hoc (not one you need to run on schedule), I'd run the query from within a Workbook to get over this limit in the Logs blade.

In Sentinel, look at my Workbook template called: Sentinel Central, open the [Hunting] tab and paste in your query, this will then run across all selected Workspaces (in the regions, subscriptions you select in the drop-downs)