Splunk Search

rex to extract field from csv

surekhasplunk
Communicator

Hi,

I want to extract below fields
First 5 fields are automatically extracted by splunk witihout any issues. But last 2 fields since they are starting with # symbol didnt get extracted correctly. How can i extract them ?
EmpNo,EmpName,EmpTitle,Region,Country,# completed,#not completed

12345,"Razal,Rafi",Lead Service Specialist,UK,United Kingdom,0,1

My file contnt looks like above line. I surrounded them with double quotes thinking that it will be easy for extraction since i have comma in Name field along with comma as delimiter to look like below.

"12345",""Razal,Rafi"","Lead Service Specialist","UK","United Kingdom","0","1"

Please help me with rex for field extraction. to extract last two fields.

Tags (3)
0 Karma
1 Solution

ips_mandar
Builder

Try this regex when other fields are not surrounded by double quotes:

rex ",(?<completed>\d),(?<notCompleted>\d)$"

View solution in original post

0 Karma

logloganathan
Motivator

Could you please provide raw event to generate the Regex

0 Karma

ips_mandar
Builder

Try this regex when other fields are not surrounded by double quotes:

rex ",(?<completed>\d),(?<notCompleted>\d)$"
0 Karma

splunker12er
Motivator
yoursearch |rex (?P<field1>\w+),(?P<field2>\w+),(?P<field3>\w+),(?P<field4>\w+),(?P<field5>\w+),(?P<field6>\#\s\w+),(?P<field7>\#\w+\s\w+)

surekhasplunk
Communicator

Its not working giving me error at field2 as it has got comma inside the name i believe

0 Karma

splunker12er
Motivator

Does all your data will have the same comma or only for this particular event?

0 Karma

splunker12er
Motivator

try this,

yoursearch |rex '(?P<Emp_No>\d+),(?P<Emp_Name>\S+),(?P<Title>[\w|\s]+),(?P<Region>\w+),(?P<Country>[\w|\s]+),(?P<Completed>\d+),(?P<Not_Completed>\d+)' | table *

splunker12er
Motivator

i saved the results here - you can verify below

https://regex101.com/r/ZenZiK/1

splunker12er
Motivator

if this help, please vote/ mark as answered//...

🙂
thanks

0 Karma

surekhasplunk
Communicator

Thanks you very much @splunker12er and ips_mandar. I just tweaked a little and it worked like magic. i used $ to get the last field and rest like (?P\d) to get the fields.

0 Karma

surekhasplunk
Communicator

only for that particular event .. As other events might have 2 commas or might not have comma at all. for that column
Can we just ignore writing rex for those fields as splunk already extracts them well . its just that m more worried about the last 2 fields.

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...