Splunk Enterprise Security

MS SQL Audit files not parsing

rahulhari88
Explorer

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

 

 

Labels (1)
Tags (1)
0 Karma

rahulhari88
Explorer

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 ? 

0 Karma

deepakc
Builder

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

 

 

0 Karma

rahulhari88
Explorer

@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
0 Karma

deepakc
Builder

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

0 Karma

deepakc
Builder

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.

0 Karma
Get Updates on the Splunk Community!

How to Monitor Google Kubernetes Engine (GKE)

We’ve looked at how to integrate Kubernetes environments with Splunk Observability Cloud, but what about ...

Index This | How can you make 45 using only 4?

October 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with this ...

Splunk Education Goes to Washington | Splunk GovSummit 2024

If you’re in the Washington, D.C. area, this is your opportunity to take your career and Splunk skills to the ...