Splunk Search

How do you extract data between double quotes?

Deepz2612
Explorer

I have logs as below.I would want to extract the data within the quotes

 **message**: "vin":"ABCDEFTGH","Type":"Obs-AVE","color":"ABSOLUTE BLACK"

Where message is a field.

I want the value as:

VIN

ABCDEFTGH

Tags (2)
0 Karma
1 Solution

renjith_nair
SplunkTrust
SplunkTrust

@Deepz2612 ,

Try

"Your current search"  |rex field=_raw max_match=0 "\"(?<KEY>.+?)\":\"(?<VALUE>.+?)\""
|eval x=mvzip(KEY,VALUE)|table x|mvexpand x
|rex field=x "(?<KEY>.+),(?<VALUE>.+)"|fields - x|transpose 0 header_field=KEY|fields - column

This will result in

vin         Type        color
ABCDEFTGH   Obs-AVE     ABSOLUTE BLACK
Happy Splunking!

View solution in original post

vnravikumar
Champion

Try this

| makeresults 
 | eval test ="**message**: \"vin\":\"ABCDEFTGH\",\"Type\":\"Obs-AVE\",\"color\":\"ABSOLUTE BLACK\"" 
 | rex field=test "\"vin\":\"(?<vin>.*)\",\"Type\":\"(?<Type>.*)\",\"color\":\"(?<color>.*)\"" 
 | table vin, Type, color
0 Karma

renjith_nair
SplunkTrust
SplunkTrust

@Deepz2612 ,

Try

"Your current search"  |rex field=_raw max_match=0 "\"(?<KEY>.+?)\":\"(?<VALUE>.+?)\""
|eval x=mvzip(KEY,VALUE)|table x|mvexpand x
|rex field=x "(?<KEY>.+),(?<VALUE>.+)"|fields - x|transpose 0 header_field=KEY|fields - column

This will result in

vin         Type        color
ABCDEFTGH   Obs-AVE     ABSOLUTE BLACK
Happy Splunking!

Deepz2612
Explorer

Hi,

The question here is how do i extract the data within the quotes.
message: "vin":"ABCDEFTGH","Type":"Obs-AVE","color":"ABSOLUTE BLACK"
message: "vin":"EFGHTYB","Type":"Obs-ABE","color":"BLACK"

Like in the logs above ,I would want to extract the values as between the quotes as a field value.
eg: whatever data follows after the word "vin":" and ended with ", should be extracted as one field.
ABCDEFTGH

0 Karma

harsmarvania57
SplunkTrust
SplunkTrust

Have you tried the query provided by me and @renjith.nair ? It is extracting ABCDEFTGH in field vin, Obs-AVE in field Type and ABSOLUTE BLACK in field color

0 Karma

Deepz2612
Explorer

It did work but not in the way i expected.
I just wanted to have a reqular expression to extract the data that is present within the quotes and a colon.

rex field = message *********************

message: "vin":"EFGHTYB"

Here the data EFGHTYB is between :" and "

0 Karma

renjith_nair
SplunkTrust
SplunkTrust

@Deepz2612 ,

If you just want VIN, try this

rex field=your_message_field "vin":\"(?<VIN>\w+)\""
Happy Splunking!
0 Karma

Deepz2612
Explorer

Cool!! Thanks for your help

0 Karma

harsmarvania57
SplunkTrust
SplunkTrust

Hi @Deepz2612,

I am not able to find an easy way to do this but you can try below query.

<yourBaseSearch>
| rex "\"(?:(?<a>\w+))\"\:\"(?<b>(?:.)*?)\"" max_match=0
| eval c = mvzip(a,b)
| mvexpand c
| eval d=mvindex(split(c,","),0), e=mvindex(split(c,","),1)
| eval {d}=e
| stats values(color) AS color, values(vin) AS vin, values(Type) AS Type by _time

Here is run anywhere search based on sample data

| makeresults | eval _raw="message: \"vin\":\"ABCDEFTGH\",\"Type\":\"Obs-AVE\",\"color\":\"ABSOLUTE BLACK\""
| rex "\"(?:(?<a>\w+))\"\:\"(?<b>(?:.)*?)\"" max_match=0
| eval c = mvzip(a,b)
| mvexpand c
| eval d=mvindex(split(c,","),0), e=mvindex(split(c,","),1)
| eval {d}=e
| stats values(color) AS color, values(vin) AS vin, values(Type) AS Type by _time

EDIT : REGEX Updated.

0 Karma
Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...