topic Re: Field extraction based on the element position in a csv in Splunk Search

Field extraction based on the element position in a csv

tallasky — Mon, 02 Dec 2013 22:46:38 GMT

Hello,

I have csv files but Splunk can`t auto extact the fields based on headers beacause we've assigned our own sourcetypes and i dont have access to props.conf or other core files, i need to extract the fields using regular expressions.
An example of line in my csv is:

"Date";"id";"disk";"partition";"disktype";"numdisk";"servers";"bwu";"bwt";"bwp";"sizeu";"sizet"

I need a regex that gives me the value of a field based on the position, maybe based on the number of ; before it regardeless of what's inside " "

If i use the generate button on a values corresponding to id for exemple i get something like:

(?i)^(?:[^:]*:){2}\d+";"(?P[^"]+) , how do i change that to have something where i can just add an element or change a value to get the next element

Thank you in advance.

Re: Field extraction based on the element position in a csv

somesoni2 — Mon, 02 Dec 2013 23:50:05 GMT

I tested with sample data and following seems to be working for me.

index=yourindex sourcetype=yoursourcetype | rex "(?i)^(?:[^;]*;){0}\"(?P<field1>[^\"]+)"

Field index will start from 1 and number within curly brackets will start from 0.
So

for field 1 "(?i)^(?:[^;]*;){0}\"(?P<field1>[^\"]+)"
for field 2 "(?i)^(?:[^;]*;){1}\"(?P<field2>[^\"]+)"
for field 3 "(?i)^(?:[^;]*;){2}\"(?P<field3>[^\"]+)"

Hope this helps

Re: Field extraction based on the element position in a csv

lguinn2 — Tue, 03 Dec 2013 08:27:58 GMT

This will work - but I might put it in a macro so that I could re-use it without typing

yoursearchhere
| rex "(?<Date>.*?);(?<id>.*?);(?<disk>.*?);(?<partition>.*?);(?<disktype>.*?);(?<numdisk>.*?);(?<servers>.*?);(?<bwu>.*?);(?<bwt>.*?);(?<bwp>.*?);(?<sizeu>.*?);(?<sizet>.*)"
| whateverelse

You won't be able to cut-and-paste this unless you remove the line-wrap...

Finally, the actual regular expression for each field is .*? which in this context means "any characters up to but not including the next ;"

Last but definitely not least: just because you can't manually edit props.conf does not mean that you can't create permanent field extractions. Under Manager or Settings (depending on your version of Splunk), find Fields then look for Field Extractions. Click New. Fill out the form. Make sure the type is Inline, and put the following in the Extraction/Transform:

(?<Date>.*?);(?<id>.*?);(?<disk>.*?);(?<partition>.*?);(?<disktype>.*?);(?<numdisk>.*?);(?<servers>.*?);(?<bwu>.*?);(?<bwt>.*?);(?<bwp>.*?);(?<sizeu>.*?);(?<sizet>.*)

Save it. You may want to set the permissions so that others can use it too. There - you just updated props.conf, the hard way IMO 🙂