MS SQL Audit files not parsing

[rahulhari88]

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

[rahulhari88]

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-d... 

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

[deepakc]

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

[rahulhari88]

@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

[deepakc]

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

[deepakc]

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.

1. Change your sourcetype as there is a typo = mssql:aud to sourcetype = mssql:audit - and see of that works.
2. 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.