Splunk Search

Exclude filter for multiple strings in Queries

NeonFlash
Explorer

Hi,

I am parsing the DNS logs in Splunk and in order to refine my search results, I use something like following.

For an IP Address: xxx.xxx.xxx.xxx, which sends DNS queries for a host at some point of time, I would like to view the list of all the different hosts queried.

So, my splunk search is:

xxx.xxx.xxx.xxx sourcetype="dns" | table _time, query

There are many results where I have entries of subdomains of yyy.com and zzz.com which I want to filter out.

I can write something like following for excluding just one of the domains from results like:

xxx.xxx.xxx.xxx sourcetype="dns" query!="yyy" | table _time, query

Now, 2 questions:

  1. How do I do this for 2 or more domains which I would like to exclude? Is there an option to use an AND or OR operator?
  2. How do I display only the unique query entries in the results?

I know we can use the below search to display all unique query entries and a count of them:

xxx.xxx.xxx.xxx sourcetype="dns" query!="yyy" | table _time, query | stats count by query

But this way, the _time column is not shown in the results which I would definitely not want to omit from the results.

Thanks.

1 Solution

Ayn
Legend
  1. Yes, there is that option. The AND operator is implicit between search terms but can also be explicitly specified. The OR operator between search terms obviously removes the implicit AND. Additionally there is the NOT operator. So, you could do something like

    xxx.xxx.xxx.xxx sourcetype="dns" NOT (query="*.yyy.com" OR query="*.zzz.com")

  2. That depends on what you consider "unique" - you say you want the time info in your results, but as long as you start removing events you will obviously also remove their corresponding time info. You could run dedup with the query field as an argument to only get one event per query. I think it would be interesting to get a count of queries towards each domain from each IP address and at what times they occurred, by doing something like this (assuming the IP address is extracted to a field called ip_address😞

    ... | stats values(eval(strftime(_time,"%+"))) as querytime,count by ip_address,query

View solution in original post

Ayn
Legend
  1. Yes, there is that option. The AND operator is implicit between search terms but can also be explicitly specified. The OR operator between search terms obviously removes the implicit AND. Additionally there is the NOT operator. So, you could do something like

    xxx.xxx.xxx.xxx sourcetype="dns" NOT (query="*.yyy.com" OR query="*.zzz.com")

  2. That depends on what you consider "unique" - you say you want the time info in your results, but as long as you start removing events you will obviously also remove their corresponding time info. You could run dedup with the query field as an argument to only get one event per query. I think it would be interesting to get a count of queries towards each domain from each IP address and at what times they occurred, by doing something like this (assuming the IP address is extracted to a field called ip_address😞

    ... | stats values(eval(strftime(_time,"%+"))) as querytime,count by ip_address,query

Get Updates on the Splunk Community!

Security Professional: Sharpen Your Defenses with These .conf25 Sessions

Sooooooooooo, guess what. .conf25 is almost here, and if you're on the Security Learning Path, this is your ...

First Steps with Splunk SOAR

Our first step was to gather a list of the playbooks we wanted and to sort them by priority.  Once this list ...

How To Build a Self-Service Observability Practice with Splunk Observability Cloud

If you’ve read our previous post on self-service observability, you already know what it is and why it ...