You dont have javascript enabled! Please enable it! Optimize Log Ingestion And Access In Microsoft Sentinel - CHARBEL NEMNOM - MVP | MCT | CCSP | CISM - Cloud & CyberSecurity

Optimize Log Ingestion and Access in Microsoft Sentinel

13 Min. Read

Ingestion time transformation in Microsoft Sentinel is a great feature that allows you to route data to multiple destinations.

In this comprehensive guide, we will look at how to customize log ingestion and access in Microsoft Sentinel to optimize costs and restrict access permissions.

Introduction

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.

You are collecting logs such as network firewall logs in Common Event Format (CEF) and the logs are in high volume, after evaluation, you found that only a portion of these logs from the data source will be used for detection. The remaining part of the logs will not be used for detection, but there is a requirement to store those logs for compliance and forensics. Furthermore, the logs that are stored for compliant purposes need only to be accessed by a non-SOC member.

Your organization has a strategy to ensure cost-effective storage and ingestion of logs. Additionally, there is a need to provide access to the logs ingested to Sentinel for compliance and forensics purposes, even for non-SOC members.

To address this scenario, we need to use a couple of important features in Microsoft Sentinel called (Data Transformations, Basic Logs, and Table-Level RBAC), so we can route the logs that are not being used for detection to a new custom table, we will set the ingestion plan to Basic Logs for the most cost-effective way, and lastly we will allow the non-SOC members to access that specific custom table only and not the rest of the tables in the Sentinel workspace.

Related: See how to optimize costs in Microsoft Sentinel.

Log Ingestion Scenario

So after investigations, we realized that we would be using the “DeviceEventClassID” as the logic to determine whether or not the logs will be used for detection or compliance purposes.

For example, if we determine that the “DeviceEventClassID” value is to equal to “url“, it will not be used for detections because it will be used for compliant purposes only, and if the value is NOT equal to “url“, for example, “scan” or “detect“, those records will be used for detections using Analytics Rules.

CommonSecurityLog | sort by TimeGenerated desc
CommonSecurityLog | sort by TimeGenerated desc

This means that after we implement this scenario, the log ingestion solution and access in Microsoft Sentinel will look like the following diagram. We will split the data stream into multiple destinations. So, based on the logic that we just mentioned, if the “DeviceEventClassID” value is NOT equal to “url“, it will be stored natively in the default “CommonSecurityLog” table so it can be used for detection using Analytics logs.

And if the “DeviceEventClassID” value equals to “url“, then it will be routed to a custom table that you need to be defined and created and that table will be configured as the Basic Logs ingestion plan (more details in the next section).

Optimize Log Ingestion and Access in Microsoft Sentinel
Optimize Log Ingestion and Access in Microsoft Sentinel

Lastly, we also need to address the access user criteria. In this example, we have two users: “Charbel“, who is part of the security operations center (SOC) team, Charbel has the “Microsoft Sentinel Reader” role, and Charbel will be able to see all the tables in the Sentinel “Log Analytics workspace“. But we also have “Ana“, who is not a SOC team member, and she will need to access the “Custom Table” only. So, with that, we will create a “Custom Role” and then assign the permission for this custom role to the specific custom table only.

OK, so let’s look at how we can implement this scenario.

Prerequisites

To follow this article, you need to have the following:

1) Azure subscription — If you don’t have an Azure subscription, you can create one here for free.

2) Log Analytics workspace — To create a new workspace, follow the instructions to create a Log Analytics workspace. A Microsoft Sentinel workspace is required to ingest CEF data into Log Analytics. You also must have read and write permissions on this workspace, you need at least contributor rights.

3) Microsoft Sentinel — To enable Microsoft Sentinel at no additional cost on an Azure Monitor Log Analytics workspace for the first 31 days, follow the instructions here. Once Microsoft Sentinel is enabled on your workspace, every GB of data ingested into the workspace can be retained at no charge (free) for the first 90 days.

4) Common Event Format (CEF) via AMA — You need to create and configure a Linux machine that will collect the logs from your devices and forward them to the Microsoft Sentinel workspace. This machine will play two roles, collector and forwarder. Please note that this machine can be physical or virtual in your on-premises environment, an Azure VM, or a VM in another cloud. Deploying the CEF collector machine close to the source (devices and appliances) is recommended.

Related: How to deploy a CEF collector machine and forward the logs to Microsoft Sentinel.

5) Network Data Source based on CEF via AMA — This could be Fortinet, Palo Alto Networks, or any other network or security appliance you have.

NOTE: It is recommended by Microsoft to install the network/security appliance through Azure Monitor Agent (AMA) Connector. Legacy connector uses the Log Analytics agent which is about to be deprecated by Aug 31, 2024, and thus should only be installed where AMA is not supported. Using MMA and AMA on the same machine can cause log duplication and extra ingestion costs.

Assuming you have all the prerequisites in place, take the following steps:

Optimize Log Ingestion

First, we need to start by creating a Data Collection Rule (DCR) to split our data stream, so we can filter and customize the log ingestion in the Microsoft Sentinel workspace into two different tables.

Azure Monitor utilizes Data Collection Rules, which are resources that establish the data collection process. These rules outline the specifics of a given data collection scenario, such as what data to gather, how to alter it, and where to transmit it.

Data Collection Rules are Azure resources that define the data collection process in Azure Monitor. It defines the details of a particular data collection scenario including which data should be collected, how to transform the data, and where to send that data.

For Microsoft Sentinel, there are three primary use cases for which you can use Data Collection Rules:

  1. You can create DCR rules with an association to an Azure Monitor Agent (AMA) — more details in the next section.
  2. You can send data to the REST API connected to a Data Collection Endpoint (DCE) linked to a Data Collection Rule (DCR).
  3. Or when you want to use transformations for a legacy workload that does not support DCR or data connectors, you can associate a DCR to the workspace and use it for supported tables.

For our scenario, we will use the first use case. We will create a DCR rule with an association to an Azure Monitor Agent (AMA), to receive the data from an AMA agent and send it to a Log Analytics workspace. This can be done for Windows Events, Linux Syslog events, or third-party syslog forwarding via a Syslog server.

To get familiar with how Data ingestion flows in Microsoft Sentinel, check the official Microsoft documentation, where you can find the following schema that can help you understand the different ingestion flows:

Data ingestion flow in Microsoft Sentinel
Data ingestion flow in Microsoft Sentinel [Image Credit Microsoft]
As you can see in the diagram, you can create transformation in DCRs to manipulate your data, and this must be done either by “Workspace Transformation DCR” or by “Standard DCR“.

Reference: See the difference between Workspace transformation DCR and Standard DCR.

For our scenario with “CommonSecurityLog“, we will use Standard DCR. In both the Custom Logs and Azure Monitor Agent structures, it is possible to create a standard DCR. The key point to note is that each connector or log source can have its own dedicated DCR, while multiple connectors or sources can also share a common standard DCR.

Standard DCRs are currently only supported for AMA agents and workflows using the Log Ingestion API and can send data to both custom tables and a couple of standard tables. The supported standard tables are currently limited as documented by Microsoft, at the time of this writing the supported standard tables are:

  • CommonSecurityLog (our use case)
  • SecurityEvents
  • Syslog
  • WindowsEvents

With data transformation, you can send data to multiple destinations in a Log Analytics workspace, where you can provide a KQL transformation for each destination. Currently, the tables in the DCR must reside in the same Log Analytics Workspace.

As mentioned in the scenario section, we want to send data to multiple destinations, we want to send firewall “url” logs to a custom table configured as Basic Logs, and other import severities to the default “CommonSecurityLogAnalytics table for interactive querying and detection.

Create Standard DCR for AMA

To create AMA-related DCRs, you can go to the Microsoft Sentinel Data Connectors blade. Then search for AMA, and we get two options:

  • Common Event Format (CEF) via AMA
  • Windows Security Events via AMA

For our scenario, we will use the Common Event Format (CEF) via AMA. Open the connector, and then click on “+Create data collection rule” so you will be able to create DCR for CEF forwarding.

Create data collection rule for CEF
Create data collection rule for CEF

Give the DCR rule a name, select your Linux CEF collector machine, and then on the “Collect” tab, you need to select the correct facilities. For the Fortinet (FortiAnalyzer) source, we are using the following facilities. The data connector wizard will help you to create the DCR for your use case.

Select data source facilities to collect for CEF
Select data source facilities to collect for CEF

Now that we created the DCR for CEF, when we check the DCR ARM template, we see that the data connector now created a data flow stream to send events to the default “CommonSecurityLog” native table.

DCR ARM template | dataFlows (streams)
DCR ARM template | dataFlows (streams)

And changed the data sources to Syslog with the Syslog facilities that we chose in the “Collect” tab.

DCR ARM template | Syslog facilities
DCR ARM template | Syslog facilities

The next step is to create a custom table.

Create Custom Table in Log Analytics workspace

In this section, we’ll create a custom table where we can store the non-related detection “url” logs and use it for compliance purposes only.

There are several methods that you can use to create a custom table, you can use the Azure portal (Log Analytics workspace > Tables) as shown in the figure below, or you can use ARM templates, Bicep, Azure PowerShell, or the Azure CLI.

New custom log (DCR-based)
New custom log (DCR-based)

To make things easier and faster, we will use PowerShell from Azure Cloud Shell to make REST API calls using the Azure Monitor Tables API to create the custom table.

Open the Cloud Shell at (https://shell.azure.com), then sign in with your account and ensure the environment is set to PowerShell and NOT to Bash.

Then copy the following PowerShell code and paste it into the Cloud Shell prompt to run it. Please note that the sample data JSON file below is just an example and is not complete for production. You need to update the column names by adding or removing “columns” based on your use case and the schema that you want.

Tip: You could use the Log Analytics REST API to get and export the entire schema for the “CommonSecurityLog” native table and use it to create the custom table “ComplianceTable_CL“.

GET https://management.azure.com/subscriptions/{subscriptionId}/resourceGroups/{resourceGroupName}/providers/Microsoft.OperationalInsights/workspaces/{workspaceName}/tables/{tableName}?api-version=2022-10-01
Get a Log Analytics workspace schema table using API
Get a Log Analytics workspace schema table using API
$customTableParams = @'
{
    "properties": {
        "schema": {
            "name": "ComplianceTable_CL",
            "columns": [
                {
                    "name": "TimeGenerated",
                    "type": "datetime",
                    "description": "The time at which the data was generated"
                },
                {
                    "name": "DeviceVendor",
                    "type": "string",
                    "description": "Name of the Device Vendor"
                },
                {
                    "name": "DeviceProduct",
                    "type": "string",
                    "description": "Name of the Device Product"
                },
                {
                    "name": "DeviceVersion",
                    "type": "string",
                    "description": "Name of the Device Version"
                },
                {
                    "name": "DeviceEventClassID",
                    "type": "string",
                    "description": "Value collected for the Device Event Class ID"
                },
                {
                    "name": "Activity",
                    "type": "string",
                    "description": "Value of the Activity"
                },
                {
                    "name": "LogSeverity",
                    "type": "string",
                    "description": "Value of the Log Severity"
                },
                {
                    "name": "Computer",
                    "type": "string",
                    "description": "Name of the Computer"
                }                
            ]
        }
    }
}
'@

Then replace the variables in the Path parameter with the appropriate values of your subscription, resource group, and workspace name in the “Invoke-AzRestMethod” command below:

Invoke-AzRestMethod -Path "/subscriptions/{subscriptionId}/resourcegroups/{resourcegroup}/providers/microsoft.operationalinsights/workspaces/{workspacename}/tables/ComplianceTable_CL?api-version=2022-10-01" -Method PUT -payload $customTableParams

You should receive a similar output to the one below with the “StatusCode: 202“.

Create a custom log table
Create a custom log table

Next, switch to the Azure portal, then go to your “Log Analytics workspace > Tables” and verify that the new custom table has been created.

Verify Custom table
Verify Custom table

To confirm the schema for the custom table, simply click on the ellipses () located on the right side. From there, select “Edit schema” and you will be able to view the columns included in the schema under “Custom Columns“, which is illustrated in the figure below.

Schema Editor | Custom Columns
Schema Editor | Custom Columns

The next step is to create transformations for the AMA (DCR-based) data source.

Create Data Transformations for AMA

When we created the DCR for the AMA connector as described in the previous step, we were not able to create a Transformation KQL in the portal. This does not mean transformations are not supported for these DCRs. We can just add a “transformKql” and an “outputStream” to the ARM template as described in the following steps.

First, you need to browse to the newly created DCR (direct URL for Data collection rules), then go to the “Export template” under “Automation“. To create KQL transformations to a supported standard table, click on “Deploy” as shown in the figure below:

Edit DCR ARM template
Edit DCR ARM template

Next, click on “Edit template” and then scroll to the “dataFlows” section. We need to add “transformKql” and the “outputStream” to split and route the logs to the corresponding table.

For this scenario, we are excluding the “DeviceEventClassID” value NOT equal to “url” from the default “CommonSecurityLog” table and send it over to the custom table “ComplianceTable_CL“.

Remember that standard tables need to include “Microsoft-” in front of them and “Custom-” for custom tables.

Creating Transformations for "CommonSecurityLog" DCR
Creating Transformations for “CommonSecurityLog” DCR

The only thing you need to make sure of is that the column (E.g. DeviceEventClassID) you use in the “transformKql” are in fact columns that are known for the DCR source table “CommonSecurityLog“. The custom table must also be created before you can send data to it. If this is not the case, the deployment of the ARM template will fail. The table where the columns need to come from is the table that is defined in the “streams” part.

Here are the KQL transformations used for this scenario.

"dataFlows": [
    {
        "streams": [
            "Microsoft-CommonSecurityLog"
        ],
        "destinations": [
            "la--1045438334"
        ],
        "transformKql": "source | where DeviceEventClassID != 'url'",
        "outputStream": "Microsoft-CommonSecurityLog"
    },
    {
        "streams": [
            "Microsoft-CommonSecurityLog"
        ],
        "destinations": [
            "la--1045438334"
        ],
        "transformKql": "source | where DeviceEventClassID == 'url'",
        "outputStream": "Custom-ComplianceTable_CL"
    }
]

Once you have saved the template, you need to create (deploy) the template to the same existing DCR for CEF-AMA.

Deploy DCR ARM template
Deploy DCR ARM template

Remember that you will need to make sure that the columns that are created or changed after the “transformKql” need to represent the columns of the standard table you chose in the “outputStream“.

Please note that the transformations do not incur any costs, but if you filter out more than 50% of the data, it’ll cause a “data processing charge” (in non-Sentinel workspaces). In that case, it may be recommended to filter that data out already in rsyslog, for example.

The next step is to optimize the cost for the newly created custom table.

Optimize Costs

Remember that we also need to optimize the ingestion costs by setting the custom table as a Basic logs ingestion plan to be used only for compliance.

We can do that easily by going to the Log Analytics workspace and then searching for the table “ComplianceTable_CL“. Next, simply click on the ellipses () located on the right side and then select “Manage table.” We need to change the table plan from “Analytics” to “Basic” as shown in the figure below.

Change Table plan to Basic logs
Change Table plan to Basic logs

Once you save and change the table plan to Basic, you will have 8 days of “Interactive retention” to query the custom table and 82 days (90 days — 8 days) of Archived logs (based on our example). However, you can set the “Total retention period” for the Archive tier up to 7 years to meet your compliance needs.

Set long-term retention for the archive tier
Set long-term retention for the archive tier

Once you extend the total retention period, you can use a search job when you start an investigation to find specific events in logs up to seven years ago. You can search events across all your logs, including events in Analytics, Basic, and Archived log plans.

In the Azure portal, you go to Microsoft Sentinel and select the appropriate workspace. Under General, select Search, then select the Table menu and choose the custom tableComplianceTable_CL” for your search. In the Search box, enter a search term and then click Start as shown in the figure below.

Search large datasets in the Archive log custom table
Search large datasets in the Archive log custom table

Related: Check how to search and restore archive logs.

The next step is to configure the access permissions.

Access Permissions

On the permission side, we need to configure a custom role with the following five least privileged permissions so the user (in this case, Ana) can read and query data of a specific table in the Log Analytics workspace.

Microsoft Operational Insights:

  • Read (Get Workspace): Gets an existing workspace
  • Other (Search Workspace Data): Executes a search query
  • Other (Search): Search using a new engine
  • Read (Query workspace table data): Run queries over the data of a specific table in the workspace
  • Read (Query Data in Workspace): Run queries over the data in the workspace

First, you need to open the Cloud Shell at (https://shell.azure.com), then sign in with your account and ensure the environment is set to PowerShell.

Then copy the following PowerShell code and paste it into the Cloud Shell prompt to run it and create a new custom role. Please make sure to replace the {subscriptionId} variables with the appropriate value of your subscription:

$role = Get-AzRoleDefinition -Name "Log Analytics Reader"

$role.Id = $null
$role.Name = "Sentinel Compliance Custom Role"
$role.Description = "Read workpace information and run queries over data of a specific table."
$role.IsCustom = $True
$role.Actions.RemoveRange(0,$role.Actions.Count)
$role.Actions.Add("Microsoft.OperationalInsights/workspaces/read")
$role.Actions.Add("Microsoft.OperationalInsights/workspaces/search/action")
$role.Actions.Add("Microsoft.OperationalInsights/workspaces/analytics/query/action")
$role.Actions.Add("Microsoft.OperationalInsights/workspaces/tables/query/read")
$role.Actions.Add("Microsoft.OperationalInsights/workspaces/query/read")
$role.AssignableScopes.Clear()
$role.AssignableScopes.Add("/subscriptions/{subscriptionId}")

New-AzRoleDefinition -Role $role

You should receive a similar output to the one below.

Create a Log Analytics workspace custom role using PowerShell
Create a Log Analytics workspace custom role using PowerShell

If we switch to the Azure portal, then go to Roles under Access control (IAM) and search for the newly created custom role (E.g. Sentinel Compliance Custom Role), and then click on View details. You should see the following five actions assigned to this custom role.

The user will have sufficient permission to see the workspace information and details. Secondly, whenever the role is assigned to a table, the user will only see the data of that table.

Microsoft Sentinel Compliance Custom Role
Microsoft Sentinel Compliance Custom Role

Next, we need to assign this custom role to the Log Analytics workspace, so the user can see the workspace information but not the data in the table. You could assign this role to the resource group where the Log Analytics workspace is deployed, or on the workspace itself at the resource level. You could also assign access to a specific user or to a security group (as a best practice) where the user is a member of that group.

Add role assignment | Log Analytics workspace
Add role assignment | Log Analytics workspace

To complete the access assignment, we have to go to the newly created custom table in the Log Analytics workspace, and then search for the table “ComplianceTable_CL“. Next, simply click on the ellipses () located on the right side, and then select “Access control (IAM)“, and finally assign the custom role for the same user or group as shown in the figure below.

Add role assignment to a specific table
Add role assignment to a specific table

So that will allow Ana first of all to see the workspace, and only be able to see the data in this specific custom table that’s been assigned.

Verify Access Permissions

Let’s check the results and verify the access permissions for Ana.

Now I am logged in as Ana in the Log Analytics workspace, if we try to query the “CommonSecurityLog” table, we shouldn’t be able to see any data. This confirms that Ana does not have access permissions on this table.

CommonSecurityLog
 | sort by TimeGenerated desc
Querying the "CommonSecurityLog" table
Querying the “CommonSecurityLog” table

And if we try to query the data in the “ComplianceTable_CL” table, Ana should be able to see the logs as shown in the figure below. Please note that the tabular operator ‘sort‘ is not supported when querying the Basic Logs table.

Querying the "ComplianceTable_CL" custom table
Querying the “ComplianceTable_CL” custom table

And since Charbel has been assigned the “Microsoft Sentinel Reader” role, he will be able to see all the tables in the Sentinel “Log Analytics workspace“, and when querying the default “CommonSecurityLog” native table, he will see non-related “url” logs under the “DeviceEventClassID” column as shown in the figure below. There you go!

Verify log ingestion and access permissions
Verify log ingestion and access permissions

Hope this helps one of you out there!

Related: Learn the difference between Analytics Logs and Basic Logs.

Conclusion

In this article, we demonstrated how to customize access and optimize log ingestion for cost-saving and compliance purposes.

Microsoft Sentinel’s ingestion time transformation is an excellent feature that enables you to send data to multiple destinations. This article has demonstrated how to collect data from a source and split the logs from the native table. To save costs, you can send it to a custom table and set a different ingestion tier, such as Basic Logs. Please note that running a query on a Basic log includes an additional cost ($0.005 per GB of data scanned).

Last, we created a custom role that will allow non-SOC users to view the workspace but limit access to only the assigned custom table data.

Happy optimizing log ingestion and access in Microsoft Sentinel!

__
Thank you for reading my blog.

If you have any questions or feedback, please leave a comment.

-Charbel Nemnom-

Photo of author
About the Author
Charbel Nemnom
Charbel Nemnom is a Senior Cloud Architect with 21+ years of IT experience. As a Swiss Certified Information Security Manager (ISM), CCSP, CISM, Microsoft MVP, and MCT, he excels in optimizing mission-critical enterprise systems. His extensive practical knowledge spans complex system design, network architecture, business continuity, and cloud security, establishing him as an authoritative and trustworthy expert in the field. Charbel frequently writes about Cloud, Cybersecurity, and IT Certifications.
Previous

Getting Started With Microsoft Graph PowerShell for Microsoft Entra ID

8 Steps for a Successful Cloud Migration Process

Next

Let us know what you think, or ask a question...