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?
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
I don't see how your SPL matches the example json you shared. Please can you share the raw json in a code block </>
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
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
Thank You @ITWhisperer
😌