How to exclude a lookup from a tstats in subsearch?

[innoce]

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!

[MaverickT]

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