Getting Data In

Calculating "browsing time" from Ironport proxy access logs

AlexD
Explorer

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!

Tags (2)
0 Karma
1 Solution

lguinn2
Legend

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

View solution in original post

lguinn2
Legend

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
New Member

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

0 Karma

sinescorey
New Member

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

0 Karma

ashabc
Contributor

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

0 Karma

lguinn2
Legend

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

0 Karma

AlexD
Explorer

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

0 Karma

sfleming
Splunk Employee
Splunk Employee

curses! lguinn beat me to the answer! 😛

0 Karma
Get Updates on the Splunk Community!

Enterprise Security Content Update (ESCU) | New Releases

In September, the Splunk Threat Research Team had two releases of new security content via the Enterprise ...

New in Observability - Improvements to Custom Metrics SLOs, Log Observer Connect & ...

The latest enhancements to the Splunk observability portfolio deliver improved SLO management accuracy, better ...

Improve Data Pipelines Using Splunk Data Management

  Register Now   This Tech Talk will explore the pipeline management offerings Edge Processor and Ingest ...