Splunk Search

Field extraction based on the element position in a csv

tallasky
Explorer

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.

0 Karma
1 Solution

somesoni2
Revered Legend

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

View solution in original post

lguinn2
Legend

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 🙂

0 Karma

somesoni2
Revered Legend

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

Get Updates on the Splunk Community!

Detecting Remote Code Executions With the Splunk Threat Research Team

WATCH NOWRemote code execution (RCE) vulnerabilities pose a significant risk to organizations. If exploited, ...

Enter the Splunk Community Dashboard Challenge for Your Chance to Win!

The Splunk Community Dashboard Challenge is underway! This is your chance to showcase your skills in creating ...

.conf24 | Session Scheduler is Live!!

.conf24 is happening June 11 - 14 in Las Vegas, and we are thrilled to announce that the conference catalog ...