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
What if you add "limit=4000" to you dbquery command? Or limit=0
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?
Try like this
| dbquery "routerdb" "select time_stamp, stats_value from tbl_test_stats" | eval _time=time_stamp | timechart span=5m avg(stats_value)
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?
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
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
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
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.
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?
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.
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).
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.
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.
Can you share your props.conf settings for this input?
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.
The time_stamp looks like 1466485800.000 and a sample stats_value is 132.87515