Combining Data from Two Cubes in IT Analytics 7.1
When authoring reports in IT Analytics, it may be beneficial to combine data from two different cubes to achieve the desired result set. For example, an administrator may want to combine Patch Management with Asset data to easily identify the location of machines that require critical updates within the same report. Because IT Analytics is built on standard, proven technologies the process to combine data from two cubes 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 assigned assets to locations and you should at least 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 asset location information later. Click the View Inline Image button toward the top of the window and select the Patch Management cube.
- Expand Measures and Patch Vulnerabilities, then drag the Vulnerable Computer Count into the main query window.
- Expand the Computer attribute and drag Computer – Name into the query window, just before Vulnerable Computer Count.
- Expand the Software Update attribute and drag Software Update – Severity into the query window, just before Computer - Name.
- 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 Vulnerable_Computer_Count to the Values window and drag Computer__Name and Software_Update__Severity 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 Computer Patch Severity and Location.
- 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 Asset 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 Asset data specific to the computer’s location. Right-click on Datasets in the Report Data pane and select Add Dataset.
- In the Dataset Properties window, name the dataset “AssetData” 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. If the Assets cube is not already selected in the upper left, click the View Inline Image button toward the top of the window and select the Assets cube.
- Expand the Measures group and then the Assets folder. Drag Asset Count to the main window.
- Expand the Asset attribute and drag the Asset – Name field into the query window.
- Expand the Location attribute and drag the Location – Name field into the query window, in between Asset – Name and Asset 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 an additional column in our table to display the location data 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 > Right.
- Click into the new column header and type “Location” 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 “Asset – Name” (from the Asset 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!Asset___Name.Value,Fields!Location___Name.Value, "AssetData")
- Note that “ - “ is 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 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.
- Expand the patch severity levels and verify that the computer names and their associated locations display correctly. If you receive values of “Unknown” for location, verify that you have actually associated a location to that specific asset in the Symantec Management Platform console. Also verify that the Assets cube has been processed in the IT Analytics settings.
- Select the Design button to go back to the Design view.
- Click the View Inline Image icon in the Report Builder toolbar to save this report to Reporting Services IT Analytics folder and name it “Computer Patch Severity and Location”.
- 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 Computers Patch Severity and Location 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. 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.
Comments
Dear Sir, Thanks for the
Dear Sir,
Thanks for the guidelines. I am currently facing an issue with IT Analytics 7.1 AMS report pack where the Software License Compliance Report by Location shows NULL output against Total Installed Count and Metered Usage columns. Can you please advice solution for this issue as we have virtually tried all the possible ways and queries. It seems that CMDB has those data but there is no way to display it.
On the contrary, if we try to generate this report from default Software Compliance Report, then it shows the Install count. The most common customer requirement is to get the license compliance report by Location/OPCO and this is what we are unfortunately not getting. Instead we are getting global view where the license count is added/summed together for each Location/OPCO to show as overall compliance report.
Pls advice
Regards
Bhagyesh
Bhagyesh, Appreciate your
Bhagyesh,
Appreciate your comments on this. Without seeing the exact reports and understanding all the columns, etc. it is somewhat difficult to diagnose this accurately. You may want to log this issue with support first to help determine the root cause of any potential issues.
That said, if you have found software compliance data you need in the Symantec CMDB but need location data from Asset, you can map up IT Analytics cube data with data in a table from a relational database. All you need is a unique identifier between the Asset cube and the table you're using to bring that data together to display in one report. It would be very similar to this example in Connect, just replace the Patch cube with the Assets cube and the custom inventory table with your software compliance table in the CMDB.
Hope that helps!
Darren
Darren, Many thanks for your
Darren,
Many thanks for your time and reply.
Here are my concerns cum issues.
( Important to mention that we are in a critical position where customer has purchased AMS for soft. lic. compliance purpose. This customer has 4 subsidaries under its IT Dept. and hence getting Soft. Lic. Compl. report by location or subsidary wise is the must have need from their Management.)
Now coming to the technicals..........
1] We have mapped Computers to Locations, Software Licenses to Software Purchase and all the pre-requisite configs for AMS and CMS
2] Out-of-Box report templates of Altiris CMS & AMS does not have any Soft. Compl. report that gives us option to select filter or location for compliance report. It rather gives a global or top view which is of no use as it gives combine license compliance report for all subsidaries. (We have central SMP managing 9000 nodes and there is no hierarchy or no child servers)
3] In IT Analytics > Reports, we found that you guys have given a report template named as "Software License Compliance by Location" This is what we actually want. But unfortunately, when we run this report, it displays only Purchased License Count and Compliance Count. Unfortunately the columns "Total Installed Count" & "Metered Usage" returns NULL value.
4] We reported this to Symantec Technical Support and they have then verified the same and released new KB article stating that it is the issue reported by us and tagged as KNOWN ISSUE. Pls see the link here... http://www.symantec.com/business/support/index?page=content&id=TECH197245&profileURL=https%3A%2F%2Fsymaccount-profile.symantec.com%2FSSO%2Findex.jsp%3FssoID%3D1353512530830P8cjBt3m4a0I7Sxf49sIZz904Cv0J95Dc98lX
5] Now if this is the product issue or limitation or product enhancement, then my question is we are already getting installed count and metered usage from IT Analytics`s "Application Metering", "Installed Software" cubes which belongs to CMS report pack. Therefore I do not think that getting our expected report needs product customization. I beleive that data is still there in CMDB by location/OPCO wise but we need the exact query/expression to get the desired result/report.
Tomorrow I will attach the screenshots showing what is the current status and what we want.
Till that time, your comments on my above points are highly obliged.
Thanks
Bhagyesh
Would you like to reply?
Login or Register to post your comment.