Splunk Search

How to extract a field from raw json?

asveturi
Path Finder

Hi Team,

From the below raw JSON string in Splunk, I am trying to display only correlationId column in a table, can someone help with a query on how to achieve this?

 

Also wanted to know if it can be achieved from a regular expression.

 

 

index= test1, sourcetype=abc



{

  "eventName": “test”,

  "sourceType”: “ats”,

  "detail": {

    "field": “abctest-1”,

    "trackInformation”: {

      "correlationId": “12345”,

      "components": [

        {

          "publisherTimeLog”: "2022-08-31T13:19:18.726",

          “MetaData”: “cmd”,

          "executionTimeInMscs”: “2”5,

          "receiverTimeLog”: "2022-08-31T13:19:18.725"

        }

      ]

    },

    "value": “imdb”,

    "timestamp": 1455677

  },

}





Output:

______



correlationID

———————

12345

 

 

Labels (1)
0 Karma
1 Solution

richgalloway
SplunkTrust
SplunkTrust

Yes, it can be extracted using a regular expression.  Try this

 

index=test1 sourcetype=abc
| rex "\\\"correlationId\\\": \\\“(?<correlationId>[^\\\"]+)"

 

Three backslashes are needed because there are multiple layers of parsing until the regex is evaluated.

---
If this reply helps you, Karma would be appreciated.

View solution in original post

ww9rivers
Communicator

Try this:

index=test1 sourcetype=abc
| table detail.trackInformation.correlationId

Obviously, you can rename that field if you want to.

0 Karma

asveturi
Path Finder

tried this one too, but some how i am getting all null rows under statistics tab and not able to display any corrl ids in table.

0 Karma

richgalloway
SplunkTrust
SplunkTrust

Yes, it can be extracted using a regular expression.  Try this

 

index=test1 sourcetype=abc
| rex "\\\"correlationId\\\": \\\“(?<correlationId>[^\\\"]+)"

 

Three backslashes are needed because there are multiple layers of parsing until the regex is evaluated.

---
If this reply helps you, Karma would be appreciated.

asveturi
Path Finder

One extra '\' helped since it was looking for escape characters. Helped a lot! Thank you.

0 Karma

asveturi
Path Finder

index=test1 sourcetype=abc | rex field=_raw "\\\"correlationId\\\": \\\“(?[^\\\"]+)" | search "*" correlationId | stats count by correlationId

 

It is able to pull up the correlation ids now, but still not able to display in table. refered few links but could not find any luck. Any pointers?

 

0 Karma

inventsekar
Ultra Champion
index=test1 sourcetype=abc
| rex "\\\"correlationId\\\": \\\“(?<correlationId>[^\\\"]+)"
| stats count by correlationId

 

Hi @asveturi ...pls update us what happen when you run this above one..

may i know why you were using  this - "search "*" correlationId" please.

 

thanks,

happy splunking

asveturi
Path Finder

@inventsekar Rather than stats by count, i had to fix regular expression to take a escape character. It helped me fix. Thanks for responding and helping with great insights.

0 Karma

asveturi
Path Finder

@inventsekar 

1. When i run the above, the table which i am expecting under statistics is not returning any results or the list of IDs displayed.

2. The reason i was using search "*" was in real time, i will be getting the value from a text box and will be passed as '$CORRELATION_ID'. To do testing from the search i am replace it with * to list all corrl ids.

 

0 Karma

inventsekar
Ultra Champion

well, lets go step by step.

1. i see you are trying to create a table output, inside a dashboard.. is that right

2. the SPL query with rex and stats.. pls run this on Splunk Search Bar and try if it creates the table u r looking for. if it gives, then, dashboard troubleshooting will be easy.

3. u r looking for search time or index time extraction pls..  (for a big environment, this makes lot of difference. for a small project, you can ignore this search time vs index time)

4. some more info like.. splunk version, any special apps, dashboard viz app or basic dashboards.. would be helpful, thanks.

Get Updates on the Splunk Community!

Adoption of RUM and APM at Splunk

    Unleash the power of Splunk Observability   Watch Now In this can't miss Tech Talk! The Splunk Growth ...

March Community Office Hours Security Series Uncovered!

Hello Splunk Community! In March, Splunk Community Office Hours spotlighted our fabulous Splunk Threat ...

Stay Connected: Your Guide to April Tech Talks, Office Hours, and Webinars!

Take a look below to explore our upcoming Community Office Hours, Tech Talks, and Webinars in April. This post ...