For some time now, Azure Active Directory (AAD) has been able to export sign-in and audit log data. The portal lets you export to the three Azure-based data sinks – Blob Storage, Event Hub, and Log Analytics – each of which is designed for different use cases. Exporting the logs is not only exciting for the security information and event management (SIEM) team dealing with security analyses and incidents, but also for AAD admins. The retention of data within AAD is limited to 30 days, so exporting protects against the loss of historical data. Additionally, identity administrators have their own complex requirements and questions regarding audit and logon. In this article, I show you how to create your own dashboards in the AAD portal with the data from exported logs.

Checking the Export Settings

Although SIEM is designed to be the data sink for analysts and security officers, the dashboards, alerts, and insights are not always suitable for those responsible for identity, single sign-on, application integration, and Office 365. Often, identity admins do not receive their own dashboards in the SIEM system. In most cases, they do not have enough time, changes are too costly, or transparency is lacking about what kind of data is available.

That situation should now be a thing of the past, because the log export functions in AAD, along with Log Analytics, allow flexibility and granular insights. The system scans the acquired logs, supporting ad hoc searches and the creation of custom dashboards. The SQL-style search language makes it easy to find what you are looking for, so you have few excuses for not investigating the features [1].

Before you use the data and create colorful dashboards, though, it's worth taking a look at the export settings in AAD, because data can only be found if it is exported. On the AAD portal in Azure Active Directory | Sign-ins you will find the familiar overview of all sign-ins to your Azure directory. In the top menu below Export Data Settings are the previous settings for log export. If you see one or more export settings, check where the logfiles flow. In the best case, you will already see an entry for a Log Analytics workspace that you can use. If nothing is entered, and you do not export the sign-in and audit logs, the data will be lost after 30 days.

To create a new export, use Diagnostics settings | Add diagnostic setting . In the Diagnostics settings blade, select SignInLogs and AuditLogs to use both data sources (Figure 1). Here, you need at least to select Send to Log Analytics and create a new workspace. After a few minutes, the first data should arrive at the workspace.

Figure 1: Configuring how the entries for audit and login histories are stored in the Diagnostics settings window.

While this is happening, you should familiarize yourself with the fields and data that are available for searches and dashboards. Switch to Azure Active Directory | Logs and then select the Log Analytics workspace you specified for the export. The portal loads a search editor with a tree view on the left, which displays all the tables known to the workspace, along with their layouts in its fields. If you have already acquired some data, you should find SignInLogs and AuditLogs as tables in the Log Management section.

Double-clicking on the relevant database table changes the query editor's focus and immediately directs queries to the newly selected data record. In the upper menu are settings that let you to restrict the time window of the search. If you are only interested in logins from the last 24 hours, focus the search on that period; then, you can define all other filters in the query editor. If you have created a particularly sophisticated search, you can save it temporarily. In this way, it can be found again, refined, and transferred to a dashboard at a later date.

Finding and Analyzing Sign-ins

Double-clicking on the SignInLogs or AuditLogs tables focuses the query editor on the respective table (Figure 2). Next to the Run button is a drop-down menu that lets you set the search time window more precisely. Predefined values are possible, as is a Custom setting. Of course, you can also define the search time window in the search with the Kusto query language, but this menu is faster for now.

Figure 2: Log Analytics has detailed information about sign-ins to AAD.

The system loads all the relevant entries when you click Run . In the results field, you can open single entries with the arrow keys for an overview of all the fields and information. All of these fields can be used for your search query. If you mouse over a field (e.g., Identity ), you will see a plus and a minus sign next to the field name that lets you transfer the field to the query editor simply by clicking.

As an example, start with SignInLogs and enter the last seven days as the period. To narrow down the first request a little, filter for a specific employee with Kusto:

SigninLogs
| where Identity contains "Jenny"

This code loads all login entries for employees whose name contains Jenny . To be more specific, use UserPrincipalName:

SigninLogs
| where UserPrincipalName == "jenny@frickelsoft.net"

All sign-ins for Jenny in the last 60 days are found by adding to the search query:

SigninLogs
| where UserPrincipalName == "jenny@frickelsoft.net"
| where TimeGenerated > ago(60d)

When AAD issues access tokens to users, it logs the associated service and how users requested the token. Details, including data from Conditional Access , if available, can also be found in the sign-in logs:

SigninLogs
| where AppDisplayName == "Office 365 Exchange Online"
| where ClientAppUsed == "Browser"
| summarize requests = count() by OriginalRequestId, UserPrincipalName

The last example changes the output of the data. Instead of a flat list, as before, it aggregates the data and lets the system do the counting. The last command, summarize, summarizes how often and who has called the mail services in the Office 365 cloud from a browser.

Finding Changes to Objects

The audit log can be searched like the logins. The query language is identical – only the table AuditLogs and its field layout differ from SignInLogs . It is worth taking a brief look at the layout in the left-hand tree view before moving on to the queries. The tree view not only shows all the tables for the acquired logs, but their data structure, including available fields and data types. Armed with this knowledge, you will find it far easier to create your initial queries.

For an overview of all group changes where new members have been added to AAD groups, enter:

AuditLogs
| where Category == "GroupManagement"
| where ActivityDisplayName == "Add member to group"
| project Identity, TargetResources[0].modifiedProperties[1].new-Value, TargetResources[0].userPrincipalName

The project command displays only those columns that are explicitly listed in the comma-separated list – that is, only what is really important. Formatting is also possible with project.

You can specifically request a group with its unique object ID in AAD and display all the changes made in the past few days, including not only membership changes, but also changes to the group name and group type or new or changed group owners:

AuditLogs
| where TargetResources contains "a0fdc91a-a1b2-4ec5-b352-03bda610be0e"
| where TimeGenerated > ago(7d)

If you are looking for all operations that have to do with invitations and how they are accepted by external entities, you can sort by timestamp in UserManagement:

AuditLogs
| where Category == "UserManagement"
| where OperationName == "Invite external user" or OperationName == "Redeem external user invite"
| project ActivityDateTime, OperationName, TargetResources[0].displayName
| order by ActivityDateTime