extracting fields & values from SQL logs

[rizzo75]

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

[rizzo75]

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 *

[geraldcontreras]

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

[rizzo75]

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

[martin_mueller]

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_").

[martin_mueller]

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

[rizzo75]

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

[martin_mueller]

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

[rizzo75]

Ah. Thanks!

[martin_mueller]

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

[rizzo75]

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.

[aelliott]

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