Splunk Search

How to combine the results of two subsearches

dusoldkai
New Member

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

0 Karma
1 Solution

richgalloway
SplunkTrust
SplunkTrust

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)
---
If this reply helps you, Karma would be appreciated.

View solution in original post

0 Karma

richgalloway
SplunkTrust
SplunkTrust

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)
---
If this reply helps you, Karma would be appreciated.
0 Karma

dusoldkai
New Member

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

0 Karma

richgalloway
SplunkTrust
SplunkTrust

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.

---
If this reply helps you, Karma would be appreciated.
0 Karma

dusoldkai
New Member

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