Splunk Search
Highlighted

Is there a best practice for creating a multivalue field instead of writing a search with a lot of OR statements?

New Member

I need to search through my email logs to determine who sends emails to personal accounts (e.g. gmail, yahoo, etc).
Right now my search looks like this:

index=exchange_logs recipient_domain="yahoo.com" OR recipient_domain="gmail.com" OR recipient_domain="google.com" OR recipient_domain="hotmail.com" OR recipient_domain="hotmail.co.uk" .....

When I want to add new personal email domains, I need to edit the search and manually add another "OR" line.
I was wondering if I can create a multivalue field that will contain all personal domains and then create a search where recipient_domain=*any value from the multivalue field*.
This will make my search look much cleaner.

p.s. I was able to create a multivalue field that looks like this: "comcast.net aol.com att.net yahoo.com hotmail.com gmail.com", but now I don't know how to compare recipient_domain to each value.

0 Karma
Highlighted

Re: Is there a best practice for creating a multivalue field instead of writing a search with a lot of OR statements?

Communicator

It sounds like you may want to have a CSV lookup table that contains your various domains, and then include the lookup table as a subsearch to your main query. See this link for a similar problem and how a lookup table was incorporated:

https://answers.splunk.com/answers/83846/use-lookup-table-to-specify-hosts-to-search.html

Also, here is info on configuring CSV lookup tables:

http://docs.splunk.com/Documentation/Splunk/6.4.1/Knowledge/ConfigureCSVlookups

View solution in original post

Highlighted

Re: Is there a best practice for creating a multivalue field instead of writing a search with a lot of OR statements?

New Member

Thanks for your help. I created a lookup table with the list of domains, then tried this search:

index=exchange_logs eventtype="smtp-outbound" recipient_domain=personal_email_domain | stats count by recipient_domain | sort -count [| inputlookup personal_domains | fields personal_email_domain]

where personaldomains is the name of a lookup csv file and personalemail_domain is a column name in the file that has a list of domain. This search is not returning anything. Is there any syntax error I made?

0 Karma
Highlighted

Re: Is there a best practice for creating a multivalue field instead of writing a search with a lot of OR statements?

SplunkTrust
SplunkTrust

If you see the solution in the answers post 83846, the lookup is used in outer search/main search (before first pipe ). Your lookup command should be used there like this

index=exchange_logs eventtype="smtp-outbound"  [| inputlookup personal_domains | fields personal_email_domain rename | rename personal_email_domain recipient_domain ]| stats count by recipient_domain | sort -count 
0 Karma
Highlighted

Re: Is there a best practice for creating a multivalue field instead of writing a search with a lot of OR statements?

New Member

Thanks so much for all your help! I am all set now.

0 Karma