During Microsoft Ignite in November 2021, Azure Sentinel is now called Microsoft Sentinel.
Microsoft Sentinel is a cloud-native Security Information Event Management (SIEM) and Security Orchestration Automated Response (SOAR) solution. Microsoft Sentinel delivers intelligent security analytics and threat intelligence across the enterprise, providing a single solution for alert detection, threat visibility, proactive hunting, and threat response.
In this article, we will share with you how to monitor sign-in activities and advanced your Azure AD hunting in KQL and Microsoft Sentinel.
In This Article
As a security administrator and engineer, you want to know how your IT environment is doing. The information about your system’s health enables you to assess whether and how you need to respond to potential issues. To support you with this goal, the Azure Active Directory portal gives you access to three activity logs:
- Sign-in – Information about sign-ins and how your resources are used by your users. Check sign-in logs in Azure Active Directory.
- Audit – Information about changes applied to your tenants such as users and group management or updates applied to your tenant’s resources. Check audit logs in Azure Active Directory.
- Provisioning – Activities performed by the provisioning service, such as the creation of a group in ServiceNow or a user imported from Workday. Check provisioning logs in Azure Active Directory.
As you probably know, the data in Azure AD sign-in logs can be quite big. If you’ve 1,000 users or even more, you’ll find you can get millions of events and it can get a little overwhelming.
In this article, we are going to show you some of the ways you can summarize Azure AD data so you can be more efficient in your hunting journey with KQL and Microsoft Sentinel.
To follow this article, you need to have the following:
1) Azure subscription – If you don’t have an Azure subscription, you can create a free one here.
2) Log Analytics workspace – To create a new workspace, follow the instructions here Create a Log Analytics workspace.
3) Microsoft Sentinel – To enable Azure Sentinel at no additional cost on an Azure Monitor Log Analytics workspace for the first 31-days, follow the instructions here.
4) Connect data from Azure Active Directory (Azure AD) to Azure Sentinel. You need to export (send) Azure AD ‘AuditLogs‘ and ‘SignInLogs‘ to Sentinel workspace enabled as shown in the figure below. Audit logs contain information about system activity relating to user and group management, managed applications, and directory activities. The good news is, you can use the Azure AD Free or Office 365 license to export Audit Logs, however, you need to have a valid Azure AD P1 or P2 license if you want to export Sign-in data. If you don’t have a P1 or P2, start a free trial.
5) Your user must be assigned the Microsoft Sentinel Contributor role on the Log Analytics workspace.
6) Your user must be assigned the Global Administrator or Security Administrator roles on the tenant you want to stream the logs from. Always keep in mind and follow the principle of least privilege and carefully assign permissions.
7) Last but not least, your user must have read/write permissions to the Azure AD diagnostic settings in order to be able to see the connection status.
Note: If you don’t have an environment ready, you can still practice the KQL queries and perform Azure AD hunting, you can use the free Log Analytics demo environment here, which includes plenty of sample data that supports the KQL queries that will be shared in this article, you just need any Microsoft account.
Assuming you have all the prerequisites in place, take now the following steps:
Microsoft Sentinel side
Now that we know we have all the capabilities for collecting Azure AD activity logs and sign-in logs, we can monitor, track and detect guest user invitations, suspicious activities, and many other Microsoft Sentinel actions.
If you’re a threat hunter who wants to be proactive about looking for security threats (i.e. Azure AD sign-in or Audit logs), Microsoft Sentinel has powerful hunting search and query tools to hunt for security threats across your organization’s data sources.
In this step, we will use different KQL queries to monitor in real-time Azure AD sign-in logs to be used in different hunting scenarios.
Now you may ask, why do we need to create a Hunting query instead of an Analytic query rule? In fact, you can do both, with a standard analytic rule, the minimum query schedule is 5 minutes or above, and the new NRT query analytic rule is nearly real-time (every minute).
The hunting query is also nearly real-time (live stream). With analytics/NRT rules, you can automate your response and be notified in many different ways, however, with hunting, you will be notified in the Azure portal and you need to respond manually.
For the remainder of this article, we will use both approaches with Hunting to create a live stream session and create an analytic rule. The good news is, when the custom query is created, you can create an analytic rule from the Hunting queries blade directly.
Open Azure Portal and sign in with a user who has Microsoft Sentinel Contributor permissions.
Click All services found in the upper left-hand corner. In the list of resources, type Microsoft Sentinel. As you begin typing, the list filters based on your input.
Click on Microsoft Sentinel and then select the desired Workspace.
From the Sentinel’s sidebar, select Hunting under the Threat management section, then click + New Query as shown in the figure below.
Enter a descriptive Name and Description. In the Custom query section, enter one of the following KQL queries based on the scenario that you are looking for. We will add descriptive details for each KQL query so you can pick and choose.
Advanced Azure AD hunting queries
Please note that you can jump directly into Logs under the General section in Sentinel and run the following queries.
Let’s check first who’s the busiest user, who’s connecting the most to the environment. This can be a good indicator of the busiest authentications for a couple of people.
SigninLogs | summarize UserCount=count()by UserPrincipalName
You can summarize by IP address, you might be interested in where users are connecting from. There are all kinds of ways to summarize this data, and maybe you are interested in when users connect from a new IP address.
SigninLogs | summarize UserCount=count()by IPAddress
Next, we can summarize by authentication requirement and that’s going to tell us whether users are logged on with single-factor or multifactor authentication.
SigninLogs | summarize count() by AuthenticationRequirement
You might think we’ve got MFA enabled everywhere, but maybe we’re not getting as much MFA coverage as we thought. Next, we want to break the authentication requirement down by each application. So if you deploy conditional access policies to protect applications, you can find out which kind of apps are covered and which apps are the least covered with MFA.
So to do that, we’re going to extend the summarize query and use the count if (aggregation function). We start by looking at which app is using Single-Factor and which one is using Multi-Factor. This will give you a good starting point to increase your MFA coverage.
SigninLogs | summarize SingleFactor=countif(AuthenticationRequirement == "singleFactorAuthentication"), MultiFactor=countif(AuthenticationRequirement == "multiFactorAuthentication") by AppDisplayName
Another interesting hunting query is to look at what Azure AD guest users are accessing in your tenant environment. You have a tendency to check guest users that pop up everywhere. You invite them to Microsoft Teams, or you share a document with SharePoint or other apps. You might have also thousands of Azure AD guests users sitting in your environment. When the guest user signs in, it’s actually flagged in the sign-in logs as “Guest“, and when a member user signs in, it’s flagged in the sign-in logs as “Member“.
The following query will show all the apps that our guests accessing versus our members. You might find and expect your guest’s users to be accessing Teams, OneDrive, SharePoint, etc. But maybe you’ve found they are accessing other apps that you’ve not hardened.
SigninLogs | summarize Guest=countif(UserType == "Guest"), Members=countif(UserType == "Member") by AppDisplayName
You probably don’t want guests users accessing unapproved applications by your security department. You’d expect them to access Teams, OneDrive, SharePoint, and maybe even Azure AD identity governance if they’re using access packages. So it’s certainly good to keep an eye on guest users’ app usage. You can do all these KQL queries in advance hunting as well if you have an Azure AD P2 license.
Another cool KQL feature is, there are two kinds of functions called “make_list()” and “make_set()“. If you’re interested in what particular users are doing, or if they’re connecting from lots of IP addresses, Kusto can build your list of data. When you’re making a list by using the list operator, it’s going to count every single IP Address even if some IPs are identical.
The following query is going to look at all Azure AD sign-in logs, and for every user that sign-in is going to retrieve each IP address they signed in from.
SigninLogs | summarize IPAddresses=make_list(IPAddress) by UserPrincipalName
On the other hand, when you’re making a set by using the set operator, it’s going to do a distinct. So you only get each IP address one time, which might be more useful to you.
Because obviously if you sign in 30 times, you probably don’t want the same IP listed 30 times and you’re gonna end up with these massive lists of IP addresses that are kind of hard to make sense of.
SigninLogs | summarize IPAddresses=make_set(IPAddress) by UserPrincipalName
We can extend this query and identify logons from IPv4 addresses not matching IPv4 subnets maintained on an allow list using Watchlist. The allow list is maintained using the built-in template “NetworkAddresses” Watchlist template using the “AAD Allow” tag as shown in the CSV file below.
This query will get all IP Subnets from the Watchlist and then put them in a variable using the let statement. We are doing the same thing for the sign-in logs. Then we are using the ipv4_lookup plugin to look up the IPv4 value in a lookup table and returns rows with matched values.
Last, we used the join kind=leftanti to merge the rows of two tables to form a new table by matching the values of the specified columns from each table, which then returns all the records from the left side that don’t have matches from the right. In this case, the source IP address is on the left side for all users that sign in, and the allowed IP address range from the Watchlist is on the right side.
// Maintain the Watchlist using the NetworkAddresses Watchlist Template with tag "AAD Allow" let watchlistNetworkAddresses = _GetWatchlist('NetworkAddresses') | where Tags has "AAD Allow" | project-rename IPSubnet = ['IP Subnet']; let allNetworks = watchlistNetworkAddresses | summarize by IPSubnet; let matchingAllowEvents = SigninLogs | where LocationDetails has "city" | extend src_ip = tostring(split(IPAddress, ":")) | summarize by src_ip | evaluate ipv4_lookup(allNetworks, src_ip, IPSubnet); SigninLogs | where LocationDetails has "city" | extend src_ip = tostring(split(IPAddress, ":")) | join kind=leftanti (matchingAllowEvents) on src_ip | project TimeGenerated, IPAddress = src_ip, UserPrincipalName, UserAgent, UserType, UserDisplayName
If you’re interested in what applications users are accessing, you can make a set of. The following KQL query is going to bring us a list of all the applications that each user has accessed. For example, you’ve got people just clicking around and trying to access things and looking at stuff they shouldn’t be allowed to. This could be interesting to you.
SigninLogs | summarize Applications=make_set(AppDisplayName) by UserPrincipalName
What you can do as well as extend the query to make more sense of the data. You can actually tell Kusto to calculate how many apps (AppCount) by using the array_length (scalar function). So it basically calculates the length of that for us. The following query is going to tell us which user is connecting to the most unique applications.
SigninLogs | summarize Applications=make_set(AppDisplayName) by UserPrincipalName | extend AppCount = array_length(Applications)
Then, you may be interested to hunt the user who is connecting to the Azure portal and/or to all kinds of security and sensitive applications like the Microsoft 365 Security and Compliance Center for example.
Last but not least, an interesting KQL query is to look for Software as a Service (SaaS) cloud applications and see their last logon time to Azure AD. This will give you a good indication of when the application last performed a single sign-on (SSO) to your tenant.
This gives you a glance for all applications which did not log on for more than > 30 days, and then investigate further if you need to stop using this app or not, maybe those apps are not very popular. As you know, each application has a service principal sitting in Azure AD potentially with some privileges as well, it’s a good practice to get alerted and delete those apps if they are not used.
// Single Sign On Applications SigninLogs | where TimeGenerated > ago(90d) | summarize arg_max(TimeGenerated, *) by AppId | project AppDisplayName, ['Last Logon Time']=TimeGenerated, ['Days Since Last Logon']=datetime_diff("day",now(),TimeGenerated) | where ['Days Since Last Logon'] > 30
Once you’ve summarized the data, you can still then run further queries on it. You’re not at the end of your query at that point.
Create an analytic rule
Next, you can promote a Livestream session to a new alert by creating an analytic rule. You can also create a new scheduled analytic rule or nearly real-time (NRT) query rule by using one of the KQL queries noted above.
From within the same Livestream session, click on the Create analytics rule as shown in the figure below.
Give the analytic rule a meaningful ‘Name‘ and ‘Description‘, then select the following 2 ‘Tactics‘ (Initial Access, and Credential Access). Those tactics are based on the MITRE ATT&CK Matrix for Enterprise. Then select ‘Medium‘ for the Severity and then click Next to Set rule logic.
In the Set rule logic tab, you will see the same rule query that we used in the previous step. You can update it or leave it as it is.
Click Next to configure the Incident settings.
You can enable group-related alerts, triggered by this analytics rule, into incidents. And keep the default settings: Grouping alerts into a single incident if all the entities match (recommended). Click Next to configure the Automated response.
In the Automated response tab, you can select the automated playbook that you’ve created to post a message in the Microsoft Teams Channel, for example, to inform the SOC team members about this operation. You can also create an ‘Incident automation‘ rule if you want. Automation rules also allow you to automate responses for multiple analytics rules at once, automatically tag, assign, or close incidents without the need for playbooks, and control the order of actions that are executed. Click Next to review and create.
In the Review and create the page, validate the settings and click Create to start the rule creation process.
Adding a little note on cost optimization. If you look into Azure AD non-interactive signing logs, we usually run the summarized count by user principal name, and then you will probably find at least in every environment like users that create 10,000 or 20,000 thousand non-interactive signing logs per day. Besides the fact that this can become a little bit costly. What is actually the reason for it?
We’ve seen several root causes and this is less of a security issue, but more of an operational cost issue. This could be Azure Virtual Desktop (AVD) VDI sessions that are left open. It can be the incorrect configuration of conditional access, which the refresh tokens. It can be users that left the company but still weren’t properly offboarded from their mobile devices, so it continues with failures continuously.
And especially in Microsoft Sentinel, if you’re ingesting and paying for non-interactive sign-in logs (NonInteractiveUserSignInLogs), they can actually be quite expensive. Thankfully in Sentinel, you can pick and choose your tables and what you want to ingest.
There are a lot of applications that are just very chatty and create a lot of non-interactive sign-in logs. For example, if we take Teams, it likes to connect in the background very quietly over, over, and over again. If you’re in advance hunting and you’re already paying for the P2 license, then you don’t need to pay and ingest non-interactive sign-in logs from Azure AD to Sentinel.
That’s it there you have it. Happy advanced Azure AD users Hunting in KQL and Microsoft Sentinel!
In this article, we showed you how to create advanced KQL hunting queries to monitor Azure AD sign-in activities in Microsoft Sentinel, so you can trigger an alert that can automatically run a security playbook to inform the organization’s Security Operation Center (SOC) team of this activity.
Please note that this is only one automation scenario on how to respond to security events by posting a message on Microsoft Teams, you could also automatically block the IP address, you could disable the Azure AD account so any access to your tenant will be denied, or you could also assign/add a manager to the invited account for access review to efficiently manage group memberships, access to enterprise applications, and role assignments.
Additional resources we highly encourage you to check:
- Learn how to monitor Azure Storage account activity logs with Microsoft Sentinel.
- Learn how to monitor Azure AD Guest Users with Microsoft Sentinel.
- Learn how to monitor Azure AD emergency accounts with Microsoft Sentinel.
- Learn more about Microsoft Sentinel, check the official documentation from Microsoft.
- Learn about Analytics Rules, check the official documentation from Microsoft.
- Learn about Playbooks, check the Microsoft Sentinel’s GitHub page contributed by the community and Microsoft.
Thank you for reading my blog.
If you have any questions or feedback, please leave a comment.