Though WFilter already has many reports by default, there has the chance that you need other report formats or data. In this case, you can directly query the database tables to get what you need.

It’s complicated to customize reports for WFilter file-based version. So you need to install WFilter database version first. For steps to install WFilter database version, please check: WFilter Database Version Installation Guide

There has a “web surfing time report” in “Detailed Reports”->”Web Report”, you can generate this report “by name” or “by website” in WFilter user interface. Now if you need to get a report of web surfing time both “by name” and “by website”, you need to customize a sql query. The data is stored in table “webreport” with below structure:

create table webreport
(

)

1. Web surfing time report by ip address

To get a report by ip address and websites for a certain date, you can execute a sql like:

select ip, host, sum(convert(int, duration)) cnt from webreport where happendate>=’20120803′ and happendate<’20120804′ and ip is not null group by ip, host order by ip, cnt desc

You will get such kind of data:

2. Web surfing time report by user

If you have “Account Monioring” enabled, you also can generate reports based on AD usernames. The sql will be:

select acct, host, sum(convert(int, duration)) cnt from webreport where happendate>=’20120803′ and happendate<’20120804′ and acct is not null group by acct, host order by acct, cnt desc

You will get such kind of data:

Please notice, the “acct” column is urlencoded. You need to urldecode it to get a readable username.

Online urldecode.