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?

 

      • Chris Boehm's avatar
        Chris Boehm
        Former Employee
        Apr 12, 2019

        Valon_Kolica 

        mvanhorenbeeck 

         

        Michael, 

         

        It appears your fix works because your IN statement is actually a NOT IN (!in) statement. So when you add the "," you're allowing the query to now search for those errors codes in which it was ignoring before. 

         

         

        When you're looking at the return data and if it has commas, normally this is a client side feature to make the data more readable. 

         

        Hope this helps,

         

        Thanks!

Resources