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

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!

Introducing the 2024 SplunkTrust!

Hello, Splunk Community! We are beyond thrilled to announce our newest group of SplunkTrust members!  The ...

Introducing the 2024 Splunk MVPs!

We are excited to announce the 2024 cohort of the Splunk MVP program. Splunk MVPs are passionate members of ...

Splunk Custom Visualizations App End of Life

The Splunk Custom Visualizations apps End of Life for SimpleXML will reach end of support on Dec 21, 2024, ...