Use Unified Sign-In logs in Advanced Hunting

One thing that always makes analyzing Sign-In logs for Entra ID (Azure AD) users a bit complicated is the different types of Sign-In logs available.

For user accounts “Interactive user sign-ins” as well as “Non-interactive user sign-ins” are where to look. And don’t get me wrong, I love that we have the non-interactive logs available. They are super important and the separation is correct.

But when working with those logs in the Azure portal is get’s complicated fast. Filters get removed when switching from one view to the other, non-interactive logins are grouped…

KQL to the rescue

In KQL you also have two different tables for the different Sign-in logs.

• SigninLogs
• AADNonInteractiveUserSignInLogs

But in KQL you can join (union) multiple tables to one table. The only tricky part is, that Microsoft decided to change the column type for some of those columns and this results in some columns named either NAME _string or NAME_dynamic.

This is where my KQL query comes in handy. It joines the two tables as one, renames the column to the original name and converts all to the same data type. And when you save it as a function, you can use it as if it where a built-in table.

How-to

Copy the following KQL query to a new query window and then…

union isfuzzy=true SigninLogs, AADNonInteractiveUserSignInLogs
// Rename all columns named _dynamic to normalize the column names
| extend ConditionalAccessPolicies = iff(isempty( ConditionalAccessPolicies_dynamic ), todynamic(ConditionalAccessPolicies_string), ConditionalAccessPolicies_dynamic)
| extend Status = iff(isempty( Status_dynamic ), todynamic(Status_string), Status_dynamic)
| extend MfaDetail = iff(isempty( MfaDetail_dynamic ), todynamic(MfaDetail_string), MfaDetail_dynamic)
| extend DeviceDetail = iff(isempty( DeviceDetail_dynamic ), todynamic(DeviceDetail_string), DeviceDetail_dynamic)
| extend LocationDetails = iff(isempty( LocationDetails_dynamic ), todynamic(LocationDetails_string), LocationDetails_dynamic)
// Remove duplicated columns
| project-away *_dynamic, *_string

… select “Save” - “Save as function”.

Give the function a easy to remember name, in my case I will use UnifiedSignInLogs.

Open a new query and switch to functions. You should now see your new functions when expanding “Workspace functions”.

Now you are already good to go. Just use UnifiedSignInLogs like you would use a table name. Everything you know, like Intellisense and autocomplete will work as expected.

UnifiedSignInLogs
| where UserPrincipalName =~ "fabian@bader.cloud"