topic Re: How to write a search to calculate the average and median for a field in my sample data and produce a time chart? in Splunk Search

How to write a search to calculate the average and median for a field in my sample data and produce a time chart?

nsrao1983 — Mon, 28 Dec 2015 23:45:28 GMT

Hi Team,

Am using Splunk for the first time.
I need to calculate the average and Median for the field rate which is shown below.

Here's the sample output from my Splunk log:

Thu Dec 17 02:48:52 GMT+00:00 2015 [STATS] bucket-> 6 , 3795 , 25322 , 318 , 240 , 0
Thu Dec 17 02:48:52 GMT+00:00 2015 [STATS] rate-> 7123440

In the search text box, I am specifying the

index=<index_name> source=<source_name>

since the above mentioned pattern is not key=value, I am unable to calculate the average and median for it, but I cannot change the pattern since it is existing.

How to calculate the average and median of this field? Please kindly help.

Your timely intervention really helps me a lot.

Based on this rate field, I need to draw a time chart for every 15ms... It will be great if you can share the usage and steps.

Re: How to write a search to calculate the average and median for a field in my sample data and produce a time chart?

acharlieh — Tue, 29 Dec 2015 00:23:08 GMT

Any text that isn't in key=value format can be extracted into a field by developing a field extraction. Once you have that field extraction created and applied to your sourcetype/source you can then search for all events in your index and source that have a rate field and calculate statistics across all events with this field.

index=<index_name> source=<source_name> rate=* | timechart avg(rate) median(rate)

One of the nice things about Splunk, is you can adjust these extractions at search time, and don't have to worry about reindexing your data. The timechart command has a number of options you may be interested in as well, span in particular if you need to adjust the bucket sizes.

Re: How to write a search to calculate the average and median for a field in my sample data and produce a time chart?

lguinn2 — Tue, 29 Dec 2015 00:28:40 GMT

You need to extract the field(s) that you want to work with. You could use the interactive field extractor, which would create a permanent field - if you will continue to analyze this data, this would probably be the best option. Or, you could create a temporary field using the rex command, like this:

index=x source=y
| rex "rate-\> (?<rate>\d+)"
| stats avg(rate) as "Average rage" median(rate) as "Median Rate"

and this

index=x source=y
| rex "rate-\> (?<rate>\d+)"
| timechart span=15ms  avg(rate) as "Average rage"

Re: How to write a search to calculate the average and median for a field in my sample data and produce a time chart?

nsrao1983 — Tue, 29 Dec 2015 01:21:23 GMT

Team,

Appreciate your prompt response.

I was unable to see the results by executing the above pattern.

For your reference am providing the sample output in logs for more clarity.

See the below output in my logs

Thu Dec 17 02:48:37 GMT+00:00 2015 [STATS] rate-> 7549440
Thu Dec 17 02:48:52 GMT+00:00 2015 [STATS] rate-> 7123440
Thu Dec 17 02:49:07 GMT+00:00 2015 [STATS] rate-> 6730800
Thu Dec 17 02:49:22 GMT+00:00 2015 [STATS] rate-> 7172400
Thu Dec 17 02:49:37 GMT+00:00 2015 [STATS] rate-> 0

For every 15 milli secs some value will be printed against the "rate"

I need to calculate the following

Average and Median for rate
(After rate either 0 or some number will be there always and no spaces.)
Timechart against the rate and time. (for every 15millisecs, we are printing the time and rate which is shown above. We need to plot the graph using the splunk for the same)

index=x source=y

It will a great help if you can provide the inputs to achieve the above two targets. So that we can start using the spunk in an extensive way

Re: How to write a search to calculate the average and median for a field in my sample data and produce a time chart?

lguinn2 — Wed, 30 Dec 2015 18:39:16 GMT

This regular expression "rate-\> (?\d+)" has a space after the >. If your data doesn't have a space, use this regular expression instead

"rate-\>(?\d+)"

HTH

If you need to create fields in order to use Splunk "in an extensive way," I recommend that you review the Splunk Search Tutorial, particularly this section: Use fields to search. You should also read the documentation on the field extractor.

Re: How to write a search to calculate the average and median for a field in my sample data and produce a time chart?

nsrao1983 — Wed, 30 Dec 2015 18:48:20 GMT

Hi lguinn,

Hey i tried the approach you suggested. It worked for averages and Medians.
But unable to plot the graph using time chart.

Please find below the query using to draw the time chart

index=x host=y source=z | rex "rate-\> (?\d+)" | stats avg(rate) as "Average rate" median(rate) as "Median Rate"  | timechart span=15ms  avg(rate) as "AVG Rate"

I'm getting this error:

Error in 'timechart' command: The value for option span (15ms) is invalid. When span is expressed using a sub-second unit (ds, cs, ms, µs), the span value needs to be < 1 second, and 1 second must be evenly divisible by the span value.

Re: How to write a search to calculate the average and median for a field in my sample data and produce a time chart?

lguinn2 — Mon, 04 Jan 2016 06:43:14 GMT

Well, it looks like you could use span=10ms or span=20ms but not span=15ms