How to format the duration on weekly summary

[auaave]

Hi,

I have the below Query. I want to have the sum of duration per week / description on time format [h]:mm:ss.
On line 5, I have inserted | eval duration=tostring(eventduration,"duration"), however, the moment I insert line 23 | chart max(eventduration) as sum over DESCRIPTION by weeknumber, the duration is back in seconds.

Thanks!

| dedup IDEVENT 
| where _time>relative_time(now(),"-4w@w") 
| bin _time span=1w 
| stats sum(DURATION) as eventduration by DESCRIPTION _time 
| eval duration=tostring(eventduration,"duration")
| appendpipe 
    [| stats latest(duration) as sortfield latest(_time) as _time by DESCRIPTION 
    | sort - sortfield limit=10 
    | streamstats count as descorder 
    | eval descorder=descorder+100 
    | table DESCRIPTION descorder]
| eventstats max(descorder) as descorder by DESCRIPTION
| where isnotnull(descorder)
| eval DESCRIPTION = descorder." ".DESCRIPTION
| fields - descorder
| appendpipe 
[| stats min(_time) as mintime max(_time) as maxtime 
| eval rollover=if(strftime(mintime,"%Y")!=strftime(maxtime,"%Y"),"Y","N")
| table rollover]
| eventstats max(rollover) as rollover
| eval weeknumber=strftime(_time,"%U")
| eval weeknumber=case(weeknumber<10 AND rollover="Y","Wk_".weeknumber,  true(),"Wk".weeknumber)
| chart max(eventduration) as sum over DESCRIPTION by weeknumber
| eval DESCRIPTION=substr(DESCRIPTION,5,len(DESCRIPTION)-4)

[cmerriman]

it is because you are using max(eventduration) and not max(duration) in the chart command on line 23.

i'm not sure if it will max the right way, because duration will be a string. you might want something like:

| dedup IDEVENT 
 | where _time>relative_time(now(),"-4w@w") 
 | bin _time span=1w 
 | stats sum(DURATION) as eventduration by DESCRIPTION _time 
 | appendpipe 
     [| stats latest(eventduration) as sortfield latest(_time) as _time by DESCRIPTION 
     | sort - sortfield limit=10 
     | streamstats count as descorder 
     | eval descorder=descorder+100 
     | table DESCRIPTION descorder]
 | eventstats max(descorder) as descorder by DESCRIPTION
 | where isnotnull(descorder)
 | eval DESCRIPTION = descorder." ".DESCRIPTION
 | fields - descorder
 | appendpipe 
 [| stats min(_time) as mintime max(_time) as maxtime 
 | eval rollover=if(strftime(mintime,"%Y")!=strftime(maxtime,"%Y"),"Y","N")
 | table rollover]
 | eventstats max(rollover) as rollover
 | eval weeknumber=strftime(_time,"%U")
 | eval weeknumber=case(weeknumber<10 AND rollover="Y","Wk_".weeknumber,  true(),"Wk".weeknumber)
 | chart max(eventduration) as sum over DESCRIPTION by weeknumber
  | eval duration=tostring(sum,"duration")
  |fields - sum
 | eval DESCRIPTION=substr(DESCRIPTION,5,len(DESCRIPTION)-4)

[gcusello]

Hi auaave,
you have to maintain duration in seconds for all the search so you can use it for all calculations, at the end, before output, you can format it.
So move line 5 at the end of your search.
Bye.
Giuseppe