Export Trade

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.

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.

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

Creating Your Own Dashboard

The queries you have created so far can be transferred to dashboards, which then have less of an ad hoc character and present a large amount of visual information at a glance. You can create a new workbook by clicking on Workbooks in the Monitoring section of the AAD portal. You will then see an overview of available workbooks. Microsoft has started to publish predefined reports as part of the engine changeover. Of course, you can leverage these as a source of inspiration. The Workbooks process acquires data from Log Analytics and your own requests. Microsoft provides some logs on sign-ins, such as which sign-ins occurred in your tenant, whether legacy authentication was used, and how conditional access influenced this.

You can create a new, custom dashboard with + New or under Quick start. An empty dashboard will be loaded showing only the commands for new elements: Add text, Add query, and some others. Start with Add text, insert the text SignIns with Admin Account, and confirm with Done Editing. Then, create the dashboard by entering a title, the Azure Subscription, and the Resource Group where the Log Analytics workbook should end up. Click Save, and you can continue with the editing. Now insert the following query under your text:

SigninLogs
| where UserPrincipalName == "admin@testtenant.onmicrosoft.com"
| where TimeGenerated > ago(7d)
| summarize count() by TimeGenerated
| render timechart

Run Query gives you a rendered time history showing the logons of the last seven days for the specified User Principal Name (UPN). Size lets you change the size of the graph, and Done Editing terminates draft mode and displays the curve below the text label. In this way, various elements are added to the dashboard. You can arrange the elements with the Up and Down arrow keys in edit mode.

Additionally, a settings menu contains the means, for example, to change the width of the elements on the dashboard. To do this, click on the gear icon at the bottom of the item and select the desired size in Make this item a custom width. You can add a short description for the element's heading in Chart Title. If no data is available, a message in No data message will help. If no chart is rendered, but a list is generated from the data, you can adjust the output columns to display only the relevant data. Once you have formulated the query, Column Settings lets you enable the relevant columns.

Queries previously created on the fly can be transferred to the dashboard without changes in most cases. The data presentation can be adjusted to make the most important findings visible at a glance. To make the previous query, which shows changes to a specific group, far more attractive, add two more lines and display a pie chart instead of a flat list (Figure 3):

AuditLogs
| where TargetResources contains "a0fdc91a-a1b2-4ec5-b352-03bda610be0e"
| where TimeGenerated > ago(7d)
| summarize a = count() by ActivityDisplayName
| render piechart

A trick lets you use the dashboard or individual requests from the dashboard on the AAD portal splash page to store the most important information there. Click on the pin and choose between Pin Workbook and Pin All. The first option creates a link to the dashboard on the splash page, whereas Pin All also displays the individual elements of the dashboard directly on the splash page. You then need to adjust the size and arrangement of the individual elements.

Azure Sentinel as SIEM

Microsoft recently announced a SIEM-in-the-cloud solution named Azure Sentinel that can accept and analyze logs and data from any source. The tool is designed to help IT managers gain insights into previously acquired data without needing to build a complex infrastructure and purchase additional software. The preview version of Sentinel can also provide insights into AAD logs. However, here too, the data must be available in a Log Analytics workspace.

Sentinel already has two predefined AAD dashboards that are designed to supplement your own overviews by helping you search for irregularities or operations in general. When you create an Azure Sentinel workspace, you will find an overview of the installed dashboards under Dashboards. To connect AAD, you first need to specify it as a data source in Data Connectors and then select Azure Active Directory and click on Connect. Back in Dashboards, selecting All reveals two new reports: Azure AD Audit Logs and Azure AD Sign-in Logs. Click View Dashboard to install the dashboards, after which you can display them immediately – provided you have already exported data to AAD.

Conclusions

Whether or not you use a SIEM tool in your company, the Kusto query language that you use for the exported AAD logs can help the directory team, because not all relevant data is always available in SIEM.

Often, "only" the security-relevant data is of interest to SIEM and is then served up to the identity team; however: What about group changes to assignments? How successful is identity provisioning from AAD to a software-as-a-service application? What methods of multifactor authentication identity verification are most commonly used with Exchange Online? These questions can be answered quite easily with data from the logs and presented neatly in a dashboard, which means that the SIEM team does not have to respond constantly to questions about access and changes to the tool.