Forum Discussion
Failed Sign-in queries not working
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?
JulianGonzalez any update on this...?
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!
Chris Boehm I figured creating a short video wouldn't hurt. In the video you'll see what I'm talking about. I'm opening the built-in dashboard for Azure AD Sign-Ins and notice some reports aren't working. I then just edit the built-in query and add the commas to get it back up-and-running. Nothing else.
The question isn't why my workaround works. But rather 1) why has this happened with this dashboard and 2) why do I need to add commas to my queries in this tenant, but not in other tenants (where the built-in reports just work fine)? Could this be due to region or language settings? Although I would expect those to be transparent to the backend...
Thanks,
Michael
