Splunk Search

Search Query Total Browsing time per user in a month.

jibin1988
Path Finder

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.

Tags (1)
0 Karma
1 Solution

damien_chillet
Builder

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

View solution in original post

0 Karma

niketn
Legend

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
____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma

jibin1988
Path Finder

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.

0 Karma

damien_chillet
Builder

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

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