Splunk Search

Dates with zero data don't populate with zeros

joshbeckett
Explorer

I have some data that is being forwarded to another entity via our heavy forwarders and I am trying to monitor that stream to ensure it doesn't fail or go too high or low.

The below query is a stepping stone toward some other graphing that I want to do, but I need to solve the issue where my charted data stops when the feed goes to zero (aka dies). To be clear, it is the source feed going to my HF on my side that has died, not the HF itself. I know this because there are multiple feeds and only one is down. The others are fine.

index=myindex sourcetype=mysourcetype group=per_sourcetype_thruput series=myfeed
| bin _time span=1d
| stats sum(ev) as dailyEv by _time sourcetype
| streamstats time_window=30d avg(dailyEv) as avgev stdev(dailyEv) as standardDev by sourcetype
| eval lowerBound=(avgev-(standardDev*2)) 
| eval upperBound=(avgev+(standardDev*2)) 
| eval isOutlier=if(dailyEv < lowerBound OR dailyEv > upperBound, 1, 0)
| table _time,dailyEv,lowerBound,upperBound,isOutlier 

I am watching a rolling 30d worth of data but when the event count [sum(ev)] goes to zero on calendar day 22, the graph stops at calendar day 21, even though today is calendar day 26.

I have tried to a couple of iterations of fillnull statements against the ev and dailyEv variables without success. I believe the issue may be related to streamstats and the fact that the _time field may be missing and required when the events are no longer seen in myfeed.

Any thoughts on how to get the table to show zero values when myfeed dies so that I can potentially alert on isOutlier?

0 Karma

to4kawa
Ultra Champion
 index=myindex sourcetype=mysourcetype group=per_sourcetype_thruput series=myfeed
 | timechart span=1d sum(ev) as dailyEv by sourcetype
 | untable _time sourcetype dailyEv
 | streamstats time_window=30d avg(dailyEv) as avgev stdev(dailyEv) as standardDev by sourcetype
 | eval lowerBound=(avgev-(standardDev*2)) 
 | eval upperBound=(avgev+(standardDev*2)) 
 | eval isOutlier=if(dailyEv < lowerBound OR dailyEv > upperBound, 1, 0)
 | table _time,dailyEv,lowerBound,upperBound,isOutlier 
0 Karma

joshbeckett
Explorer

Thank you for your help. Certainly an interesting solution. I wasn't familiar with that command.

Unfortunately, I am getting the same results as before. The final table and visualization do not have dates with zero data when the data does to zero.

0 Karma

to4kawa
Ultra Champion

The final table and visualization do not have dates with zero data when the data does to zero.
What is the condition?
If field value is NULL , use fillnull

0 Karma

joshbeckett
Explorer

Per my original question:

I have tried to a couple of iterations of fillnull statements against the ev and dailyEv variables without success. I believe the issue may be related to streamstats and the fact that the _time field may be missing and required when the events are no longer seen in myfeed.

0 Karma

to4kawa
Ultra Champion

with my query. fillnull can't work?

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...