This section will dive into advanced report creation in IT Analytics Symantec Endpoint Protection Content Pack using Microsoft Report Builder to create and publish a SQL Server Reporting Services report.
Report Builder is a component of SQL Server Reporting Services that allows ad-hoc reporting functionality, enabling end users to build their own reports and charts. Users can then publish these reports into Reporting Services where they can be accessed, viewed and incorporated back into IT Analytics alongside existing reporting.
NOTE: Although the output produced by Report Builder is integrated with IT Analytics, the tools and subsequent query language behind it are separate Microsoft entities and are thereby outside the default capabilities of the IT Analytics product itself.
In this example we will create a custom dashboard that displays the number of alerts by virus threat type.
In this example we will add an additional report to the dashboard created in above.
In this example we will add a date range selector to the dashboard created above.
WITH MEMBER [Measures].[Date] AS 'CDate(Format(DateAdd(‘d’, -90, Now()), ‘yyyy-MM-dd 00:00:00’))' SELECT [Measures].[Date] ON COLUMNS FROM [SEP Alerts]
WITH
MEMBER [Measures].[Date] AS 'CDate(Format(DateAdd(‘d’, -90, Now()), ‘yyyy-MM-dd 00:00:00’))'
SELECT [Measures].[Date] ON COLUMNS
FROM [SEP Alerts]
WITH MEMBER [Measures].[Date] AS 'CDate(Format(Now(), ‘yyyy-MM-dd 00:00:00’))' SELECT [Measures].[Date] ON COLUMNS FROM [SEP Alerts]
MEMBER [Measures].[Date] AS 'CDate(Format(Now(), ‘yyyy-MM-dd 00:00:00’))'
SELECT NON EMPTY { [Measures].[Alerts] } ON COLUMNS, NON EMPTY { ([Virus].[Virus - Threat Type].[Virus - Threat Type].ALLMEMBERS ) } ON ROWS FROM ( SELECT ( STRTOMEMBER(‘[Alert Date].[Alert Date - Date].&[‘ + Format(CDATE(@pFrom), ‘yyyy-MM-dd’) + ‘]’) : STRTOMEMBER(‘[Alert Date].[Alert Date - Date].&[‘ + Format(CDATE(@pTo), ‘yyyy-MM-dd’) + ‘]’) ) ON COLUMNS FROM [SEP Alerts])
SELECT NON EMPTY
{
[Measures].[Alerts]
} ON COLUMNS,
NON EMPTY
([Virus].[Virus - Threat Type].[Virus - Threat Type].ALLMEMBERS )
} ON ROWS
FROM ( SELECT ( STRTOMEMBER(‘[Alert Date].[Alert Date - Date].&[‘ + Format(CDATE(@pFrom), ‘yyyy-MM-dd’) + ‘]’) : STRTOMEMBER(‘[Alert Date].[Alert Date - Date].&[‘ + Format(CDATE(@pTo), ‘yyyy-MM-dd’) + ‘]’) ) ON COLUMNS
FROM [SEP Alerts])
SELECT NON EMPTY { [Measures].[Computers] } ON COLUMNS, NON EMPTY { ([Alert].[Alert - Source].[Alert - Source].ALLMEMBERS ) } ON ROWS FROM ( SELECT ( STRTOMEMBER(‘[Alert Date].[Alert Date - Date].&[‘ + Format(CDATE(@pFrom), ‘yyyy-MM-dd’) + ‘]’) : STRTOMEMBER(‘[Alert Date].[Alert Date - Date].&[‘ + Format(CDATE(@pTo), ‘yyyy-MM-dd’) + ‘]’) ) ON COLUMNS FROM [SEP Alerts])
[Measures].[Computers]
([Alert].[Alert - Source].[Alert - Source].ALLMEMBERS )