How can I search for events that match two subsearches?

[spohara79]

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

[elliotproebstel · Accepted solution]

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]

{
"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",
 ....
 }

[elliotproebstel]

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.

[spohara79]

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]

You got it! Glad I could help. 🙂