Edit

Share via

Queries for the SigninLogs table

  • Article

For information on using these queries in the Azure portal, see Log Analytics tutorial. For the REST API, see Query.

All SiginLogs events

All Azure signin events.

SigninLogs
| project UserDisplayName, Identity,UserPrincipalName,  AppDisplayName, AppId, ResourceDisplayName

Resources accessed by user

Lists the resources accessed for a specific user.

// Set v_Users_UPN with the UPN of the user of interest
let v_Users_UPN = "osotnoc@contoso.com";
SigninLogs
| where UserPrincipalName == v_Users_UPN
| summarize Count=count()  by ResourceDisplayName, AppDisplayName

User count per Resource

Distinct count if users by resource.

SigninLogs
| project UserDisplayName, Identity,UserPrincipalName,  AppDisplayName, AppId, ResourceDisplayName
| summarize UserCount=dcount(UserPrincipalName) by ResourceDisplayName

User count per Application

Distinct count of users by application.

SigninLogs
| project UserDisplayName, Identity,UserPrincipalName,  AppDisplayName, AppId, ResourceDisplayName
| summarize UserCount=dcount(UserPrincipalName) by AppDisplayName

Failed Signin reasons

The query list the main reasons for sign in failures.

SigninLogs
| where ResultType != 0
| summarize Count=count() by ResultDescription, ResultType
| sort by Count desc nulls last

Failed MFA challenge

Highlights sign in failures caused by failed MFA challenge.

SigninLogs
| where ResultType == 50074
| project UserDisplayName, Identity,UserPrincipalName, ResultDescription,  AppDisplayName, AppId, ResourceDisplayName
| summarize FailureCount=count(), FailedResources=dcount(ResourceDisplayName), ResultDescription=any(ResultDescription) by UserDisplayName

Failed App tried silent signin

Failed silent app signin attempts.

SigninLogs
| where ResultType == 50058
| project UserDisplayName, Identity,UserPrincipalName, ResultDescription,  AppDisplayName, AppId, ResourceDisplayName
| summarize FailureCount=count(), FailedResources=dcount(ResourceDisplayName), ResultDescription=any(ResultDescription) by UserDisplayName

Failed login Count

Resources with most failed log in attempts.

SigninLogs
| where ResultType !=0
| summarize FailedLoginCount=count() by ResourceDisplayName
| sort by FailedLoginCount desc nulls last

Signin Locations

Failed and successful sig ins by source location.

SigninLogs
| summarize Successful=countif(ResultType==0), Failed=countif(ResultType!=0) by Location

Logins To Resource

Lists API sign ins.

SigninLogs
| where ResourceDisplayName == "Windows Azure Service Management API"
| project TimeGenerated, UserDisplayName, Identity,UserPrincipalName,  AppDisplayName, Success=iff(ResultType==0, "Success", "Fail")