topic Re: Fillnull with previous known or conditional values? in Splunk Search

Fillnull with previous known or conditional values?

keycoldstorage — Thu, 17 Mar 2011 03:19:56 GMT

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.

Re: Fillnull with previous known or conditional values?

sideview — Thu, 17 Mar 2011 03:58:05 GMT

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.

Re: Fillnull with previous known or conditional values?

keycoldstorage — Fri, 18 Mar 2011 21:23:27 GMT

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.

Re: Fillnull with previous known or conditional values?

sideview — Fri, 18 Mar 2011 23:38:24 GMT

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.

Re: Fillnull with previous known or conditional values?

hexx — Sat, 19 Mar 2011 14:42:03 GMT

A thousands hurrays for streamstats!

Re: Fillnull with previous known or conditional values?

sideview — Sat, 19 Mar 2011 14:56:37 GMT

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.

Re: Fillnull with previous known or conditional values?

sideview — Sat, 19 Mar 2011 15:07:45 GMT

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

Re: Fillnull with previous known or conditional values?

keycoldstorage — Fri, 13 May 2011 03:32:02 GMT

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!

Re: Fillnull with previous known or conditional values?

keycoldstorage — Fri, 13 May 2011 04:08:54 GMT

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.

Re: Fillnull with previous known or conditional values?

sideview — Fri, 13 May 2011 05:12:48 GMT

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.

Re: Fillnull with previous known or conditional values?

keycoldstorage — Mon, 28 Sep 2020 09:42:30 GMT

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_

Re: Fillnull with previous known or conditional values?

keycoldstorage — Mon, 27 Jun 2011 23:12:58 GMT

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?

Re: Fillnull with previous known or conditional values?

keycoldstorage — Mon, 18 Jul 2011 05:52:08 GMT

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

Re: Fillnull with previous known or conditional values?

landen99 — Fri, 16 Sep 2016 23:12:08 GMT

| filldown

Re: Fillnull with previous known or conditional values?

landen99 — Fri, 16 Sep 2016 23:15:40 GMT

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

Re: Fillnull with previous known or conditional values?

landen99 — Tue, 27 Mar 2018 15:18:57 GMT

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

Re: Fillnull with previous known or conditional values?

Nextbeat — Thu, 19 Apr 2018 14:01:48 GMT

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.

Re: Fillnull with previous known or conditional values?

richielynch89 — Tue, 31 Mar 2020 12:14:14 GMT

Exactly what I was looking for! Thanks @landen99