topic Re: How to use mvindex to break json file with multiple values in Splunk Search

How to use mvindex to break json file with multiple values

rczone — Mon, 30 Aug 2021 19:53:56 GMT

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

Re: How to use mvindex to break json file with multiple values

rczone — Mon, 30 Aug 2021 20:04:40 GMT

@ITWhisperer any inputs @kamlesh_vaghela

Re: How to use mvindex to break json file with multiple values

ITWhisperer — Tue, 31 Aug 2021 08:45:55 GMT

Try something like this

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

Re: How to use mvindex to break json file with multiple values

rczone — Tue, 31 Aug 2021 13:44:54 GMT

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

Re: How to use mvindex to break json file with multiple values

kamlesh_vaghela — Wed, 01 Sep 2021 05:32:05 GMT

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

Re: How to use mvindex to break json file with multiple values

ITWhisperer — Wed, 01 Sep 2021 08:27:33 GMT

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

Re: How to use mvindex to break json file with multiple values

rczone — Wed, 01 Sep 2021 19:03:01 GMT

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

Re: How to use mvindex to break json file with multiple values

kamlesh_vaghela — Thu, 02 Sep 2021 05:42:09 GMT

@rczone

It looks some variations in JSON event.

Can you please try these two options?

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,",")

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.