Splunk Search

Fillnull with previous known or conditional values?

keycoldstorage
Explorer

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.

1 Solution

sideview
SplunkTrust
SplunkTrust

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> | eventstats last(switchField) as lastKnownState

<your search terms> | streamstats last(switchField) as lastKnownState

eventstats streamstats will paint statistics through the set of events, and I think it will do what you're looking for.

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.

View solution in original post

landen99
Motivator

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

sideview
SplunkTrust
SplunkTrust

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.

keycoldstorage
Explorer

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.

0 Karma

sideview
SplunkTrust
SplunkTrust

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> | eventstats last(switchField) as lastKnownState

<your search terms> | streamstats last(switchField) as lastKnownState

eventstats streamstats will paint statistics through the set of events, and I think it will do what you're looking for.

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.

View solution in original post

keycoldstorage
Explorer

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

0 Karma

landen99
Motivator

If you don't use filldown (answer below) then the correct answer is streamstats with current=false followed by coalesce.

0 Karma

keycoldstorage
Explorer

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?

0 Karma

Nextbeat
Path Finder

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.

0 Karma

keycoldstorage
Explorer

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_

0 Karma

keycoldstorage
Explorer

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!

0 Karma

landen99
Motivator
| filldown

richielynch89
Path Finder

Exactly what I was looking for! Thanks @landen99

0 Karma

sideview
SplunkTrust
SplunkTrust

Well, not fulltime for a year I guess. Fulltime for a longtime, parttime for many many years. I'm shutting up now.

sideview
SplunkTrust
SplunkTrust

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.

0 Karma

hexx
Splunk Employee
Splunk Employee

A thousands hurrays for streamstats!

0 Karma

sideview
SplunkTrust
SplunkTrust

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.

0 Karma

keycoldstorage
Explorer

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.

0 Karma