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!

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 ...