Options: Draft | InProgress | Stable | Deprecated
Entra ID Shared Mailbox Accounts
Query
let Lookback = 30d;
let TargetUPNs = datatable (TargetUPN:string)
[
"[email protected]",
"[email protected]",
"[email protected]",
"[email protected]"
];
let AccountInfo =
IdentityAccountInfo
| where TimeGenerated >= ago(Lookback)
| summarize arg_max(TimeGenerated, *) by IdentityId
| extend JoinUPN = tolower(AccountUpn);
let IdentityDetails =
IdentityInfo
| where Timestamp >= ago(Lookback)
| summarize arg_max(Timestamp, *) by IdentityId;
let SignInActivity =
SigninLogs
| where TimeGenerated >= ago(Lookback)
| extend JoinUPN = tolower(UserPrincipalName)
| extend SignInSuccessful = tostring(ResultType) == "0"
| summarize
SignInAttempts = count(),
SuccessfulSignIns = countif(SignInSuccessful),
FailedSignIns = countif(not(SignInSuccessful)),
LastSignIn = max(TimeGenerated),
LastSuccessfulSignIn = maxif(TimeGenerated, SignInSuccessful),
LastFailedSignIn = maxif(TimeGenerated, not(SignInSuccessful)),
SignInDetails = make_set(
bag_pack(
"TimeGenerated", TimeGenerated,
"Successful", SignInSuccessful,
"Application", AppDisplayName,
"IPAddress", IPAddress,
"ResultType", ResultType,
"ResultDescription", ResultDescription,
"Location", Location,
"ConditionalAccessStatus", ConditionalAccessStatus
),
1000
)
by JoinUPN;
TargetUPNs
| extend JoinUPN = tolower(TargetUPN)
| join kind=leftouter (
AccountInfo
) on JoinUPN
| join kind=leftouter (
IdentityDetails
) on IdentityId
| join kind=leftouter (
SignInActivity
) on JoinUPN
| extend
Status = iff(isempty(AccountUpn), "Not Found", "Found"),
SignInAttempts = coalesce(SignInAttempts, 0),
SuccessfulSignIns = coalesce(SuccessfulSignIns, 0),
FailedSignIns = coalesce(FailedSignIns, 0),
SignInDetails = coalesce(SignInDetails, dynamic([]))
| extend
HasSignInAttempts = SignInAttempts > 0,
HasSuccessfulSignIn = SuccessfulSignIns > 0
| project
TargetUPN,
Status,
AccountUpn,
DisplayName,
IsAccountEnabled,
AccountStatus,
HasSignInAttempts,
HasSuccessfulSignIn,
SignInAttempts,
SuccessfulSignIns,
FailedSignIns,
LastSignIn,
LastSuccessfulSignIn,
LastFailedSignIn,
SignInDetails
| order by Status asc, TargetUPN ascAbout this query
Explanation
This KQL query is designed to audit Microsoft Entra ID accounts linked to Exchange Online shared mailboxes to ensure they are blocked from direct sign-in, as recommended by Microsoft. Here's a simplified summary of what the query does:
-
Purpose: The query checks if shared mailbox accounts are blocked from direct sign-in. This is important because if these accounts are not blocked and their credentials are compromised, attackers could misuse them.
-
Data Sources: It uses data from three tables:
IdentityAccountInfo: Provides account information.IdentityInfo: Offers additional identity details.SigninLogs: Contains sign-in activity logs.
-
Process:
- The query looks at a list of user principal names (UPNs) for shared mailboxes.
- It checks the account status and sign-in activity over the past 30 days.
- It reports whether the account is found, if it's enabled, and details about any sign-in attempts (successful or failed).
-
Output: The query outputs a table showing:
- The UPN of each shared mailbox.
- Whether the account was found in the records.
- If the account is enabled.
- The number of sign-in attempts, both successful and failed.
- Details of the last sign-in activities.
-
Risk: If a shared mailbox account is enabled, it poses a risk if compromised. The query helps identify such accounts for further investigation and action.
-
Instructions: Before running the query, users need to gather the UPNs of shared mailbox accounts and replace sample entries in the query. They can also adjust the lookback period if their data retention allows for longer analysis.
Overall, this query helps ensure shared mailbox accounts are configured securely by auditing their sign-in settings and activity.