topic Re: How to extract a field from raw json? in Splunk Search

How to extract a field from raw json?

asveturi — Wed, 31 Aug 2022 19:19:09 GMT

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

Re: How to extract a field from raw json?

richgalloway — Wed, 31 Aug 2022 21:02:56 GMT

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.

Re: How to extract a field from raw json?

asveturi — Wed, 31 Aug 2022 20:24:51 GMT

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?

Re: How to extract a field from raw json?

inventsekar — Wed, 31 Aug 2022 20:44:58 GMT

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

Re: How to extract a field from raw json?

ww9rivers — Wed, 31 Aug 2022 20:47:05 GMT

Try this:

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

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

Re: How to extract a field from raw json?

asveturi — Wed, 31 Aug 2022 21:29:43 GMT

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

Re: How to extract a field from raw json?

asveturi — Wed, 31 Aug 2022 21:30:40 GMT

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.

Re: How to extract a field from raw json?

inventsekar — Wed, 31 Aug 2022 21:42:22 GMT

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.

Re: How to extract a field from raw json?

asveturi — Tue, 06 Sep 2022 01:15:05 GMT

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

Re: How to extract a field from raw json?

asveturi — Tue, 06 Sep 2022 01:17:37 GMT

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