Splunk Search

Lookup table from external file for search exclusions possible?

tmeader
Contributor

I've currently got a summary search setup going against DNS query data that I use to produce a reporting chart of the top 50 searches over the past 3 days. As part of this, I generally go through every couple days to see what is showing up in the top 50 that isn't interesting and can be discarded from the chart. This basically amounts to then appending another "query!="some.domain.y" (where "query" is a defined field in my dns eventtype") such as:

sourcetype="dns" query_type="A" query!="some.domain.x" | sitop query limit="50"

At this point though, this has made my search quite long as there's probably upwards of 30 or so "query!=xxxx" statements appended now. To try to make this a little cleaner, I'm wondering if there's a way to leverage a lookup table in order to just read in the domains to ignore via a loop? If not, no big deal... but simply appending to the end of a "domains.ignore" file would be a lot easier than having to edit the search itself daily.

Thanks in advance for any help with this.

Tags (3)
0 Karma
1 Solution

southeringtonp
Motivator

Sure, this is pretty easy to do.

Create a CSV-based lookup table with two columns, e.g.:

query, ignored
"some.domain.x","true"
"some.domain.y","true"
...

When you call the lookup, any values for query not in your CSV list will have an empty value for ignored. So, your search becomes:

sourcetype="dns" query_type="A"
| lookup domainsToIgnore query OUTPUT ignored
| search NOT ignored=true

If you need more more general information on how to create the lookup table, take a look at: http://www.splunk.com/base/Documentation/latest/Knowledge/Addfieldsfromexternaldatasources

View solution in original post

gkanapathy
Splunk Employee
Splunk Employee

Hmm, well, it's unfortunate that the name of your field is query, otherwise this would work:

sourcetype="dns" query_type=A NOT [inputlookup excluded_queries | fields query]

But the problem is that a field named either query or search is treated specially by subsearch. If you renamed the field in your original sourcetype from query to, say, qry, then this would work:

sourcetype="dns" query_type=A NOT [inputlookup excluded_queries | fields qry]

In 4.1.5, I think you'll be unlikely to have problems, but in earlier version (including earlier 4.1 releases), you may run into a 100-term limit that a subsearch will return, which would need to be raised in limits.conf.

gkanapathy
Splunk Employee
Splunk Employee

The table would be a list of domains, one per line, except the first line would be the column/field name, qry or dns_query. You could use the rename command if it's different, but you might as well make it the same as in your sourcetype.

0 Karma

gkanapathy
Splunk Employee
Splunk Employee

yes, edited now.

0 Karma

tmeader
Contributor

gkanapathy, was that second search line supposed to be identical to the first? Or was it supposed to end in "| fields qry]" instead? It's fairly simple for me to change that field name if need be (probably to "dns_query" actually), so I might go your route. In that case, I'm assuming it would be: "| fields dns_query]" at the end, and the lookup file would just be a list of the domains (one per line)?

Thanks.

0 Karma

southeringtonp
Motivator

Sure, this is pretty easy to do.

Create a CSV-based lookup table with two columns, e.g.:

query, ignored
"some.domain.x","true"
"some.domain.y","true"
...

When you call the lookup, any values for query not in your CSV list will have an empty value for ignored. So, your search becomes:

sourcetype="dns" query_type="A"
| lookup domainsToIgnore query OUTPUT ignored
| search NOT ignored=true

If you need more more general information on how to create the lookup table, take a look at: http://www.splunk.com/base/Documentation/latest/Knowledge/Addfieldsfromexternaldatasources

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