topic Re: Combining Searches in Splunk Search

Combining Searches

tchamp — Mon, 10 Mar 2025 20:37:36 GMT

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.

Re: Combining Searches

livehybrid — Mon, 10 Mar 2025 21:01:05 GMT

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

Re: Combining Searches

PickleRick — Tue, 11 Mar 2025 09:23:52 GMT

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.

Re: Combining Searches

tchamp — Tue, 11 Mar 2025 02:49:18 GMT

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!

Re: Combining Searches

tchamp — Tue, 11 Mar 2025 13:14:59 GMT

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.

Re: Combining Searches

tchamp — Tue, 11 Mar 2025 13:33:31 GMT

I found that I had a typo! 😪