Splunk Search

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

Path Finder

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)

• ### stats

1 Solution
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.
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!

#### Introducing Edge Processor: Next Gen Data Transformation

We get it - not only can it take a lot of time, money and resources to get data into Splunk, but it also takes ...

#### Take the 2021 Splunk Career Survey for \$50 in Amazon Cash

Help us learn about how Splunk has impacted your career by taking the 2021 Splunk Career Survey. Last year’s ...

#### Using Machine Learning for Hunting Security Threats

WATCH NOW Seeing the exponential hike in global cyber threat spectrum, organizations are now striving more for ...