How to combine the results of two subsearches

[dusoldkai]

I would like to find occurences of Name and Prename in email logfiles and only report that ones that match both column of an inputlookup table.

an Event from the email Server contains Envelop-Sender(suser),Recipient(duser), Content-Sender(from) and some more fields that are not interesting for this task.

My inputlookup table names.csv looks like

name,prename,comment 
smith,winston,added on 15.05.20

when using two subsearches in a regular search like the following

index="mail" sourcetype="mailserver" direction="incoming" [| inputlookup names.csv | eval from="*".name."*"| fields from| format] [| inputlookup names.csv| eval from="*".prename."*"| fields from| format] | fields suser,duser,from| format]

all Matches are displayed. Matches for "name" and for "prename".

I tryed to use the WHERE clause but the from field is not existing in the Input lookup table. i did not manage to find a regex that can search for both fields content (name and prename) in the same eval clause Independent of the location of the search Patterns in the target string.

Maybe if clauses can be used in nested form?

I tryed also some join like this

(index="mail" sourcetype="mailserver" direction"incoming"  [| inputlookup names.csv | eval from="*".prename."*"| fields from| format] ) join type=inner from ([ search index="mail" sourcetype="mailserver" direction="incoming"  [| inputlookup names.csv| eval from="*".name."*"| fields from| format]]) |table _time,suser,duser,from

But did not get any Matches, also the data do have from entries with both values (name,prename) in it.
The lookup-table names.csv was created to be case-insensitive.

Is there a way to join two Subsearches and get only the values that matched both searches ?
Or is there an easy way to use a eval clause on a single field that can search for two search Patterns at the same time ?
(Independent of Location of pattern in searchfield)

Thank You

[richgalloway · Accepted solution]

It seems common for users to fixate on inputlookup and overlook the lookup command. To solve the problem statement "I would like to find occurences of Name and Prename in email logfiles and only report that ones that match both column of an inputlookup table" you don't need subsearches, just a single lookup. Pass the two names to lookup and if there is a match you will get the comment field back. Filter out empty comments and you'll be left with matching names.

index="mail" sourcetype="mailserver" direction="incoming"
| lookup name, prename OUTPUT comment
| where isnotnull(comment)

[dusoldkai]

Thank you for the hint with the lookup table. That sounds too good ! No nested subsearches would be very good. I adjusted my search

 index="mail" sourcetype="emailserver" direction="incoming" | lookup names.csv name,prename OUTPUT comment | where isnotnull(comment)

But it does not match log entries. I guess that is because there are no wildcards in my lookup table. Can i ajust the search in a way to match a partial string ?

 index="mail" sourcetype="emailserver" direction="incoming" | lookup names.csv ("*".name."*"),("*".prename."*") as from OUTPUT comment | where isnotnull(comment)

Thank You

[richgalloway]

Splunk supports wildcards in the lookup file. Create a lookup definition (Settings->Lookups->Lookup definitions->New Lookup Definition) and check the Advanced box. In the "Match type" box, enter "WILDCARD(name),WILDCARD(prename)". Then change your query to use the lookup definition in place of the lookup file.

[dusoldkai]

Thank You that hint in combination with the link https://docs.splunk.com/Documentation/Splunk/8.0.3/Knowledge/Usefieldlookupstoaddinformationtoyourev... worked !

i had to change also my lookup file to include wildcards in the corresponding columns.

name,prename,comment
*smith*,*john*, CEO of Some Inc

the working search is then

  index="mail" sourcetype="emailserver" direction="incoming" | lookup namesDefinition.csv name as from prename as from OUTPUT comment | where isnotnull(comment)