Introduction
If you have not heard, there are changes coming to Microsoft Sentinel’s CommonSecurityLog table. This is the table that stores information received from CEF ingestion.
To read about the changes, go to Upcoming changes to the CommonSecurityLog table – Microsoft Community Hub
This blog post is about a PowerShell script that I wrote which will help you determine what queries, whether they be in Analytic Rules or as saved queries, reference the table so you can see if any changes would be needed. It will output a CSV file that first lists the rules and then the other queries.
You can get the code from: garybushey/DetectCommonSecurityLog (github.com)
The Script
I am not going to go into much detail here, since this is just a combination of other code that I have used before. The only thing to remember is there are two different APIs being used.
The first one makes a call to get a list of the rules, namely
https://management.azure.com/subscriptions/$($subscriptionId)/resourceGroups/$($resourceGroupName)/providers/Microsoft.OperationalInsights/workspaces/$($workspaceName)/providers/Microsoft.SecurityInsights/alertrules?api-version=2022-12-01-preview"The code then iterates through this and outputs the name of the query to the CSV file.
The second call, gets the saved queries
"https://management.azure.com/subscriptions/$($subscriptionId)/resourceGroups/$($ResourceGroupName)/providers/Microsoft.OperationalInsights/workspaces/$($workspaceName)/savedSearches/?api-version=2017-03-03-preview"Then, once again, the code will iterate through the entries and output the name as well as the category of the query, to make it easier to find the query.
Summary
That is all there is to it. I hope it is helpful but if there is additional information that you think would be helpful, please add a comment to let me know.