Microsoft Azure Active Directory differentiates between different sign-in types when a user authenticates. For example: Azure Active Directory differentiates between an “interactive” sign-in request and a “non-interactive” sign-in request when a user logs in.

An “interactive sign-in request” happens when user authenticates with a username and password and optionally a multi-factor authentication token. Another way of saying this is that an interactive sign-in happens if a user logs in by using a keyboard “interactively”.

A “non-interactive sign-in request” happens when a user authenticates with a saved or cached credential from a previously authenticated session. For example: a browser session that was previously authenticated interactively and is now authenticated because of the cached credential. In this case the user is already authenticated and does not have to provide a username and password interactively.

When you connect Azure Active Directory sign-in logs to Microsoft Sentinel with the “Azure Active Directory Sentinel connector” the interactive sign-in logs and non-interactive sign-in logs are stored in different tables. For interactive sign-ins this table is called “SigninLogs” and for non-interactive sign-ins this table is called the “AADNonInteractiveUserSignInLogs” table.

Why is this Important?

Knowing that Azure Active Directory differentiates between sign-in types and that Microsoft Sentinel stores the sign-in logs in different tables is important when investigating sign-in related incidents from products like “Azure Active Directory Identity Protection” because any one table does not tell the whole story. Querying both tables individually and comparing the results gives a more complete picture but can become annoying rather quickly if tasked with investigating large amounts of incidents on a daily basis.

Querying the SigninLogs Table

For example you can query the “SigninLogs” table with the following KQL query which by default will give you the sign-in events of the last 24 hours. On line two we sort the results by “TimeGenerated” in descending order.

1
2

SigninLogs
| sort by TimeGenerated desc

The output will look something like the screenshot below. The results are cumbersome to navigate and are almost impossible to investigate or analyze quickly.

Querying the AADNonInteractiveUserSignInLogs Table

Alternatively you can query the “AADNonInteractiveUserSignInLogs” table in the same way with the following KQL query.

1
2

AADNonInteractiveUserSignInLogs
| sort by TimeGenerated desc

The new query will overwrite your old results if ran from the same tab. On top of that the output of this query is equally cumbersome to navigate, investigate and analyze. While you are able to open multiple tabs to run multiple queries and compare results there is an easier way.

Querying both Tables in Microsoft Sentinel

Querying both tables at the same time gives a more complete picture of the sign-in history and is easy with KQL by using the “union” operator. While still cumbersome to navigate, investigate and analyze the union operator does combine the results of both tables together in a single output.

1
2

union SigninLogs, AADNonInteractiveUserSignInLogs
| sort by TimeGenerated desc

As you can see the union operator joined both tables together and the results for both are displayed in the same output. You can distinguish which result comes from what table by looking at the “Category” column.

Projecting Useful Columns

Now we know how to combine both tables but the current output is still not very friendly to navigate, investigate or analyze. With the “project” operator in the following KQL query we can select the columns we want to see and display some useful information to make navigation, investigation and analysis more pleasant.

1
2
3

union SigninLogs, AADNonInteractiveUserSignInLogs
| project TimeGenerated, UserPrincipalName, IsInteractive, ResultType, IPAddress, Location, AppDisplayName, ClientAppUsed, UserAgent
| sort by TimeGenerated desc

With the query above we can quickly see if the sign-in is interactive or not, see the “ResultType” of the sign-in, in this case 0 which is successful. We can also see the “IP address” used to sign-in as wel as the “location” the user logged in from and “application” that the user logged into.

Projecting Additional Columns

Both the SigninLogs and AADNonInteractiveUserSignInLogs table have columns with additional information that can be beneficial while investigating or analyzing sign-in related incidents. The “DeviceDetail” column for example: holds information about the “deviceId”, “operating system” and “browser”.

With the KQL query below we will query the “SigninLogs” table and parse the “DeviceDetail” column with the “extend” operator. We then “project” some useful information from this column such as the Operating System and Browser used when the sign-in took place.

1
2
3
4

SigninLogs
| extend DeviceDetail
| project TimeGenerated, UserPrincipalName, IsInteractive, DeviceDetail.operatingSystem, DeviceDetail.browser
| sort by TimeGenerated desc

However when we try to perform the same query while joining the tables together with the “union” operator we get the following error.

1
2
3
4

union SigninLogs, AADNonInteractiveUserSignInLogs
| extend DeviceDetail
| project TimeGenerated, UserPrincipalName, IsInteractive, DeviceDetail.operatingSystem, DeviceDetail.browser
| sort by TimeGenerated desc

Querying Additional Columns from both Tables

When we look at the KQL code, specifically line two where we extend the DeviceDetail column there seems to be an error. Looking at code completion for the “DeviceDetail” column it becomes clear why. Both tables store the data in the “DeviceDetail” column in a different way. Looking at the documentation of the SigninLogs table the data in the DeviceDetail column is stored as a “dynamic type” while the AADNonInteractiveUserSignInLogs table stores the data in the DeviceDetail column as a “string type”.

Projecting both DeviceDetail Columns with Iff

Now we known that both columns store the data in a different format and we can not join both tables together with a the “union” operator if we do not handle this. To handle columns with a different data type in the same KQL query the “iff” function can be used.

In the query below we will leverage the “iff” function to evaluate if the “DeviceDetail_dynamic” column is empty or not with a “true” or “false” statement. If the column is not empty the statement will evaluate as “false” and the column exists. If however, the column is empty and does not exist it evaluates as “true” and the query will parse the “DeviceDetail_static” column instead.

1
2
3
4

union SigninLogs, AADNonInteractiveUserSignInLogs
| extend DeviceDetail = iff(isempty(DeviceDetail_dynamic) == true, parse_json(DeviceDetail_string), DeviceDetail_dynamic)
| project TimeGenerated, UserPrincipalName, IsInteractive, DeviceDetail.operatingSystem, DeviceDetail.browser
| sort by TimeGenerated desc

As can be seen in the screenshot below the “DeviceDetail” column is parsed for both tables and displays the Operating System and Browser used when the sign-in took place.

Completing the Query to do Something Useful

Now that we know how to handle the “DeviceDetail” column with different data types we can make a more optimized and useful query. In the following query we will join both tables together with the “union” operator and parse several columns with useful information to aid in investigation and analysis. The columns used in this query are:

• DeviceDetail
• LocationDetails
• MfaDetail

On top of adding useful information from the above columns we will add two variables “upn” to target the sign-in logs of a single user account and “timeAgo” which is set to query the sign-in logs of the past seven days. The code is commented for convenience.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19

// Define the variable for the UserPrincipalName of the user account to investigate
let upn = "user@example.com"; // The UserPrincipalName of the user account. Example: user@example.com
// Define the variable for the timespan
let timeAgo = ago(7d); // The time to look back. Example: 7d, 7h
// Join both tables together with the union operator
union SigninLogs, AADNonInteractiveUserSignInLogs
| where TimeGenerated > timeAgo
| where OperationName == "Sign-in activity" // Filter on Sign-in activities
| where Category == "SignInLogs" or Category == "NonInteractiveUserSignInLogs" // Filter the category of logs
| where UserPrincipalName == upn // Filter on UserPrincipalName
// Extend and handle the DeviceDetail column
| extend DeviceDetail = iff(isempty(DeviceDetail_dynamic) == true, parse_json(DeviceDetail_string), DeviceDetail_dynamic)
// Extend and handle the LocationDetails column
| extend LocationDetails = iff(isempty(LocationDetails_dynamic) == true, parse_json(LocationDetails_string), LocationDetails_dynamic)
// Extend and handle the MfaDetail column
| extend MfaDetail = iff(isempty(MfaDetail_dynamic) == true, parse_json(MfaDetail_string), MfaDetail_dynamic)
// Project useful information for investigation and analysis
| project TimeGenerated, UserPrincipalName, AuthenticationRequirement, MfaDetail.authMethod, IsInteractive, ResultType, IPAddress, LocationDetails.countryOrRegion, LocationDetails.city, AppDisplayName, DeviceDetail.operatingSystem, ClientAppUsed, DeviceDetail.browser, UserAgent
| sort by TimeGenerated desc

This query comes in handy when investigating sign-in related incidents and will give some useful information to work with for our analysis.

Conclusion

KQL in essence is a an easy language to start with especially if you have a background in either PowerShell or SQL. Knowing how to query multiple tables, parse different data types and project relevant information is a useful skill to have and aids in daily investigation and analysis.