When authoring 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 example below illustrates how to achieve this goal, without needing to understand the underlying schema or implement complex queries.
To complete this example, you will have to already have both the Patch Management and SEP Clients cube 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 will only include Report Builder 1.0. If possible, Symantec strongly recommends using SQL Server 2008 SP2 or higher to take advantage of new features included in Report Builder 3.0 for a more robust custom report authoring experience. For more information on using Report Builder, please see the Microsoft website.
- Within the Symantec Management Platform console, navigate to: Settings > Notification Server > IT Analytics, then click on Reports in the left menu tree.
- 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 screen, select Table or Matrix Wizard
- In the next step you will be prompted to choose a dataset. A dataset includes the desired fields and values to populate the report, similar to how the totals and attributes are selected when browsing a cube and creating a pivot table view in the Symantec Management Platform console. If this is the first time Report Builder has been used there will most likely be no dataset to choose from. Make sure the Create a dataset radio button is selected and click Next.
- The next step will prompt you 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 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 contact 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 will be a data source called ITAnalytics. Select this as the data source for the report and click Open.
- Verify that the data source you just browsed to is displayed on the next screen of the wizard.
- Ensure 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:
- Click Next and you will be prompted to design a query, which will make up the data set for the report.
- We will first build the report with patch management information and then add in the SEP data later. Click the
button toward the top of the window and select the Patch Management cube.
- Expand Measures and Patch Vulnerabilities, then drag the Vulnerability Count into the main query window.
- Expand the Computer attribute and drag Computer – Name into the query window, just before Vulnerability Count.
- Expand the Software Update attribute and drag Software Update – Severity into the filter window, just above the main query window.
- Check the Parameter box and under the Filter Expression dropdown, only select Critical and click OK. This will prompt the report to automatically filter by critical severity when executed.
- Click Next to complete the creation of the data set.
- The next step will prompt you 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. When completed, click Next.
- The next step will prompt you to choose the layout of the report. Accept the default settings and click Next.
- The next step will prompt you 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 via 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 do this in the same way you would with Excel, simply click on the line between the columns, and when a grey 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 dataset as “PatchData” which will help to differentiate it from the new data set we will create next to pull in the SEP Data. Also notice the query for the dataset which has been written entirely in the background by going through the wizard.
- We will now create a new dataset to pull in SEP data. Right-click on Datasets in the Report Data pane and select Add Dataset.
- In the Dataset Properties window, name the dataset “SEPData” and select to Use a dataset embedded in my report, then select ITAnalytics in the Data source dropdown.
- Click the Query Designer button and the query designer window will appear. Change the cube to create a query on by clicking the button toward the top of the window and select the SEP Clients cube.
- Expand the Measures group and then the Client folder. Drag Client Count to the main window.
- Expand the Computer attribute and drag the Computer – Computer Name field into the query window.
- Expand the Client attribute and drag the Client – Firewall Status field into the query window, in between Computer – Computer Name and Client Count.
- Expand the Virus Definition attribute 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 datasets listed in the Report Date 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 grey 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 pre-defined functions built into it that can be leveraged to form an expression and extend report functionality. This works much the same way functions work in Excel, where users need to understand the format of specific functions and the arguments expected to be able to use them accordingly. For this example, we will utilize the Lookup function to tie data from the two datasets together. In the Category column, expand Common Functions and click Miscellaneous, then in the Item column that appears select Lookup.
- Notice the description and example provided on the right hand side for the Lookup function. To tie the datasets 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) will be 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 field above:
=Lookup(Fields!Computer___Name.Value,Fields!Computer___Computer_Name.Value,Fields!Client___Firewall_Status.Value, "SEPData")
- Note that “ - “ are replaced by “___” because spaces and dashes are not allowed in the expression. The expression window should now look consistent with the screenshot below. If it does, click OK to close the expression window.
- You should see an abbreviated place holder in that cell within the table, which represents the expression.
- We now need to add the next column for virus definition version. To do so, repeat steps 32 – 34 above and name the column Virus Definition Version.
- For the lookup expression value, we will match on computer name as we did in the previous column, and this time we will pull the Virus Definition – Version field.
=Lookup(Fields!Computer___Name.Value,Fields!Computer___Computer_Name.Value,Fields!Virus_Definition___Version.Value, "SEPData")
- The expression window should now look consistent with the screenshot below. If it does, click OK to close the expression window.
- We are now ready to preview the report and ensure the data has been tied together correctly. To preview the report, click the Run button at the top left.
- You should see firewall status and virus definition version 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.
NOTE: Steps 43 – 48 are optional.
- Depending on your own environment, you may have blank fields for the SEP data as seen in the screenshot above. This is because either those machines do not have SEP installed, or the SEP Clients cube needs to be processed after those were added. For the purposes of this report, we’ll make a slight change to the aesthetics and replace the blanks with “N/A” to make the report easier to read.
- Select the Design button to go back to the Design view.
- Select the middle row of the report where the data displays and select white as the background color for the toolbar above.
- Right-click on the Firewall Status expression cell and edit the expression value to read the following, then click OK. This will replace any blank cell with “N/A”:
=IIF
(
(Lookup(Fields!Computer___Name.Value,Fields!Computer___Computer_Name.Value,Fields!Client___Firewall_Status.Value, "SEPData")) = ""
, "N/A" ,
(Lookup(Fields!Computer___Name.Value,Fields!Computer___Computer_Name.Value,Fields!Client___Firewall_Status.Value, "SEPData"))
)
- In similar fashion, right-click on the Virus Definition Version expression cell and edit the expression value to read the following, then click OK:
=IIF
(
(Lookup(Fields!Computer___Name.Value,Fields!Computer___Computer_Name.Value,Fields!Virus_Definition___Version.Value, "SEPData")) = ""
, "N/A" ,
(Lookup(Fields!Computer___Name.Value,Fields!Computer___Computer_Name.Value,Fields!Virus_Definition___Version.Value, "SEPData"))
)
- Re-run the report and you should see something similar to the following:
- Select the Design button to go back to the Design view and save the report.
-
Click the 
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 dropdown box, select Report, verify the Folder Name is consistent with where you saved it and then in the Report Name dropdown 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.
- Locate and select the report you just added.
Conclusion
IT Analytics provides users several ways to author custom reports, from simple drag-and-drop views to leveraging proven 3rd party applications (like Microsoft Report Builder), all in an effort to deliver flexible and robust reporting. This advanced authoring technique shows how users can tie data together from different cubes and expand reporting capabilities to meet various requirements, without needing to understand the CMDB schema or have advanced knowledge in developing SQL reports. Additionally, this also opens the door to additional reporting possibilities utilizing various out-of-the-box cube combinations to help meet the business’ evolving report needs.