Splunk Search

How to fix query to prevent Duplication of events when searching multi-value fields in Json using mvzip and mvexpand?

Marian
Explorer

Here is a reduced version of my JSON:

{
   records: [
     {
       errors4
       namename1
       pluginp1
       typetype1
     }
     {
       errors7
       namename2
       pluginp1
       typetype2
     }
     {
       errors0
       namename3
       pluginp2
       typetype3
     }
   ]

   session: {
     documentmy_doc
     userme
     version7.1
   }

}

There are 3 records in records{} so I expect to get 3 events using mvexpand, but I get 6 events. I'm using a similar query I've found in an answer in this community:

 

| spath
| rename records{}.name AS name, records{}.type AS type, records{}.plugin as plugin, records{}.errors as errors
| eval x=mvzip(mvzip(mvzip(name,type),plugin),errors)
| mvexpand x
| eval x=split(x,",")
| eval name=mvindex(x,0)
| eval type=mvindex(x,1)
| eval plugin=mvindex(x,2)
| eval errors=mvindex(x,3)
| table name, type, plugin, errors

 

 

I get 6 rows instead of 3:

name type plugin errors
name1 type1 p1 4
name2 type2 p1 7
name3 type3 p2 0
name1 type1 p1 4
name2 type2 p1 7
name3 type3 p2 0

 

Any suggestion how to fix the query to avoid the duplication?  Thanks!

Labels (2)
0 Karma
1 Solution

Marian
Explorer

mvdedup removed the duplication, thanks!

View solution in original post

Tags (1)
0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

The search works with example data - here is a runanywhere example

| makeresults
| eval _raw="{
   \"records\": [
     {
       \"errors\": 4,
       \"name\": \"name1\",
       \"plugin\": \"p1\",
       \"type\": \"type1\"
     },
     {
       \"errors\": 7,
       \"name\": \"name2\",
       \"plugin\": \"p1\",
       \"type\": \"type2\"
     },
     {
       \"errors\": 0,
       \"name\": \"name3\",
       \"plugin\": \"p2\",
       \"type\": \"type3\"
     }
   ],
   \"session\": {
     \"document\": \"my_doc\",
     \"user\": \"me\",
     \"version\": 7.1
   }
}"
| spath
| rename records{}.name AS name, records{}.type AS type, records{}.plugin as plugin, records{}.errors as errors
| eval x=mvzip(mvzip(mvzip(name,type),plugin),errors)
| mvexpand x
| eval x=split(x,",")
| eval name=mvindex(x,0)
| eval type=mvindex(x,1)
| eval plugin=mvindex(x,2)
| eval errors=mvindex(x,3)
| table name, type, plugin, errors
0 Karma

Marian
Explorer

This example indeed works. So I don't understand why it doesn't work with a real json.

I ran:

| table _raw

I got this:

{"session": {"version": "7.1", "user": "me", "document": "my_doc"}, "records": [{"plugin": "p1", "type": "type1", "name": "name1", "errors": 4}, {"plugin": "p1", "type": "type2", "name": "name2", "errors": 7}, {"plugin": "p2", "type": "type3", "name": "name3", "errors": 0}]}

No duplications in _raw, and only one event.

What is the difference between the makeresults example and a json with the same data?

 

0 Karma

harishalipaka
Motivator

@Marian - Use dedup or mvdedup commands 

| eval x=mvdedup(mvzip(mvzip(mvzip(name,type),plugin),errors))
Thanks
Harish
0 Karma

Marian
Explorer

mvdedup removed the duplication, thanks!

Tags (1)
0 Karma
Get Updates on the Splunk Community!

Exporting Splunk Apps

Join us on Monday, October 21 at 11 am PT | 2 pm ET!With the app export functionality, app developers and ...

Cisco Use Cases, ITSI Best Practices, and More New Articles from Splunk Lantern

Splunk Lantern is a Splunk customer success center that provides advice from Splunk experts on valuable data ...

Build Your First SPL2 App!

Watch the recording now!.Do you want to SPL™, too? SPL2, Splunk's next-generation data search and preparation ...