Combining Searches

[tchamp]

I am trying to figure out the best way to perform this search. I have some json log/events where the event data is slightly different based on the type of fruit (this is just an example). I have two searches that return each thing that I want. I'm not sure if it is best to try and combine the two searches or if there is a better way all together. 

Here is an example of my event data:

Event Type 1
{
    "data": {
        "fruit": {
            "common": {
                "type": "apple",
                "foo": "bar1"
            },
            "apple": {
                "color": "red",
                "size": "medium",
                "smell": "sweet"
            }
        }
    }
}

Event Type 2
{
    "data": {
        "fruit": {
            "common": {
                "type": "pear",
                "foo": "bar2"
            },
            "pear": {
                "color": "green",
                "size": "medium",
                "taste": "sweet"
            }
        }
    }
}

I want to extract all of the "color" values from all of the log/json messages. I have two separate queries that extract each one but I want them in a single table.

Here are my current queries/searches:

index=main | spath "data.pear.color" | search "data.pear.color"=* | eval fruitColor='data.pear.color' | table _time, fruitColor

index=main | spath "data.apple.color" | search "data.apple.color"=* | eval fruitColor='data.apple.color' | table _time, fruitColor

I know that there must be a way to do something with the 'type' field to do what I want but can't seem to figure it out.

Any suggestion is appreciated.

[PickleRick]

1. That's awful data. Either make your data normalized (causing a bunch of problems) or make it redundant (causing other problems 🙂 - here you have both approaches mixed.

2. Don't put data into main index.

3. You can either use coalesce or foreach. A coalesce example:

index=main
| spath
| search 'data.fruit.common.type' IN ("apple","pear")
| eval color=coalesce('data.pear.color','data.apple.color')

EDIT: Fixed field references in coalesce() - without single quotes Splunk would interpret it as concatenating fields data, pear/apple and color.

[livehybrid · Accepted solution]

Hi @tchamp 

How about something like this?

| spath "data.fruit.common.type" output=fruitType 
| eval colorPath="data.fruit." . fruitType . ".color" 
| eval fruitColor=json_extract(_raw,colorPath)

Below is a screenshot of how this might work (based on a sample data gen)

This is the full SPL for the example

| makeresults 
| eval json="[{\"data\":{\"fruit\":{\"common\":{\"type\":\"apple\",\"foo\":\"bar1\"},\"apple\":{\"color\":\"red\",\"size\":\"medium\",\"smell\":\"sweet\"}}}},{\"data\":{\"fruit\":{\"common\":{\"type\":\"pear\",\"foo\":\"bar2\"},\"pear\":{\"color\":\"green\",\"size\":\"medium\",\"taste\":\"sweet\"}}}}]"
| eval events=json_array_to_mv(json)
| mvexpand events
| eval _raw=events
| fields _raw
| spath "data.fruit.common.type" output=fruitType 
| eval colorPath="data.fruit." . fruitType . ".color" 
| eval fruitColor=json_extract(_raw,colorPath)

Please let me know how you get on and consider adding karma to this or any other answer if it has helped.
Regards

Will

[tchamp]

Using your SPL, I expanded it a bit to be closer to my dataset. This new SPL is not working whereas the one you provided does in deed work. Maybe it is something stupid but I'm stumped!

| makeresults
| eval json="[{
  \"data\": {
    \"tree\": {
      \"branch\": {
        \"common\": {
          \"type\": \"Apple\"
        },
        \"apple\": {
          \"color\": \"red\"
        }
      }
    }
  }
}]"
| eval events=json_array_to_mv(json)
| mvexpand events
| eval _raw=events
| fields _raw
| spath "data.tree.branch.common.type" output=TypeTemp
| eval type = lower(TypeTemp)
| eval colorPath="data.tree.branch." . type . ".color "
| eval color=json_extract(_raw,colorPath)
| table _time, color, type, colorPath

Suggestions welcome once again.

[tchamp]

I found that I had a typo! 😪

[tchamp]

This seems really close to working. It does work for the dataset that I provided but isn't working for my actual dataset. I have not figured out why just yet. My actual dataset is MUCH larger and convoluted. 

As PickleRick pointed out, this is awful data!