How to use mvindex to break json file with multiple values

[rczone]

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

[kamlesh_vaghela · Accepted solution]

@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]

@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"] }}

[kamlesh_vaghela]

@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.

[ITWhisperer]

Try something like this

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

[rczone]

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

[ITWhisperer]

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

[rczone]

 @ITWhisperer  any inputs @kamlesh_vaghela