topic Re: Json key value mapping in Getting Data In

Json key value mapping

vivekkumarkk — Fri, 01 Oct 2021 17:04:59 GMT

I have my splunk Jason in below format

{ [-] delete_me: True vendor: Dbruzy name: Rahul date: [ [-] 10-jan-2022 30-dec-2022 ] count_target: [ [-] 1700 300 ] site: India type: Sales }

I am looking for a query to get output like this:

Vendor	Name	Date	Count_Target	Site	Type
Dbruzy	Rahul	10-jan-2022	1700	India	Sales
Dbruzy	Rahul	30-dec-2022	300	India	Sales

But I am getting as below:

Vendor

Name

Date

Count_Target

Site

Type

Dbruzy

Rahul

10-jan-2022

30-dec-2022

1700

300

India

Sales

Dbruzy

Rahul

10-jan-2022

30-dec-2022

1700

300

India

Sales

Query I am using:

my index | rename count_target{} as target | rename Date{} as voltage | spath input=voltage path=voltage output=someOtherField | spath input=someOtherField | foreach voltage* [ eval voltage=mvappend(voltage, '<<FIELD>>')] | spath input=target path=target output=someOtherField1 | spath input=someOtherField1 | foreach target* [ eval target=mvappend(target, '<<FIELD>>')] | mvexpand target| mvexpand voltage | stats values(voltage) as Date values(target) as Count_Target by Vendor, Name,Site,Type

Can you please help?

Re: Json key value mapping

ITWhisperer — Fri, 01 Oct 2021 18:02:08 GMT

I don't see how your SPL matches the example json you shared. Please can you share the raw json in a code block </>

Re: Json key value mapping

vivekkumarkk — Sat, 02 Oct 2021 05:20:55 GMT

Thanks for the response.

My SPL may not be right. You can find the raw data:

{"delete_me": "True", "vendor": "Dbruzy", "site": "India", "type": "Sales", "name": "Rahul", "date": ["10-jan-2022", "30-dec-2022"], "count_target": ["1700", "300"]}

SPL I corrected in below way:

my index | rename count_target{} as target | rename Date{} as voltage | foreach voltage* [ eval voltage=mvappend(voltage, '<<FIELD>>')] | foreach target* [ eval target=mvappend(target, '<<FIELD>>')] | mvexpand target| mvexpand voltage | stats values(voltage) as Date values(target) as Count_Target by Vendor, Name,Site,Type

Re: Json key value mapping

ITWhisperer — Sat, 02 Oct 2021 08:53:11 GMT

You are using foreach incorrectly - you only have one field that matches voltage* and target* - foreach doesn't iterate through the multivalues in the field - you could use mvmap to do that but I am not sure that would help you in this instance. From your SPL, it looks like you already have the fields extracted from the json, but I have used spath in this runanywhere example

| makeresults | eval _raw="{\"delete_me\": \"True\", \"vendor\": \"Dbruzy\", \"site\": \"India\", \"type\": \"Sales\", \"name\": \"Rahul\", \"date\": [\"10-jan-2022\", \"30-dec-2022\"], \"count_target\": [\"1700\", \"300\"]}" | table _raw | spath | rename count_target{} as target | rename date{} as voltage | mvexpand target | streamstats count as row by name site type vendor | eval voltage=mvindex(voltage,row-1) | fields - row

Re: Json key value mapping

vivekkumarkk — Sun, 03 Oct 2021 05:53:34 GMT

Thank You @ITWhisperer

😌