Splunk Search

Find days with no events

johnnymc
Path Finder

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

Tags (1)

Micheal_S
Explorer

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; ```
`

0 Karma

gcoles
Communicator

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.

0 Karma

sideview
SplunkTrust
SplunkTrust

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
Communicator

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.

0 Karma

sideview
SplunkTrust
SplunkTrust

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.

0 Karma

sideview
SplunkTrust
SplunkTrust

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
SplunkTrust
SplunkTrust

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.

0 Karma

johnnymc
Path Finder

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.

0 Karma

johnnymc
Path Finder

ill try this. thanks.

0 Karma

johnnymc
Path Finder

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
Path Finder

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

0 Karma

johnnymc
Path Finder

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.

0 Karma
Get Updates on the Splunk Community!

Adoption of RUM and APM at Splunk

    Unleash the power of Splunk Observability   Watch Now In this can't miss Tech Talk! The Splunk Growth ...

March Community Office Hours Security Series Uncovered!

Hello Splunk Community! In March, Splunk Community Office Hours spotlighted our fabulous Splunk Threat ...

Stay Connected: Your Guide to April Tech Talks, Office Hours, and Webinars!

Take a look below to explore our upcoming Community Office Hours, Tech Talks, and Webinars in April. This post ...