DEV Community

Anthony Buhnerkemper
Anthony Buhnerkemper

Posted on

Microsoft Sentinel Rules as Code with PowerShell 7 and KQL

#powershell #microsoft365 #azure #security

I have worked in IT for about 20 years. A lot of that was MSP operations. Today I work as an M365 cloud admin.

I kept writing the same scripts. So I cleaned them up and shared them on GitHub. This toolkit is for Microsoft Sentinel. It uses PowerShell 7, Az.SecurityInsights and KQL.

Repo: sentinel-kql-toolkit

The problem it solves

Analytics rules built in the portal are hard to track. Who changed what? Is there a backup?

Data connectors can also stop sending data without much noise. Then your detections see nothing.

This toolkit treats rules as code. It also checks that data still flows.

Highlight scripts

Export-SentinelAnalyticsRule.ps1

Exports analytics rules to JSON, one file per rule. Put them in Git for backup and review.

./Export-SentinelAnalyticsRule.ps1 -Kind Scheduled
Enter fullscreen mode Exit fullscreen mode

New-SentinelRuleFromKql.ps1

Creates or updates a Scheduled rule from a .kql file. Each detection has a metadata header. Samples include MFA fatigue, new Global Admin and brute force success.

./New-SentinelRuleFromKql.ps1 -KqlFile ./detections/*.kql -WhatIf
Enter fullscreen mode Exit fullscreen mode

Get-SentinelDataConnectorHealth.ps1

Checks connectors and the last record per table. It flags tables that have gone quiet.

./Get-SentinelDataConnectorHealth.ps1 -StaleHours 6
Enter fullscreen mode Exit fullscreen mode

Other scripts cover rule import, a rule report, an incident report and a KQL query runner. The docs include workbook notes.

How the scripts are built

  • PowerShell 7.4 or later.
  • Comment-based help on every script. Run Get-Help ./script.ps1 -Full.
  • Reports export to CSV.
  • Change scripts support -WhatIf and -Confirm.
  • Certificate (app-only) or interactive sign-in.
  • No secrets in the repo. Examples use contoso.com and placeholder GUIDs.

Please test in a dev or test tenant first. Read each script before you run it.

My usual workflow

I start with the reports. They are read-only and safe. I open the CSV in Excel and look for anything odd.

Then I pick one fix. I run the change script with -WhatIf and read the output. If it looks right, I run it again for real.

For jobs I repeat, I use a certificate and schedule the script. That way nobody has to remember to run it.

Getting started

Clone the repo:

git clone https://github.com/anthonybuhnerkemper/sentinel-kql-toolkit.git
cd sentinel-kql-toolkit
Enter fullscreen mode Exit fullscreen mode

Every toolkit ships the same Connect-M365Admin script. Dot-source it, then connect.

# Interactive sign-in
. ./scripts/Connect/Connect-M365Admin.ps1
Connect-M365Admin -Service Azure -Interactive -ClientId 00000000-0000-0000-0000-000000000000

# Or app-only with a certificate
$env:M365_TENANT_ID       = '00000000-0000-0000-0000-000000000000'
$env:M365_CLIENT_ID       = '00000000-0000-0000-0000-000000000000'
$env:M365_CERT_THUMBPRINT = '0000000000000000000000000000000000000000'
Connect-M365Admin -Service Azure
Enter fullscreen mode Exit fullscreen mode

Run a report, check the CSV, then call Disconnect-M365Admin when you are done. The README lists the modules and roles each script needs.

More toolkits

I have nine more free toolkits that use the same pattern: graph-powershell7-admin-toolkit, entra-id-powershell-toolkit, exchange-online-powershell-toolkit, sharepoint-online-powershell-toolkit, onedrive-powershell-toolkit, teams-powershell-toolkit, intune-powershell-toolkit, defender-xdr-powershell-toolkit, purview-compliance-toolkit.

You can find them all on my GitHub profile.

Feedback welcome

Issues, ideas and pull requests are welcome. What would you add? Tell me in the comments.

Top comments (0)