Splunk Search

How to format the duration on weekly summary

auaave
Communicator

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)
0 Karma

cmerriman
Super Champion

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)
0 Karma

gcusello
SplunkTrust
SplunkTrust

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

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