Need assistance creating a timechart using a calculated value

[john_byun]

I have the following search that gives me the ratio between the values from 2 separate searches. I'm sure it's pretty simple, but I'm struggling with putting this is a simple timechart. I want to see a line graph for the last 30 days plotting the 'ratio' value in 1 day increments. Also, how do I format the graph to only show tickmarks and labels for a each week instead of each day?

sourcetype=production eventtype="completedTransaction" tag=pilot | stats count as transactions| join [search sourcetype=production eventtype="totalErrors" tag=pilot | transaction host maxspan=3m | stats count as errors] | eval ratio=(errors/transactions)*100 | fieldformat ratio=tostring(round(ratio,1))+"%"

[Ayn · Accepted solution]

There are two ways I see you can do this depending on what kind of results you want. If you want to plot each and every data point over time, it's as simple as adding this at the end of the search:

| table _time ratio

Because a line chart (or area, or similar) works by taking the first column of its input as the X axis value and the rest of the columns to be the value that should be plotted on the Y axis, it doesn't matter if these columns were generated by a chart command or not.

One problem you will run into is that if you do this over a result set that includes many data points, your graph will take a long time to load or even drop data past a certain point - iirc the new JSChart module takes more datapoints than the FlashChart module that was previously used by default, but there's still a limit and there will still be performance issues even before you approach that limit. Because of this, timechart automatically divides the input into buckets of time and will output only one value per bucket. By default timechart will create a maximum of 100 buckets, which means that if you search the past 5 hours, each bucket will be 3 minutes long (300 minutes divided by 100 buckets = 3 minutes per bucket).

Now, because timechart divides the events into buckets based on time, several events might end up in the same bucket, and timechart somehow needs to find a way of still representing only one value out of that. This is why you can't just simply do timechart ratio - you need to specify a statistical function that tells timechart what to do with its input. You could do timechart first(ratio) as ratio which unsurprisingly grabs the first value in each span and outputs that. You could use last, or avg to take an average, or max, or, or...

tl;dr: For the timechart option, do something like

sourcetype=production eventtype="completedTransaction" tag=pilot | stats count as transactions| join [search sourcetype=production eventtype="totalErrors" tag=pilot | transaction host maxspan=3m | stats count as errors] | eval ratio=(errors/transactions)*100 | fieldformat ratio=tostring(round(ratio,1))+"%" | timechart first(ratio) as ratio

[Ayn]

Ah, I see now that you're doing a stats early in your search. After that stats command, the _time field will no longer be available. I don't know enough about your data to say how you would switch away from stats, but generally if you want to run stats against your events but without having it consume all fields, leaving only the aggregated results, switch to using eventstats instead. It will write its results as field values in the original events instead.

[john_byun]

Sorry for taking so long to get back to you. I've tried both options and neither are working for me. Adding
| table _time ratio
shows me a blank line graph.
The timechart query shows no results found. Thoughts?

[john_byun]

5.0.1. Thanks.

[ShaneNewman]

What version of Splunk are you using?