Search Query Total Browsing time per user in a month.

[jibin1988]

Hi Splunkers,

I want to generate a catogery wise Browsing time report per user. Here is my search given below :

host="192.xxx.xxx.xxx" user=* |transaction appcat| stats sum(duration) AS session_time sum(sentbyte) as sent sum(rcvdbyte) as received by appcat user
| eval browsing-time=tostring(session_time, "duration")
| eval TotalMB=round((sent+received)/1024/1024)
| table user,appcat,browsing-time,TotalMB | rename appcat as Category

I am able to generate report. but In the browsing-time i am getting value like 1+00:34:04 , 32+12:23:43, etc. I want this feild without days. I hope the format 1+00:34:04 is Days+hh:mm:sec. I want it in only time format. Is there any formula for that? Please help.

[niketn]

Try the following run anywhere search. I have created several separate fields for explaining the answers, you would need to get days and hours from duration string and then convert days to hours and sum with hours from duration string to get total hours. Finally prefix to the duration containing only Minute and Seconds.

PS: First two pipes are to create dummy data. Change duration in second pipe between 0, 10, 100, 1000, 10000, 100000 etc to verify expected output durationFinal

| makeresults
| eval duration=1000000
| eval durString=tostring(duration,"duration")
| eval daysString=case(match(durString,"\+"),replace(durString,"(\d+)\+(.*)","\1"),true(),0)
| eval daysToHours=24*daysString
| eval hoursString=case(match(durString,"\+"),replace(durString,"(\d+)\+(\d+):(.*)","\2"),true(),replace(durString,"(\d+):(.*)","\1"))
| eval durHours=daysToHours+hoursString
| eval durHours=case(durHours>=10,daysToHours+hoursString,true(),"0".durHours)
| eval durRemainingString=case(match(durString,"\+"),replace(durString,"(\d+)\+(\d+):(.*)","\3"),true(),replace(durString,"(\d+):(.*)","\2"))
| eval durationFinal=durHours.":".durRemainingString
| table duration durString daysString daysToHours hoursString durRemainingString durationFinal

[jibin1988]

Its bit complicated. I need a simple query. Where i should get result for category wise duration and MB per day with duration hr:mm:ss.

[damien_chillet · Accepted solution]

Hi,

I think @sideview 's answer to the following question is what your are looking for:
https://answers.splunk.com/answers/108248/tostring-x-duration-working-wierd.html