topic Re: extract count of name value pairs from a json in Splunk Search

extract count of name value pairs from a json

mhdzabi — Fri, 01 Mar 2024 10:34:54 GMT

Hi,

I have multiple events with the following JSON object.

{ "timeStamp": "2024-02-29T10:00:00.673Z", "collectionIntervalInMinutes": "1", "node": "plgiasrtfing001", "inboundErrorSummary": [ { "name": "400BadRequestMalformedHeader", "value": 1 }, { "name": "501NotImplementedMethod", "value": 2 }, { "name": "otherErrorResponses", "value": 1 } ] }

I am trying to extract the name/values from the inboundErrorSummary array and display the sum total of all the values of the same name and plot them by time.

So the output should be something like

Date	400BadRequestMalformedHeader	501NotImplementedMethod	otherErrorResponses
2024-02-29T10:00:00	1	2	1
2024-02-29T11:00:00	10	40	50

Even a total count of each name field should also work. I am quite new to splunk queries, so hope someone can help and also explain the steps on how its done.

Thanks in advance.

Re: extract count of name value pairs from a json

PickleRick — Fri, 01 Mar 2024 11:25:52 GMT

Argh. That's ugly data.

You need to firstly extract the array part

| spath inboundErrorSummary{}

Then you have to split it into separate rows

| mvexpand inboundErrorSummary{}

And then you have to parse the json again

| spath input=inboundErrorSummary{}

At this point you'll have separate fields called "name" and "value" at each result row and you'll be able to do stats/chart/timechart/whatever you want with it.

Re: extract count of name value pairs from a json

thambisetty — Fri, 01 Mar 2024 13:59:57 GMT

| makeresults | eval _raw="{ \"timeStamp\": \"2024-02-29T10:00:00.673Z\", \"collectionIntervalInMinutes\": \"1\", \"node\": \"plgiasrtfing001\", \"inboundErrorSummary\": [ { \"name\": \"400BadRequestMalformedHeader\", \"value\": 1 }, { \"name\": \"501NotImplementedMethod\", \"value\": 2 }, { \"name\": \"otherErrorResponses\", \"value\": 1 } ] }| { \"timeStamp\": \"2024-02-29T10:00:00.674Z\", \"collectionIntervalInMinutes\": \"1\", \"node\": \"plgiasrtfing001\", \"inboundErrorSummary\": [ { \"name\": \"400BadRequestMalformedHeader\", \"value\": 10 }, { \"name\": \"501NotImplementedMethod\", \"value\": 5 }, { \"name\": \"otherErrorResponses\", \"value\": 6 } ] }" | makemv _raw delim="|" | rename _raw as raw | mvexpand raw | rex field=raw "timeStamp\"\: \"(?<_time>[^\"]+)" | rename raw as _raw ```Below is the SPL you need potentially``` | spath inboundErrorSummary{} | mvexpand inboundErrorSummary{} | spath input=inboundErrorSummary{} | chart values(value) over _time by name

Re: extract count of name value pairs from a json

yuanliu — Fri, 01 Mar 2024 21:07:07 GMT

I think @thambisetty meant to use sum instead of values. Also, your seem to desire result be bucketed by hour aligned at the beginning of a calendar hour. If so, you also need a bin command.

```Below is the SPL you need potentially``` | spath inboundErrorSummary{} | mvexpand inboundErrorSummary{} | spath input=inboundErrorSummary{} | bin _time span=1h@h | chart sum(value) over _time by name

Now, I recently learned fromjson command introduced in Splunk 9. It makes SPL somewhat easier to read.

| fromjson _raw | mvexpand inboundErrorSummary | spath input=inboundErrorSummary | timechart span=1h@h sum(value) by name

Here, timechart is equivalent to @thambisetty's chart but you do not have to enter a separate bin command.