Splunk Enterprise Security

How to exclude a lookup from a tstats in subsearch?

innoce
Path Finder

Hi,

I have list of domains in a lookup and I need to exclude it from my query

 

| tstats summariesonly=true allow_old_summaries=false dc("DNS.query") as count from datamodel=Network_Resolution where nodename=DNS "DNS.message_type"="QUERY" by "DNS.src","DNS.query" index sourcetype 
| rename "DNS.src" as src "DNS.query" as message index as orig_index sourcetype as orig_sourcetype 
| eval length=len(message) 
| stats sum(length) as length by src message orig_index orig_sourcetype 
| append 
    [ tstats summariesonly=true allow_old_summaries=false dc("DNS.answer") as count from datamodel=Network_Resolution where nodename=DNS "DNS.message_type"="QUERY" by "DNS.src","DNS.answer" index sourcetype 
    | rename "DNS.src" as src "DNS.answer" as message index as orig_index sourcetype as orig_sourcetype 
    | eval message=if(message=="unknown","", message) 
    | eval length=len(message) 
    | stats sum(length) as length by src message orig_index orig_sourcetype] 
| dedup src 
| stats sum(length) as length by message src orig_index orig_sourcetype

 

Now I have to exclude the domains lookup from both my tstats.. I tried this but not seeing any results.. First part works fine but not the second one.. 

 

| tstats summariesonly=true allow_old_summaries=false dc("DNS.query") as count from datamodel=Network_Resolution where nodename=DNS "DNS.message_type"="QUERY" NOT 
    [| inputlookup domainslist
    | fields domains 
    | rename domains as DNS.query 
    | format] by "DNS.src","DNS.query" index sourcetype 
| rename "DNS.src" as src "DNS.query" as message index as orig_index sourcetype as orig_sourcetype 
| eval length=len(message) 
| stats sum(length) as length by src message orig_index orig_sourcetype 
| append 
    [ tstats summariesonly=true allow_old_summaries=false dc("DNS.answer") as count from datamodel=Network_Resolution where nodename=DNS "DNS.message_type"="QUERY" NOT 
        [| inputlookup domainslist
        | fields domains 
        | rename domains as DNS.answer 
        | format] by "DNS.src","DNS.answer" index sourcetype 
    | rename "DNS.src" as src "DNS.answer" as message index as orig_index sourcetype as orig_sourcetype 
    | eval message=if(message=="unknown","", message) 
    | eval length=len(message) 
    | stats sum(length) as length by src message orig_index orig_sourcetype] 
| dedup src 
| stats sum(length) as length by message src orig_index orig_sourcetype

 

Any suggestions would be appreciated.. thanks!

0 Karma

MaverickT
Communicator

I think the catch is not the lookup. It is the data structure. While testing your search I got the results if I put IP address to my domainslist lookup table.

The thing is that "DNS.answer" should be the IP address of the queried domain. Or am I wrong? If you want to search answers then your lookup should also include IP addresses.

Note that in my experience if it is a big lookup file, this method will not work. It returns error and seach does not complete. In that case you have to return everything with tstats and then use lookup + search (or search + inputlookup).

0 Karma
Get Updates on the Splunk Community!

Prove Your Splunk Prowess at .conf25—No Prereqs Required!

Your Next Big Security Credential: No Prerequisites Needed We know you’ve got the skills, and now, earning the ...

Splunk Observability Cloud's AI Assistant in Action Series: Observability as Code

This is the sixth post in the Splunk Observability Cloud’s AI Assistant in Action series that digs into how to ...

Splunk Answers Content Calendar, July Edition I

Hello Community! Welcome to another month of Community Content Calendar series! For the month of July, we will ...