Splunk Search

Json Parsing.

onthakur
Engager

I have below json and I want table of url and corresponding duration.

 

{
"details": {
"sub-trans": [
{
"app-trans-id": "123",
"sub-trans-id": "234",
"startTime": "2024-01-18T12:37:12.482Z",
"endTime": "2024-01-18T12:37:12.502Z",
"duration": 20,

"req": {
"url": "http://abc123",

},
{
"app-trans-id": "123",
"sub-trans-id": "567",
"startTime": "2024-01-18T12:37:12.506Z",
"endTime": "2024-01-18T12:37:12.550Z",
"duration": 44,

"req": {
"url": "https://xyz567",

},
]
}
}

 

I am using below splunk query but duration field is not populating in table.

Kindly help

index=hello
|spath output=url details.sub-trans{}.req.url| mvexpand url
|spath output=duration details.sub-trans{}.duration |mvexpand duration
|table url,duration

Labels (1)
0 Karma
1 Solution

richgalloway
SplunkTrust
SplunkTrust

The duration field populates in my sandbox, but values are duplicated.  That's likely because the two mvexpand calls break the association between url and duration.  Try this query, instead:

index=hello
| spath output=url details.sub-trans{}.req.url
| spath output=duration details.sub-trans{}.duration
``` Combine url and duration ```
| eval pairs=mvzip(url,duration)
``` Put each pair into a separate event ```
| mvexpand pairs
``` Extract the url and duration fields ```
| eval pairs=split(pairs,","), url=mvindex(pairs,0), duration=mvindex(pairs,1)
| table url,duration

 

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

View solution in original post

0 Karma

dtburrows3
Builder

By going off what you pasted it is coming back as an invalid JSON, I would check that first.

dtburrows3_1-1705601292399.png

But assuming that it is just a copy/paste error and you do have a valid json object as _raw then I would probably do an spath like this to retain associations between url and durations.

index=hello
    | spath input=_raw path=details.sub-trans{} output=sub_trans
    | fields - _raw
    | table sub_trans
    | mvexpand sub_trans
    | spath input=sub_trans
    | fields - sub_trans

 

dtburrows3_0-1705601059634.png


You can see here all the field are extracted and they maintained their relationships to their individual url/duration according to the structure of detail.sub-trans{} array. Does require an mvexpand though, just keep an eye out for memory limits.

To retain specific associations of the url to its respective duration by extracting both as individual multivalued fields is possible but can be problematic. If any of them have a null entry for whatever reason then all associations are thrown off from that point on. Thats why in these sort of situations I would much rather extract the entire nested json object out of the array, mvexpand that, then spath that internal json. 

Also want to note that doing a mvexpand against two multivalue fields like in your original search will completely loose all association between which url should have which duration. you will actually end up with N^2 results when by the structure of the json I believe there should only be N results.

0 Karma

richgalloway
SplunkTrust
SplunkTrust

The duration field populates in my sandbox, but values are duplicated.  That's likely because the two mvexpand calls break the association between url and duration.  Try this query, instead:

index=hello
| spath output=url details.sub-trans{}.req.url
| spath output=duration details.sub-trans{}.duration
``` Combine url and duration ```
| eval pairs=mvzip(url,duration)
``` Put each pair into a separate event ```
| mvexpand pairs
``` Extract the url and duration fields ```
| eval pairs=split(pairs,","), url=mvindex(pairs,0), duration=mvindex(pairs,1)
| table url,duration

 

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

onthakur
Engager

Thanks you very much , your solution worked perfectly.

 

0 Karma
Get Updates on the Splunk Community!

Detecting Remote Code Executions With the Splunk Threat Research Team

WATCH NOWRemote code execution (RCE) vulnerabilities pose a significant risk to organizations. If exploited, ...

Enter the Splunk Community Dashboard Challenge for Your Chance to Win!

The Splunk Community Dashboard Challenge is underway! This is your chance to showcase your skills in creating ...

.conf24 | Session Scheduler is Live!!

.conf24 is happening June 11 - 14 in Las Vegas, and we are thrilled to announce that the conference catalog ...