topic Splunk for SQL Server - props.conf field extractions RegEx not returning data in All Apps and Add-ons

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

millern4 — Mon, 28 Sep 2020 16:17:26 GMT

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.

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

millern4 — Tue, 01 Apr 2014 15:53:41 GMT

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

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

ahall_splunk — Thu, 03 Apr 2014 00:04:36 GMT

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!

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

millern4 — Fri, 04 Apr 2014 18:41:59 GMT

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?"