Calculating "browsing time" from Ironport proxy access logs

[AlexD]

We're currently using another software package to generate reports from our Ironport proxy access logs and we need to replace this with Splunk.

What's got me stumped is the "browsing time" entry for each user shown on the report.

The explanation the old package gives for browsing time is that it groups the timestamps of each hit into sessions:

If two hits are written to the log file within five minutes of each other, they are grouped into the same session. If another hit is made within five minutes of the second hit, this too is added to the same session. If there is a break of more than five minutes with no hits made, then the previous session is finished and a new session is started when the next hit is made.

The total time of one session is calcuated as the time between the first and last hits of the session. The time of all sessions is added together to get the total browsing time for a user.

I have no idea where to start with this, so any help would be greatly appreciated!

[lguinn2 · Accepted solution]

Assuming that you have a username field that reflects the user who is doing the browsing (or an ip address or whatever), and the sourcetype is called cisco_wsa_squid, try

sourcetype=cisco_wsa_squid | transaction username maxpause=5m | 
stats sum(duration) as browsing_time by username |
fieldformat browsing_time = tostring(browsing_time,"duration")

[jordi_schlooz]

Hi there,
is it possible to get this result in a time chart format (with different colours) username vs duration?

[sinescorey]

to ashabc, I think this should do it (sub in your user ID for user_ID=😞
search sourcetype=wsa_accesslogs user_id= | search x_webcat_code_full!=Advertisements | transaction user_id maxpause=5m | search dest_domain=*| stats sum(duration) as browsing_time by dest_domain | fieldformat browsing_time = tostring(browsing_time,"duration") |sort browsing_time

[ashabc]

How can I modify this result to report for a single user showing username, date/time, domain visited, time spent/browsing time for each each domain and sorted by date/time

Thanks in advance

[lguinn2]

Yes, this result is in seconds - but I just modified the search to show the time as a duration, which should be easier to read...

[AlexD]

I gave this a test run, and I'm getting numbers in the two thousands for the highest users over the last 60 minutes. I'm guessing that since the timestamps are in Unix time, the browsing_time result is in seconds (or is this the default)?

If this is the case, none of the results are gerater than 3600s, so I guess that's probably right (though some of our usrs do spend a large portion of their time surfing).

[sfleming]

curses! lguinn beat me to the answer! 😛