Identity Mfamethodspivottable

KQL detection rule by reprise99 · MIT

Create a pivot table of all non password authentication methods by user. This is useful to migrate users from less secure methods like text message to more secure methods.

//Create a pivot table of all non password authentication methods by user. This is useful to migrate users from less secure methods like text message to more secure methods.

//Data connector required for this query - Azure Active Directory - Signin Logs

let isGUID = "[0-9a-z]{8}-[0-9a-z]{4}-[0-9a-z]{4}-[0-9a-z]{4}-[0-9a-z]{12}";
SigninLogs
| where TimeGenerated > ago(30d)
| mv-expand todynamic(AuthenticationDetails)
| extend ['Authentication Method'] = tostring(AuthenticationDetails.authenticationMethod)
//Exclude previously satisifed, passwords and other data and any UserPrincipalName that comes through as a guid
| where ['Authentication Method'] !in ("Previously satisfied", "Password", "Other")
    and isnotempty(['Authentication Method'])
    and not(UserPrincipalName matches regex isGUID)
//Create pivot table of each method and the count by user
| evaluate pivot(['Authentication Method'], count(), UserPrincipalName)
| sort by UserPrincipalName asc

[[ currentRule.title && currentRule.title.length > 60 ? currentRule.title.slice(0, 60) + '…' : currentRule.title ]]

3063a1e8-7122-4a04-b96e-e79334cf4351

Propose edits, view related rules, and engage with the community through comments.

Executable code, not a declarative rule

This submission is arbitrary [[ currentRule.format ? currentRule.format.toUpperCase() : '' ]] code, not a declarative detection rule — review it as you would any script before running it, and note that it cannot be safely aggregated or auto-executed alongside YARA/Sigma/Suricata/CRS rules.

Rejected pattern present

[[ reason ]]

What does this mean?

Format & documentation suggestions

Not a security issue — this rule is valid and was accepted as-is. These are optional fields/conventions its own format recommends, mainly affecting its quality score and how well it documents itself for other reviewers.

[[ reason ]]

What do these messages mean?

[[ currentRule.title && currentRule.title.length > 60 ? currentRule.title.slice(0, 60) + '…' : currentRule.title ]]

[[ currentRule.title ]]

Community Discussion