Must Learn KQL Part 20: Building Your First Microsoft Sentinel Analytics Rule
Chapter 20
This post is part of an ongoing series to educate about the simplicity and power of the Kusto Query Language (KQL). If you’d like the 90-second post-commercial recap that seems to be a standard part of every TV show these days…
The full series index (including code and queries) is located here:
The book version (pdf) of this series is located here:
https://github.com/rod-trent/MustLearnKQL/tree/main/Book_Version
The book will be updated when each new part in this series is released.
…
The intent of this series has been to enable you to understand the structure, flow, capability, and simplicity of the KQL query language. Way back in part/chapter 3, I said…
I tell customers all the time that it’s not necessary to be a pro at creating KQL queries. It’s OK not to be a pro on day 1 and still be able to use tools like Microsoft Sentinel to monitor security for the environment. As long as you understand the workflow of the query and can comprehend it line-by-line, you’ll be fine. Because ultimately, the query is unimportant. Seriously. What’s important for our efforts as security folks is the results of the query. The results contain the critical information we need to understand if a threat exists and then – if it does exist – how that threat occurred from compromise to impact.
And that remains the case. I’ll dig much, much deeper into KQL in the Addicted to KQL series, but for our purposes here in the Must Learn KQL series, you should have become comfortable with eyeing a query and understanding it’s intent line-by-line. If you’re just joining us because this part/chapter has the words Microsoft Sentinel and Analytics Rules on it, you’re starting at the wrong spot. I entreat you to jump back to the beginning and ingest this series in the methodical, logical manner it was intended.
(If you have suggestions for the TOC for the Addicted to KQL series, let me know. Current TOC is here: https://aka.ms/Addicted2KQL)
Keeping with the original plan to build your first Analytics Rule, we’re going to work together to understand an existing Analytics Rule example that you can use in your own Microsoft Sentinel environment. This example takes many of the concepts and operators we’ve learned together on this journey, so you should be intimately familiar with them. And, if all works well, you should be on your way to mastering KQL and hungry for what’s next. If you’re like me, this stuff just geeks you out. You may find yourself thinking about Joins and Summarizations at strange times, but don’t fret – you are not alone. KQL can do that. I regularly zone off thinking about table schema while the wife is telling me something that’s probably important. Heck even my email signature is homage to KQL:
Feel free to steal that, by the way.
Analytics Rule
So, let’s take a well-established Analytics Rule and pick it apart. This one is intended to capture individuals that run Cloud Shell in the Azure portal. This query/rule needs to be run in your own Microsoft Sentinel environment and not in the KQL Playground (https://aka.ms/LADemo). To get results, someone must have run Cloud Shell in recent record.
Cloud Shell activity is logged in the AzureActivity table. So, our first line of the query will be:
AzureActivity //the table - this is where Cloud Shell activity is loggedOnce we’ve identified the table we need to query against, as per the Workflow discussion in part/chapter 3, it’s time to start filtering the data. So, using the Where operator covered in part/chapter 8, let’s dig into what exactly identifies Cloud Shell usage.
In the filtering section of the query in the next samplet, we’re looking for CLOUD-SHELL in the ResourceGroup data column, but then digging even deeper to get more accurate results by ensuring the activity is related to a successful Start of storage creation. Anytime Cloud Shell is executed, storage is created in Azure.
| where ResourceGroup startswith "CLOUD-SHELL" //filtering for Cloud Shell
| where ResourceProviderValue == "MICROSOFT.STORAGE" //To not mistake this for some other Cloud Shell operation, also filtering on MICROSOFT.STORAGE. Storage is created anytime Cloud Shell runs.
| where ActivityStatusValue == "Start" //Making sure that the activity is the spawning of a new Cloud Shell instanceThe next thing we need to do is determine how many times the individual that has been captured has run Cloud Shell. We do this with the Summarize operator as covered in part/chapter 11.
| summarize count() by TimeGenerated , ResourceGroup , Caller , CallerIpAddress , ActivityStatusValue //Getting a count of how many times each individual has run Cloud ShellThe last thing we need to do is take a couple pieces of important information and assign them as Entities. Entities are important for investigations. Without Entities, such as users, IP addresses, hostnames, file hashes, etc. we would have no evidence, or no clues with which to progress through an actual investigation. There are different ways to do this in the Analytics Rule wizard in Microsoft Sentinel, but you can also assign Entities in your KQL query by using the Extend operator to create custom data views – as covered in part/chapter 13.
Microsoft Sentinel allows for four different custom entities in the queries. Those are:
AccountCustomEntity – the user
IPCustomEntity – the IP Address
HostCustomEntity – the host (computer/device)
URLCustomEntity – the capture URL the user accessed
Now, there’s a much deeper discussion that can be had on Entities because much has changed (and is constantly changing) for this area in Microsoft Sentinel. See the following for more:
But, for our purposes of learning KQL and applying the series’ knowledge, let’s stick with custom entities. Shown in the example below, we are assigning the known data columns of Caller (user name) and CallerIpAddress (user’s IP) to the custom entities. This will capture the user and the user’s IP address and place them in the Entities list associated with the Microsoft Sentinel Incident once an alert is generated based on our KQL logic.
| extend AccountCustomEntity = Caller //Assigning the Caller column - name of person - to AccountCustomEntity - this is what is used for the User Entity in Microsoft Sentinel Incidents
| extend IPCustomEntity = CallerIpAddress //Assigning the CallerIpAddress column - IP Address of user's system - to IPCustomEntity - this is what is used for the IP Entity in Microsoft Sentinel IncidentsSo, our full and complete KQL query to use when creating the Analytics Rule is:
AzureActivity //the table - this is where Cloud Shell activity is logged
| where ResourceGroup startswith "CLOUD-SHELL" //filtering for Cloud Shell
| where ResourceProviderValue == "MICROSOFT.STORAGE" //To not mistake this for some other Cloud Shell operation, also filtering on MICROSOFT.STORAGE. Storage is created anytime Cloud Shell runs.
| where ActivityStatusValue == "Start" //Making sure that the activity is the spawning of a new Cloud Shell instance
| summarize count() by TimeGenerated , ResourceGroup , Caller , CallerIpAddress , ActivityStatusValue //Getting a count of how many times each individual has run Cloud Shell
| extend AccountCustomEntity = Caller //Assigning the Caller column - name of person - to AccountCustomEntity - this is what is used for the User Entity in Microsoft Sentinel Incidents
| extend IPCustomEntity = CallerIpAddress //Assigning the CallerIpAddress column - IP Address of user's system - to IPCustomEntity - this is what is used for the IP Entity in Microsoft Sentinel IncidentsUse the following instructions to create an Analytics Rule with this query: Create custom analytics rules to detect threats
And if someone in your environment has run Cloud Shell recently, an alert and Incident will be generated that looks similar to the following:
OK. Here’s our very last extra credit together for this series and it’s a reminder of some other things we’ve done together along the way. There’s a bit more that we can do with this KQL query and Analytics Rule to make it a bit more intelligent. What if there are certain “trusted” people in our organization who should be able to run Cloud Shell without being captured as a potential suspect?
By creating a Watchlist (see: Create watchlists in Microsoft Sentinel) and modifying our KQL query slightly, we can ensure that only those individuals who shouldn’t be able to run Cloud Shell are the only ones captured in our alerts.
How do we do that? Let’s think back to part/chapter 17 for the Let statement and the section on Creating Variables from Microsoft Sentinel Watchlists and part/chapter 8 when we discussed the allowable string and numeric predicates for the Where operator.
The following example shows those adjustments. I have a Watchlist in my environment called TrustedUsers that has a data column called Username. I maintain this Watchlist so that it contains the most current list of trusted users.
let watchlist = (_GetWatchlist('TrustedUsers') | project Username); //Putting the Usernames from our Watchlist into memory to use later
AzureActivity //the table - this is where Cloud Shell activity is logged
| where Caller !in (watchlist) //filtering out our trusted users
| where ResourceGroup startswith "CLOUD-SHELL" //filtering for Cloud Shell
| where ResourceProviderValue == "MICROSOFT.STORAGE" //To not mistake this for some other Cloud Shell operation, also filtering on MICROSOFT.STORAGE. Storage is created anytime Cloud Shell runs.
| where ActivityStatusValue == "Start" //Making sure that the activity is the spawning of a new Cloud Shell instance
| summarize count() by TimeGenerated , ResourceGroup , Caller , CallerIpAddress , ActivityStatusValue //Getting a count of how many times each individual has run Cloud Shell
| extend AccountCustomEntity = Caller //Assigning the Caller column - name of person - to AccountCustomEntity - this is what is used for the User Entity in Microsoft Sentinel Incidents
| extend IPCustomEntity = CallerIpAddress //Assigning the CallerIpAddress column - IP Address of user's system - to IPCustomEntity - this is what is used for the IP Entity in Microsoft Sentinel Incidents
Thanks for the series! <3