Find days with no events

[johnnymc]

hello,
i would like to find days in which a particular sourcetype is missing.
With this, i'll drive an alert.

for now i was able to render this graphically, however i need the logic to determine every single day missing.

sourcetype=foo | bucket _time span=24h | dedup date_mday| stats span=24h count(date_mday) by date_month

Any hint?
Thanks

[Micheal_S]

I had a similar issue where I needed to find spans of time where the logs I was looking for dropped to 0 to represent a potential outage. I used the `timechart` like what was described but then used `untable` to get it back into a usable format for my purposes.

`Base search | timechart count by clientip useother=f limit=0 | untable _time clineip count` 

Then I could further stats to count my iterations of "0" values for a threshold.

`
<BASE SEARCH>
| timechart count by clientip useother=f limit=0
| untable _time clientip count
| sort 0 clientip _time
| eval no_events=if(count=0,1,0)
| streamstats sum(no_events) as consecutive values(no_events) by clientip reset_before=count=1
| stats max(consecutive) as consecutive_missed by clientip
| where consecutive_missed>=10 ```Adjust Based on Alert Period, e.g, 24h set to 3; 1hr set to 10; ```
`

[gcoles]

I also ran into this problem yesterday. I think the best way would be to create a generating command (creates synthetic events) that outputs a fake event for every day (or configurable timespan) in the timerange for which it is called, then use a join type=outer _time [search your_search] to join your timechart or bucket'ed stats results by time. Then you can use eval to find events where the field(s) from the join is/are null (eval zeroresults=if(isnull(myfield),"true","false")). If writing generating commands is too difficult, you could create a csv file with a column for _time, and set the timestamp for each row to the day boundary (in seconds since 1970), then use the inputlookup command to generate the synthetic events and perform the join mentioned earlier.

Thinking of this, I'm surprised Splunk wouldn't already have a command to do this. May need to dig deeper in the commands documentation.

[sideview]

Notwithstanding the differences you describe, timechart can be added onto the end of any search, whether it's a complex report that uses stats, or a simple events search, and assuming that the result rows coming into timechart all have _time fields that are epochtime valued, "timechart span=1d count | where count=0" will always give back the days in that time, within which the incoming rows had no data.

At any rate, for the purposes of the question being asked, which was to find the days in which a given sourcetype "foo" is not present, timechart count | where count=0 will work great.

[gcoles]

Thats true, sideview, but timechart handle output columns differently than stats (a column will exist for each permutation of the field being split upon rather than a single column with a row for each permutation), and also doesn't let you segment by multiple fields, like stats does (count by a,b,c). Since most of my work involves nested calls to stats, eventstats, and streamstats, using timechart anywhere in the search flattens the data too much to be useful. However, it could be used as the generating command I was talking about, since it could create the _time fields.

[sideview]

The timechart command does this. For any search, lets say that search is for "foo", "foo | timechart span=1d count | where count=0" will return the days that have no events. You can do a lot more but that's the basic starting point that I would use.

[sideview]

This seems to be a simple matter of:

sourcetype=foo | timechart count span=1d | where count=0

But I could certainly be missing something.

bin and stats will only work with the data that's there - they have no way of creating new rows. It's easy after a while to think of timechart count as just a big macro for bin _time | stats count by _time, but there are some things like this that only timechart does.

[sideview]

Yes, the timechart command is just part of detecting the 0 events. You can throw this timechart command and the subsequent where command on the end of any search that has _time values in its rows, and it will give you the subset of days in those results that had no events. Simple.

[johnnymc]

it seems to work - the key here is that i can catch zero event count only with the timechart command, so i'll use it to get the count value.

[johnnymc]

ill try this. thanks.

[johnnymc]

I'm still wondering how to solve this..
the issue is that if events are missing, i have no data to hook to.
perhaps using evals helps?

any clue appreciated.

[cnk]

Hey johnnymc, it might be easier to look splunk's internal metrics log which tracks events as they're indexed. Is this what you're looking for?

index=_internal source=metrics.log splunk_server="" | search group="per_sourcetype_thruput" | bucket _time span=1d | stats count(ev) as total by series,_time | where total=0

[johnnymc]

i suspect that i will never have a 0 count of total , since when a particular sourcetype is never received i have 0 events.

I'll have to craft the zero value with a fake field.. i think.