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 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)
 
As you can see, the query is looking for the ErrorCodes above. However, when manually querying the logs, the data is formatted differently. For example, 50140 is "50,140", 65001 is "65,001" etc.
When I (manually) update the query (below), it works again (stripped for brevity):
 
...| where ErrorCode !in ("0","5048","50,140", "51,006", "50,059", ....
 
Can someone look into the data format and/or default queries for the dashboard?
 
Thanks,
 
Michael
  • 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