HOWTO: Configure Azure Sentinel data export for long-term storage

While Azure Sentinel (SIEM & SOAR) is becoming more popular every day, customers & partners face the challenge with costs & data retention. How long you preserve data in Azure Sentinel (log analytics workspace retention)? Where should you store data for longer periods of time (for cheaper price)? How data can be used/queried?

Preferred method for described scenario is pushing the data from Sentinel to Event Hub and ingest it to Azure Data Explorer (ADX) for N years. I'm not going to cover functionalities & pricing of ADX nor Event Hub in this blog, but explain the steps needed to make it work.

There used to be substantial amount of pain involved in this method, since building the table schema in ADX needed lots and lots of manual work; today we're introducing a new way to easily generate exact commands needed to build the table(s). Thank you Javier Soriano for the Powershell script!!

This is going to be very straightforward proof-of-concept on HOWTO implement long-term data retention with ADX. We'll use (Azure AD) SigninLogs table in Azure Sentinel as an example, but the same process can be used in other tables as well.

After the data is flowing towards ADX, the table schema and queries are identical in Azure Sentinel and in Azure Data Explorer database.

High level steps:

Set the stage (one-time shot):

1. Create Azure Data Explorer (ADX) cluster and database

2. Create Event Hub

Configure data export & ingestion in ADX (repeat per table):

3. Configure Data Export in Azure Sentinel's Log Analytics workspace

4. Create table(s), table mapping and update policy in ADX - (we'll use Powershell script to automate this)

5. Create data ingestion connection in ADX (from Event Hub)

(Repeat steps #3-5 for the tables you wish to export.)

Let's get started...

1. Create Azure Data Explorer (ADX) cluster and database

In Azure search box, look for "Azure Data Explorer Clusters"

• Create new cluster:

• Once the deployment is ready, go to resource and "Create database":

2. Create Event Hub

In Azure search box, look for "Event Hubs".

• Create new Event Hub namespace:

• Open newly created Event Hubs namespace. Choose "Properties" and write down "Resource ID" to your notepad - we'll need this shortly.

Now we've set the scene. You need to do this only once. Let's continue with data export.

3. Configure Data Export in Azure Sentinel's Log Analytics workspace

In Azure search box, look for "Log Analytics workspaces" and choose your Sentinel workspace.

• Take note your Resource group name and Workspace name and Workspace Id. ("Temp" and "VetoniittiSentinel" in my case)
• Start Azure Cloud Shell
• Register resource provider (if not registered already) using this command: Register-AzResourceProvider -ProviderNamespace Microsoft.insights
• Configure data export for your Azure Sentinel workspace. In this example we'll export SigninLogs and SecurityAlert tables:

The commands you need:

$eventHubsNamespacesResourceId = '/subscriptions/xxxxx-xxxxx-xxxx/resourceGroups/Temp/providers/Microsoft.EventHub/namespaces/VetoniittiNamespace'

(your Event Hub resource ID from notepad)

az monitor log-analytics workspace data-export create --resource-group Temp --workspace-name VetoniittiSentinel --name toEventHub --tables SigninLogs SecurityAlert --destination $eventHubsNamespacesResourceId

(your Azure Sentinel resource group name and workspace name from notepad)

4. Create table(s), table mapping and update policy in ADX

This is the most tricky part, yet quite easy - huge thanks ?? to Javier Soriano for creating the powershell script which we'll use! This would be way too painful without it.

• Download Javier's Powershell script: sentinel-scripts/Create-TableInADX.ps1 at main · javiersoriano/sentinel-scripts · GitHub
• Upload the script in Azure Cloud Shell
• Run the script and provide TableName you want to export (SigninLogs) and Azure Sentinel's Workspace Id

As a result, you'll get the exact commands needed to populate ADX database. Copy these commands to clipboard (or notepad) for next step.

• Go back to ADX cluster. Choose Databases. Choose your database. Choose Query.
• Paste the commands you got from the script and Run each command one-by-one. (To be clear - place your cursor to each command and choose Run for all of them.)

You'll get the new tables populated. There will be SigninLogsRaw -table where we're going to ingest raw json temporarily, then we have SigninLogsExpand function and Policy which will map the raw data towards actual end-result table SigninLogs.

5. Create data ingestion connection in ADX (from Event Hub)

Now we're almost done. Just one step away from completion.

• In ADX cluster/Database, choose "Data ingestion"
• Choose "Add data connection" and fill in following attributes and hit Create.

(NOTE: In step #3 we configured Sentinel data export to push data to Event Hub. However, it takes some time to kick in, be patient! Once it's rolling you should have "am-signinlogs" event hub available to select.)

As a Table name we use "SigninLogsRaw" - this is the raw json table which we created in previous step. As Mapping name we use "SigninLogsRawMapping" which we also created in previous step.

DONE! ????

How we know it all worked?

Now we can just observe the magic happening. Open your new shiny Event Hub and under Entities choose Event Hubs and choose "am-signinlogs". Here we can see incoming and outgoing messages:

And from ADX perspective, Open ADX/Databases - Choose database - Choose Data ingestion - select "signinlogs" and here you can monitor data ingestion to SigninLogs table in ADX:

And of course the most important...

...we can query the data in ADX:

...we can query the ADX data directly from Azure Sentinel using the same kql we used when the log was still in Sentinel:

(and union/join with other data and cross-correlate if needed)

Some additional remarks:

• Azure Sentinel workspace data can also be exported to Storage account/blob (which is cheaper than ADX). However, there's multiple usability issues why you probably don't want to go that route.
• Azure can push various logs (using Diagnostics) directly to Event Hub. However, ADX table schema and commands needs to be built manually (script can't be used).

Update 30th June 2021

• Since publishing this article, kudos to Sreedhar Ande, who has published a tool to fully automate the whole process for all tables - Azure-Sentinel/Tools/AzureDataExplorer at master · Azure/Azure-Sentinel · GitHub

Resources:

• Using Azure Data Explorer for long term retention of Azure Sentinel logs
• Log Analytics workspace data export in Azure Monitor (preview)
• Quickstart: Create an Azure Data Explorer cluster & DB
• Tutorial: Ingest monitoring data in Azure Data Explorer without code
• Cross Service Query - Azure Monitor and Azure Data Explorer
• ADX pricing (and calculator): Azure Data Explorer