topic Re: browsing time by day in Splunk Search

browsing time by day

mcbradford — Mon, 28 Sep 2020 12:19:18 GMT

I am using the following to determine the amount of browsing time for a user.

I would like to have a table that actually shows browsing time by day. So if I run this command for the past 7 days, I would like to have a breakdown per day.

index=webproxy user="test" Company="test" | transaction user maxpause=5m | stats sum(duration) as browsing_time by user | fieldformat browsing_time = tostring(browsing_time,"duration")

Re: browsing time by day

kallu — Thu, 23 Aug 2012 13:07:22 GMT

Do you mean something like

... | stats sum(duration) as browsing_time by user, date_year, date_month, date_mday

Re: browsing time by day

mcbradford — Thu, 23 Aug 2012 13:31:48 GMT

This works great!!!, but.... the results are display (sorted) based on the count value, so the sequence to date is not correct. How can I get the values sorted by year, month, day?

Re: browsing time by day

Ayn — Mon, 28 Sep 2020 12:19:21 GMT

sort date_year date_month date_mday

Or, if you want descending order, add a leading "-", or trailing "DESC".

Re: browsing time by day

mcbradford — Mon, 28 Sep 2020 12:19:23 GMT

One last thing.

index=webproxy user="test" Company="test" | transaction user maxpause=5m | stats sum(duration) as browsing_time by user | fieldformat browsing_time = tostring(browsing_time,"duration")

If I want to list the top 10 users with the most time?

I have tried top user. top browsing_time. This is not working?

Re: browsing time by day

ziegfried — Thu, 23 Aug 2012 17:22:16 GMT

Re: browsing time by day

mcbradford — Mon, 28 Sep 2020 12:19:28 GMT

Based on the following search:

index=webproxy NOT user="-" user="test" | transaction user maxpause=5m | stats sum(duration) as browsing_time by user | fieldformat browsing_time = tostring(browsing_time,"duration")

I would love to use the radial gauge to show the value in hours. I tried this, but I am sure the format of the "browsing_time" is preventing the gauge from populating.

Re: browsing time by day

mcbradford — Thu, 23 Aug 2012 21:01:45 GMT

I figured it out...

| fields + browsing_time

Re: browsing time by day

mcbradford — Thu, 23 Aug 2012 21:09:30 GMT

I just ran into a problem. If the total count is greater than 24, it get converted??? Like 1+05:00:28

Re: browsing time by day

mcbradford — Mon, 28 Sep 2020 12:19:31 GMT

I figured it out, | fieldformat browsing_time = (browsing_time/3600)

Re: browsing time by day

wellmore — Fri, 07 Apr 2017 16:28:26 GMT

This gave me a roll-up of browse time for my search windows of last 7 days, which is progress. But is there a way to have it show per day totals?

Thanks,
Lee

Re: browsing time by day

wellmore — Fri, 07 Apr 2017 16:33:14 GMT

Also, for one day it shows browse_time: 10:01:10. But 10 hours is not possible for this given users shift. Is it read mm:sec:ms ?

Re: browsing time by day

somesoni2 — Fri, 07 Apr 2017 16:44:02 GMT

Try this

index=webproxy user="test" Company="test" | transaction user maxpause=5m | eval Date=strftime(_time,"%m/%d/%Y")
| chart sum(duration) as browsing_time by user Date

Regarding your other comment, tostring(X,"duration") converts seconds X to readable time format HH:MM:SS.

Re: browsing time by day

wellmore — Fri, 07 Apr 2017 18:08:07 GMT

This is not providing accurate results for us. I narrowed my search to current day and one user = 13:57:59 and another user 05:27:38. How can I interpret these results?

<usernamehere> | transaction user maxpause=5m | stats sum(duration) as browsing_time by user | sort -browsing_time | head 10 | fieldformat browsing_time = tostring(browsing_time,"duration")

Re: browsing time by day

wellmore — Fri, 07 Apr 2017 18:21:53 GMT

It appears that most of my users show browse_time to be right around the total time they are in the office, which is 8 hours. Is my FSSO agent not configured correctly?

Re: browsing time by day

somesoni2 — Fri, 07 Apr 2017 18:24:48 GMT

IMO, it gives the total duration between first and last browsing event, for a set which are more that 5m apart. If you've a better rule to define browsing session, you should add it to transaction command. The transaction command provides a lot of good options for that (see here).

Please note that transaction is not the most optimal command and may be replaced with other alternative commands. See this http://docs.splunk.com/Documentation/Splunk/6.5.2/Knowledge/Abouttransactions#When_to_use_stats_instead_of_transactions

Re: browsing time by day

wellmore — Fri, 07 Apr 2017 18:34:10 GMT

I am looking for a better rule, or one that can give me a report on total browse time for a user in a given day. The results from this one are not even close.

Re: browsing time by day

somesoni2 — Fri, 07 Apr 2017 18:53:41 GMT

It would be better if you open up a new question, with your sample events, preferably a mock output of what report you want. You can see this post is already clutterred and anyone else looking for same info may get lost searching for option they should adopt to.

Re: browsing time by day

wellmore — Tue, 11 Apr 2017 16:44:29 GMT

The browsing time by user from the Fortinet FortiAnalyzer, which my vendor ran for me does not even come close to the Splunk browse time results I am getting.

Fortinet FortiAnalyzer Report:

Splunk browse time results for same user and same day:

Is there a way to get this corrected in Splunk?