Archive

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

Engager

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

0 Karma

SplunkTrust
SplunkTrust

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

0 Karma

Engager

Turns out there are about 300 statsvalues associated with each timestamp... 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?

0 Karma

SplunkTrust
SplunkTrust

Try like this

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

Engager

Okay this makes a lot more sense now... There are about 300 statsvalues associated with each timestamp... 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?

0 Karma

Legend

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

0 Karma

Engager

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

0 Karma

Legend

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
0 Karma

Engager

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

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

0 Karma

Legend

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?

0 Karma

SplunkTrust
SplunkTrust

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.

0 Karma

Engager

Statsvalue and timestamp 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).

0 Karma

SplunkTrust
SplunkTrust

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.

0 Karma

Engager

It kinda looks like this:
timestamp statsvalue
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 statsvalues that are associated with one timestamp.

0 Karma

SplunkTrust
SplunkTrust

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

0 Karma

Engager

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.

0 Karma

Engager

The timestamp looks like 1466485800.000 and a sample statsvalue is 132.87515

0 Karma