9 Replies Latest reply: Dec 20, 2013 12:20 PM by sroering RSS

    Web Reporter query

    blackinux

      Hi, wonder if anyone knows how to build a query that would exclude facebook.com\plugins since ill get many false positive for users that aren’t getting directly to facebook. I have try different combinations in the exclude tab but none will pass past www.facebook.com Thanks.

        • 1. Re: Web Reporter query
          sroering

          You would need to run a report using the detailed data set, and you would need to filter the URL column on the "colum properties" tab of the query.  In the exclude box, put your pattern.  *www.facebook.com/plugins*

           

          But you can expect performance to be much slower than other reports because this is a costly operation.

          • 2. Re: Web Reporter query
            blackinux

            It work perfect for detail. Thanks. But its there anyway to make it work for Summary too?

            • 3. Re: Web Reporter query
              sroering

              Because you want to filter on the "path" part of the URL, you cannot use a standard siten ame filter. You must filter on the URL, which means you must use detailed data. But I recommend you use the standard filters whenever possible.  Putting your filters under the query are hidden and easily forgotten which may give you confusing results.

               

              However, Detailed Data has everything that summary data does, so this shouldn't be a limiting factor for you. They will just run slower than summary data reports because the detaild dataset is around 10x larger.

               

              here are a couple of tips for making detaled reports reflect summary reports.

              1) don't forget to check the "combine similar" checkbox on the second tab.  That will condense multiple lines from this

               

              www.mcafee.com  business   1111 bytes

              www.mcafee.com  business   2222 bytes

              www.mcafee.com  busiiness  4444 bytes

               

              into this

               

              www.mcafee.com  business  7777 bytes

               

              2) If you need to filter on the URL path, but only want to see the site name on the report, you still need to include URL in the report. On the column properties tab, you can click a checkbox to hide the URL from the report results, but still use your filter.  If you cannot hide the column, then the query type is probably a graph; make sure the column is not included on the graph results.

              • 4. Re: Web Reporter query
                blackinux

                I was able to generate the report, but am having a hard time using the "combine similar" seems to combine some columns and other dont. For example i got User Name, Ip Address, URL HIDEN for blocking pluggins, Hits. and set for the first 50 from the report on detail. it will show hits as a whole but it will duplicate the name for every 1 hit in names. Summary works perfect, the only problem it keep showing me people that visited urls with the plugins that i want to ignore.

                • 5. Re: Web Reporter query
                  sroering

                  Not sure.  Search for the report name in the .../reporter/log/server.log

                  A few lines after, you should see a SQL='SELECT ..... etc..... "  line.  Copy and past that query here so I can see it.

                   

                  We should not see scr_fct_exact_access.url  in the SELECT statement, and we should see a GROUP BY clause that has all or most of the columns in the select statement.

                  • 6. Re: Web Reporter query
                    blackinux

                    SQL='SELECT CONCAT('(', scr_dim_user.user_name, ')', (CASE scr_dim_user.user_real_name WHEN '-' THEN '' ELSE CONCAT(' ', scr_dim_user.user_real_name) END)), scr_fct_exact_access.url AS COL1 FROM scr_dim_directory scr_dim_directory, scr_dim_user scr_dim_user, scr_fct_exact_access scr_fct_exact_access, scr_dim_ipaddress scr_dim_ipaddress, scr_dim_site_request scr_dim_site_request, scr_dim_action_request scr_dim_action_request, scr_dim_action scr_dim_action, scr_dim_log_source scr_dim_log_source, scr_dim_category scr_dim_category, scr_dim_site scr_dim_site WHERE NOT(CONCAT('(', scr_dim_user.user_name, ')', (CASE scr_dim_user.user_real_name WHEN '-' THEN '' ELSE CONCAT(' ', scr_dim_user.user_real_name) END))='(-)') AND scr_dim_directory.directory_id = scr_dim_user.directory_id AND scr_dim_user.user_id = scr_fct_exact_access.user_id AND scr_dim_ipaddress.ip_id = scr_fct_exact_access.user_ip_id AND scr_dim_site_request.site_request_id = scr_fct_exact_access.site_request_id AND scr_dim_action_request.action_request_id = scr_fct_exact_access.action_request_id AND scr_dim_action_request.action_id = scr_dim_action.action_id AND scr_dim_log_source.log_source_id = scr_fct_exact_access.log_source_id AND NOT( scr_fct_exact_access.url LIKE '%www.facebook.com/plugins%') AND NOT( scr_fct_exact_access.url LIKE '%connect.facebook.net%') AND NOT( scr_fct_exact_access.seconds_since_epoch<1380585600) AND NOT( scr_fct_exact_access.seconds_since_epoch>1385855999) AND ( scr_dim_category.category_name='Social Networking' ) AND scr_dim_category.category_id = scr_fct_exact_access.category_one_id AND ( scr_dim_site.site_name LIKE '%facebook.com%' ) AND scr_dim_site.site_id = scr_dim_site_request.site_id AND ( CASE WHEN scr_dim_action.action_id = 6 THEN 0 ELSE scr_dim_action.action_id END='0' ) GROUP BY CONCAT('(', scr_dim_user.user_name, ')', (CASE scr_dim_user.user_real_name WHEN '-' THEN '' ELSE CONCAT(' ', scr_dim_user.user_real_name) END)), COL1'

                    • 7. Re: Web Reporter query
                      sroering

                      The query you posted does not match the description in your previous thread. You are missing columns in the select clause. But on a hunch, I tried to create it and I found what is likely a bug. I have the correct columns in the select (i.e. no URL), but appears that the url is still included in the group by clause. We was able to get the correct results if I removed the URL from the group by and run the query directly against the database.

                       

                      I'd recommend you open a support ticket.

                      • 8. Re: Web Reporter query
                        blackinux

                        In that case, if i take URL out, can i use the exclusions in HITS columns?

                        • 9. Re: Web Reporter query
                          sroering

                          You won't be able to work-around this issue by taking out the URL. From what you described, you are doing everything 100% correct.

                           

                          I'm saying that I tried to create the report you described from your description.  What I found appears to be a bug that has the symptoms you reported (similar lines not being grouped).  So I recommend you open a support ticket and we can give you better tracking of the issue. Just mention this thread in your support ticket.