Splunk Search

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

amandaxtru
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

jkat54
SplunkTrust
SplunkTrust

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

0 Karma

amandaxtru
Engager

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?

0 Karma

somesoni2
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

amandaxtru
Engager

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?

0 Karma

sundareshr
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

amandaxtru
Engager

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

0 Karma

sundareshr
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

amandaxtru
Engager

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.

0 Karma

sundareshr
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

jkat54
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

amandaxtru
Engager

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).

0 Karma

jkat54
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

amandaxtru
Engager

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.

0 Karma

jkat54
SplunkTrust
SplunkTrust

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

0 Karma

amandaxtru
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

amandaxtru
Engager

The time_stamp looks like 1466485800.000 and a sample stats_value is 132.87515

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...