topic Re: MS SQL Audit files not parsing in Splunk Enterprise Security

MS SQL Audit files not parsing

rahulhari88 — Fri, 07 Jun 2024 09:28:11 GMT

Hi all,
I am trying to integrate MS SQL audit log data with a UF instead of DB Connect.
What is the best and recommended way to do it that maps all fields?
At the moment it is integrated with the UF and using the "Splunk Add-on for Microsoft SQL Server"
Also i am seeing one additional dummy event(no values nothing blank event) with every event that is coming

My Inputs.conf

[WinEventLog://Security] start_from = oldest current_only = 0 checkpointInterval = 5 whitelist1 = 33205 index = test_mssql renderXml=false sourcetype = mssql:aud disabled = 0

Re: MS SQL Audit files not parsing

deepakc — Fri, 07 Jun 2024 11:32:11 GMT

I suspect that the MSSQL TA is normally supported and works in-conjuncton with the DB addon for sourcetype formatting (So it uses SQL queries and then does the props and transforms part, hence you’re not seeing and values. So, I suspect it’s not being parsed correctly.

For the Windows Logs you normally use the Windows TA which contains the props and transforms from the standard Windows Events channels(app/information/security etc) and the TA contains the parsing code.

I don’t have a test environment, so can't check, but you could try.

Change your sourcetype as there is a typo = mssql:aud to sourcetype = mssql:audit - and see of that works.
Perhaps set renderXml = true in the inputs.conf with new sourectype mssql:aud:xml and create a props.conf with the mssql:aud:xml sourcetype add KV_MODE=xml (this is just a try and see without testing)

If that doesn't work then stick with the DB connect solution.

Re: MS SQL Audit files not parsing

rahulhari88 — Sat, 08 Jun 2024 05:55:57 GMT

Hi @deepakc

I was checking if i can use SECMD to remove that blank event . However i am not sure how to use it ?

Or try this

https://community.splunk.com/t5/Splunk-Search/Why-is-the-regex-creating-empty-events-from-incoming-data/m-p/396432

The previous event ends with a "." so can i try the above method ?

Re: MS SQL Audit files not parsing

deepakc — Fri, 07 Jun 2024 15:41:28 GMT

The Sedcmd is mainly used to transform the raw data mask or delete lines etc not the event, so I don't think it would work ( I can't test) and I don't think you can use SEDCMD on the UF level.

The option you may have is to use send data with no data to the null queue, so worth a go.

Configure the below and deploy on the indexer.

This would send any data with no data to null queue (again I don't have a test environment, so you will have to trail and error it)

props.conf (Add your sourcetype here)

[wineventlog] TRANSFORMS-my_windows_blank_events = setnull

transforms.conf

[setnull] REGEX = ^\s*$ DEST_KEY = queue FORMAT = nullQueue

Re: MS SQL Audit files not parsing

rahulhari88 — Sat, 08 Jun 2024 08:02:11 GMT

@deepakc
Actually we are using Splunk Cloud so no indexer . However we are sending the UF to an HF and then to Splunk Cloud ,so i believe we can test this on the HF and see .Also i realized that second empty event does have date and time being captured ,so practically its not empty event , it just does not have any valuable info .So based on your method i am planning to use this config .Hope this works ? But i have one doubt where in HF should i place it .Since its only function is to drop events .Is it in /opt/splunk/etc/system or in the /opt/splunk/etc/apps ?

# props.conf [sql:audit] TRANSFORMS-null_events = strip_null_events # transforms.conf [strip_null_events] REGEX = ^\d{2}/\d{2}/\d{4} \d{1,2}:\d{2}:\d{2} [AP]M$ DEST_KEY = queue FORMAT = nullQueue

Re: MS SQL Audit files not parsing

deepakc — Sat, 08 Jun 2024 07:01:09 GMT

On the HF place the file on there (Easy)

/opt/splunk/etc/system/local/props.conf
/opt/splunk/etc/system/local/transforms.conf

If you know how to create a custom TA app, then that's better - this will give you a start - but you need to understand the app structure
https://dev.splunk.com/enterprise/tutorials/quickstart_old/createyourfirstapp/

If you manage to create the app, then place it in /opt/splunk/etc/apps/ on the HF