Splunk Search

How to find the average of columns in a timechart?

yaminims
New Member

I am trying to to the average of columns in a timechart as a grand average. Below is my query, any help on this will be highly appreciated

index=summary_avg_transaction_time TRANSACTION_TYPE=IB* (TRANSACTION_TYPE="*ATT" OR TRANSACTION_TYPE="*VEZ" OR TRANSACTION_TYPE="*SPR")
| table "ADD A LINE" "HARDWARE SWAP" "NEW ACTIVATION" "PLAN CHANGE" "SPEEDY UPGRADE" TRANSACTION_TYPE _time
|eval CARR=ltrim(TRANSACTION_TYPE,"NONIB")
| timechart span=1w avg("ADD A LINE") as "ADD A LINE" avg("NEW ACTIVATION") as "NEW ACTIVATION" avg("PLAN CHANGE") as "PLAN CHANGE" avg("SPEEDY UPGRADE") as "SPEEDY UPGRADE" avg("HARDWARE SWAP") as "HARDWARE SWAP" by CARR
| append
[
search index=summary* index=summary_avg_transaction_time TRANSACTION_TYPE=IB* (TRANSACTION_TYPE="*ATT" OR TRANSACTION_TYPE="*VEZ" OR TRANSACTION_TYPE="*SPR")
| table "ADD A LINE" "HARDWARE SWAP" "NEW ACTIVATION" "PLAN CHANGE" "SPEEDY UPGRADE" TRANSACTION_TYPE _time
|eval CARR=ltrim(TRANSACTION_TYPE,"NONIB")
| timechart span=1month avg("ADD A LINE") as "ADD A LINE" avg("NEW ACTIVATION") as "NEW ACTIVATION" avg("PLAN CHANGE") as "PLAN CHANGE" avg("SPEEDY UPGRADE") as "SPEEDY UPGRADE" avg("HARDWARE SWAP") as "HARDWARE SWAP" by CARR
]

Tags (3)
0 Karma

yannK
Splunk Employee
Splunk Employee

Hi Yaminims

You can do a stats command (including the time bucket interval), then apply a timechart that will do the average of the values over an equal or larger interval.

Example with a 5 minute interval:

<mysearch> | bucket _time span=5m | stats max(somefield) AS max_field by _time somethingelse | timechart avg(max_field) by somethingelse

Remarks about your search :

  • You do not need table at all, you can do the timechart directly.
  • If you do use a list of fields, instead of table please prefer field. Because a table command is a presentation function and will not be optimized for the map-reducing and not take advantage of the splunk search-architecture. example : <mysearch> | field fieldA fieldB fieldC
0 Karma

OL
Communicator

Could you simply the search so that it is easy to read? for instance call a field A, B or C and remove unnecessary eval for instance. This will make it more "helper friendly" and you'll probably have more response 🙂

0 Karma
Get Updates on the Splunk Community!

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

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...