add tier combined weight on Privileged Account Authentication Method Audit

[rolandogaleazzi-mikron]

Hi benscha,
looking at your code I had an idea to add also a combined weight for the tiers, this is the resulting vibe code ;-)
feel free to make it available

let Lookback = 30d;
let PrivilegedRoles = dynamic([
"Global Administrator", "Privileged Role Administrator", "Privileged Authentication Administrator",
"Security Administrator", "Conditional Access Administrator", "Application Administrator",
"Cloud Application Administrator", "Hybrid Identity Administrator", "Partner Tier2 Support",
"Authentication Administrator", "Authentication Policy Administrator", "Authentication Extensibility Administrator",
"User Administrator", "Helpdesk Administrator", "Password Administrator", "Directory Writers",
"Directory Synchronization Accounts", "Domain Name Administrator", "External Identity Provider Administrator",
"Lifecycle Workflows Administrator", "Groups Administrator", "Identity Governance Administrator",
"Exchange Administrator", "SharePoint Administrator", "Teams Administrator", "Teams Telephony Administrator",
"Skype for Business Administrator", "Intune Administrator", "Compliance Administrator", "Security Operator",
"Power Platform Administrator", "Dynamics 365 Administrator", "AI Administrator",
"Global Secure Access Administrator", "Attribute Assignment Administrator", "Attribute Provisioning Administrator",
"B2C IEF Keyset Administrator", "Cloud App Security Administrator", "External ID User Flow Administrator",
"Partner Tier1 Support", "Windows 365 Administrator", "Microsoft 365 Backup Administrator",
"Microsoft 365 Migration Administrator", "Yammer Administrator", "Knowledge Administrator", "Billing Administrator",
"Global Reader", "Security Reader", "Attribute Provisioning Reader", "Application Developer",
"Cloud Device Administrator", "Azure AD Joined Device Local Administrator"
]);
// ===== Tier weighting table (adjust weights to your org's risk appetite) =====
let RoleTiers = datatable(RoleName:string, Tier:string, TierWeight:int)
[
// ----- Tier 0: Critical / Tenant-Control -----
"Global Administrator", "Tier 0", 100,
"Privileged Role Administrator", "Tier 0", 100,
"Privileged Authentication Administrator", "Tier 0", 100,
"Security Administrator", "Tier 0", 100,
"Conditional Access Administrator", "Tier 0", 100,
"Application Administrator", "Tier 0", 100,
"Cloud Application Administrator", "Tier 0", 100,
"Hybrid Identity Administrator", "Tier 0", 100,
"Partner Tier2 Support", "Tier 0", 100,
// ----- Tier 1: High / Service-Control-Write -----
"Authentication Administrator", "Tier 1", 50,
"Authentication Policy Administrator", "Tier 1", 50,
"Authentication Extensibility Administrator","Tier 1", 50,
"User Administrator", "Tier 1", 50,
"Helpdesk Administrator", "Tier 1", 50,
"Password Administrator", "Tier 1", 50,
"Directory Writers", "Tier 1", 50,
"Directory Synchronization Accounts", "Tier 1", 50,
"Domain Name Administrator", "Tier 1", 50,
"External Identity Provider Administrator","Tier 1", 50,
"Lifecycle Workflows Administrator", "Tier 1", 50,
"Groups Administrator", "Tier 1", 50,
"Identity Governance Administrator", "Tier 1", 50,
"Exchange Administrator", "Tier 1", 50,
"SharePoint Administrator", "Tier 1", 50,
"Teams Administrator", "Tier 1", 50,
"Teams Telephony Administrator", "Tier 1", 50,
"Skype for Business Administrator", "Tier 1", 50,
"Intune Administrator", "Tier 1", 50,
"Compliance Administrator", "Tier 1", 50,
"Security Operator", "Tier 1", 50,
"Power Platform Administrator", "Tier 1", 50,
"Dynamics 365 Administrator", "Tier 1", 50,
"AI Administrator", "Tier 1", 50,
"Global Secure Access Administrator", "Tier 1", 50,
"Attribute Assignment Administrator", "Tier 1", 50,
"Attribute Provisioning Administrator", "Tier 1", 50,
"B2C IEF Keyset Administrator", "Tier 1", 50,
"Cloud App Security Administrator", "Tier 1", 50,
"External ID User Flow Administrator", "Tier 1", 50,
"Partner Tier1 Support", "Tier 1", 50,
"Windows 365 Administrator", "Tier 1", 50,
"Microsoft 365 Backup Administrator", "Tier 1", 50,
"Microsoft 365 Migration Administrator", "Tier 1", 50,
"Yammer Administrator", "Tier 1", 50,
"Knowledge Administrator", "Tier 1", 50,
"Billing Administrator", "Tier 1", 50,
// ----- Tier 2: Medium / Read-only-Restricted -----
"Global Reader", "Tier 2", 10,
"Security Reader", "Tier 2", 10,
"Attribute Provisioning Reader", "Tier 2", 10,
"Application Developer", "Tier 2", 10,
"Cloud Device Administrator", "Tier 2", 10,
"Azure AD Joined Device Local Administrator","Tier 2", 10
];
let PrivilegedUsers =
IdentityInfo
| where TimeGenerated >= ago(Lookback)
| where AssignedRoles has_any (PrivilegedRoles)
| summarize
AssignedRoles = make_set(AssignedRoles),
Department = take_any(Department),
JobTitle = take_any(JobTitle),
AccountEnabled = take_any(IsAccountEnabled)
by AccountUpn;
// ===== Resolve highest-privilege tier per user =====
let UserTier =
PrivilegedUsers
| mv-expand Role = AssignedRoles to typeof(string)
| join kind=inner RoleTiers on $left.Role == $right.RoleName
| summarize HighestTierWeight = max(TierWeight), TiersHeld = make_set(Tier) by AccountUpn
| extend TierLabel = case(
HighestTierWeight == 100, "🔴 Tier 0 (Critical)",
HighestTierWeight == 50, "🟠 Tier 1 (High)",
HighestTierWeight == 10, "🟡 Tier 2 (Medium)",
"⚪ Unclassified"
);
// Count per user + method
let AuthMethodCounts =
SigninLogs
| where TimeGenerated >= ago(Lookback)
| where ResultType == 0
| extend AuthDetails = parse_json(AuthenticationDetails)
| extend AuthMethod = tostring(AuthDetails[0].authenticationMethod)
| extend AuthMethodNorm = case(
AuthMethod == "Password", "🔑 Password",
AuthMethod == "Mobile app notification", "📱 Authenticator (Push)",
AuthMethod == "Mobile app OTP", "📱 Authenticator (OTP)",
AuthMethod == "SMS", "💬 SMS OTP",
AuthMethod == "FIDO2 security key", "🔐 FIDO2 / Passkey",
AuthMethod == "Windows Hello for Business", "🖥️ Windows Hello",
AuthMethod == "Certificate-based authentication", "📜 Certificate (CBA)",
AuthMethod == "Voice call", "📞 Phone Call",
AuthMethod == "Temporary Access Pass", "⏳ Temporary Access Pass",
AuthMethod == "Previously satisfied", "✅ SSO (Previous Session)",
isempty(AuthMethod), "❓ Unknown",
AuthMethod
)
| summarize MethodCount = count() by UserPrincipalName, AuthMethodNorm;
// Build bag per user
let AuthMethodBags =
AuthMethodCounts
| summarize AuthMethodBreakdown = make_bag(bag_pack(AuthMethodNorm, MethodCount))
by UserPrincipalName;
// Aggregate remaining metrics
let AuthEvents =
SigninLogs
| where TimeGenerated >= ago(Lookback)
| where ResultType == 0
| extend AuthDetails = parse_json(AuthenticationDetails)
| extend AuthMethod = tostring(AuthDetails[0].authenticationMethod)
| summarize
TotalSignins = count(),
UniqueAuthMethods = dcount(AuthMethod),
LastSignin = max(TimeGenerated),
UniqueIPs = dcount(IPAddress),
UniqueApps = dcount(AppDisplayName),
MFASuccess = countif(AuthenticationRequirement == "multiFactorAuthentication"),
PasswordOnlySignins = countif(AuthMethod == "Password" and AuthenticationRequirement == "singleFactorAuthentication")
by UserPrincipalName;
// Combine data
PrivilegedUsers
| join kind=leftouter AuthEvents
on $left.AccountUpn == $right.UserPrincipalName
| join kind=leftouter AuthMethodBags
on $left.AccountUpn == $right.UserPrincipalName
| join kind=leftouter UserTier
on $left.AccountUpn == $right.AccountUpn
| extend
SigninCountVal = coalesce(TotalSignins, 0),
MethodCountVal = coalesce(UniqueAuthMethods, 0),
PasswordOnlyVal = coalesce(PasswordOnlySignins, 0),
MFAVal = coalesce(MFASuccess, 0),
TierWeightVal = coalesce(HighestTierWeight, 0)
// ===== Behavioral risk points =====
| extend AuthRiskPoints =
(iif(PasswordOnlyVal > 0, 40, 0)) +
(iif(MFAVal == 0, 25, 0)) +
(iif(MethodCountVal == 1, 20, 0)) +
(iif(SigninCountVal == 0, 30, 0))
// ===== Combined score = privilege criticality + behavior =====
| extend CompositeScore = TierWeightVal + AuthRiskPoints
| extend RiskIndicator = case(
PasswordOnlyVal > 0, "⚠️ Password-only sign-ins present",
SigninCountVal == 0, "🔴 No sign-ins in 30 days",
MethodCountVal == 1, "🟡 Only one auth method active",
"🟢 OK"
)
| extend CriticalityScore = case(
CompositeScore >= 120, "🟥 Critical",
CompositeScore >= 80, "🟧 High",
CompositeScore >= 40, "🟨 Medium",
"🟩 Low"
)
| project
User = AccountUpn,
Roles = AssignedRoles,
TierLabel = coalesce(TierLabel, "⚪ Unclassified"),
AccountActive = AccountEnabled,
SigninCount = SigninCountVal,
UsedMethodsCount = MethodCountVal,
AuthMethodsDetail = AuthMethodBreakdown,
LastSignin = LastSignin,
UniqueIPs = coalesce(UniqueIPs, 0),
UniqueApps = coalesce(UniqueApps, 0),
MFASignins = MFAVal,
PasswordOnlySignins = PasswordOnlyVal,
RiskIndicator,
CompositeScore,
CriticalityScore
| where SigninCount > 0
| sort by CompositeScore desc