Splunk Search

How do I do a field extraction with a special character?

kcchu01
Explorer

Hello, I have some logs that required to extract the fields. the raw data is in the format as below.

"xxx","yyy","zzz","sd323ds","3343tds23@343"

I would like to extract the field like this
field 1: xxx
field 2: yyy
field 3: zzz
field 4: sd323ds
field 5: 3343tds23@343

I use the field extractor, and it almost did what I want. The regular expression extracts the field by the double quotes, but there are some cases where double quotes may appear in some field contents, such as

"xxx","yyy","zzz","sd323ds","3343"Hello World" tds23@343"

I want the field 5 is 3343"Hello World" tds23@343, but it cannot be done by the current regular expression.

How can I modify my existing regular expression to achieve this?

0 Karma
1 Solution

whrg
Motivator

Hi!
Your logs look very much like a CSV file. How about you add a CSV header to your log file:

field_1,field_2,field_3,field_4,field_5
"xxx6","yyy","zzz","sd323ds","3343tds23@343"
"xxx","yyy","zzz","sd323ds","3343"Hello World" tds23@343"
...

Then you only need to set sourcetype=csv on your input. Splunk will extract the fields automatically.

Alternatively, have a look at this regular expression:

^"(?<field_1>.*)","(?<field_2>.*)","(?<field_3>.*)","(?<field_4>.*)","(?<field_5>.*)"$

Example on regex101.com

View solution in original post

vnravikumar
Champion

Try this

| makeresults 
| eval test= "\"xxx\",\"yyy\",\"zzz\",\"sd323ds\",\"3343\"Hello World\" tds23@343\"" 
| rex field=test "\"(?P<field1>.*)\",\"(?P<field2>.*)\",\"(?P<field3>.*)\",\"(?P<field4>.*)\",\"(?<field5>.*)"
0 Karma

whrg
Motivator

Hi!
Your logs look very much like a CSV file. How about you add a CSV header to your log file:

field_1,field_2,field_3,field_4,field_5
"xxx6","yyy","zzz","sd323ds","3343tds23@343"
"xxx","yyy","zzz","sd323ds","3343"Hello World" tds23@343"
...

Then you only need to set sourcetype=csv on your input. Splunk will extract the fields automatically.

Alternatively, have a look at this regular expression:

^"(?<field_1>.*)","(?<field_2>.*)","(?<field_3>.*)","(?<field_4>.*)","(?<field_5>.*)"$

Example on regex101.com

kcchu01
Explorer

Thank you very much for your suggestion which almost perfect for my need. I modified a little bit such that the line break is also accepted. For my understanding the expression is separating the fields by the "," . I am asked that if the content is containing this pattern, fields will be extracted wrongly. Is it possible for regular expression to handle such the case that may be something like "escape character"? I am not good at regular expression and have no idea on this.

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 ...