Splunk Search

How handle JSON-Event with associative Array

Roger_FB
Engager

Hi,
i need a special result, but i dont know how to iterate over an associative array.

Here is this JSON-Events:

Event 1:

{
"created": "28\/May\/2018:06:24:00 +0200",
"response": {           
            "products": {
                "1": {
                    "id": 10,                       
                    "price": 120                        
                },
                "2": {
                    "id": 20,                       
                    "price": 65                     
                },
                "3": {
                    "id": 30,                       
                    "price": 80 
                }
            }
        }       
}

Event 2:

{
"created": "30\/May\/2018:08:10:00 +0200",
"response": {           
            "products": {
                "1": {
                    "id": 40,                       
                    "price": 120                        
                },
                "2": {
                    "id": 50,                       
                    "price": 65                     
                }
            }
        }       
}

And i need the folowing result:

ID      Price
-------------------
10      120
20      65
...      
50      65

Any idea?
Many thanks 🙂

0 Karma

kamlesh_vaghela
SplunkTrust
SplunkTrust

@Roger_FB

Can you please try this?

YOUR_SEARCH 
| eval id="",price=""
| foreach response.products.*.id 
    [ eval id=id.if(id=="","",if(isnull('<<FIELD>>'),"",",")).if(isnull('<<FIELD>>'),"",'<<FIELD>>')] 
| foreach response.products.*.price 
    [ eval price=price.if(price=="","",if(isnull('<<FIELD>>'),"",",")).if(isnull('<<FIELD>>'),"",'<<FIELD>>')] 
| eval id=split(id,","),price=split(price,","),temp=mvzip(id,price) 
| mvexpand temp 
| table temp | eval id=mvindex(split(temp,","),0),price=mvindex(split(temp,","),1) | table id price

My Sample Search:

| makeresults 
| eval _raw="{ \"created\": \"28\/May\/2018:06:24:00 +0200\", \"response\": {\"products\": { \"1\": { \"id\": 10,\"price\": 120}, \"2\": { \"id\": 20,\"price\": 65}, \"3\": { \"id\": 30,\"price\": 80 } } } }" 
| append 
    [| makeresults 
    | eval _raw="{\"created\": \"30\/May\/2018:08:10:00 +0200\",\"response\": {\"products\": {\"1\": {\"id\": 40,\"price\": 120},\"2\": {\"id\": 50,\"price\": 65}}}}"] 
| kv 
| eval id="",price="" 
| foreach response.products.*.id 
    [ eval id=id.if(id=="","",if(isnull('<<FIELD>>'),"",",")).if(isnull('<<FIELD>>'),"",'<<FIELD>>') ] 
| foreach response.products.*.price 
    [ eval price=price.if(price=="","",if(isnull('<<FIELD>>'),"",",")).if(isnull('<<FIELD>>'),"",'<<FIELD>>') ] 
| eval id=split(id,","),price=split(price,","),temp=mvzip(id,price) 
| mvexpand temp 
| table temp | eval id=mvindex(split(temp,","),0),price=mvindex(split(temp,","),1) | table id price

Thanks

indigo42
Explorer

Hey! Thanks so much for this!! The OP's problem was nearly identical to mine. I'm parsing thru Ansible's win_update JSon and they put in this stupid GUID thing for an object name...anyway...

I didn't know about having to pre populate my field for the foreach! I can't tell you how many hours and hours I spent wondering why, oh why, doesn't my foreach concatonation work???

I am totally stealing this from you. 

| eval upd_kb=""
| foreach ansible_result.filtered_updates.*.kb{} [eval upd_kb=upd_kb.if(upd_kb=="","",if(isnull('<<FIELD>>'),"",",")).if(isnull('<<FIELD>>'),"",'<<FIELD>>') ] 
| table upd_kb

I'm not sure if I'll need the isnull check, but it sure couldn't hurt to have!

Thanks!!

J

0 Karma

martin_mueller
SplunkTrust
SplunkTrust

The best approach would be to store arrays as arrays. Once you have that, you can use this to get to the individual array elements:

 | spath response.products | mvexpand response.products | spath input=response.products

The way your data is structured right now is that you have unknown/unbounded field/object names. Without known field/object names, how do you access fields/objects?

0 Karma
Get Updates on the Splunk Community!

Observability | How to Think About Instrumentation Overhead (White Paper)

Novice observability practitioners are often overly obsessed with performance. They might approach ...

Cloud Platform | Get Resiliency in the Cloud Event (Register Now!)

IDC Report: Enterprises Gain Higher Efficiency and Resiliency With Migration to Cloud  Today many enterprises ...

The Great Resilience Quest: 10th Leaderboard Update

The tenth leaderboard update (11.23-12.05) for The Great Resilience Quest is out &gt;&gt; As our brave ...