Getting Data In

Json key value mapping

vivekkumarkk
Explorer

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:

VendorNameDateCount_TargetSiteType
DbruzyRahul10-jan-20221700IndiaSales
DbruzyRahul30-dec-2022300IndiaSales

 

But I am getting as below:

VendorNameDateCount_TargetSiteType
DbruzyRahul

10-jan-2022

30-dec-2022

1700

300

IndiaSales
DbruzyRahul

10-jan-2022

30-dec-2022

1700

300

IndiaSales

 

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?

Labels (1)
0 Karma
1 Solution

ITWhisperer
SplunkTrust
SplunkTrust

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

View solution in original post

ITWhisperer
SplunkTrust
SplunkTrust

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

vivekkumarkk
Explorer

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
0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

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

vivekkumarkk
Explorer

Thank You @ITWhisperer 

😌

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...