Exclude filter for multiple strings in Queries

[NeonFlash]

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.

[Ayn · Accepted solution]

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