Splunk Search

How can I search for events that match two subsearches?

spohara79
Explorer

I'm trying to pull back events that have a specific field value, but should only return events that match that field value if it has related events (two criteria of subsearches match).

E.g., I have a part, I only want to return that part if it has two subparts
{
"part_id": 1234,
"part_name": "main",
....
}

{
"ref_part_id": 1234,
"part_name": "docker",
"manufacturer": "bar",
...
}
{
"ref_part_id": 1234,
"part_name": "docker",
"manufacturer": "foo",
...
}
{
"part_id": 5678,
"part_name": "main",
....
}

{
"ref_part_id": 5678,
"part_name": "docker",
"manufacturer": "foo",
...
}
{
"ref_part_id": 5678,
"part_name": "docker",
"manufacturer": "bar",
...
}

I only want to return events where the field 'part_name' is 'main', but I need those events only where the belong to the main part which has a specific ID, the part_name is 'docker' and it has both 'docker' parts from two manufactures ('foo' and 'bar') (it can have other parts and manufacturers, but HAS to at least have those two)

```part_name=main | join max=0 part_id [search manufacturer=bar part_name=docker| rename ref_part_id AS part_id] | join max=0 part_id [search manufacturer=foo part_name=docker | rename ref_part_id as part_id]```

I'm getting unexpected results

0 Karma
1 Solution

elliotproebstel
Champion

Can you give an example of what your desired sample output would be, based on the sample input? I suspect some of the unexpected behavior stems from the fact that you are joining:

{
"part_id": 1234,
"part_name": "main",
....
}

with:
{
"ref_part_id": 1234,
"part_name": "docker",
"manufacturer": "bar",
...
}

by renaming the ref_part_id field name but not handling the fact that they both have a part_name field. So this short run-anywhere which is meant to mimic the code from your post:
|stats count | eval part_id=1234, part_name="main" | join max=0 part_id [| stats count | eval part_name="docker", ref_part_id=1234, manufacturer="bar" | rename ref_part_id AS part_id]
will result in a single event:
count=0 manufacturer="bar" part_id=1234 part_name="docker"

Does that match your desired result? I'm suspecting it doesn't, so maybe some sample output from you will help.

UPDATE

As per clarified understanding of the use case:

part_name=main [ search manufacturer=foo OR manufacturer=bar part_name=docker | eval has_foo=if(manufacturer="foo", 1, NULL), has_bar=if(manufacturer="bar", 1, NULL | eventstats values(has_foo) AS has_foo BY ref_part_id | where has_foo=1 AND has_bar=1| stats values(ref_part_id) AS part_id | format ] 

The subsearch looks for events with part_name=docker where the manufacturer is either foo or bar. It adds a field of either has_foo or has_bar, to track the manufacturer and then applies the has_foo field across all events with matching ref_part_id values. After that, it filters down to only events with both has_foo and has_bar, extracts the ref_part_id values (renaming them to part_id) and searches across all events with part_name=main for the corresponding events with those part_id values.

View solution in original post

elliotproebstel
Champion

Can you give an example of what your desired sample output would be, based on the sample input? I suspect some of the unexpected behavior stems from the fact that you are joining:

{
"part_id": 1234,
"part_name": "main",
....
}

with:
{
"ref_part_id": 1234,
"part_name": "docker",
"manufacturer": "bar",
...
}

by renaming the ref_part_id field name but not handling the fact that they both have a part_name field. So this short run-anywhere which is meant to mimic the code from your post:
|stats count | eval part_id=1234, part_name="main" | join max=0 part_id [| stats count | eval part_name="docker", ref_part_id=1234, manufacturer="bar" | rename ref_part_id AS part_id]
will result in a single event:
count=0 manufacturer="bar" part_id=1234 part_name="docker"

Does that match your desired result? I'm suspecting it doesn't, so maybe some sample output from you will help.

UPDATE

As per clarified understanding of the use case:

part_name=main [ search manufacturer=foo OR manufacturer=bar part_name=docker | eval has_foo=if(manufacturer="foo", 1, NULL), has_bar=if(manufacturer="bar", 1, NULL | eventstats values(has_foo) AS has_foo BY ref_part_id | where has_foo=1 AND has_bar=1| stats values(ref_part_id) AS part_id | format ] 

The subsearch looks for events with part_name=docker where the manufacturer is either foo or bar. It adds a field of either has_foo or has_bar, to track the manufacturer and then applies the has_foo field across all events with matching ref_part_id values. After that, it filters down to only events with both has_foo and has_bar, extracts the ref_part_id values (renaming them to part_id) and searches across all events with part_name=main for the corresponding events with those part_id values.

spohara79
Explorer
{
"part_id": 9999,
"part_name": "main",
....
}

{
"ref_part_id": 9999,
"part_name": "docker",
"manufacturer": "baz",
...
}
{
"ref_part_id": 9999,
"part_name": "docker",
"manufacturer": "fooz",
...
}
{
"part_id": 1234,
"part_name": "main",
....
}

{
"ref_part_id": 1234,
"part_name": "docker",
"manufacturer": "bar",
...
}
{
"ref_part_id": 1234,
"part_name": "docker",
"manufacturer": "foo",
...
}
{
"part_id": 5678,
"part_name": "main",
....
}

{
"ref_part_id": 5678,
"part_name": "docker",
"manufacturer": "foo",
...
}
{
"ref_part_id": 5678,
"part_name": "docker",
"manufacturer": "bar",
...
}

Given the above, I'd want the return result to be two items:

 {
 "part_id": 1234,
 "part_name": "main",
 ....
 }
 {
 "part_id": 5678,
 "part_name": "main",
 ....
 }
0 Karma

elliotproebstel
Champion

Ahh, I think this should do what you want:

part_name=main [ search manufacturer"foo OR manufacturer=bar part_name=docker | eval has_foo=if(manufacturer="foo", 1, NULL), has_bar=if(manufacturer="bar", 1, NULL | eventstats values(has_foo) AS has_foo BY ref_part_id | where has_foo=1 AND has_bar=1| stats values(ref_part_id) AS part_id | format ]

The subsearch looks for events with part_name=docker where the manufacturer is either foo or bar. It adds a field of either has_foo or has_bar, to track the manufacturer and then applies the has_foo field across all events with matching ref_part_id values. After that, it filters down to only events with both has_foo and has_bar, extracts the ref_part_id values (renaming them to part_id) and searches across all events with part_name=main for the corresponding events with those part_id values.

0 Karma

spohara79
Explorer

Thanks, this works as expected! Can you respond or update the initial post and I'll accept it, I can't accept it as a comment?

elliotproebstel
Champion

You got it! Glad I could help. 🙂

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