topic Re: Complex JSON (and Multivalue) processing in Splunk Search

Complex JSON (and Multivalue) processing

jerewill — Tue, 19 Jul 2022 18:58:55 GMT

I've been working on a project with JSON in the event where Tags are stored similar to this...

{
"Name": "example",
"Tags": [
 {"Key": "Building", "Value": "1"},
 {"Key": "Floor", "Value": "2"},
 {"Key": "Color", "Value": "Red"}
 ]
}

The default extract from spath provided the Tags{}.Key and Tags{}.Value fields which were pretty much useless as-is. What I wanted was for each tag to be a field on the event so that you could use them in a search, ex. Building=1 AND Color=Red. But the number of tags varies and the same value could appear in multiple tags (i.e. Building=1 AND Floor=1).

Here's what I came up with so far... I'm curious if anyone has a better suggestion.

| rename Tags{}.Key as Key, Tags{}.Value as Value | eval zip=mvzip(Key,Value, ":") | mvexpand zip |rex field=zip mode=sed "s/$/\"}/g" |rex field=zip mode=sed "s/^/{\"tag./g"| rex field=zip mode=sed "s/:/\": \"/g" | spath input=zip | transaction Name

This approach basically uses mvzip and mvexpand to pull apart the Tags, then uses rex with sed to rebuild a JSON object to pass back through spath. It seems pretty complex, but I just can't see a better way to do it.

I'm interested to hear if anyone has a better suggestion?

Re: Complex JSON (and Multivalue) processing

bowesmana — Tue, 19 Jul 2022 22:46:01 GMT

This app may help you - developed by Brett Adams, a Splunk Trust member, it is designed to extract arrays of KV pairs from JSON arrays.

https://splunkbase.splunk.com/app/6161/

Re: Complex JSON (and Multivalue) processing

yuanliu — Wed, 20 Jul 2022 01:47:06 GMT

You shouldn't have to zip as the data is well contained. Use path in spath.

``` assuming field json contains that JSON object ``` | spath input=json path=Tags{} | mvexpand Tags{} | spath input=Tags{}

With sample data, you get

Key	Tags{}	Value	_time
Building	{"Key": "Building", "Value": "1"}	1	2022-07-20 01:45:58
Floor	{"Key": "Floor", "Value": "2"}	2	2022-07-20 01:45:58
Color	{"Key": "Color", "Value": "Red"}	Red	2022-07-20 01:45:58

Does this look like something you want?

Re: Complex JSON (and Multivalue) processing

jerewill — Wed, 20 Jul 2022 03:25:07 GMT

Not quite…

I want one record that looks more like this…

Name	Building	Floor	Color
example	1	2	Red

The problem is that you can’t effectively search multiple tags with what you have there. For example, I can search `Key=Floor AND Value=2` to get the second record of the mvexpand, but I can’t get a search that links them together. I need to be able to do a search that is more like `Building=1 AND Floor=2` Does that make sense?

Re: Complex JSON (and Multivalue) processing

bowesmana — Wed, 20 Jul 2022 05:35:29 GMT

This will do the trick - using foreach

| makeresults | eval _raw="{ \"Name\": \"example\", \"Tags\": [ {\"Key\": \"Building\", \"Value\": \"1\"}, {\"Key\": \"Floor\", \"Value\": \"2\"}, {\"Key\": \"Color\", \"Value\": \"Red\"} ] }" | spath | rename Tags{}.* as * | foreach 0 1 2 3 4 5 6 7 8 9 10 [ eval k=mvindex(Key, <<FIELD>>), v=mvindex(Value, <<FIELD>>), x_{k}=v ] | table name x_* | rename x_* as *

just make the foreach loop big enough to accommodate the array length.

However, I do believe that's exactly what the app is designed to to more efficiently.

Re: Complex JSON (and Multivalue) processing

yuanliu — Wed, 20 Jul 2022 06:04:24 GMT

That looks awfully like transpose. Would this work?

| spath input=json path=Tags{} | mvexpand Tags{} | spath input=Tags{} | table Key Value | transpose header_field=Key column_name=Name

Name	Building	Floor	Color
Value	1	2	Red

Re: Complex JSON (and Multivalue) processing

jerewill — Wed, 20 Jul 2022 14:22:34 GMT

That doesn't seem to work for me. The table before the transpose looks like this...

Name	Key	Value
example	Building	1
example	Floor	2
example	Color	Red

Then the transpose turns the Name field into columns which, again, doesn't get something that can be well searched for things like (Building=1 AND Floor=2).

Re: Complex JSON (and Multivalue) processing

jerewill — Wed, 20 Jul 2022 14:55:50 GMT

I do think that this seems like a good solution, but I think adding an application will have to be an alternative solution for me in this case. But this is very interesting and does do what I want very nicely. I was not aware of it previously, so thank you.

array2object path="Tags" key=Key value=Value

Very easy.

Complex JSON (and Multivalue) processing

yuanliu — Thu, 21 Jul 2022 05:21:14 GMT

A different hack - similar idea to mvzip but looks more SPLy, is to use mvmap and extract.

| rename Tags{}.* AS * | eval ind = mvrange(0, mvcount(Key)) | eval pair=mvmap(ind, mvappend(pair, mvindex(Key, ind). "=" . mvindex(Value, ind))) | rename _raw AS temp, pair AS _raw | kv | rename temp As _raw

Output from the sample data is thus

Name	Building	Floor	Color
example	1	2	Red