Split log streams into Analytics and Auxiliary Table

Table of Contents

Auxliliary Logs - This article is part of a series.

Part 1: Auxiliary Logs in Azure Log Analytics

Part 2: Remap column names in Data Collection Rules (DCR)

Part 3: This Article

In the last article we found out that KQL transformation at ingestion time is not available for Auxiliary Logs. But in real cases you want to have the ability to send selective log lines to Analytics while sending the rest to Auxiliary. In this article we will discover the possibilities.

Introduction

If you using a SIEM often not all ingested log data is security relevant. But to be able to use Analytic rules you have to send data to Analytic tables. Only those provide the ability to have Analytic rules in place which implements certain use cases. In such scenarios you often have data in the logs which are not needed for the specific use case. So we want to discover how to avoid be charged for the expensive Analytic Log space for data which is not security relevant by sending them to the Auxiliary Log table space.

We will use again our well known table defintion:

TimeGenerated, type datetime
Message, type string
Info, type string

This time we will have two tables with that definition: TestDataAnalytics_CL and TestDataAuxiliary_CL.

The goal is to have in the Auxiliary Log table all log data which is not needed in the Analytics Log table for the use case. To simplify this we just define that as soon the field Info contains the value Security it is use case relevant means we send the corresponding log line to the Analytics Log table; log lines which do not match get sent to the Auxiliary Log table.

At the time of this writeup it is not clear if this approach is working.

This are the steps we will do:

Create the two tables TestDataAnalytics_CL and TestDataAuxiliary_CL
Try to define the corresponding Data Collection Rule (DCR)
Send test data to the DCR and see if it is working as expected

Table creation

This is simple if you already followed this article serie. But if you didn’t - no problem; we will do it here again to make sure you have those tables.

$analyticTableParams = @'
{
  "properties": {
    "totalRetentionInDays": 90,
    "plan": "Analytics",
    "schema": {
      "name": "TestDataAnalytics_CL",
      "columns": [
          { "name": "TimeGenerated", "type": "datetime" },
          { "name": "Message", "type": "string" },
          { "name": "Info", "type": "string" }
      ]
    },
  },
}
'@

Invoke-AzRestMethod -Path "/subscriptions/a2aeb284-51bd-4807-adbe-94095a10b175/resourceGroups/rg-security-logs-prod-001/providers/Microsoft.OperationalInsights/workspaces/log-security-prod-001/tables/TestDataAnalytics_CL?api-version=2023-01-01-preview" -Method PUT -payload $analyticTableParams

$auxiliaryTableParams = @'
{
  "properties": {
    "totalRetentionInDays": 365,
    "plan": "Auxiliary",
    "schema": {
      "name": "TestDataAuxiliary_CL",
      "columns": [
          { "name": "TimeGenerated", "type": "datetime" },
          { "name": "Message", "type": "string" },
          { "name": "Info", "type": "string" }
      ]
    },
  },
}
'@

Invoke-AzRestMethod -Path "/subscriptions/a2aeb284-51bd-4807-adbe-94095a10b175/resourceGroups/rg-security-logs-prod-001/providers/Microsoft.OperationalInsights/workspaces/log-security-prod-001/tables/TestDataAuxiliary_CL?api-version=2023-01-01-preview" -Method PUT -payload $auxiliaryTableParams

Create Data Collection Rule (DCR)

So now here the tricky part. As mentioned before the Data Collection Rule (DCR) has to select data and send them accordingly to the Analytics or Auxiliary table. As you can see we define "transformKql": "source | where Info contains 'Security'" to select data only for our Analytics table. The whole stream should be sent additionally to the Auxiliary Log table.

$dcrParams = @'
{
  "location": "switzerlandnorth",
  "kind": "Direct",
  "properties": {
    "description": "A direct ingestion rule for TestData logs",
    "streamDeclarations": {
      "Custom-TestDataStream": {
        "columns": [
          { "name": "TimeGenerated", "type": "datetime" },
          { "name": "Message", "type": "string" },
          { "name": "Info", "type": "string" }
        ]
      }
    },
    "destinations": {
      "logAnalytics": [
        {
          "workspaceResourceId": "/subscriptions/a2aeb284-51bd-4807-adbe-94095a10b175/resourceGroups/rg-security-logs-prod-001/providers/Microsoft.OperationalInsights/workspaces/log-security-prod-001",
          "name": "log-security-prod-001"
        }
      ]
    },
    "dataFlows": [
      {
        "streams": [
          "Custom-TestDataStream"
        ],
        "destinations": [
          "log-security-prod-001"
        ],
        "outputStream": "Custom-TestDataAnalytics_CL",
		    "transformKql": "source | where Info contains 'Security'"
      },
      {
        "streams": [
          "Custom-TestDataStream"
        ],
        "destinations": [
          "log-security-prod-001"
        ],
        "outputStream": "Custom-TestDataAuxiliary_CL"
      }
    ]
  }
}
'@

Invoke-AzRestMethod -Path "/subscriptions/a2aeb284-51bd-4807-adbe-94095a10b175/resourceGroups/rg-security-logs-prod-001/providers/Microsoft.Insights/dataCollectionRules/DCR-Generic-CollectionRule?api-version=2023-03-11" -Method PUT -payload $dcrParams

So what is the result of this API call?

StatusCode : 400
Content    : {
               "error": {
                 "code": "InvalidPayload",
                 "message": "Data collection rule is invalid",
                 "details": [
                   {
                     "code": "InvalidDataFlow",
                     "message": "Stream (Custom-TestDataStream) with Auxiliary Table destination
             (log-security-prod-001) permits no other destinations.",
                     "target": "properties.dataFlows[1]"
                   }
                 ]
               }
             }

API response when submitting our Data Collection Rule to split data between Analytics and Auxiliary Log tables.

As you can see the API is verbose and states: Stream (Custom-TestDataStream) with Auxiliary Table destination (log-security-prod-001) permits no other destinations.

Summary

We tried to split the incoming data so only security relevant data is sent to Analytics Log table. The Auxiliary Log table should receive all data (unfiltered). Unfortunatly the API does not allow this definition. This means that splitting data between Analytics and Auxiliary Logs is not supported and you have to address this data split issue before sending data to the Log Analytics Workspace.

Introduction

Table creation

Create Data Collection Rule (DCR)

Summary

Further Reading