Integrating cube data from different content packs in IT Analytics
When you author reports in IT Analytics, it may be beneficial to combine data from two different content packs to achieve the desired result set. For example, an administrator may want to combine Patch Management data from the Altiris Client and Server Management content pack with the Symantec Endpoint Protection content pack to view critical severities and virus definition versions, all within the same report. Because IT Analytics is built on standard, proven technologies the process to combine data from two cubes in separate content packs into a single report is achievable with a consistent pattern through leveraging Microsoft Report Builder. The following example illustrates how to achieve this goal, without needing to understand the underlying schema or implement complex queries.
To complete this example, both the Patch Management and SEP Clients cube must be installed and processed, and have Report Builder 3.0 installed. Note that while SQL Server 2005 meets the minimum prerequisites for installation of IT Analytics, it only includes Report Builder 1.0. If possible, Symantec recommends that you use SQL Server 2008 SP2, 2008 R2 or 2012 to take advantage of the new features that are included in Report Builder 3.0 to author a more robust custom report. For more information on using Report Builder, please see the Microsoft website.
Creating a data set for Patch data
In the Symantec Management Console, on the Settings menu, click Notification Server > IT Analytics Settings.
In the left pane, click Reports.
Click the Report Builder tab and then the Launch Report Builder button.
Allow a few minutes for the application to load.
From the Getting Started window, select Table or Matrix Wizard.
In the Choose a dataset panel, you are prompted to choose a data set.
A data set includes the desired fields and values to populate the report, similar to how the totals and attributes are selected when you browse a cube and create a pivot table view in the Symantec Management Platform console. If it is the first-time Report Builder is used then most likely there is no data set to choose from. Make sure that the Create a dataset radio button is selected and click Next.
In the Choose a connection to a data source panel, you are prompted to choose a connection to a data source.
A data source is the repository where the data for the report is stored. In the case of IT Analytics, the data is stored in the Microsoft Analysis Services Database that is specified when IT Analytics was installed. If you do not know the Analysis Services Database name, the server where it resides, or have the credentials necessary to connect to it please contacts your Altiris Administrator.
To create a new data source, click the Browse button and navigate to the ReportServer/IT Analytics folder on the server that houses SQL Reporting Services. Within that folder there is a data source called ITAnalytics. Select ITAnalytics as the data source for the report and click Open.
Verify that the data source you browsed to is displayed on the next screen of the wizard.
Ensure that the connection to the data source is valid by clicking the Test Connection button in the lower right of the wizard. Assuming the test succeeded, you should see the following message:
"Connection created Successfully"
Click Next. You are prompted to design a query to make up the data set for the report.
First we build the report with patch management information and then add in the SEP data later. In the Design a query panel, click the browse ...button toward the top of the panel. In the Cube Selection window, select the Patch Management cube and click OK.
Expand Measures > Patch Vulnerabilities, then drag the Vulnerability Count into the main query window.
Expand Computer and drag Computer - Name into the query window, before Vulnerability Count.
Expand Software Update and drag Software Update - Severity into the filter window that is above the main query window.
Check the Parameter box and under the Filter Expression drop-down, only select Critical and click OK. Selecting Critical prompts the report to automatically filter by critical severity when executed.
Click Next to complete the creation of the data set.
In the Arrange fields panel, you are prompted to arrange the fields to display properly in the table. Drag Vulnerability_Count to the Values window and drag Computer__Name to the Row Groups window. Click Next.
In the Choose the layout panel, you are prompted to choose the layout of the report. Accept the default settings and click Next.
In the Choose a style panel, you are prompted to select a style for the report. Choose a color scheme you prefer and click Finish.
You should see a sample table on the report canvas. The data source and data set that display on the left navigation have already been created for you by the wizard. Rename the title of the report to SEP Data for Computers with Critical Vulnerabilities.
Resize the font of the title so that it fits within the given area. Also, widen the columns of the table so that you can read the column headers. You can format in the same way you would with Excel, click on the line between the columns, and when a gray bar appears at the top of the table, then expand by dragging the columns.
Right-click on DataSet1 which was created automatically by the wizard (this displays in the Report Data pane on the far left) and then click on Dataset Properties.
Rename the data set as "PatchData" which helps to differentiate it from the new data that we create in the next procedure to pull in the SEP Data. Also notice the query for the data set which has been written entirely in the background by going through the wizard.
Creating a data set for SEP data
Now we create a new data set to pull in SEP data. In the Report Data pane, right-click on Datasets and select Add Dataset.
In the Dataset Properties window, name the data set "SEPData" and select to Use a dataset embedded in my report, then select ITAnalytics in the Data source drop-down.
Click the Query Designer button.
In the Design a query window, click the browse ...button toward the top of the window.
In the Cube Selection window, select the SEP Clients cube and click OK.
Expand Measures > Client and drag Client Count into the query window.
Expand Computer and drag the Computer - Computer Name field into the query window.
Expand Client and drag the Client - Firewall Status field into the query window, in between Computer - Computer Name and Client Count.
Expand Virus Definition and drag the Virus Definition - Version field into the query window, in between Client - Firewall Status and Client Count.
Click OK to close the Query Designer window and click OK again to close the Dataset Properties window. You should see both data sets listed in the Report Data pane.
We now need to create two additional columns in our table to display the firewall status and virus definition version for each computer. To add a column, right-click the gray column header that appears when you click into the Computer Name field, then select: Insert Column > Inside Group - Right.
Click into the new column header and type "Firewall Status" then right-click on the data cell below and select Expression.
Report Builder has several predefined functions that are built into it that can be leveraged to form an expression and extend report functionality. These functions work much the same way functions work in Excel, where users need to understand the format of specific functions and the arguments that are expected to be able to use them accordingly. For this example, use the Lookup function to tie data from the two data sets together.
In the Category column, expand Common Functions and click Miscellaneous, then in the Item column that appears select Lookup.
Notice the description and the example that is provided on the right-hand side for the Lookup function. To tie the data sets together we need a common identifier that resides in both sets.
In this example, Computer - Name (from the Patch Management cube) and Computer - Computer Name (from the SEP Clients cube) are used to signify the 1-to-1 relationship. Once that is established we can then add in the Location Name for each computer to display in the report. To set the expression value for the Lookup function, type the following into the Set expression for: Value field :
We are now ready to preview the report and ensure that the data has been tied together correctly. To preview the report, click the Run button at the top left.
You should see the firewall status and the virus definition version that is populated from the SEP Clients cube, alongside computer name, vulnerability count and software update severity (as a filter) from the Patch Management cube. Note that you can still change the filter to show other severities and click the View Report button to see it render accordingly.
Select the Design button to go back to the Design view and save the report.
Click the save icon in the Report Builder toolbar to save this report to Reporting Services IT Analytics folder and name it "SEP Data for Computers with Critical Vulnerabilities".
To link this report into the Symantec Management Platform console open the console then navigate to the Reports > IT Analytics > Reports > Patch Management folder.
Right-click on the Reports folder and select New > IT Analytics Report.
In the Report Type drop-down box, select Report, verify that the Folder Name is consistent with where you saved it and then in the Report Name drop-down select the SEP Data for Computers with Critical Vulnerabilities report. Then click the Add Report button.
You should see a message saying that the report was added successfully.
Refresh your browser and expand the Reports folder.