topic Re: timechart shows 0 when there is no result in Splunk Search

timechart shows 0 when there is no result

LuiesCui — Wed, 05 Aug 2015 03:30:44 GMT

Hi guys, I have a problem with timechart and I need ur help!
I got a search line here:

index="perform" "Bytes Received/sec" | timechart span=1h count as num1 | fillnull
| join [search index="perform" sourcetype="Perfmon:Processor118"| timechart span=1h count as num2 | fillnull] 
| table _time num1 num2

When both subqueries get results, it works well and some empty slots are filled with 0. However, these subqueries usually get only a few results or maybe no results at all ( which means the machine works well ). In this case, I get "no result" in my dashboard when either subquery returns nothing. Fillnull doesn't help.
I want to get:

_time                num1  num2
2015-07-31 09:00:00 4    0
2015-07-31 10:00:00 10    0
2015-07-31 11:00:00 11  0
2015-07-31 12:00:00 12  0

 _time               num1  num2
    2015-07-31 09:00:00 0   4
    2015-07-31 10:00:00 0    4
    2015-07-31 11:00:00 0   4
    2015-07-31 12:00:00 0   4

or even

 _time               num1  num2
    2015-07-31 09:00:00 0   0
    2015-07-31 10:00:00 0    0
    2015-07-31 11:00:00 0   0
    2015-07-31 12:00:00 0   0

in my dashboard. How can I do that? Thx a lot!

Re: timechart shows 0 when there is no result

somesoni2 — Wed, 05 Aug 2015 03:46:07 GMT

Try something like this

 index="perform" "Bytes Received/sec" | timechart span=1h count as num1 
 | join [search index="perform" sourcetype="Perfmon:Processor118"| timechart span=1h count as num2 ] \
 | appendpipe [| gentimes start=-1 | addinfo | eval temp=info_min_time." ".info_max_time | makemv temp | mvexpand temp | rename temp as _time | table _time ]
 | table _time num1 num2 | fillnull | timechart span=1h sum(*) as *

Re: timechart shows 0 when there is no result

LuiesCui — Wed, 05 Aug 2015 04:27:42 GMT

Doesn't work...when either subquery returns nothing, the table shows like:
_time num1 num2
1970-01-01 08:00 0 0
and there's only one row.

Re: timechart shows 0 when there is no result

acharlieh — Wed, 05 Aug 2015 06:09:46 GMT

I have two thoughts on this, the first being to save processing time by doing everything you have so far in one search like so:

 index="perform" ("Bytes Received/sec" OR  sourcetype="Perfmon:Processor118") | timechart span=1h count(eval(searchmatch("Bytes Received/sec"))) as num1 count(eval(sourcetype="Perfmon:Processor118")) as num2

Next we generate a dummy event for each hour that won't match either condition but with events we ensure the timechart runs and spits out zeroes if appropriate. (not sure if this is necessary but that looks like):

index="perform" ("Bytes Received/sec" OR  sourcetype="Perfmon:Processor118") | append [gentimes [noop | stats count | addinfo | convert timeformat="%m/%d/%Y:%T" ctime(info_*_time) | rename info_min_time as start info_max_time as end | fields start end | format "" "" "" "" "" ""] increment=1h | rename starttime as _time | fields] | timechart span=1h count(eval(searchmatch("Bytes Received/sec"))) as num1 count(eval(sourcetype="Perfmon:Processor118")) as num2

Check out the gentimes and append commands for generating dummy events. The subsearch inside the append subsearch is just to figure out the start and end parameter for gentimes based on the selected timeframe.

Re: timechart shows 0 when there is no result

LuiesCui — Wed, 05 Aug 2015 06:35:22 GMT

In fact, sometime I face the same issue when I use the subqueries with different indexes. If so can I use your way? If yes, how?

Re: timechart shows 0 when there is no result

LuiesCui — Wed, 05 Aug 2015 09:51:44 GMT

need help...

Re: timechart shows 0 when there is no result

acharlieh — Wed, 05 Aug 2015 11:20:02 GMT

Assuming the other queries follow the same pattern separate indexes don't matter. The search is all of them OR'ed together (or you use multisearch) then you adjust the count conditions accordingly. (The part inside eval is just like a where statement)

Re: timechart shows 0 when there is no result

woodcock — Wed, 05 Aug 2015 13:41:58 GMT

Try this:

index="perform" "Bytes Received/sec"  append [search earliest =-1h@h index=* OR index=_* | head 1] | timechart span=1h count as num1 | fillnull | join [search index="perform" sourcetype="Perfmon:Processor118" append [search earliest =-1h@h index=* OR index=_* | head 1] | timechart span=1h count as num2 | fillnull] | table _time num1 num2  | eval tooMany=strftime(now(), "%Y-%m-%d %H") . ":00" | eval tooMany = round(strptime(tooMany, "%Y-%m-%d %H")) | eval num1 = num1 - if(_time=tooMany, 1, 0) | eval num2 = num2 - if(_time=tooMany, 1, 0)

This makes sure the last row always has 1 extra (never all zeros) and then subtracts it at the end.

Re: timechart shows 0 when there is no result

somesoni2 — Wed, 05 Aug 2015 14:44:25 GMT

Is your time range selected for search "All time"?

Re: timechart shows 0 when there is no result

LuiesCui — Thu, 06 Aug 2015 01:18:45 GMT

I did choose "All time" so I tried "Last 24 hours" later. Well it shows the data of every hour by now but when one of the subquery returns nothing ( let's say column "num1" shows all 0 ), no matter if column "num2" should return something or not, column "num2" shows all 0, too.

Re: timechart shows 0 when there is no result

LuiesCui — Thu, 06 Aug 2015 01:52:49 GMT

Works! Thank you!

Re: timechart shows 0 when there is no result

woodcock — Thu, 06 Aug 2015 02:35:53 GMT

I am sure you noticed that the last eval was wrong (had num1 instead of num2). I fixed my answer (but you must have done so already if it worked for you) so nobody else will be confused.

Re: timechart shows 0 when there is no result

LuiesCui — Thu, 06 Aug 2015 03:41:55 GMT

In fact I just edited my comment a few minutes before your comment, and I asked why num2 didn't need to be subtracted ( silly me ). Anyway thx again!