All Apps and Add-ons

Splunk for SQL Server - props.conf field extractions RegEx not returning data

millern4
Communicator

Hello,

We are currently evaluating the Microsoft SQL Server App and none of our Security Dashboards are populating. We have been investigating this for a few days now and I believe we have narrowed down to the field extractions in props.conf not returning any data both within Splunk, and also when I run through a regular expression tester.

For example:

EXTRACT-mssql_33205_class_type=(?ms)EventCode=33205\n.\nclass_type:(?.?)\n

when put against my audit log file (I omitted lines for security related purposes)

SidType=1
TaskCategory=Failover
OpCode=None
RecordNumber=3393348
Keywords=Audit Success, Classic
Message=Audit event: event_time:2014-03-28 14:36:09.0903622
sequence_number:2
action_id:VSST
succeeded:true
permission_bitmask:0
is_column_permission:false
session_id:59
server_principal_id:2
database_principal_id:1
target_server_principal_id:0
target_database_principal_id:0
object_id:0
class_type:SR

We do know however that all eventtypes are working properly and going into their proper Indexes because I consistently see EventCode 33205 being brought into our Splunk Indexer Development environment based on the sample from my Splunk event below:

03/28/2014 10:36:09 AM
LogName=Security
SourceName=MSSQLSERVER$AUDIT
EventCode=33205
EventType=0
Type=Information

I'm extremely puzzled that the props.conf that came with the app would have Regular Expressions that do not work but as I mentioned both when I run them within Splunk or through an online RegEx tested and my sample data I never see any matches, hence we go to use the eventtype=mssql-audit and process it through the transforms.conf file against the lookups, we never return any data which explains why the dashboards do not populate.

Any help on this appreciated because rather than write our own RegEx's we want to see where the error might be at.

Thank you in advance.

0 Karma
1 Solution

ahall_splunk
Splunk Employee
Splunk Employee

The latest version of the SQL Server corrected the EXTRACT-based field extractions and replaced them with the more appropriate transforms-based extractions. Please re-try with the latest version.

Thanks!

View solution in original post

ahall_splunk
Splunk Employee
Splunk Employee

The latest version of the SQL Server corrected the EXTRACT-based field extractions and replaced them with the more appropriate transforms-based extractions. Please re-try with the latest version.

Thanks!

millern4
Communicator

Thanks Adrian,

After doing the update all security dashboards are now displaying correctly with the exception of the failed server logins:

It's failing on a part of the transforms:
stats latest(time) as lastattempt,sum(flc) as flc,sum(slc) as slc,values(flv) as flv,values(slv) as slv by srcip | where flc > 0 | eval slc=if(slc>0,"Yes","") | eval lastattempt=strftime(lastattempt,"%F %T") | table srcip,lastattempt, flv, slv, slc | rename srcip as "Source", last_attempt as "Last Attempt", flv as "Failed Logon IDs", slv as "Successful Logon IDs", slc as "Successful?"

0 Karma

millern4
Communicator

Additional information on this:

The data we are looking to process through the field extractions is not showing up in the field 33205, it is showing up under "Message" which is now explaining why these do not return data when run against the extractions contained within the Splunk for SQL Server App props.con

0 Karma
Get Updates on the Splunk Community!

Observability Highlights | November 2022 Newsletter

 November 2022Observability CloudEnd Of Support Extension for SignalFx Smart AgentSplunk is extending the End ...

Avoid Certificate Expiry Issues in Splunk Enterprise with Certificate Assist

This blog post is part 2 of 4 of a series on Splunk Assist. Click the links below to see the other ...

Using Machine Learning for Hunting Security Threats

REGISTER NOW Seeing the exponential hike in global cyber threat spectrum, organizations are now striving more ...