timechart shows 0 when there is no result

[LuiesCui]

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

or

 _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!

[woodcock · Accepted solution]

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.

[LuiesCui]

Works! Thank you!

[woodcock]

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.

[LuiesCui]

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!

[LuiesCui]

need help...

[acharlieh]

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.

[LuiesCui]

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?

[acharlieh]

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)

[somesoni2]

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 *

[LuiesCui]

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.

[somesoni2]

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

[LuiesCui]

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.