topic Re: extracting fields & values from SQL logs in Splunk Search

extracting fields & values from SQL logs

rizzo75 — Wed, 02 Apr 2014 15:31:43 GMT

I am trying to extract field names and values from SQL logs.
IE - “… INSERT INTO table (COL1, COL2) VALUES ('VAL1', 'VAL2’)”

COL1=VAL1
COL2=VAL2

Any thoughts on how to do this?

Thanks,
Joe

Re: extracting fields & values from SQL logs

aelliott — Wed, 02 Apr 2014 15:56:48 GMT

Have you attempted to use db connect? https://apps.splunk.com/app/958/

Re: extracting fields & values from SQL logs

martin_mueller — Wed, 02 Apr 2014 16:33:34 GMT

You might get away with a transforms.conf extraction like this:

[your_stanza]
REGEX = (?i)insert\s*into\s*(?<table>\S+)\s*\((?<_KEY_1>\w+)(?:,\s*(?<_KEY_2>\w+))?(?:,\s*(?<_KEY_3>\w+))?(?:,\s*(?<_KEY_4>\w+))?\)\s*values\s*\('(?<_VAL_1>[^']+)'(?:,\s*'(?<_VAL_2>[^']+)')?(?:,\s*'(?<_VAL_4>[^']+)')?(?:,\s*'(?<_VAL_4>[^']+)')?\)

That's for up to four columns, but you probably see how it's expandable to any number. I didn't test this though, but I'm optimistic it might actually work. For a little background, see http://docs.splunk.com/Documentation/Splunk/6.0.2/Admin/transformsconf (search for "_key_").

Re: extracting fields & values from SQL logs

rizzo75 — Wed, 02 Apr 2014 16:34:30 GMT

I wish I could. I do not have access to the DB.

Re: extracting fields & values from SQL logs

rizzo75 — Wed, 02 Apr 2014 16:35:56 GMT

Thanks - that is actually what I am doing now.
I was hoping to automatically extract the field name and values as this is tedious and static.

Re: extracting fields & values from SQL logs

martin_mueller — Mon, 28 Sep 2020 16:18:00 GMT

This should extract the column names automatically, due to the magic _KEY_x and _VAL_x names.

Re: extracting fields & values from SQL logs

rizzo75 — Thu, 03 Apr 2014 18:26:35 GMT

Ah. Thanks!

Re: extracting fields & values from SQL logs

martin_mueller — Thu, 03 Apr 2014 20:40:46 GMT

Does it work? I still didn't get around to actually test my expression 😄

Re: extracting fields & values from SQL logs

rizzo75 — Fri, 11 Apr 2014 20:31:36 GMT

I actually did not try it, but it looks like it would work if the number of fields was static.

Re: extracting fields & values from SQL logs

rizzo75 — Fri, 11 Apr 2014 20:32:26 GMT

Here is an example of what I went with. Not that I am proud of it. 🙂

| history events=true | head 1 | fields - * _* | eval sql="INSERT INTO `table1` (`field1`, `field2`, `field3`, `field4`) VALUES (0, 1, NULL, 'sometext')"
| rex field=sql "INSERT INTO\s+.(?<tablename>\w+).\s+(?<FIELDS>\(.*\))\s+VALUES\s+(?<VALUES>\(.*\))"
| rex mode=sed field=FIELDS "s/`//g"
| rex mode=sed field=VALUES "s/'//g"
| rex max_match=0 field=FIELDS "\(?(?<MVFIELD>[\s\S]+?)[,)]\s?"
| rex max_match=0 field=VALUES "\(?(?<MVVALUE>[\s\S]+?)[,)]\s?"
| eval _raw=mvzip(MVFIELD,MVVALUE,"=") | extract
| fields - _raw FIELDS VALUES MVFIELD MVVALUE
| table *

Re: extracting fields & values from SQL logs

martin_mueller — Sat, 12 Apr 2014 04:48:49 GMT

You could create one expression with the maximum expected number of fields, they're all marked as optional.

Re: extracting fields & values from SQL logs

geraldcontreras — Thu, 19 Nov 2020 06:44:50 GMT

Thank you thank you thank you!

This was exactly what i was after, then i used mvexpand and mvindex with split to split the values to the field