Troubleshooting reporting

This topic provides guidance for diagnosing and resolving issues that you may encounter when configuring and generating reports. Forefront TMG reports are based on log summaries that are derived from the web proxy and firewall logs. Using SQL Server reporting services, Forefront TMG generates log summaries on a daily, weekly or monthly basis.

The following sections provide:

  • Troubleshooting the reporting configuration

  • Troubleshooting report generation

  • Troubleshooting summary reports

Troubleshooting the reporting configuration

The following procedure describes steps you might need to take when you have problems with the reporting configuration. For information on configuring Forefront TMG reports, see Configuring Forefront TMG reports.

To troubleshoot reporting configuration:

  1. The reporting services run on the elected report server. Check which server is configured as the report server:

    1. In the Forefront TMG Management console, in the tree, click the Logs & Reports node.

    2. In the details pane, click the Reporting tab.

    3. On the Tasks tab, click Configure Reporting Settings, and then click the Report Server tab.

    4. Check the Report Server name.

  2. Check for SQL Server reporting services alerts, including the following Forefront TMG alerts:

    • Reporting Services Configuration Failure—An error occurred while configuring SQL Server reporting services for Forefront TMG.

    • Reporting Services - Service Initialization Failure—This Forefront TMG server is defined as the active report server, but the following related service could not be started: service %1. This service is necessary for Forefront TMG reporting services. It is recommended that you review previous events for possible causes, and then start the service manually.

    • Reporting Services - Service Shutdown Failure—This server is no longer the acting report server. However, the following related service could not be stopped: service %1. It is recommended that you stop the service manually because it is no longer required. If the service cannot be stopped, review previous events for possible causes.

  3. Check more broadly for the following types of alerts:

    • Configuration failure alerts caused by an SQL Server or reporting service internal error, or a reporting service configuration attempt when Forefront TMG is reconfigured.

    • Reporting services alerts triggered by services going down (for example, SQL patches, Forefront TMG not started, and so on), time-outs (lack of machine resources), or SQL Server reporting services crashing.

    • Election alerts caused by no elected report server or a mismatch when the configuration is imported, or when services cannot be started or stopped.

  4. Check that all the Forefront TMG services are running, including the following services:

    • SQL Server (MSFW) (MSSQL$MSFW)

    • Microsoft Firewall (fwsrv)

    • Microsoft Forefront TMG Managed Control (IsaManagedCtrl)

    • Microsoft Forefront TMG Job Scheduler (isasched)

    • SQL Server (ISARS) (MSSQL$ISARS)

    • SQL Server Reporting Services (ISARS) (ReportServer$ISARS)

      Note

      The SQL Server services are managed by Server Manager.

  5. If the Microsoft Firewall (fwsrv) service restarts, the following services must be started manually:

    • Microsoft Forefront TMG Managed Control (IsaManagedCtrl)

    • Microsoft Forefront TMG Job Scheduler (isasched)

  6. Verify the intra-array communication:

    1. In the Forefront TMG Management console, in the tree, expand the ServerName of the array, and then click System.

    2. On the Servers tab, select a server, then on the Task tab, click Configure Selected Server.

    3. On the Communication tab, on the Intra-Array Communication dialog box, check the IP address that is used to communicate with other array members.

    4. Ping the specified address.

  7. To verify the elected Forefront TMG firewall, check the logging for blocked traffic (from or to the other firewalls).

  8. Check that the ReportingServicesConfigurationIDs are the same in the Forefront TMG storage and in SQL Server, by running the following two commands:

    • To see ReportingServicesConfigurationId in the Forefront TMG storage, run the following VBScript code:

      Set fpc = CreateObject("FPC.Root")

      set arr = fpc.GetContainingArray

      WScript.Echo "From TMG storage: ReportingServicesConfigurationId = " &

      arr.Reports.ReportingServicesProperties.ReportingServicesConfigurationId

    • To see the ReportingServicesConfigurationId in SQL Server, run the following batch code:

      "%programfiles%\Microsoft SQL Server\100\Tools\Binn\OSQL.EXE" -E -Q "SELECT ConfigurationVersion FROM [ISA_RS_Db].[dbo].[tblVersionConfig]"

  9. If the ReportingServicesConfigurationId in the Forefront TMG storage is different to the one in SQK Server, you must trigger the Microsoft Forefront TMG Job Scheduler (ISASCHED) service to reload the configuration. To trigger the reload, you must change one of the reporting properties. It is recommended that you do this by changing the ReportingServicesConfigurationId. The target value used in this code is an arbitrary GUID, generated from the command line using the uuidgen utility. Run the following VBScript code:

    Set fpc = CreateObject("FPC.Root")

    set arr = fpc.GetContainingArray

    arr.Reports.ReportingServicesProperties.ReportingServicesConfigurationId = "{47233cc9-aea2-4d62-a365-400bec9fe040}"

    arr.Save

    Alternatively, trigger the reporting services reload by running the following SQL Server command:

    SQLCMD –E –S .\ISARS –Q “DROP TABLE ISA_RS_DB..tblversionConfig”

  10. After the configuration has been reloaded, check the alerts again.

  11. Check that the SOAP endpoint is accessible and valid, by typing the following in Microsoft Internet Explorer:

    https://127.0.0.1:8008/Reports\_ISARS/Pages/Folder.aspx

  12. Verify that the timestamps are all the same. If they are not, trigger the configuration reload as described above.

  13. Install Reporting Services Configuration Manager (2008). This is an optional component that is not installed by default.

    1. Run Program Files\Microsoft ISA Server\SQLE\SQLExpress2008SP1.exe.

    2. Click Installation.

    3. Click New SQL Server stand-alone installation or add features to an existing installation.

    4. Click OK. This may take a few moments.

    5. Click Install.

    6. Click Add features to an existing instance of SQL Server 2008.

    7. Click Next.

    8. Select Management Tools - Basic and Business Intelligence Development Studio.

    9. Click Next.

    10. Click Install.

    11. Click Close.

    Tip

    The following error may appear:

    TITLE: SQL Server Setup failure. SQL Server Setup has encountered the following error: Invoke or BeginInvoke cannot be called on a control until the window handle has been created.

    If the error appears, install the Windows Process Activation Service .NET Environment feature using the command line:

    ServerManagerCmd -install WAS-NET-Environment

  14. Use the Reporting Services Configuration Manager (2008) to check the values currently in use; for example, check for reporting server name consistency.

  15. Check the Windows Event Viewer for SQL Server and reporting services errors.

  16. All severe errors appear in the Windows Event Viewer. If you have the required experience, you can check for more details in the following service logs:

    • Reporting Services—Program Files\Microsoft SQL Server\MSRS10.ISARS\Reporting Services\LogFiles

    • SQL Server—Program Files\Microsoft SQL Server\MSSQL10.ISARS\MSSQL\LOG

Troubleshooting report generation

The following procedure describes the steps you might need to take if you have problems with generating reports.

To troubleshoot report generation:

  1. Check that logging is enabled for the Firewall Logging and Web Proxy Logging services:

    1. In the Forefront TMG Management console, in the tree, click the Logs & Reports node.

    2. In the details pane, click the Logging tab.

    3. On the Tasks tab, select the appropriate task:

      • Select Configure Firewall Logging to configure the location of the Firewall log.

      • Select Configure Web Proxy Logging to configure the location of the Web Proxy log.

    4. On the Log tab, select the Enable logging for this service check box.

    5. To disable logging, clear the Enable logging for this service check box.

  2. Logging can also be disabled within a rule, impacting reporting accuracy. Check the relevant rules:

    1. In the Forefront TMG Management console, in the tree, click the Web Access Policy or Firewall Policy node.

    2. In the details pane, select a rule.

    3. On the Tasks tab, click Edit Selected Rule.

    4. On the Action tab, select Log requests matching this rule check box.

  3. If specific report categories are not generated, check that the report categories are specified as report content.

    1. In the Forefront TMG Management console, in the tree, click the Logs & Reports node.

    2. In the details pane, click the Reporting tab.

    3. In the Details pane, select the required report.

    4. On the Tasks tab, click Edit Selected.

    5. On the Content tab, check the report categories.

  4. Check the report alerts for the following problems:

    • Configuration errors.

    • Time-out issues.

    • Published reports directory issues, in particular, that you have the necessary permissions.

    • Email issues.

  5. Check that all the Forefront TMG services are running, including the following services:

    • SQL Server (MSFW) (MSSQL$MSFW)

      Note

      Logging does not run if this service is down.

    • Microsoft Firewall (fwsrv)

    • Microsoft Forefront TMG Managed Control (IsaManagedCtrl)

    • Microsoft Forefront TMG Job Scheduler (isasched)

      Note

      Scheduled reports do not run if this service is down.

    • SQL Server (ISARS) (MSSQL$ISARS)

    • SQL Server Reporting Services (ISARS) (ReportServer$ISARS)

      Note

      The SQL Server services are managed by Server Manager.

  6. The reporting services run on the elected report server. Check which server is configured as the report server:

    1. In the Forefront TMG Management console, in the tree, click the Logs & Reports node.

    2. In the details pane, click the Reporting tab.

    3. On the Tasks tab, click Configure Reporting Settings, and then click the Report Server tab.

    4. Check the Report Server name.

  7. Verify that the directory you are publishing exists to which and that it is accessible with publishing credentials. When you publish a report, the Forefront TMG computer requires write permissions to the folder in which you publish the report. By default, the Local System account is used to publish the report. However, if you publish the report to a folder that resides on a different computer, the Local System account credentials are passed as the account of the Forefront TMG-based computer. For this reason, the account of the Forefront TMG-based computer must have sufficient permissions to write to the destination folder. If you specify a user account to publish the report, you must make sure that this user account is granted write permissions to the destination folder.

    If Forefront TMG is installed in workgroup mode, Forefront TMG uses the Unauthenticated account. In this case, it is recommended that you specify user credentials when publishing reports to another computer.

  8. Verify that the email settings are correct.

  9. Not every failure results in an alert and it is therefore recommended to check whether the SMTP server is available.

  10. Check the SQL Server connectivity by running:

    1. SQLCMD –E –S Tcp:.\ISARS,1433

    2. SQLCMD –E –S Tcp:.\MSFW

  11. Check the configuration status:

    1. Check for a Configuration error alert.

    2. In the Monitoring node, click the Configuration tab, and verify the configuration status of the servers listed under Configuration Status.

  12. Verify the intra-array communication:

    1. In the Forefront TMG Management console, in the tree, expand the ServerName of the array, and then click System.

    2. On the Servers tab, select a server, then on the Task tab, click Configure Selected Server.

    3. On the Communication tab, on the Intra-Array Communication dialog box, check the IP address that is used to communicate with other array members.

    4. Ping the specified address.

  13. Check the system policy rules, and verify that there are no rules that block access to the elected report server.

  14. Check that the SOAP endpoint is accessible and valid by typing:

    https://127.0.0.1:8008/Reports\_ISARS/Pages/Folder.aspx

Troubleshooting summary reports

The following procedure describes steps you might need to take if you have problems with generating summary reports.

To troubleshoot summary reports:

  1. Ensure that you have run all the checks described in Troubleshooting report generation.

  2. Verify that summary reports are enabled:

    1. In the Forefront TMG Management console, in the tree, click the Logs & Reports node.

    2. In the details pane, click the Reporting tab.

    3. On the Tasks tab, click Configure Reporting Settings.

    4. On the Log Summary tab, check that Enable daily and monthly summaries is selected.

  3. Review the header at the top of the report that indicates which servers have data for the report, and which servers have missing data for the report. If there are servers with missing data, check the alerts to see if they identify the problem.

  4. Check the alerts for summary generation problems:

    • Failures for date X on TMG Firewall Y.

    • Daily, monthly or trimming (aggregation) failures.

    • SQL Server errors corresponding to these report alerts.

  5. Log summaries are generated at night (by default at 12:30am); however this time is configurable. If the SQL Server (MSFW) (MSSQL$MSFW) service is down and is restarted close to the end of the day, the data may not be stored by the time the summary report runs (at 12:30) and the data will be missing from the report. Consider changing the time at which the log summary is generated to later in the day.

    1. In the Forefront TMG Management console, in the tree, click the Logs & Reports node.

    2. In the details pane, click the Reporting tab.

    3. On the Tasks tab, click Configure Reporting Settings.

    4. On the Log Summary tab, change the Generation time.

  6. Add the following extended error info flag to the reporting registry entry:

    HKLM\Software\Microsoft\rat\stingray\debug\REPORTING /v ALERT_EXTENDED_ERROR_INFORMATION /t REG_DWORD /d 1

    Note

    The change takes effect immediately.

  7. Verify which summaries succeed by running the following SQL Server command:

    SQLCMD –E –S .\ISARS –Q “SELECT * FROM isa_rs_db..tblserverparticipationSummary_Daily ORDER by [fromdate] asc”

  8. Check the summary generation the following day.