topic Re: Extracting data from Json key value(containing no array), using transpose and outer join in Splunk Search

Extracting data from Json key value(containing no array), using transpose and outer join

ruhibansal — Thu, 08 Jul 2021 05:38:25 GMT

I have json in following format.

{ "timestamp": "1625577829075", "debug": "true", "A_real": { "Sig1": { "A01": "Pass", "A02": "FAIL", "A03": "FAIL", "A04": "FAIL", "A05": "Pass", "finalEntry": "true" }, "Sig2": { "A01": "Pass", "A02": "FAIL", "A03": "FAIL", "A04": "Pass", "A05": "FAIL", "finalEntry": "true" }, "finalEntry": "true" } }

and one csv file as following:

Id Timestamp A02 T1 A03 T2 A05 T3

I want to create a saved search using outer join on Id and transpose which gives me result as following:

Id Sig1 Sig2

A02 Fail Fail

A03 Fail Fail

A05 Pass Fail

Please sugget query.

Re: Extracting data from Json key value(containing no array), using transpose and outer join

kamlesh_vaghela — Thu, 08 Jul 2021 05:54:59 GMT

@ruhibansal

Can you please try this?

YOUR_SEARCH | fields A_real.S*.A* | rename A_real.* as * |eval dummy=null() | foreach S* [ eval dummy= if(isnull(dummy),"<<FIELD>>".":".'<<FIELD>>',dummy."|"."<<FIELD>>".":".'<<FIELD>>') ] | eval dummy=split(dummy,"|") | stats count by dummy | fields - count | eval f1= mvindex(split(dummy,"."),0),I1= mvindex(split(dummy,"."),1), Id=mvindex(split(I1,":"),0),{f1}=mvindex(split(I1,":"),1) | fields - dummy I1 f1 | stats values(*) as * by Id

My Sample Search :

| makeresults | eval _raw="{\"timestamp\": \"1625577829075\",\"debug\": \"true\",\"A_real\": {\"Sig1\": {\"A01\": \"Pass\",\"A02\": \"FAIL\",\"A03\": \"FAIL\",\"A04\": \"FAIL\",\"A05\": \"Pass\",\"finalEntry\": \"true\"},\"Sig2\": {\"A01\": \"Pass\",\"A02\": \"FAIL\",\"A03\": \"FAIL\",\"A04\": \"Pass\",\"A05\": \"FAIL\",\"finalEntry\": \"true\"},\"finalEntry\": \"true\"}}" | extract | rename comment as "Upto Now is sample data only" | fields A_real.S*.A* | rename A_real.* as * |eval dummy=null() | foreach S* [ eval dummy= if(isnull(dummy),"<<FIELD>>".":".'<<FIELD>>',dummy."|"."<<FIELD>>".":".'<<FIELD>>') ] | eval dummy=split(dummy,"|") | stats count by dummy | fields - count | eval f1= mvindex(split(dummy,"."),0),I1= mvindex(split(dummy,"."),1), Id=mvindex(split(I1,":"),0),{f1}=mvindex(split(I1,":"),1) | fields - dummy I1 f1 | stats values(*) as * by Id

Thanks
KV
▄︻̷̿┻̿═━一

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

Re: Extracting data from Json key value(containing no array), using transpose and outer join

kamlesh_vaghela — Thu, 08 Jul 2021 13:29:57 GMT

@ruhibansal

Can you please try this?

Thanks
KV
▄︻̷̿┻̿═━一

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

Re: Extracting data from Json key value(containing no array), using transpose and outer join

ruhibansal — Fri, 27 May 2022 17:36:16 GMT

Hi @kamlesh_vaghela

I have input json while getting uploaded to splunk every 5 minutes. I am using 'head 1' to see data for the latest one.

In the above mentioned data/query, I want to search all the input json files for which the following field has never been null.

fields A_real.S*.A*

Can you please help me in the query?

Re: Extracting data from Json key value(containing no array), using transpose and outer join

kamlesh_vaghela — Thu, 02 Jun 2022 08:21:26 GMT

@ruhibansal

Can you please share some sample events and expected output? So can help you.