Passive DNS

[kjstogn]

I am trying to create a passive dns collection based on splunk stream data. My current SPL is this:

index=botsv2 sourcetype=stream:dns query=*frothly.local
| stats earliest(_time) as firstSeen, latest(_time) as lastSeen by query
| eval firstSeen=strftime(firstSeen, "%m/%d/%Y %H:%M:%S")
| eval lastSeen=strftime(lastSeen, "%m/%d/%Y %H:%M:%S")

What I am trying to accomplish is show how many days as "activeDays" a query was made. Just because its first and last may be seen 30 days apart it may have only been queried a couple times. 

Also the stream queries and answers are separate events and how would i join them to create a by query and answers

[richgalloway]

Here's an untested search to find out how many separate days each query was made.  It has the side effect of retaining only the date of each query, not the time.

index=botsv2 sourcetype=stream:dns query=*frothly.local
| bin span=1d _time
| stats earliest(_time) as firstSeen, latest(_time) as lastSeen, dc(_time) as queryDays by query
| eval firstSeen=strftime(firstSeen, "%m/%d/%Y %H:%M:%S")
| eval lastSeen=strftime(lastSeen, "%m/%d/%Y %H:%M:%S")