topic Re: Limiting a query to 'known good' values plus 'others' in Splunk Search

Limiting a query to 'known good' values plus 'others'

gavinsopra — Wed, 18 Nov 2020 10:39:39 GMT

I have a query similar to the following which we are using to capture information about email traffic between certain (internal) email domains

index="exchdomains" | stats count by SenderDomain,recipientDomain | xyseries SenderDomain,recipientDomain,count

This builds a nice table, however:

Part 1
How do I limit the query to only certain values of 'SenderDomain' and 'recipientDomain' without having to type all the domains (there are about 8 ATM) into the query? Can I use a CSV lookup for this?

Part 2
Assuming part one is achievable how do I add a 'catchall' row where I count messages sent from domains NOT in my list of 'SenderDomain' values of interest? And similarly add a 'catchall' column for 'recipientDomain' not in a list of 'known good'.

Re: Limiting a query to 'known good' values plus 'others'

richgalloway — Wed, 18 Nov 2020 15:40:29 GMT

To limit a query to values in a lookup file, read the lookup in a subsearch, like this:

index="exchdomains" [ | inputlookup mylookup.csv | return foo ] | stats count by SenderDomain,recipientDomain | xyseries SenderDomain,recipientDomain,count

The above query will read only those events that have the appropriate field values in the CSV so there is no need for a catch-all. An alternative query to create a catch-all might look like this:

index="exchdomains" | lookup mylookup.csv domain as SenderDomain OUTPUT domain as foundSDomain | lookup mylookup.csv domain as recipientDomain OUTPUT domain as foundRDomain | fillnull value="unknown" foundSDomain foundRDomain | stats count by foundSDomain, foundRDomain | xyseries foundSDomain,foundRDomain,count

Re: Limiting a query to 'known good' values plus 'others'

gavinsopra — Thu, 19 Nov 2020 12:05:01 GMT

Richgallowy,

Thanks for the response but I am having some difficulty getting it to work.

I have added a lookup table which returns the following

| inputlookup SenderRecipDomains.csv Domain DomainName dept1.org.com Dept1 dept2.org.com Dept2 dept3.org.com Dept3 dept4.org.com Dept4 dept5.org.com Dept5 deptA.remote.com RemoteDeptA deptB.remote.com RemoteDeptB deptC.remote.com RemoteDeptC

If I run the following query with a very tight date/time range I get 47 events

index="exchdomains"

If I run this query

index="exchdomains" [ | inputlookup SenderRecipDomains.csv | return Domain ]

or this query

index="exchdomains" [ | inputlookup SenderRecipDomains.csv | fields Domain ]

against the same date/time range I get 0 events.

Have I configured the lookup incorrectly?

Re: Limiting a query to 'known good' values plus 'others'

richgalloway — Thu, 19 Nov 2020 18:31:08 GMT

If you run the subsearch by itself

| inputlookup SenderRecipDomains.csv | return Domain

you should get a result like this

((Domain=dept1.org.com) OR (Domain=dept2.org.com) OR ...)

So the final search becomes

index="exchdomains" ((Domain=dept1.org.com) OR (Domain=dept2.org.com) OR ...)

Which should work fine as long as your exchdomains index has a field called "Domain". If there is no such field then the subsearch needs a rename command to make the field name match what's in the index.

Re: Limiting a query to 'known good' values plus 'others'

gavinsopra — Fri, 20 Nov 2020 16:39:05 GMT

Curiouser and curiouser.

As stated before this query

| inputlookup SenderRecipDomains.csv

Returns this result

Domain DomainName dept1.org.com Dept1 dept2.org.com Dept2 dept3.org.com Dept3 dept4.org.com Dept4 dept5.org.com Dept5 deptA.remote.com RemoteDeptA deptB.remote.com RemoteDeptB deptC.remote.com RemoteDeptC

So if I run this query

| inputlookup SenderRecipDomains.csv | return Domain

I get this

Domain="dept1.org.com"

However, it appears that 'return' requires a "count" which leads to this query and result

| inputlookup SenderRecipDomains.csv | return 2 Domain (Domain="dept1.org.com") OR (Domain="dept2.org.com")

So I am running this to get all the data from my CSV

| inputlookup SenderRecipDomains.csv | return 9999 Domain (Domain="dept1.org.com") OR (Domain="dept2.org.com") OR (Domain="dept3.org.com") OR (Domain="dept4.org.com") OR (Domain="dept5.org.com") OR (Domain="deptA.remote.com") OR (Domain="deptB.remote.com") OR (Domain="deptC.remote.com")

Which means that part 1 is now working - many thanks.

I have just started looking at part 2.

I have started with

index="exchdomains" | inputlookup SenderRecipDomains.csv Domain as SenderDomain OUTPUT Domain as foundSDomain

But I get the error "Error in 'inputlookup' command: Invalid argument: 'domain'"

Re: Limiting a query to 'known good' values plus 'others'

gavinsopra — Fri, 20 Nov 2020 16:42:27 GMT

My bad. I was using 'inputlookup' when I should have used 'lookup'

Re: Limiting a query to 'known good' values plus 'others'

richgalloway — Fri, 20 Nov 2020 18:17:51 GMT

If your problem is resolved, then please click the "Accept as Solution" button to help future readers.