I am logging a number of simple on/off switches that Splunk has done a wonderful job automagically parsing. The data is timestamped, has a field name, and the value which can either be a 1 or a 0 to represent state. The data is only logged when it changes state so, if a switch is turned on, there is one line for that, and one line for it when it turns off.
My problem is, I would like to fill in the null values in a results table with their previous event value as that would represent the state the field is in until it reflects the next change.
Any suggestions? I interpret fillnull to be a static solution that isn't applicable to filling null with conditional values. If I'm wrong, please enlighten me.
arri
edit: nick suggested I look at eventstats, in trying this I am not seeing appropriate results. I read the following in the documentation:
sourcetype=access_combined | eventstats avg(kbps) as avgkbps by host
When you run this set of commands, Splunk adds a new avgkbps field to each
sourcetype=access_combined event that includes the kbps field. The value of
avgkbps is the average kbps for that event.
What I am finding is that | eventstats last() is showing only THE last event value and using that throughout. Whereas, what I am hoping to find is something to reveal EACH last event value prior to a known value to fill in the gaps between events in the table kind of like the treatment for null values in the reporting editor allowing one to omit, connect or treat as zero; I'd like to "treat as previous". Since my data value is inherently binary, perhaps there is a simple transform I can apply.
Well there are a couple missing pieces, but I think I can give you some pieces of your puzzle:
It sounds to me like the rows in your results table are events. If that is the case and you want to kind of paint the later events with whatever the 'last known' state was, then I would look at eventstats `streamstats`, ie
<your search terms>
<your search terms> | streamstats last(switchField) as lastKnownState
will paint statistics through the set of events, and I think it will do what you're looking for. eventstats streamstats
If on the other hand the rows in your results table are not individual events but groups of events, ie if your search ends in some transforming command like stats
or chart
etc..
Then into your stats command or whatever you can just put a last(switchField) as lastKnownState
and it'll do the same thing. However if that was the case I suspect you would have discovered the answer already.
UPDATE: streamstats is what you are looking for, not eventstats. Hence the changes and strikethroughs you see above.
The SPL command filldown is your friend.
sourcetype=access_combined | filldown kbps | eventstats avg(kbps) as avgkbps by host
http://docs.splunk.com/Documentation/Splunk/6.4.3/SearchReference/Filldown
You definitely want streamstats instead of eventstats. Eventstats will get the aggregate 'last' over the entire set. Streamstats will get the 'last' value up to (and including) the current position in the set.
I think in simple terms what I want is the opposite of "dedup"! I deduped my logs by not generating redundant data; however, to more clearly view the data, I'd like to see all that duplication.
Well there are a couple missing pieces, but I think I can give you some pieces of your puzzle:
It sounds to me like the rows in your results table are events. If that is the case and you want to kind of paint the later events with whatever the 'last known' state was, then I would look at eventstats `streamstats`, ie
<your search terms>
<your search terms> | streamstats last(switchField) as lastKnownState
will paint statistics through the set of events, and I think it will do what you're looking for. eventstats streamstats
If on the other hand the rows in your results table are not individual events but groups of events, ie if your search ends in some transforming command like stats
or chart
etc..
Then into your stats command or whatever you can just put a last(switchField) as lastKnownState
and it'll do the same thing. However if that was the case I suspect you would have discovered the answer already.
UPDATE: streamstats is what you are looking for, not eventstats. Hence the changes and strikethroughs you see above.
Streamstats look pretty damn close, but the last function evaluates in the wrong direction for my needs. I started another question based on my newfound knowledge and experience with it: http://splunk-base.splunk.com/answers/27725/change-the-evaluation-direction-of-streamstats
If you don't use filldown (answer below) then the correct answer is streamstats with current=false followed by coalesce.
Streamstats works great, up until the most recent event. It paints in the values as long as there are events to paint with; however, it leaves the values blank after the most recent event. Is there a way to sustain the most recent event in addition to the stream?
Use reverse before and after streamstats/filldown:
<search> | reverse | streamstats last(<desired_field>) as <field_name> | reverse
or
<search> | reverse | filldown <desired_field> | reverse
This was martin_mueller ♦'s way to fill in values including the most recent event.
Got it, Thanks! Streamstats to the rescue! Here's a sample search that's working great:
source=*Plant3.log CompB OR CompC_Pallette OR CompA OR CompD | streamstats last(CompA) as CompA_ last(CompB) as CompB_ last(CompC_Pallette) as CompC_ last(CompD) as CompD_
Thanks guys. In looking over streamstats, it seems like they should do what I'm looking for; but I must be to much of a noob to get anything out of them.
My data is basically boolean, the value is either a 1 or a 0. I just want to fill in the blank rows with the previous 1 or 0. I can't believe this is the bottom!
| filldown
Exactly what I was looking for! Thanks @landen99
Well, not fulltime for a year I guess. Fulltime for a longtime, parttime for many many years. I'm shutting up now.
Yea. The search language rocks. It is pretty hard to find the bottom on this thing. I've been building splunk apps fulltime for over a year and I'm still learning a ton.
A thousands hurrays for streamstats!
Actually I may have misspoke, it may be the streamstats command that you're looking for. I dont use these very often I'm afraid but when you need them they're very cool.
Thank you Nick, I'll take a look at eventstats. You are correct in surmising that each row represents an individual event, or in my case an individual binary state change.