Splunk Search

How to combine fields and get average value by other field?

michaeler
Communicator

I feel like I'm dancing circles around the solution to this problem. I created a field named "Duration" with rex that has system recovery time in the 1d 1h 1m format but it doesn't always have all values; can also be 1d 1m, 1h 1m, 1m, 1h, or 1d (with values other than 1). I want to show the average downtime over 60days by system.

index= ....... earliest=-60d latest=0h
| rex field=issue ".*\((?P<Duration>\d[^\)]+"
rex field=Duration "((?P<Days>\d{0,2})d\s*)?((?P<Hours>\d{0,2})h\s*)?((?P<Mins>\d{0,2})m)?"
| where isnotnull(Duration)
| eval D=tonumber(Days)*1440

eval H=tonumber(Hours)*60
eval M=tonumber(Mins)
| stats sum(D) as DT sum(H) as HT sum(M) as MT count(event_id) as Events by System
| addtotals fieldname=TTotal
| eval HTime=TTotal/60

This gets me the numbers I need but am having trouble displaying the average time by System. It still needs to be divided by event_id per system and I need this to be an ongoing report so I can't do it manually.

| stats avg(HTime) by System - only gives me the HTime value per system, not the average per event per system.

Suggestions?

 

Labels (2)
0 Karma
1 Solution

richgalloway
SplunkTrust
SplunkTrust

It would be helpful to see a mock-up of the desired results since it's not clear what you mean by "average per event per system".  Once the stats ... by System command is processed the event_id field no longer exists.

See if this query comes close to what you want.  It uses multiple rex commands to handle all combinations of D, H, and M values.

index= ....... earliest=-60d latest=0h
| rex field=issue ".*\((?P<Duration>\d[^\)]+"
| where isnotnull(Duration)
| rex field=Duration "(?P<Days>\d{0,2})d\s"
| rex field=Duration "(?P<Hours>\d{0,2})h\s"
| rex field=Duration "(?P<Mins>\d{0,2})m"
| eval D=tonumber(coalesce(Days, 0))*1440
| eval H=tonumber(coalesce(Hours, 0))*60
| eval M=tonumber(coalesce(Mins, 0))
| eval TTotal = D + H + M
| stats avg(Ttotal) as HTime, count() as Events by event_id, System

 

---
If this reply helps you, Karma would be appreciated.

View solution in original post

richgalloway
SplunkTrust
SplunkTrust

It would be helpful to see a mock-up of the desired results since it's not clear what you mean by "average per event per system".  Once the stats ... by System command is processed the event_id field no longer exists.

See if this query comes close to what you want.  It uses multiple rex commands to handle all combinations of D, H, and M values.

index= ....... earliest=-60d latest=0h
| rex field=issue ".*\((?P<Duration>\d[^\)]+"
| where isnotnull(Duration)
| rex field=Duration "(?P<Days>\d{0,2})d\s"
| rex field=Duration "(?P<Hours>\d{0,2})h\s"
| rex field=Duration "(?P<Mins>\d{0,2})m"
| eval D=tonumber(coalesce(Days, 0))*1440
| eval H=tonumber(coalesce(Hours, 0))*60
| eval M=tonumber(coalesce(Mins, 0))
| eval TTotal = D + H + M
| stats avg(Ttotal) as HTime, count() as Events by event_id, System

 

---
If this reply helps you, Karma would be appreciated.
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 ...