How to extract a field from raw json?

[asveturi]

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

[ww9rivers]

Try this:

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

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

[asveturi]

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.

[richgalloway · Accepted solution]

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.

[asveturi]

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

[asveturi]

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?

[inventsekar]

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]

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

[asveturi]

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

[inventsekar]

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.