topic Re: trouble creating query for single value chart with trend for the last 24 hs in Dashboards & Visualizations

trouble creating query for single value chart with trend for the last 24 hs

pgoldweic — Wed, 30 Sep 2020 04:58:45 GMT

I've been trying to create query of the following type:

… some base search... | timechart span=1d count

with the trending value comparing the count right now with the count 24 hours ago. Unfortunately this is not working, as the trend happens to compare a count for today's date (a partial day) with the count for the whole day yesterday.
I read answers to similar questions: 1) https://answers.splunk.com/answers/333319/how-to-create-a-search-to-show-a-trending-single-v.html and 2) https://answers.splunk.com/answers/86659/timechart-day-offset.html, which led me to believe that I need to offset the time to get this working. So my current query looks like:

… base search | timechart span=1h count | addinfo | eval hour_of_time = strftime("%H",info_search_time), eval _time = _time - (hour_of_time * 3600) | timechart span=1d sum(count) as count

to which I believe I need to add an eventual _time = _time + (hour_of_time * 3600) . Since the hour_of_time field is gone from the result of the query above, I tried appending the following to the query again:

  |addinfo |eval hour_of_time=strftime("%H",info_search_time)| eval _time = _time + (hour_of_time * 3600)

However, the results:
- include a _time column with no values in it
- do not include the 'hour_of_time' field

What am I missing?

Re: trouble creating query for single value chart with trend for the last 24 hs

DalJeanis — Fri, 10 Apr 2020 23:41:10 GMT

If you only want a single value and a trend, then you don't really need to deal with hours.

Just throw away any records that are later than the same time yesterday, before you do the timechart.

 … base search earliest=1d@d
| eval Day=relative_time(_time,"@d")
| eval Moment=_time - Day

| addinfo 
| eval last_Moment=info_search_time -relative_time(info_search_time,"@d")
| where Moment <= last_Moment

| timechart span=1d sum(count) as count

There are other ways, but that should do.

Re: trouble creating query for single value chart with trend for the last 24 hs

pgoldweic — Sun, 12 Apr 2020 22:35:04 GMT

Thanks @DalJeanis although I'm a bit confused by your answer; perhaps I need to explain my needs better. All the data I really need is the following:
- a count of the events for the last 24 hours (starting the count now)
- a count of the events for the previous 24 hours
So, for example, if it is 3 pm now, then I'd want as #1 the count of events from 3 pm yesterday till now, and so forth. By having the two values, the trend value would represent the difference between them, which is what I need.
It would seem that your query above is not giving me that (or I'm misunderstanding it), but it is gathering all the events that happened since the beginning of the earlier day until 24 hours ago.

Am I reading this correctly? I'm really trying to find the simplest query that can represent what I need.

Re: trouble creating query for single value chart with trend for the last 24 hs

pgoldweic — Mon, 13 Apr 2020 00:36:15 GMT

So I ended up with the following query, which I believe is much simpler and I believe may be enough:

...base search | eval moment=now() - relative_time(now(), "@d") | eval _time = _time - moment | timechart span=1d  count| tail 3 | tail 2

in which I did not worry about getting the resulting timestamps to represent the correct times (I would need to add back in the relative time I subtracted earlier in that case), since I only really need to present the last count and the comparison with the previous count (that is, a single chart visualization with the trend value).

NOTE: I am using also an earliest value of "-48h@h" and a latest value of 'now'

Re: trouble creating query for single value chart with trend for the last 24 hs

DalJeanis — Tue, 14 Apr 2020 18:13:02 GMT

If you are using earliest=-48h@h, then use latest =@h

Do this

 … base search earliest=48h@h latest=@h
 | addinfo 
 | eval Midpoint=(info_min_time+info_max_time)/2
 | eval _time = if(_time<=Midpoint,MidPoint,info_max_time)
 | timechart span=1d sum(count) as count

This will give the date of the endpoint of the search as the date of the current point, and 24 hours prior as the data of the prior point.

Re: trouble creating query for single value chart with trend for the last 24 hs

pgoldweic — Tue, 14 Apr 2020 18:32:43 GMT

That seems useful. Thanks @DalJeanis ! I'll be accepting your answer to the post.