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
Sincere thanks
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.
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!
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
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.
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]