7 Replies Latest reply on Sep 20, 2012 9:42 PM by lukeh

    Web Reporter custom query with totals

      Hi all,


      I've been asked to get a report out of Web Gateway that shows the total browsing time for each user for each date in a date range (the current month).


      So I'd like to see something like this:



      9/20/12     domain\user1     9:00AM        3 minutes 20 seconds     Social Networking     www.facebook.com

      9/20/12     domain\user1    11:00AM      5 minutes 10 seconds     Business                     www.business.com


                                                                            8 minutes 30 seconds    


      9/20/12     domain\user2     8:00AM       2 minutes 30 seconds     Social Networking     www.facebook.com

      9/20/12     domain\user2    4:00PM      8 minutes 20 seconds     Business                     www.business.com


                                                                            10 minutes 50 seconds    



      9/19/12     domain\user1     10:00AM        7 minutes 40 seconds     Social Networking     www.facebook.com

      9/19/12     domain\user1     12:00PM      4 minutes 20 seconds     Entertainment               www.entertainment.com


                                                                             12 minutes 0 seconds



      9/19/12     domain\user2     9:00AM        9 minutes 10 seconds     Social Networking     www.facebook.com

      9/19/12     domain\user2     2:00PM      8 minutes 50 seconds     Entertainment               www.entertainment.com


                                                                             18 minutes 0 seconds



      So far I've been able to run the report multiple times against each user, but I get a total browse time for that user in the date range (eg. 1 day, 7 hours, 23 minutes 4 seconds)!


      Any ideas?





        • 1. Re: Web Reporter custom query with totals

          Browse time can only be compared against client IP and/or user name. You should not include site names, categories, etc.  More information here.





          Second, the "Run report multiple times" option is for use when you have a list of things you want to separate into filtered reports. For example, you want to know the top sites (hits/requests) for each of the top 10 users with the most browse time.  Then, you are iterating over a list of users, filtering the report that shows top sites by hits.  Another way to think about it is that you could get the same results by running a report to show the top 10 users by browse time. Then you take the results of that report, and manually run a top sites report 10 times, while changing the user filter each time.


          That is essentially the recommendation provided in the KB above, and the closest (accurate) report that Web Reporter can make for this scenario.

          • 2. Re: Web Reporter custom query with totals

            I have a similar report that includes categories,but not websites. We determined that categories would be sufficient and could be investigated further if neccesary.


            I'll look for it.


            Found an example!


            We also limited the report to categories because of the behaviour that you can see here. If it also listed websites, you'd potentially be looking at hundreds or thousands of enteries for one user depending on your time frame (ours is by month).


            Message was edited by: ittech on 9/20/12 10:11:39 AM EDT
            • 3. Re: Web Reporter custom query with totals

              I created the report starting with a Web-Summary Query containing User Name, Browse Time, and URL Category


              After that I filtered the Query so it only contained certain users and categories which are normally blocked for all users. This way we can see if anyone is abusing the extended access they've been granted.



              Hope that helps!


              Message was edited by: ittech on 9/20/12 10:20:27 AM EDT
              1 of 1 people found this helpful
              • 4. Re: Web Reporter custom query with totals

                Hello ittech,


                I know that you can include browse time on the same report with categories, but it isn't accurate; especially if paged views are not enabled.  If paged views are enabled, and you are not load balancing multiple gateways, then browse time by category and site could be fairly accurate, but as a side affect and not as a deliberate intention.


                Web pages pull content from many different sites, such as for advertising or media content.  Web Reporter is only tracking sequential requests while looking at the user name and client IP address.  When the end of an access log is reached, or no more requests from that user/client_ip is found for 2 minutes, the browse time session is closed and divided amung the requests (this varies depending on which version of Web Reporter you are using, but is true for all versions for the past year or so).


                Ultimately you are able to include browse time with other categories as a side affect of how it is stored in the database and a limitation in the GUI to prevent you from doing this.

                • 5. Re: Web Reporter custom query with totals

                  We took into consideration that browse time would not be 100% accurate. For example, reading a news article which has facebook comments at the bottom for reader input. This is why the report is just a starting point to see if further investigation is necessary. It's also a good self-auditing tool to make sure users haven't been granted access they shouldn't have.


                  I didn't realize the time was divided though, that's interesting. Thanks for the input


                  How do you enable paged views?


                  Message was edited by: ittech on 9/20/12 11:11:57 AM EDT
                  • 6. Re: Web Reporter custom query with totals

                    Page Views are part of the log parsing options. (Administration -> Setup -> Log Sources -> edit your log source(s) -> Processing tab)


                    For more information on page views, see this document



                    • 7. Re: Web Reporter custom query with totals

                      Thanks ittech, I've run a report similar to the example you posted and I think it'll quench the thirst of my superiors.


                      @sroering - thanks for your advice - it's good to know how to use the multiple run function. BTW, Page Views are enabled, yes.