Error loading the Virus Alert Details report in SQL Server Reporting Services

Products

• IT Analytics

Issue/Introduction

The SEP Virus Alerts Detail report in the IT Analytics console loads properly in a reasonable amount of time; however, the same report when accessed via the SQL Server Reporting Services (SSRS) web service URL returns no data and eventually fails with the following error:

Sys.WebForms.PageRequestManagerServerErrorException: An unknown error occurred while processing the request on the server. The status code returned from the server was: 500

Environment

Release : 2.9.0, 2.9.1

Component : Reports

Cause

The underlying query and parameters for this report in SSRS returns all virus names including those with no associated alerts. Given the size of the unfiltered dataset, this can trigger a timeout on the server.

Resolution

To enable the Virus Alert Details report to return results in SSRS, the report's query must be modified to return results for only those viruses that have an associated alert.

To modify the query, follow this procedure:

1. Open the SSRS web service URL
2. Navigate to the folder Symantec Endpoint Protection
3. Select Virus Alert Details > Edit in Report Builder
4. In Report Builder, right-click on Datasets and select Show Hidden Datasets
5. Right-click on dVirusName and select Dataset Properties
6. Select ƒx
7. Replace the query with the following:

   WITH
   MEMBER [Measures].[ParameterCaption] AS 'IIF([Virus].[Virus - Name].CURRENTMEMBER.MEMBER_CAPTION=null, "(Blank)", [Virus].[Virus - Name].CURRENTMEMBER.MEMBER_CAPTION)'
   MEMBER [Measures].[ParameterValue] AS '[Virus].[Virus - Name].CURRENTMEMBER.UNIQUENAME' MEMBER [Measures].[ParameterLevel] AS '[Virus].[Virus - Name].CURRENTMEMBER.LEVEL.ORDINAL'
   SELECT {[Measures].[ParameterCaption], [Measures].[ParameterValue], [Measures].[ParameterLevel]} ON COLUMNS , [Virus].[Virus - Name].ALLMEMBERS
   having [measures].[alerts] >0
   ON ROWS FROM [SEP Alerts]

8. Close the Report Builder to save this change
9. Refresh SSRS and select the Virus Alert Details report to confirm it loads correctly