Splunk-2-ADX (Azure Data Explorer) Data Migration

Python demo using Kusto SDK to ingest to and query from ADX Database (Table|SplunkTableRaw)

How-To:

Log into Azure with the required permissions / access
Create App Registration & Secret from Entra ID
Record Client_ID, Client_Secret, and Tenant_ID from App Registration
Create ADX Cluster
Create a Database within your ADX Cluster
Enable Managed Identity (System) for you ADX Cluster
Within the Database -- Assign "Admin permission" to App Registration
Create Tables, Mapping, and Expand function within ADX Database

Install Python 3.X w/ the Kusto Python SDK (Windows / Linux / MacOS)

   mkdir adx_demo
   python -m venv adx_demo
   source adx_demo/bin/activate

   pip install azure-kusto-data
   pip install azure-kusto-ingest

Illustration

Ingestion: Kusto Python SDK used to programmatically authenicate & ingest data [data_ingest_all.json]

Sample Data (JSON): data_ingest_all.json

ADX Database Query (SplunkTable) via Kusto Python SDK

ADX Database Query (SplunkTable) via ADX

Create temp table where data will ingest to

.create table SplunkTableRaw (Records:dynamic)

$.FWLogEntry <-> MUST MATCH THE JSON OBJECT GETTING INGESTED FROM THE SOURCE [JSON FILE]!!
FWLogEntry gets MAPPED TO THE "RECORDS" COLUMN of the SplunkTableRaw Table

// e.g. {"FWLogEntry":{"TimeGenerated":"2024-03-13T18:45:54.9122018Z", "Company":"MFCC-G9-DOG", "Hacker":"Maj JJ Bottles", "Venue":"BSides DC", "Type":"SplunkTable"}}
.create table SplunkTableRaw ingestion json mapping 'SplunkTableMapping' '[{"column":"Records", "Properties":{"Path":"$.FWLogEntry"}}]'

.alter-merge table SplunkTableRaw policy retention softdelete = 0d

Create table (SplunkTable) where the data will reside

.create table SplunkTable (FWLogEntry:dynamic)

Create SplunkTableExpand() function

.create-or-alter function SplunkTableExpand() {
    SplunkTableRaw
    | project Records
}

Apply SplunkTableExpand() function to the SplunkTable

.alter table SplunkTable policy update @'[{"Source": "SplunkTableRaw", "Query": "SplunkTableExpand()", "IsEnabled": true, "IsTransactional": true}]'

If you ever need to drop the SplunkTable or the SplunkTableExpand() Function

//.drop function SplunkTableExpand
//.drop table SplunkTable ingestion json mapping "SplunkTable_JSON_Mapping"

Test data to ingest and ensure the tables, policies, mapping, and expand function is operating correctly.

.ingest inline into table SplunkTable with (format = "json") <| {"FWLogEntry":{"TimeGenerated":"2024-03-13T18:45:54.9122018Z", "Company":"MFCC-G9-DOG", "Hacker":"Maj JJ Bottles", "Venue":"BSides DC", "Type":"SplunkTable"}}

Query SplunkTable

SplunkTable
| extend t = parse_json(FWLogEntry)
| project TimeGenerated=todatetime(t.TimeGenerated), Company=tostring(t.Company), Hacker=tostring(t.Hacker), Venue=tostring(t.Venue), Type=tostring(t.Type)

Enable Continious Export of ADX DBase (Tables) to ADLSv2 (LT storage) via managed identities (system)

Microsoft Source Document: here!

dcodev1702/splunk_2_adx