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

[michaeler]

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?

[richgalloway · Accepted solution]

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