Splunk Search

How to use mvindex to break json file with multiple values

rczone
Path Finder

Hello All,

 

So i have a field like below with JSON file

 

 

 

{"results_appcodes": [{"count": 2, "app_code": "XYZ", "group": "", "instance": "PQ1", "job_names": ["XYZ#cmd#johntest1", "XYZ#cmd#remetest"]}, {"count": 2, "app_code": "ZZZ", "group": "ABC1234", "instance": "PQ1", "job_names": ["ZZZ#ADM#cmd#pac", "ZZZ#cmd#GET_APP_CODE"]}, {"count": 1, "app_code": "ZZZ", "group": "", "instance": "PQ1", "job_names": ["ZZZ#cmd#mila3098"]}, {"count": 192, "app_code": "GKU", "group": "CAD45678", "instance": "PQ1", "job_names": ["ZZZ#cmd#test123"] ,["ZZZ#cmd#test890"], ["ZZZ#cmd#gola456"], ["ZZZ#cmd#test9990"] }}

 

 

 

 Im using below query to break down the JSON file above 

All the fields  count,app_code, group, instance are getting as expected but for  job_names  im unable to break down and that particular attrbute has a list of jobs underneath it

Im looking for a query to get jobnames also 

 

 

 

<<mysearch>>| spath input=results|rename unique_appcodes{}.* as *  | eval x = mvzip(count,mvzip(app_code,mvzip(group,mvzip(instance,mvzip(instance,job_names))))) | mvexpand x | eval x = split(x, ",")| eval job_count=mvindex(x,0), app_code = mvindex(x,1) ,group=mvindex(x,2), instance = mvindex(x,3),job_names = mvindex(x,4) |table app_code job_count group instance job_names

 

 

 

 

 

Expected output

 

 

 

app_code  count	    group	instance	job_names
XYZ	    2	                 PQ1  	XYZ#cmd#johntest1,XYZ#cmd#remetest
ZZZ	    2       ABC1234	   PQ2	ZZZ#ADM#cmd#pac,ZZZ#cmd#GET_APP_CODE

 

 

 

 

 

 

 

Labels (5)
0 Karma
1 Solution

kamlesh_vaghela
SplunkTrust
SplunkTrust

@rczone 

Can you please try this?

YOUR_SEARCH 
| kv 
| spath path=results_appcodes{} output=results_appcodes 
| stats count by results_appcodes |fields - count 
| rename results_appcodes as _raw 
| kv 
| eval job_names=if(isnotnull('job_names{}'),'job_names{}','job_names{}{}') 
| fields - "job_names{*"
| eval n=1 | accum n | eventstats max(n) as mn by app_code
| where  n=mn
|table app_code	count group instance job_names | eval job_names=mvjoin(job_names,",")

 

My Sample Search :

| makeresults 
| eval _raw="{\"results_appcodes\": [{\"count\": 2,\"app_code\": \"XYZ\",\"group\": \"\",\"instance\": \"PQ1\",\"job_names\": [\"XYZ#cmd#johntest1\", \"XYZ#cmd#remetest\"]}, {\"count\": 2,\"app_code\": \"ZZZ\",\"group\": \"ABC1234\",\"instance\": \"PQ1\",\"job_names\": [\"ZZZ#ADM#cmd#pac\", \"ZZZ#cmd#GET_APP_CODE\"]}, {\"count\": 1,\"app_code\": \"ZZZ\",\"group\": \"\",\"instance\": \"PQ1\",\"job_names\": [\"ZZZ#cmd#mila3098\"]}, {\"count\": 192,\"app_code\": \"GKU\",\"group\": \"CAD45678\",\"instance\": \"PQ1\",\"job_names\": [[\"ZZZ#cmd#test123\"],[\"ZZZ#cmd#test890\"],[\"ZZZ#cmd#gola456\"],[\"ZZZ#cmd#test9990\"]]}]}" 
| kv 
| spath path=results_appcodes{} output=results_appcodes 
| stats count by results_appcodes |fields - count 
| rename results_appcodes as _raw 
| kv 
| eval job_names=if(isnotnull('job_names{}'),'job_names{}','job_names{}{}') 
| fields - "job_names{*"
| eval n=1 | accum n | eventstats max(n) as mn by app_code
| where  n=mn
|table app_code	count group instance job_names | eval job_names=mvjoin(job_names,",")

 

My Sample Event.

{
	"results_appcodes": [{
		"count": 2,
		"app_code": "XYZ",
		"group": "",
		"instance": "PQ1",
		"job_names": ["XYZ#cmd#johntest1", "XYZ#cmd#remetest"]
	}, {
		"count": 2,
		"app_code": "ZZZ",
		"group": "ABC1234",
		"instance": "PQ1",
		"job_names": ["ZZZ#ADM#cmd#pac", "ZZZ#cmd#GET_APP_CODE"]
	}, {
		"count": 1,
		"app_code": "ZZZ",
		"group": "",
		"instance": "PQ1",
		"job_names": ["ZZZ#cmd#mila3098"]
	}, {
		"count": 192,
		"app_code": "GKU",
		"group": "CAD45678",
		"instance": "PQ1",
		"job_names": [
			["ZZZ#cmd#test123"],
			["ZZZ#cmd#test890"],
			["ZZZ#cmd#gola456"],
			["ZZZ#cmd#test9990"]
		]
	}]
}

 

You can changes the search incase variation in event 🙂 

Thanks
KV
▄︻̷̿┻̿═━一  

If any of my reply helps you to solve the problem Or gain knowledge, an upvote would be appreciated.

View solution in original post

kamlesh_vaghela
SplunkTrust
SplunkTrust

@rczone 

Can you please try this?

YOUR_SEARCH 
| kv 
| spath path=results_appcodes{} output=results_appcodes 
| stats count by results_appcodes |fields - count 
| rename results_appcodes as _raw 
| kv 
| eval job_names=if(isnotnull('job_names{}'),'job_names{}','job_names{}{}') 
| fields - "job_names{*"
| eval n=1 | accum n | eventstats max(n) as mn by app_code
| where  n=mn
|table app_code	count group instance job_names | eval job_names=mvjoin(job_names,",")

 

My Sample Search :

| makeresults 
| eval _raw="{\"results_appcodes\": [{\"count\": 2,\"app_code\": \"XYZ\",\"group\": \"\",\"instance\": \"PQ1\",\"job_names\": [\"XYZ#cmd#johntest1\", \"XYZ#cmd#remetest\"]}, {\"count\": 2,\"app_code\": \"ZZZ\",\"group\": \"ABC1234\",\"instance\": \"PQ1\",\"job_names\": [\"ZZZ#ADM#cmd#pac\", \"ZZZ#cmd#GET_APP_CODE\"]}, {\"count\": 1,\"app_code\": \"ZZZ\",\"group\": \"\",\"instance\": \"PQ1\",\"job_names\": [\"ZZZ#cmd#mila3098\"]}, {\"count\": 192,\"app_code\": \"GKU\",\"group\": \"CAD45678\",\"instance\": \"PQ1\",\"job_names\": [[\"ZZZ#cmd#test123\"],[\"ZZZ#cmd#test890\"],[\"ZZZ#cmd#gola456\"],[\"ZZZ#cmd#test9990\"]]}]}" 
| kv 
| spath path=results_appcodes{} output=results_appcodes 
| stats count by results_appcodes |fields - count 
| rename results_appcodes as _raw 
| kv 
| eval job_names=if(isnotnull('job_names{}'),'job_names{}','job_names{}{}') 
| fields - "job_names{*"
| eval n=1 | accum n | eventstats max(n) as mn by app_code
| where  n=mn
|table app_code	count group instance job_names | eval job_names=mvjoin(job_names,",")

 

My Sample Event.

{
	"results_appcodes": [{
		"count": 2,
		"app_code": "XYZ",
		"group": "",
		"instance": "PQ1",
		"job_names": ["XYZ#cmd#johntest1", "XYZ#cmd#remetest"]
	}, {
		"count": 2,
		"app_code": "ZZZ",
		"group": "ABC1234",
		"instance": "PQ1",
		"job_names": ["ZZZ#ADM#cmd#pac", "ZZZ#cmd#GET_APP_CODE"]
	}, {
		"count": 1,
		"app_code": "ZZZ",
		"group": "",
		"instance": "PQ1",
		"job_names": ["ZZZ#cmd#mila3098"]
	}, {
		"count": 192,
		"app_code": "GKU",
		"group": "CAD45678",
		"instance": "PQ1",
		"job_names": [
			["ZZZ#cmd#test123"],
			["ZZZ#cmd#test890"],
			["ZZZ#cmd#gola456"],
			["ZZZ#cmd#test9990"]
		]
	}]
}

 

You can changes the search incase variation in event 🙂 

Thanks
KV
▄︻̷̿┻̿═━一  

If any of my reply helps you to solve the problem Or gain knowledge, an upvote would be appreciated.

rczone
Path Finder

@kamlesh_vaghela  Thankyou so much it worked most of the part for me its truncating job_names with count 1 if the job_names is duplicate


here is my sample json file -- XYZ job_names has 2 records  i it ,with count 2 and count 1 respectively in this case XYZ is only displayed once in output but i have to get 2 rows for XYZ

 

{"results_appcodes": [{"count": 2, "app_code": "XYZ", "group": "", "instance": "PQ1", "job_names": ["XYZ#cmd#johntest1", "XYZ#cmd#remetest"]}, {"count": 2, "app_code": "ZZZ", "group": "ABC1234", "instance": "PQ1", "job_names": ["ZZZ#ADM#cmd#pac", "ZZZ#cmd#GET_APP_CODE"]}, {"count": 1, "app_code": "XYZ", "group": "", "instance": "PQ1", "job_names": ["XYZ#cmd#mila3098"]}, {"count": 192, "app_code": "GKU", "group": "CAD45678", "instance": "PQ1", "job_names": ["ZZZ#cmd#test123"] ,["ZZZ#cmd#test890"], ["ZZZ#cmd#gola456"], ["ZZZ#cmd#test9990"] }}

 



0 Karma

kamlesh_vaghela
SplunkTrust
SplunkTrust

@rczone 

It looks some variations in JSON event.

Can you please try these two options?

1)

 

YOUR_SEARCH
| spath path=results_appcodes{} output=results_appcodes 
| stats count by results_appcodes |fields - count 
| rename results_appcodes as _raw 
| kv 
| eval job_names=if(isnotnull('job_names{}'),'job_names{}','job_names{}{}') 
| fields - "job_names{*"
|table app_code count group instance job_names | eval job_names=mvjoin(job_names,",")

 

 My Sample Search :

 

| makeresults 
| eval _raw="{\"results_appcodes\": [{\"count\": 2,\"app_code\": \"XYZ\",\"group\": \"\",\"instance\": \"PQ1\",\"job_names\": [\"XYZ#cmd#johntest1\", \"XYZ#cmd#remetest\"]}, {\"count\": 2,\"app_code\": \"ZZZ\",\"group\": \"ABC1234\",\"instance\": \"PQ1\",\"job_names\": [\"ZZZ#ADM#cmd#pac\", \"ZZZ#cmd#GET_APP_CODE\"]}, {\"count\": 1,\"app_code\": \"XYZ\",\"group\": \"\",\"instance\": \"PQ1\",\"job_names\": [\"XYZ#cmd#mila3098\"]}, {\"count\": 192,\"app_code\": \"GKU\",\"group\": \"CAD45678\",\"instance\": \"PQ1\",\"job_names\": [[\"ZZZ#cmd#test123\"],[\"ZZZ#cmd#test890\"],[\"ZZZ#cmd#gola456\"],[\"ZZZ#cmd#test9990\"]]}]}" 
| kv 
| spath path=results_appcodes{} output=results_appcodes 
| stats count by results_appcodes |fields - count 
| rename results_appcodes as _raw 
| kv 
| eval job_names=if(isnotnull('job_names{}'),'job_names{}','job_names{}{}') 
| fields - "job_names{*"
|table app_code count group instance job_names | eval job_names=mvjoin(job_names,",")

 

 

2)

 

YOUR_SEARCH
| eval data = split(_raw,"}, {") | stats count by data
| rex field=data "\"count\":\s(?<count>\d+),\s\"app_code\":\s\"(?<app_code>[^,]+)\",\s\"group\"\:\s\"(?<group>[^,]*)\",\s\"instance\"\:\s\"(?<instance>[^,]+)\",\s\"job_names\":\s(?<job_names>.*)"
| rex field=job_names "\[?\"(?<job_names>[^\"]+)\""  max_match=0 | eval job_names=mvjoin(job_names,",")
|table app_code	count group instance job_names

 

 

My Sample Search :

 

| makeresults 
| eval _raw="{\"results_appcodes\": [{\"count\": 2, \"app_code\": \"XYZ\", \"group\": \"\", \"instance\": \"PQ1\", \"job_names\": [\"XYZ#cmd#johntest1\", \"XYZ#cmd#remetest\"]}, {\"count\": 2, \"app_code\": \"ZZZ\", \"group\": \"ABC1234\", \"instance\": \"PQ1\", \"job_names\": [\"ZZZ#ADM#cmd#pac\", \"ZZZ#cmd#GET_APP_CODE\"]}, {\"count\": 1, \"app_code\": \"XYZ\", \"group\": \"\", \"instance\": \"PQ1\", \"job_names\": [\"XYZ#cmd#mila3098\"]}, {\"count\": 192, \"app_code\": \"GKU\", \"group\": \"CAD45678\", \"instance\": \"PQ1\", \"job_names\": [\"ZZZ#cmd#test123\"] ,[\"ZZZ#cmd#test890\"], [\"ZZZ#cmd#gola456\"], [\"ZZZ#cmd#test9990\"] }}"
| eval data = split(_raw,"}, {") | stats count by data
| rex field=data "\"count\":\s(?<count>\d+),\s\"app_code\":\s\"(?<app_code>[^,]+)\",\s\"group\"\:\s\"(?<group>[^,]*)\",\s\"instance\"\:\s\"(?<instance>[^,]+)\",\s\"job_names\":\s(?<job_names>.*)"
| rex field=job_names "\[?\"(?<job_names>[^\"]+)\""  max_match=0 | eval job_names=mvjoin(job_names,",")
|table app_code	count group instance job_names

 

 

I hope this will help you with your all type of events. 🙂 

Thanks
KV
▄︻̷̿┻̿═━一   😉

If any of my reply helps you to solve the problem Or gain knowledge, an upvote would be appreciated.

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

Try something like this

| spath result_appcodes{} output=result_appcodes
| mvexpand result_appcodes 
| spath input=result_appcodes 
0 Karma

rczone
Path Finder

Thankyou...but im looking to split the values individually as i need them to use further in my query...with jso spath split we cant use the values as individually

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

Can you give an example of what you get from this search and what further it needs to do?

0 Karma

rczone
Path Finder

 @ITWhisperer  any inputs @kamlesh_vaghela 

0 Karma
Get Updates on the Splunk Community!

.conf24 | Day 0

Hello Splunk Community! My name is Chris, and I'm based in Canberra, Australia's capital, and I travelled for ...

Enhance Security Visibility with Splunk Enterprise Security 7.1 through Threat ...

 (view in My Videos)Struggling with alert fatigue, lack of context, and prioritization around security ...

Troubleshooting the OpenTelemetry Collector

  In this tech talk, you’ll learn how to troubleshoot the OpenTelemetry collector - from checking the ...