How to generate a table that will validate when there is at least one event per day over a time span

[yacht_rock]

I want a table that is formatted like...

Monday, yes
Tuesday, no

Where the yes/no column is based on if that particular day has ANY events. I don't want a count, I just want to know that on Monday we received events, but on Tuesday we didn't for example.

I can achieve something like this with index=foo | bin span=1d _time | stats min(_time) by _time but min still needs to do a bunch of counting to find the min value - what I want Splunk to do is just find one single event per my span and then move on. I know my query doesn't show a literal "yes" or "no", my table was just demonstrative.

What is the best approach to this?

[DalJeanis]

How much time are we talking about here? You could use something like this-

| gentimes start=01/01/2017 end=01/5/2017 
| map maxsearches=0 search="search index=windows_log EventID=4624 earliest=$starttime$ latest=$endtime$ | head 1"

The gentimes command produces one record for each day, then the map looks for your events in each time window.

I'm assuming that splunk is optimized to notice that "head 1" and not return more than the first record it finds.

You could also probably use a tstats command, either solo, or with the map as above, depending on whether the events you are looking for can be identified by columns indexed at index time.

[lguinn2]

Try thsi

| tstats prestats=true count where index=* by _time span=1d
| timechart span=1d count
| eval Result=if(count>0,"Yes","no") 
| eval Day = strftime(_time,"%A %x")
| table Day Result

Note that tstats is blazing fast compared to standard searches (even though it is counting).

[s2_splunk · Accepted solution]

Try this:

 | tstats count where index=varmour by _time 
    | eval yesno=if(count>0,"yes","no"), t=_time 
    | eval dow = strftime(strptime(t, "%s"), "%A") 
    | fields - count, _time, t

Should be pretty efficient, since you can use tstats if all you want to have is a literal representation of event count

[lguinn2]

Dang, I typed too long again 😄

Although, will tstats actually present a count of zero for "empty days"? I don't think it will...

[s2_splunk]

Ah, yes. Of course! Hmmm... another approach is needed.