Microsoft Secure Tech Accelerator
Apr 03 2024, 07:00 AM - 11:00 AM (PDT)
Microsoft Tech Community
Enriching Windows Security Events with Parameterized Function
Published Sep 29 2020 04:56 PM 13.1K Views
Microsoft

 

Overview

 

Monitoring Windows Security Auditing logs is essential in helping SOC analysts to keep track of any unplanned changes in a computer's system audit policy settings. If there is an indication of suspicious anomalous activity, an investigation must be performed as soon as possible. Hence, the ability to analyze collected Windows Security Auditing logs efficiently in Microsoft Sentinel is crucial.

 

Windows Security Events can be collected in Microsoft Sentinel with Security Events Connector or Data Collection in Azure Security Center Standard Tier (If both Microsoft Sentinel and Azure Security Center are running in the same workspace).

 

In this blog post, you will learn how to use Parameterized Function to perform data enrichment and simplify your KQL query. We will use Windows Security Audit Policy Change events as our use case for data enrichment. You can refer to a list of Audit Policy Change Event IDs here if you are unfamiliar with these events.

 

First, let me give you an overview of the use case that I am about to walkthrough: let’s take Event ID 4719 (“System audit policy was changed”) as an example. The following event, 4719, is logged when the audit policy was changed.

 

event.png

 

Below is how the event data would look when you query for this event in your Sentinel workspace. As you can see, the Category, Subcategory, and AuditPolicyChanges fields are captured in their IDs by design instead of the text values. This might be a challenge when you need to understand more about the event or perform a filter based on the string value such as “Object Access.”

 

QueryResultbyDesign.png

  

In the following sections of this blog, you will learn:

  • What is a Parameterized Function?
  • How to enrich Category, Subcategory, and Changes fields with parameterized function when analyzing Windows Security Audit Policy Change events in Microsoft Sentinel.

 

 

The Plan

 

Here is a summary of what we are about to do. In the following diagram, I have lookup tables for Category, Subcategory, and Changes with their values and IDs. The diagram illustrates how these IDs will be mapped to each table.

 

ThePlan.png

 

I will simplify the mapping process by consolidating all the values for Category, SubCategory, and Changes in a single lookup table, as shown in the diagram below. This will our Parameterized Function body, which I will discuss in the next section. The function will return the corresponding value when provided with an ID.

 

Lookup.png

 

 

What is a Parameterized Function?

 

Parameterized Function is a user-defined function that can be invoked through the function alias. It accepts zero or more input arguments and produces a single output (which can be scalar or tabular) based on the function body. However, Parameterized Function is not the same as the KQL function – a common feature in Log Analytics (you can refer to this blog post for example) as the KQL function doesn’t support input arguments.

 

Below is an example of a Parameterized Function with a single argument (ID). The function body is a dynamic table with a list of IDs and the corresponding values. The reason for using dynamic scalar data type is to allow scalar expression when passing a column value as the argument.

 

let myfunc = (ID:string){

   dynamic(

    { "%%8272":"System",

     "%%8273":"Logon/Logoff",

     "%%8274":"Object Access",

     "%%8275":"Privilege Use",

     "%%8276":"Detailed Tracking",

     "%%8277":"Policy Change",

     "%%8278":"Account Management",

     "%%8279":"DS Access",

     "%%8280":"Account Logon"

})[ID]};

 

Here is an example of how the function is being invoked. The function will return the lookup value of the provided ID:

 

SecurityEvent

| where EventID == 4719

| project TimeGenerated, Computer, CategoryId

| extend Category = myfunc(CategoryId)

| project-away CategoryId

 

However, Parameterized Function can only be created programmatically (creation in UI is not supported at the time of writing). Hence, I will provide sample PowerShell scripts to create Parameterized Functions in this blog.

 

 

Create the Parameterized Function

 

Here is the sample Powershell script to create the Parameterized Function for the lookup table of Category, Subcategory, and Changes. Edit the Powershell script to provide the following information before executing it:

  • $ResourceGroup: Resource Group of your Microsoft Sentinel Workspace.
  • $WorkspaceName: Name of your Microsoft Sentinel Workspace.
  • $SubscriptionID: Subscription ID of the Microsoft Sentinel Workspace.

Run the script after you have updated the above details. The script will prompt for your Azure credentials, and it should take less than a minute to complete. Below is the sample screenshot of how a successful creation looks like:

 

PowerShellOutput.png

 

The script will create a Parameterized Function on your defined workspace with the name and alias of “AuditEventDataLookup_Func”.

 

Note that the Parameterized Function is not visible in the workspace UI. You can still use API to GET the function details or List all Saved Searches (including Parameterized functions) for your workspace.

 

 

Using Parameterized Function to enrich Category ID, Subcategory ID, and Changes ID

 

Now, let’s begin enriching the Category and Subcategory fields using the function we just created. These two fields will only have a single value and they are more straightforward. We just need to invoke the function with CategoryId/SubcategoryId as the input argument.

 

CategoryAndSub.png

 

Below is the sample query:

 

SecurityEvent

| where EventID == 4719

| project TimeGenerated, Computer, Activity, CategoryId, SubcategoryId

| extend Category = AuditEventDataLookup_Func(CategoryId)

| extend SubCategory = AuditEventDataLookup_Func(SubcategoryId)

| project-away CategoryId, SubcategoryId

 

The query results are now showing Category and Subcategory names instead of IDs, as shown in the image below:

 

Pic8.png

 

Next is the AuditPolicyChanges field. This field will either have one or two values separated by a comma, as shown in the image below. Hence, we can’t pass the AuditPolicyChanges value directly to the function like what we did earlier because if the field contained two values, the function would not be able to find a match in the lookup table.

 

AuditPolicyChangesField.png

 

But this can be resolved with the help of a few KQL commands. First of all, we can use parse_csv() or split() command to get the string values from the comma-separated record. The returned values will be in a string array. Below is the sample query:

 

SecurityEvent

| where EventID == 4719

| extend Category = AuditEventDataLookup_Func(CategoryId)

| extend SubCategory = AuditEventDataLookup_Func(SubcategoryId)

| extend AuditPolicyChangesParse = parse_csv(AuditPolicyChanges)

 

Next, We will invoke the Parameterized function for each AuditPolicyChanges value using the array index (AuditPolicyChangesParse[0] and AuditPolicyChangesParse[1]). But be aware that there is a whitespace in the AuditPolicyChanges field after the comma as shown in the below image, which also appears after parsing. That means we need to remove the whitespace for the second value of AuditPolicyChanges when passing it to our function, and we can do that by using the trim() command.

 

whitespace.png

   

Below is the sample command line for removing the whitespace using trim(). Note that we are using strcat() to concatenate each AuditPolicyChange value returned by the function and separate them with a comma.

 

| extend AuditPolicyChange = strcat(AuditEventDataLookup_Func(AuditPolicyChangesParse[0]) ,",",

AuditEventDataLookup_Func(trim(" ",tostring(AuditPolicyChangesParse[1]))))

 

We have now resolved the scenario of enriching the AuditPolicyChange field, which might consist of two values. But what if the AuditPolicyChange field only has a single value? Since we are using strcat() in the above query, the record will look like the below image if there is only a single value.

 

2020-09-22_14-38-19.png

  

This will be an easy fix as we can use trim_end() to remove the comma at the end of the text (if any).

Below is how the command looks like:

 

| extend AuditPolicyChange = trim_end(",",strcat(AuditEventDataLookup_Func(AuditPolicyChangesParse[0]) ,",",

AuditEventDataLookup_Func(trim(" ",tostring(AuditPolicyChangesParse[1])))))

 

Below is the final query for your reference:

 

 

 

SecurityEvent
| where EventID == 4719
| extend Category = AuditEventDataLookup_Func(CategoryId)
| extend SubCategory = AuditEventDataLookup_Func(SubcategoryId)
| extend AuditPolicyChangesParse = parse_csv(AuditPolicyChanges)
| extend AuditPolicyChange = trim_end(",", strcat(AuditEventDataLookup_Func(AuditPolicyChangesParse[0]) ,",",AuditEventDataLookup_Func(trim(" ",tostring(AuditPolicyChangesParse[1])))))
| project TimeGenerated, Computer, Activity, Category, SubCategory, AuditPolicyChange

 

 

 

 

2020-08-28_10-34-39.png

 

 

Nested Function

 

Now you have seen how the function works. There might be times when you need to create multiple functions for different purposes (such as enrichment, parsing, filtering, etc.). The ability to invoke another function within a function will be useful. Let me show you that in the next example.

 

Below is the query from the previous section, and my use case is to simplify the query by configuring the yellow highlighted lines in a parameterized function called EnrichAuditEvents_Func. This function will take the table records (highlighted in blue) as the input argument (we will assign the table records to a Let() variable and passing it as an argument).

2020-09-24_15-46-44.png

  

You can create the EnrichAuditEvents_Func function with the script here.

 

The function will return the same output results as we saw earlier, but the KQL query will be much simpler and shorter.

Below is how the final query looks like:

 

 

 

let AuditEvents = (SecurityEvent | where EventID == 4719);
EnrichAuditEvents_Func(AuditEvents)

 

 

 

 

 

Summary

 

In summary, you have seen how we can use the Parameterized Function to perform enrichment and simplify the KQL query. I hope you find this useful, and this information will help you analyze Windows Security Audit events more efficiently.

 

 

Download Scripts:

 

AuditEventDataLookup_Func - Enrich Category, Subcategory and AuditPolicyChanges fields.

EnrichAuditEvents_Func - The nested function. Make sure you have deployed the above function before invoking this.

 

 

Special thanks to @Ofer_Shezaf, @Sarah_Young, and @Alexander Sloutsky for your help.

 

8 Comments
Version history
Last update:
‎Nov 02 2021 06:13 PM
Updated by: