6 Replies Latest reply: Aug 27, 2013 3:14 PM by scottp RSS

    Report for user agents failing to run and doing something I've not seen

    scottp

      Trying to create a report for user agent strings in web reporter.  I've tried each useragent field possible and they are all failing.  I'm not including other fields.  And I've tried for a day of data which is never an issue for other reports.  Any ideas why?

       

      Since the user agent is sent to our logging solution via the same log files it is included.

       

       

      User Agents

      Line Agent ID Browser Name and Version Browser Name

      Communications link failure

      The last packet successfully received from the server was 28 milliseconds ago. The last packet sent successfully to the server was 1,451,354 milliseconds ago.

      sql: SELECT scr_dim_agent.agent_id_string AS COL1, scr_dim_agent.agent_id_group_1 AS COL2, scr_dim_agent.agent_id_group_2 AS COL3

      FROM scr_dim_agent scr_dim_agent, scr_fct_exact_access scr_fct_exact_access, scr_dim_ipaddress scr_dim_ipaddress, scr_dim_user scr_dim_user, 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_site scr_dim_site

      WHERE scr_dim_agent.agent_id = scr_fct_exact_access.agent_id AND scr_dim_ipaddress.ip_id = scr_fct_exact_access.user_ip_id AND scr_dim_user.user_id =

      scr_fct_exact_access.user_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.seconds_since_epoch<1377129600) AND NOT( scr_fct_exact_access.seconds_since_epoch>1377215999) AND

      NOT( scr_dim_site.site_name LIKE '%.site1.com') AND NOT( scr_dim_site.site_name LIKE '%.site2.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' )

      ORDER BY COL1 ASC

       

       

      Message was edited by: scottp on 8/23/13 1:34:11 PM CDT

       

      Message was edited by: scottp on 8/23/13 1:40:57 PM CDT
        • 1. Re: Report for user agents failing to run and doing something I've not seen
          sroering

          Does this only happen when you include the user agent field to your query or does it happen for all reports, etc?  

          What are the exact steps to reproduce the issue?

          • 2. Re: Report for user agents failing to run and doing something I've not seen
            scottp

            Its just the 3 fields related to browser info that I've seen this on.  The thread below is related it seems.  It is possible I'm seeing a similar issue.

             

            https://community.mcafee.com/message/174826

             

            This may be due to these browser fields not being baked into Web Reporter for reporting by default? 

             

            The article here explains how to add a custom field (but its not accurate in my version). https://community.mcafee.com/docs/DOC-4929

             

            I also found a spot where it says that the browser fields are automatically parsed for reports. (Under Log Sources / Custom Columns).

             

            "Custom columns are pre-populated and ready for you to include in advanced queries".    Browser Info and version is included i nthis list.

             

            So why would I need to do anything given this?  I don't want to make custom columns (per article) if that means it will break my reporting if I have to undo it.

             

            Message was edited by: scottp on 8/26/13 7:38:09 AM CDT
            • 3. Re: Report for user agents failing to run and doing something I've not seen
              sroering

              Basically, your first post shows an error with the SQL query.  I took this as the text of the report body.  When that occurs, it simply means the report failed, for some reason.  I'm trying to understand what may have caused that since it isn't a common scenario.  You haven't given me alot of details yet.  you said that you are using an older version, but you didn't say which version.  

               

              The thread you said "It is possible I'm seeing a similar issue" isn't related to your problem.

               

              User agent has been a part of Web Reporter as long as the user-defined columns, so I don't know why you would try to use user-defined (custom) columns.  So you should try to use custom columns for user agent data.

               

              The error you seem to be getting could happen if there is a problem with the query, but I verified that it is syntatically correct.  The server.log may contain more information regarding what caused the error.  I think that the "Communications link failure" error is just the output of the exception handeling, and not the original cause of the error.

               

               

              Could you please

              1) Confirm which version of Web Reporter you are using.

              2) Answer my previous 2 questions more clearly.

              3) Recreate the error and post the more complete message from the bottom of server_err.log

              • 4. Re: Report for user agents failing to run and doing something I've not seen
                scottp

                1.  We have 5.1.1.01 build 1042.

                 

                2. 

                Does this only happen when you include the user agent field to your query or does it happen for all reports, etc? 

                 

                           Its just the 3 fields related to browser info that I've seen this on.

                 

                 

                What are the exact steps to reproduce the issue?

                 

                          Create any report, and include the "browser version" field.

                 

                 

                3.  Today of course, this works.  It figures.  I've shrunken the dataset to 1 hour, and checked the option to group similar data.  If it occurs again, I will provide the error.

                 

                Due to this I'm fine for now.  Al lthe Brower Name, and Version columns say "Other".  So I guess that doesn't work so well.  The UserAgent is good enough for me.

                • 5. Re: Report for user agents failing to run and doing something I've not seen
                  sroering

                  Based on your feedback and the version you are running, this may be a bug.  I haven't see an issue like this in a very long time and version 5.1.1 is quite dated.

                   

                   

                  I cannot think of a scenario where changing the date range for the report would cause the error you got.  Looking at the server.log should provide more information.

                  • 6. Re: Report for user agents failing to run and doing something I've not seen
                    scottp

                    I managed to recreate a SIMILAR issue on another report.  This was not related to the same fields, so this could be something else.  Here is the server error log entry for that.  Not trying to waste anyones time, this is just now morphing.  I am not too concerned on it since I was able to get past the original issue.  This could be a transient issue.

                     

                     

                    2013-08-27 14:31:41,796 ERROR [securecomputing.smartfilter.reportengine.advanced.output.processing.QueryGener ator] setErrorMessage=Communications link failure

                    The last packet successfully received from the server was 71 milliseconds ago.  The last packet sent successfully to the server was 5,334,350 milliseconds ago.
                    sql: SELECT scr_fct_exact_access.url AS COL1, CASE WHEN scr_dim_action.action_id = 0 OR scr_dim_action.action_id = 6  THEN 1 END
                    FROM scr_fct_exact_access scr_fct_exact_access, scr_dim_ipaddress scr_dim_ipaddress, scr_dim_user scr_dim_user, 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_site scr_dim_site
                    WHERE scr_dim_ipaddress.ip_id = scr_fct_exact_access.user_ip_id AND scr_dim_user.user_id = scr_fct_exact_access.user_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.seconds_since_epoch<1377043200) AND NOT( scr_fct_exact_access.seconds_since_epoch>1377647999) AND ( scr_dim_site.site_name IN ( 'mail.site.com', 'webmail.site.com', 'mobile.site.com' ) ) AND scr_dim_site.site_id = scr_dim_site_request.site_id
                    ORDER BY COL1 ASC
                    com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure

                    The last packet successfully received from the server was 71 milliseconds ago.  The last packet sent successfully to the server was 5,334,350 milliseconds ago.
                    at sun.reflect.GeneratedConstructorAccessor526.newInstance(Unknown Source)
                    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructor AccessorImpl.java:27)
                    at java.lang.reflect.Constructor.newInstance(Constructor.java:513)
                    at com.mysql.jdbc.Util.handleNewInstance(Util.java:409)
                    at com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:1118)
                    at com.mysql.jdbc.MysqlIO.nextRowFast(MysqlIO.java:1653)
                    at com.mysql.jdbc.MysqlIO.nextRow(MysqlIO.java:1409)
                    at com.mysql.jdbc.MysqlIO.readSingleRowSet(MysqlIO.java:2886)
                    at com.mysql.jdbc.MysqlIO.getResultSet(MysqlIO.java:476)
                    at com.mysql.jdbc.MysqlIO.readResultsForQueryOrUpdate(MysqlIO.java:2581)
                    at com.mysql.jdbc.MysqlIO.readAllResults(MysqlIO.java:1757)
                    at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2171)
                    at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2562)
                    at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2512)
                    at com.mysql.jdbc.StatementImpl.executeQuery(StatementImpl.java:1476)
                    at securecomputing.smartfilter.common.core.util.sql.QueryExecutor.call(Unknown Source)
                    at securecomputing.smartfilter.common.core.util.sql.QueryExecutor.call(Unknown Source)
                    at java.util.concurrent.FutureTask$Sync.innerRun(FutureTask.java:303)
                    at java.util.concurrent.FutureTask.run(FutureTask.java:138)
                    at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java: 886)
                    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:908)
                    at java.lang.Thread.run(Thread.java:619)
                    Caused by: java.io.EOFException: Can not read response from server. Expected to read 255 bytes, read 0 bytes before connection was unexpectedly lost.
                    at com.mysql.jdbc.MysqlIO.readFully(MysqlIO.java:2502)
                    at com.mysql.jdbc.MysqlIO.nextRowFast(MysqlIO.java:1634)
                    ... 16 more