Splunk Search

browsing time by day

mcbradford
Contributor

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")

Tags (1)

wellmore
Explorer

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:
alt text

Splunk browse time results for same user and same day:
alt text

Is there a way to get this corrected in Splunk?

0 Karma

mcbradford
Contributor

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

wellmore
Explorer

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?

0 Karma

wellmore
Explorer

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

somesoni2
SplunkTrust
SplunkTrust

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

0 Karma

wellmore
Explorer

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.

0 Karma

somesoni2
SplunkTrust
SplunkTrust

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.

0 Karma

mcbradford
Contributor

I figured it out...

| fields + browsing_time

0 Karma

kallu
Communicator

Do you mean something like

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

mcbradford
Contributor

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

0 Karma

mcbradford
Contributor

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.

0 Karma

ziegfried
Influencer

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

0 Karma

wellmore
Explorer

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

0 Karma

somesoni2
SplunkTrust
SplunkTrust

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.

0 Karma

wellmore
Explorer

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 ?

0 Karma

mcbradford
Contributor

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?

0 Karma

Ayn
Legend

sort date_year date_month date_mday

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

0 Karma

mcbradford
Contributor

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?

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