topic Re: How do you average multiple stats values at one time stamp as a timechart? in Splunk Search

How do you average multiple stats values at one time stamp as a timechart?

amandaxtru — Fri, 15 Jul 2016 18:10:17 GMT

I have multiple values connected to a timestamp at 5 minute intervals and I want to get the average of these multiple values at each interval and graph them as a timechart.

For example:
Values/Timestamp
123.54/21-JUN-16 01:00:00
76.43/21-JUN-16 01:00:00
6.6/21-JUN-16 01:00:00
4.3/21-JUN-16 01:00:00

65.6/21-JUN-16 01:05:00
55.4/21-JUN-16 01:05:00
38.84/21-JUN-16 01:05:00
5.57/21-JUN-16 01:05:00
76/21-JUN-16 01:05:00
233.45/21-JUN-16 01:05:00
675.33/21-JUN-16 01:05:00

I tried the query
| timechart span=5m avg(stats_value)

pls help

Re: How do you average multiple stats values at one time stamp as a timechart?

jkat54 — Fri, 15 Jul 2016 22:49:31 GMT

Is each event one line or multi line?

If each one is one line then |timechart avg(Values_field)

If it's multi line events then your best bet is to break each line into one event and use the same search.

Re: How do you average multiple stats values at one time stamp as a timechart?

sundareshr — Fri, 15 Jul 2016 23:27:59 GMT

If Timestamp is the name of the field, try this

... | stats avg(Values) as v by Timestamp

else, try this
... | stats avg(Values) as v by _time

Re: How do you average multiple stats values at one time stamp as a timechart?

amandaxtru — Tue, 29 Sep 2020 10:16:03 GMT

Stats_value and time_stamp are two different columns. Also there's about 4000 rows... I just don't know how to average each 5 minute increment as one value and graph it. It just says no results found if I do | timechart avg(stats_value).

Re: How do you average multiple stats values at one time stamp as a timechart?

amandaxtru — Tue, 29 Sep 2020 10:16:06 GMT

I forgot to mention that there's like 4000 rows. Sadly those two don't seem to work. 😞
I used
| dbquery "routerdb" "select time_stamp, stats_value from tbl_test_stats" | stats avg(stats_value) by time_stamp
and it said no results.
Stats_value and time_stamp are two different columns. Pls help

Re: How do you average multiple stats values at one time stamp as a timechart?

jkat54 — Mon, 18 Jul 2016 13:24:54 GMT

Ok so your time extraction must be "off". Does the _time field show up for each event? If it does, the time column should appear on the left of each event when you do a normal
Search. AND it should match the DATETIME stamp in the events.

Re: How do you average multiple stats values at one time stamp as a timechart?

jkat54 — Mon, 18 Jul 2016 13:27:22 GMT

Can you share your props.conf settings for this input?

Re: How do you average multiple stats values at one time stamp as a timechart?

amandaxtru — Tue, 29 Sep 2020 10:16:08 GMT

It kinda looks like this:
time_stamp stats_value
1466485800.000 132.87515
1466490600.000 59.48096
1466491500.000 64.9257
1466492400.000 67.09146
1466486400.000 70.14782
1466487000.000 82.2223
1466488200.000 99.02853

Graphing the first 1000 entries seems to work but I wanted to average out all the stats_values that are associated with one time_stamp.

Re: How do you average multiple stats values at one time stamp as a timechart?

sundareshr — Mon, 18 Jul 2016 13:46:19 GMT

What do you get when you do

    | dbquery "routerdb" "select time_stamp, stats_value from tbl_test_stats" | table time_stamp, stats_value

Also, can you try converting the stats_value field to number, like this

| dbquery "routerdb" "select time_stamp, stats_value from tbl_test_stats" | convert num(stats_value) as stats_value | stats avg(stats_value) by time_stamp

Re: How do you average multiple stats values at one time stamp as a timechart?

amandaxtru — Mon, 18 Jul 2016 13:46:25 GMT

I don't know why none of my comments are posting.
How do I access the props.conf? I don't think I have access to the file.
Also I'm using the general search for this query.

Re: How do you average multiple stats values at one time stamp as a timechart?

amandaxtru — Tue, 29 Sep 2020 10:16:11 GMT

The time_stamp looks like 1466485800.000 and a sample stats_value is 132.87515

Re: How do you average multiple stats values at one time stamp as a timechart?

amandaxtru — Tue, 29 Sep 2020 10:16:17 GMT

For the first query I get no results found which makes no sense...

The second query the "| concert num(stats_value) as stats_value" works but when "| stats avg(stats_value) by time_stamp" is added it also returns no results.

Re: How do you average multiple stats values at one time stamp as a timechart?

somesoni2 — Mon, 18 Jul 2016 14:34:05 GMT

Try like this

| dbquery "routerdb" "select time_stamp, stats_value from tbl_test_stats" | eval _time=time_stamp  | timechart span=5m avg(stats_value)

Re: How do you average multiple stats values at one time stamp as a timechart?

sundareshr — Mon, 18 Jul 2016 14:34:51 GMT

It would appear the issue is with the dbquery, not the stats. If the first doesn't return any results, the second will not work.

Do you see any errors in the DBConnect app? I assume you are using v1?

Re: How do you average multiple stats values at one time stamp as a timechart?

amandaxtru — Tue, 29 Sep 2020 10:16:20 GMT

Okay this makes a lot more sense now... There are about 300 stats_values associated with each time_stamp... I thought it was only a couple. What should I do to make this data meaningful if I can't just average 300 values or each time_stamp?

Re: How do you average multiple stats values at one time stamp as a timechart?

jkat54 — Mon, 18 Jul 2016 14:43:46 GMT

What if you add "limit=4000" to you dbquery command? Or limit=0

Re: How do you average multiple stats values at one time stamp as a timechart?

amandaxtru — Tue, 29 Sep 2020 10:16:23 GMT

Turns out there are about 300 stats_values associated with each time_stamp... I thought it was only a couple. What should I do to make this data meaningful if I can't just average 300 values or each time_stamp?