topic Re: Why is the timechart avg function showing the value from last event instead of average for the selected time range from the time picker? in Splunk Search

Why is the timechart avg function showing the value from last event instead of average for the selected time range from the time picker?

sidekix24 — Mon, 22 Feb 2016 20:02:43 GMT

Hello all,

I have another issue with timechart, stats, and timepicker. I have the search below that needs to pull up a value of the average "response time" over the time range picked in the timepicker input, as well as show trending and with color thresholds. I have the trending and thresholds all set, but the issue I'm having is that the value appearing is just the "response time" avg in the last event instead of changing when I change the range in the timepicker.

| stats count by _time,source,Login_Status,Login_Response,QuickSearch_Status,QuickSearch_Response,Recruiter_Status,Recruiter_Response | eval Response_Time=(Login_Response+QuickSearch_Response+Recruiter_Response)  | table _time,Response_Time | chart avg(Response_Time) by _time

Anyone have any ideas on how to tweak the search to show the value of the avg of the range selected in the timepicker?

Re: Why is the timechart avg function showing the value from last event instead of average for the selected time range from the time picker?

sidekix24 — Mon, 22 Feb 2016 20:05:36 GMT

Here's what it looks like in the dashboard panel

Re: Why is the timechart avg function showing the value from last event instead of average for the selected time range from the time picker?

cramasta — Tue, 29 Sep 2020 08:53:01 GMT

You are mentioning using timechart however Im not seeing timechart command anywhere

From the example you are providing you are splitting by _time in both the stats and chart command. Unless you are bucketing your time earlier in your query you are going to get a avg metric for each unique time value

Are each one of these fields found in a single event?
Login_Response,QuickSearch_Response,Recruiter_Response

If they are you should be able to do this below without having to use stats or chart
| eval Response_Time=(Login_Response+QuickSearch_Response+Recruiter_Response) | timechart avg(Response_Time)

Re: Why is the timechart avg function showing the value from last event instead of average for the selected time range from the time picker?

sidekix24 — Tue, 29 Sep 2020 08:53:04 GMT

Thanks cramasta

I must've copied the query after I starting trying different commands.

Those are separate fields that can be found in 1 single event. The event is generated using a script that runs every 15 mins.

I tried your suggestion and it's still not coming up with what I'm looking for. This might be easier....below is query that I have on another dashboard panel that gives me the avg response time of the selected timerange from the timepicker. The value changes correctly as I change the range. I need to be able to do this same thing within a single value panel that will change colors by set thresholds and show trending.

It's like I can get one (get the corrected averages) or the other (show value with color changes and trending) but not both in one panel.

|stats count by QuickSearch_Response,Login_Response,Recruiter_Response | eval Response_Time=(Login_Response+QuickSearch_Response+Recruiter_Response) | stats avg(eval(Response_Time)) AS Avg_Response_Time

I'm sure I'm probably just missing some to make it work correctly

Thanks again

Re: Why is the timechart avg function showing the value from last event instead of average for the selected time range from the time picker?

masonmorales — Mon, 22 Feb 2016 21:25:12 GMT

It's hard to tell without sample data, but why wouldn't you just do this?

...base search... | eval Response_Time=(Login_Response+QuickSearch_Response+Recruiter_Response)  | timechart avg(Response_Time)

I don't think there is really a need to do a stats and table before your timechart in this case. You can also play with the minspan and maxspan options. See: http://docs.splunk.com/Documentation/Splunk/6.3.3/SearchReference/Timechart

Edit: Try searching a longer time range as well, or see if the events from your script are being timestamped correctly in Splunk.

Re: Why is the timechart avg function showing the value from last event instead of average for the selected time range from the time picker?

sidekix24 — Tue, 29 Sep 2020 08:53:07 GMT

Thanks masonmorales,

I tried the search with ...base search... | eval Response_Time=(Login_Response+QuickSearch_Response+Recruiter_Response) | timechart avg(Response_Time)

The issue that I come up with now is the value shows as 0 if the timepicker preset range is set lower than "last 24 hours"

Re: Why is the timechart avg function showing the value from last event instead of average for the selected time range from the time picker?

cramasta — Mon, 22 Feb 2016 21:44:43 GMT

What version are you running?
Also have you checked out this page for any pointers that you might be missing?

http://docs.splunk.com/Documentation/Splunk/6.3.3/Viz/Visualizationreference#About_single_value_visualizations

Re: Why is the timechart avg function showing the value from last event instead of average for the selected time range from the time picker?

somesoni2 — Mon, 22 Feb 2016 21:53:04 GMT

How do you want to show the trend and against which value? What is the threshold value for it to change color?

Re: Why is the timechart avg function showing the value from last event instead of average for the selected time range from the time picker?

cramasta — Tue, 29 Sep 2020 08:53:09 GMT

in 6.3.3 this all happens automatically if you are using a timechart thats displaying a single metric such as
| eval Response_Time=(Login_Response+QuickSearch_Response+Recruiter_Response) | timechart avg(Response_Time)

You can customize the colors and whatnot via the format editor for the single value.

Here is a simple example. All i did was select single value and it automatically did the trendline because i was using timechart. Im curious if you are getting 0 because your timechart is creating automatic timespans . What if you manually specified a span so that each time span is garenteed to have data?

| eval Response_Time=(Login_Response+QuickSearch_Response+Recruiter_Response) | timechart span=3h avg(Response_Time)

Re: Why is the timechart avg function showing the value from last event instead of average for the selected time range from the time picker?

sidekix24 — Mon, 22 Feb 2016 22:07:25 GMT

somesoni2,

Basically these are different response times to different parts of a site. We want to add the 3 response times and get an average time using the timepicker input. So we have the thresholds for the avg total response time set up to show green < 6s, Yellow <9 and then red >9. We want the trend arrows and sparkline using a compare from a week ago.

I can get bits and pieces working but can't put it all together.

I have the threshold and sparkline/trending arrows working on the total response time but when I try to do avg response time, the value doesn't change when the timepicker changed. I can get it to show the correct avg value with the color per threshold but without being able to time chart the avg, I cant get the trending portion to show.

Hope that all made sense 🙂

Re: Why is the timechart avg function showing the value from last event instead of average for the selected time range from the time picker?

sidekix24 — Tue, 29 Sep 2020 08:51:31 GMT

I tried

| eval Response_Time=(Login_Response+QuickSearch_Response+Recruiter_Response) | timechart span=15mins avg(Response_Time)

And it works sort of.....the value stays the same when I change the timepicker, it looks like it's just grabbing the value in the last event instead of taking all the events in the timerange then adding them and then getting the avg.

Re: Why is the timechart avg function showing the value from last event instead of average for the selected time range from the time picker?

cramasta — Mon, 22 Feb 2016 22:25:34 GMT

Have you tried running the query outside the dashboard to validate the data set you are getting back?

Or could it be the single value panel is not hooked up to the time range picker?

Re: Why is the timechart avg function showing the value from last event instead of average for the selected time range from the time picker?

sidekix24 — Tue, 29 Sep 2020 08:51:33 GMT

Yup...I ran the query below just in the search and used the visualization tab to see what the single value chart looks like and I still get zeros until I change the range to 24 hours. It keeps grabbing the value in the last event. almost like I'm doing a timechart last instead of timechart avg

| eval Response_Time=(Login_Response+QuickSearch_Response+Recruiter_Response) | timechart avg(Response_Time)

Re: Why is the timechart avg function showing the value from last event instead of average for the selected time range from the time picker?

sidekix24 — Mon, 22 Feb 2016 22:34:28 GMT

sorry...I'd post screen shots to help you guys see what I see but I don't have enough Karma points 🙂

Re: Why is the timechart avg function showing the value from last event instead of average for the selected time range from the time picker?

cramasta — Tue, 29 Sep 2020 08:53:12 GMT

Have you tried setting your span?

| eval Response_Time=(Login_Response+QuickSearch_Response+Recruiter_Response) | timechart span=1h avg(Response_Time)

Your script is only generating events once every 15 minutes. If you dont set a span for the timechart and search a small timeframe, it might create spans of 5 minutes, which would result in you getting some 5 minute spans with no events to calculate a average on.

Re: Why is the timechart avg function showing the value from last event instead of average for the selected time range from the time picker?

masonmorales — Mon, 22 Feb 2016 22:43:45 GMT

Any chance you can post 24 hours of some sample data that is being used by the timechart command? i.e.

 ...base search... | eval Response_Time=(Login_Response+QuickSearch_Response+Recruiter_Response) | table _time Response_Time

Re: Why is the timechart avg function showing the value from last event instead of average for the selected time range from the time picker?

sidekix24 — Mon, 22 Feb 2016 22:50:45 GMT

Tried that and that's where some of my confusion is....or least where I see it not working correctly.

So with that search, the value stays at 4.85 secs as I change to timepicker range. With the "last 15 mins" chosen, the value is 4.85 s which makes sense to be cause that's the value in the 1 event showing. With the "last 24 hours" chosen the value stays at 4.85s even though some events show that the total responsetime was over 10s. Same goes with "last 60 mins" or "last 4 hours" chosen. The value always matches the "last 15 mins".

Re: Why is the timechart avg function showing the value from last event instead of average for the selected time range from the time picker?

sidekix24 — Mon, 22 Feb 2016 22:58:33 GMT

sorry masonmorales,

I don't have enough Karma points to attach a file

Re: Why is the timechart avg function showing the value from last event instead of average for the selected time range from the time picker?

somesoni2 — Mon, 22 Feb 2016 23:05:11 GMT

Upon checking the dashboard xml from the "Splunk 6.x Dashboard Example" app-> Single Value Element example, the Trend compare the last value with previous value for the span mentioned in the option trendInterval. Do you want to compare data for "Selected timerange" vs "7 days before the selected timerange" ?

Re: Why is the timechart avg function showing the value from last event instead of average for the selected time range from the time picker?

cramasta — Mon, 22 Feb 2016 23:11:19 GMT

Let's forget about the single value and just look at the results from a last hour search and a last 24 hours. In both cases what what are the 2 last the avg values and time values that you are getting back. Thinking we have to understand the data you are getting back before trying to put it into single value.