Display Time in Stats Chart

[caviman2201]

I have built a dashboard panel for management that shows concurrent connections by hour during peak hours to one our VDI environment. It also includes a trendline. It works great except by doing a chart using "stats" instead of "timechart", I can't get the X-axis to show the dates and the mouse-over values show the date as this hard to read format. For instance, 4/10/2014 at 3PM shows as "2014-04-10T15:00:00.000-04:00". When I display my data, which is excluding nights and weekends, in a timechart, the dates appear and the time values are human-readable, but the nights and weekends are just plotted with no data instead of a smooth line.

I either need to figure out how to get dates to show on the X-axis of a stats chart along with readable time values or I need to figure out how to get timechart to exclude nights and weekends completely... as in not plot them all so the line at 5PM on Friday directly connects to 9AM on Monday.

Here is my query:

sourcetype=csv host=host123 date_hour>=9 date_hour<=17  (date_wday=monday OR date_wday=tuesday OR date_wday=wednesday OR date_wday=thursday OR date_wday=friday) | stats sum(ConnectedSessions) by _time | trendline sma24(sum(ConnectedSessions)) as "24hr Moving Average" | rename sum(ConnectedSessions) as "Hourly Live Connections"

[Richfez]

A bit of a different search turns up this solution, which converts the _time field into a different format. Now my mouse-over values show "07/01@12" (change formatting to whatever you want), and the X axis shows - as long as I don't get too long of a time period - the same sorts of values. I found it useful to edit the chart's format and change the X-Axis label's direction to vertical so more fit easily.

Problems hopefully solved: time values shown sanely, "gaps" on weekends and evenings don't display on my graph (e.g. it skips from 06/30@17 to 07/01@09), moving average still seems correct.

Revised command below.

host=192.168.0.24 date_hour>=9 date_hour<=17  (date_wday=monday OR date_wday=tuesday OR date_wday=wednesday OR date_wday=thursday OR date_wday=friday) | timechart count(src) as counter span=1h cont=F |trendline sma12(counter) as "24 Hour Moving Average" | fieldformat _time=strftime(_time, "%m/%d@%H")

[Richfez]

That being said, depending again on exactly what they're trying to learn from this data, creative use of max, avg and min may also tell them a lot. For that, something like ...

... |bucket span=1h _time | stats count by _time | timechart cont=F span=1d max(count) as Peak avg(count) as Average min(count) as Minimum

That will give the max/min/average of each 1 hour period per day. I must admit I haven't figured out how to work the trendline in yet.

And have you thought to put several charts in a Dashboard for them?

[Richfez]

A search hit I ran across made me think of something else. If the purpose is to be in a PowerPoint slide, the chart they want will have rather a lot of data points for 180 days worth of data. Depending on what information they're actually interested in, they may not effectively see it anyway. IMO, at these densities the trendline is all. 🙂

[caviman2201]

Sure - I understand that. We're all about automating here and that's exactly what we're doing now... generating the reports in Excel. The problem is that it has to be done by hand. Part of the whole purpose of us purchasing Splunk was to automate a lot of these analytic reports.

[Richfez]

If they only need it updated occasionally, if I were you I'd use Splunk to grab the data and create a table, then dump that into Excel and use its pivot tables and charting to make whatever graphs you want.

I hate to give this non-answer, but sometimes you have to just rethink how you are doing something.

[caviman2201]

Well, it's a report for management, so they really just want one chart that can be slapped on a PP slide...

[Richfez]

Glad we're getting closer.

The time period selected won't change the mouse-over information, but can remove the useful X axis labels.

Long term, I'd ask Splunk to see if they could enhance the timechart to either use the Event's Timeline heuristics for x-axis labels or allow an option for "x-axis granularity" or something like that, because unless I stumble on some other solution this may be the best way to resolve this.

But, for the short term I'll keep looking for something.

Would it be possible to break this into two or three charts?

[caviman2201]

This is awesome - we're getting closer. This helps with the mouse-over time values, but the problem now is the time period. The goal is for this to be a 6-month chart... 😞

[Richfez]

Would a mild reworking of the search be OK? I don't have your data, but munging up a search on what our mail filtering device is syslogging, I can create a nice, continuous chart using timechart, complete with trendline, by adding "cont=F" to the timechart command to tell it to NOT be continuous, but instead skip missing values.

host=192.168.0.24 date_hour>=9 date_hour<=17  (date_wday=monday OR date_wday=tuesday OR date_wday=wednesday OR date_wday=thursday OR date_wday=friday) | 
timechart count(src) as counter span=1h cont=F |trendline sma24(counter) as "24 Hour Moving Average"

That gets a chart similar to what you describe in the second option with the line being plotted, connecting the dots in the way you want and completely skipping hte "zero" values that you are creating with your filtering.

[Richfez]

I've been poking at this. Support says there's no real fix for the not-what-you-want disappearing axis labels. The guy I was working with is putting in an enhancement request so we'll see where that goes.

[caviman2201]

This works, but for me it creates exactly the same chart as the stats chart. Using the cont=F option removes the time on the X-axis and still displays the mouse-over time values in that ugly format. My 2nd option regarding timechart was only because the normal (cont=T) timechart displays mouse-over time values as human-readable and includes the dates on the X-axis.