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]

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?

[cramasta]

So is this correct?
Your single value should be the average total response time based on the entire timeframe selected in the timerange picker?

If yes, what are we comparing this average total response time to? The past 7 days average total response time?

[sidekix24]

ok...that makes sense in regard to the span being 15mins and whey the value is the same.

So the value wanted is the avg response time of the selected time frame of the time picker. So if they choose the last 4 hours then we get an avg of the response time for the last 4 hours (sum of all the response times divided by the 16 events in that time frame).

[sidekix24]

I was able to accomplish that in another dashboard just showing that one value by using this search:

| stats count by QuickSearch_Status,QuickSearch_Response,Recruiter_Response | eval Response_Time=(Login_Response+QuickSearch_Response+Recruiter_Response) | table Response_Time

But now they want me to do a single value panel with colors and trends showing the value and having it change related to the time range in the time picker. I know that means I have to add the timechart or chart by time for trending to be available and that's where I'm stuck

[cramasta]

Ok nows its starting to make sense. So are you trying to have the trend compare the past 15 minute average to the past 4 hour average?

[sidekix24]

the single value chart is showing 4.845 for both time ranges

[cramasta]

So we dont want to look at the first value, its the last value (which is what the single value will display), and the second to the last value( which is what the trend will be based on )

As somesoni2 mentioned what timespan the trend is based on would be configured by the trendInterval setting.

But as for the value being the same it makes sense based on the query. You are bucketing averages in 15 minute spans, so searching the past hour or the past 24 hours, the last 15 minutes span will be the same between the two queries.

Maybe we are not understanding what 2 timespan averages you are trying to compare.

[sidekix24]

ok

I'm running this search

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

just so we're on the same page

for the last 60mins:

the first value is 4.500 at 14:15
the last value is 4.845 at 15:15

For the last 24 hours:

the first value is 5.960 at 15:00 yesterday
the last value is 4.845 at 15:15

[masonmorales · Accepted solution]

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.

[sidekix24]

sorry masonmorales,

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

[sidekix24]

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"

[masonmorales]

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

[sidekix24]

Here's what it looks like in the dashboard panel

[cramasta]

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)

[sidekix24]

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

[somesoni2]

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

[sidekix24]

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 🙂

[cramasta]

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?

[sidekix24]

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)

[cramasta]

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.

[sidekix24]

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".

[cramasta]

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.