topic Re: Fill nulls based on previous value in Splunk Search

Fill nulls based on previous value

arramack — Wed, 08 Apr 2015 14:28:12 GMT

I have events that contain the following data:

Time, Name, Value, Quality.

The Quality value can either be "Good" or "Bad", meaning the measurement was made or not. If Quality is "Bad", then the Value will be 0. Otherwise Value is a number (which can also be 0).

I am logging the data per second, but only if there is a data change. What I want is a search that always returns a value for every second, even if there is no event in that second.

If there is no event, then the event must be the same as the previous event logged.

I have tried to use

source="tcp:51112" | timechart span=1s Max(Value) by Tag | filldown

but that doesn't show me the Quality. I will also have many tags later on (up to 5000), so then I will have 5000 columns. I don't know if that is very efficient. This will be used for graphing 1-10 Tags at a time.

Re: Fill nulls based on previous value

ngatchasandra — Wed, 08 Apr 2015 15:05:52 GMT

Hi arramack,
I think that , if your query doesn't show you the Quality, ist because with filldown command, If there were not any previous values for a field (in this case its Quality field), it will be left blank (NULL). I refer to Search Reference Manual. Follow the link that follow:

http://docs.splunk.com/Documentation/Splunk/latest/SearchReference/Filldown

Re: Fill nulls based on previous value

gyslainlatsa — Wed, 08 Apr 2015 15:33:04 GMT

hi arramack,
try add this query eval QUALITY= if (quality="Bad",0,' ')in your query for the values of the quality

source="tcp:51112" | eval QUALITY= if (Quality="Bad",0,' ')  | timechart span=1s Max(Value) by Tag | filldown

Re: Fill nulls based on previous value

arramack — Wed, 08 Apr 2015 17:04:06 GMT

After I run timechart my columns are _time, TagName1, TagName2, TagName3 etc..

Under the TagName I have the value for each timestamp.

That's the problem. Timechart completely screws up the table structure. There is no place to put the Quality component.

Re: Fill nulls based on previous value

Stevelim — Thu, 21 Apr 2016 15:59:28 GMT

Hi Arramack,

How about

TagName= "Your Tag" Value=* | Eval Quality=if(Quality=="good", 192, 0) | timechart  span=1s sum(Value) as Value, sum(Quality) as Quality by TagName | filldown

This will only work for one tag but if you couple with a input filed, you can have a dropdown for all 5000 to select them one by one. I am not exactly this is the best way to visualize for so many tags. What you can consider is having clones of the table, i.e. Clone 10 of this table for 10 Tags on your dashboard.