How to use 「inputlookup」fliter duplicate domains (BIND dns query log)

[Sunjux]

hello ervery:

Scenario:

In my case,I use daily search create DnsQueryLog.csv,record the domains inquired every day in this *csv file(Not repeating),And I hope to find a new query domain by comparing these domains every day.

Problem:

Now I have DnsQueryLog.csv  contains 8,038 domains ,and I confirmed that data can be displayed using  the following command:

| inputlookup DnsQueryLog.csv

And I use the following command wnat find some new query domain today,

sourcetype="isc:bind:query" 
| stats count(query) by query
| sort - count 
| fields query
| search NOT 
    [| inputlookup DnsQueryLog.csv]

But it's not work,In this test, the number of domains queried today is equal to the data in the csv file, which is also 8,038 , My understanding is that if it runs correctly, the number of search data should be 「0」,But it shows 8038 records, which confuses me .Can someone help me confirm which part I am doing wrong?

p.s

• I have confirmed that the domain name in the csv file is the same as the query result.(8,038)
• The csv field name is the same as the output field of the query result.

Sincere thanks

[rnowitzki]

Hi @Sunjux ,

I made some tests and my assumption is that it does not work as you expect due to additional fields (other than "query") in your csv. Is that the case - do you have more fields in that csv?

So, you might want to try: 

sourcetype="isc:bind:query" 
| stats count(query) by query
| sort - count 
| fields query
| search NOT 
    [| inputlookup DnsQueryLog.csv | fields query]

Hope it helps
BR
Ralph

--
Karma and/or Solution Tagging appreciated.

[Sunjux]

hi @rnowitzki :

Only one field in this *.csv,

And I tried the following search, but it didn’t work

sourcetype="isc:bind:query" 
| stats count(query) by query
| sort - count 
| fields query
| search NOT 
    [| inputlookup DnsQueryLog.csv | fields query]

 I appreciate your reply, thanks!

[rnowitzki]

Hi @Sunjux ,

Interesting.

Maybe there is something different to the query fields. Could be data type e.g.

Try this to confirm:

sourcetype="isc:bind:query" 
| stats count(query) by query
| sort - count 
| fields query
| appendcols
[| inputlookup DnsQueryLog.csv | rename query as csv_query]
| eval compare=if(query==csv_query, "equal", "not_equal")
| eval type_of_query=typeof(query)
| eval type_of_csv_query=typeof(csv_query)

It should show equal in the "compare" field, if the fields are really equal, looking at content and type.
In type_of_query/type_of_csv_query it will show probably string in both cases...but just make sure it is the same.

If that does not solve the riddle, can you maybe show one/some examples of "query" in that case? You could change some characters to anonymize the data.

BR
Ralph

[Sunjux]

Hi, @rnowitzki 

I tried the search syntax you provided,and the results are shown below :

I think the cause of the problem is that 「type_of_csv_query」 shows 「invalid」,

These csv files were uploaded using Lookup Editor after saving a new file through search syntax. Is this caused by this?

Thank you for your assistance, I think my question is getting clearer.

Thanks again.

[rnowitzki]

Hi @Sunjux ,

Ugh, that is strange, I don't know what kind of data would result in "invalid" for the typeof() function.

Is there no visible difference between query and csv_query? Maybe one of them in quotes and the other not or something like that?

What you could try is to transform both of them to string. But I am not sure if it works with the csv_query in that case.

See if the "confirmation" SPL works like that:

sourcetype="isc:bind:query" 
| stats count(query) by query
| sort - count 
| fields query
| eval query=tostring(query)
| appendcols
[| inputlookup DnsQueryLog.csv | eval query=tostring(query) | rename query as csv_query]
| eval compare=if(query==csv_query, "equal", "not_equal")
| eval type_of_query=typeof(query)
| eval type_of_csv_query=typeof(csv_query)

If that works, you might want to try the initial SPL again like this:

sourcetype="isc:bind:query" 
| stats count(query) by query
| sort - count 
| fields query
| eval query=tostring(query)
| search NOT 
    [| inputlookup DnsQueryLog.csv | eval query=tostring(query) | fields query]