Splunk Search

timechart shows 0 when there is no result

LuiesCui
Communicator

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!

0 Karma
1 Solution

woodcock
Esteemed Legend

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.

View solution in original post

woodcock
Esteemed Legend

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
Communicator

Works! Thank you!

0 Karma

woodcock
Esteemed Legend

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.

0 Karma

LuiesCui
Communicator

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!

0 Karma

LuiesCui
Communicator

need help...

0 Karma

acharlieh
Influencer

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
Communicator

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?

0 Karma

acharlieh
Influencer

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)

0 Karma

somesoni2
SplunkTrust
SplunkTrust

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
Communicator

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.

0 Karma

somesoni2
SplunkTrust
SplunkTrust

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

0 Karma

LuiesCui
Communicator

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.

0 Karma
Get Updates on the Splunk Community!

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...