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
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 *
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
I wish I could. I do not have access to the DB.
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_").
You could create one expression with the maximum expected number of fields, they're all marked as optional.
I actually did not try it, but it looks like it would work if the number of fields was static.
Does it work? I still didn't get around to actually test my expression 😄
Ah. Thanks!
This should extract the column names automatically, due to the magic _KEY_x and _VAL_x names.
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.
Have you attempted to use db connect? https://apps.splunk.com/app/958/