Forum Discussion

mvanhorenbeeck's avatar
mvanhorenbeeck
MVP
Mar 14, 2019

Failed Sign-in queries not working

Hi all,   Today, I noticed that the queries to display failed sign-ins are not working. This is (one of) the queries used in the default Azure AD Sign-Ins dashboard:   SigninLogs | extend...
JulianGonzalez's avatar
JulianGonzalez
Icon for Microsoft rankMicrosoft
Mar 16, 2019

Hi mvanhorenbeeck,

 

I couldn't find which query in which dashboard this query was associated with.  I clicked through all of the queries on the AD dashboard and didn't see this query in any of them.  Do you mind giving me more information so I can find it?

 

Also, the query you showed is looking for error codes that do not appear in the list.  (!in)  I couldn't find any problems with the query but let me know if I am missing something.

 

In the query you showed, the "ErrorCode" column is an extended column displaying a dynamic data type derived from an integer data type ("errorCode").  Both columns are referring to the same data, just displayed differently.  I put a query below to demonstrate the difference.  I am not sure if this sheds light on this situation, so let me know if this doesn't address your concern.

 

SigninLogs
| where TimeGenerated >= ago(365d)
| extend ErrorCode = Status.errorCode
| extend FailureReason = Status.failureReason
| evaluate bag_unpack(Status)
| project ErrorCode, errorCode, errorString = tostring(errorCode), errorInt = toint(errorCode)
| summarize by tostring(ErrorCode), errorCode, errorString, errorInt
  • mvanhorenbeeck's avatar
    mvanhorenbeeck
    MVP
    Apr 02, 2019

    JulianGonzalez sorry for the belated reply. Because of travel (MVP summit) and some other engagements, I have not had much time to look into the Tech Community... 

     

    The query/queries I am referring to are part of the built-in Azure AD Sign-in logs dashboard (see screenshot below).

    When looking into the query behind any of these given 'reports', the issue is the same. To view the query, I click "edit query".

     

    For example, the following is the query for the "Sign-in errors"-report:

    SigninLogs | extend ErrorCode = Status.errorCode | extend FailureReason = Status.failureReason | where ErrorCode !in ("0","5048","50140", "51006", "50059", "65001", "52004", "50055", "50144","50072", "50074", "16000","16001", "16003", "50127", "50125", "50129","50143", "81010", "81014", "81012") | summarize count() by bin(TimeGenerated, 24h)

     

    This query returns no results. However, when I update the query to the following, it works as expected:

    SigninLogs | extend ErrorCode = Status.errorCode | extend FailureReason = Status.failureReason | where ErrorCode !in ("0","5,048","50,140", "51,006", "50,059", "65,001", "52,004", "50,055", "50,144","50,072", "50,074", "16,000","16,001", "16,003", "50,127", "50,125", "50,129","50,143", "81,010", "81,014", "81,012") | summarize count() by bin(TimeGenerated, 24h)

     

    Note that I had to add the commas manually. Result in the screenshot below:

     

     

    I hope this clarifies things?

     

Resources