Inhalt

Use Unified Sign-In logs in Advanced Hunting

One thing that always makes analyzing Sign-In logs for 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…

/unified-sign-logs-advanced-hunting/images/AzurePortal.png
Azure portal view of Sign-ins

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.

/unified-sign-logs-advanced-hunting/images/DynamicVsString.png
Different column types result in indifferent naming

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 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”.

/unified-sign-logs-advanced-hunting/images/SaveAsFunction.png
Save as function

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

/unified-sign-logs-advanced-hunting/images/SaveAsFunctionName.png
Name your function

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

/unified-sign-logs-advanced-hunting/images/SavedFunction.png
Saved function in shown in the workspace

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"

/unified-sign-logs-advanced-hunting/images/IntelliSense.png
Query sing-in data like always, but better.