Video Screencast Help
Protect Your POS Environment Against Retail Data Breaches. Learn More.

Integrating Custom Inventory Data in IT Analytics 7.1

Created: 17 Sep 2012 • Updated: 22 Nov 2013
Language Translations
dprager's picture
+18 18 Votes
Login to vote

When authoring reports in IT Analytics, it may be beneficial to combine data from custom inventory previously configured to achieve the desired result set. For example, an administrator may want to combine hardware inventory data with registry settings acquired from custom inventory, all within the same report. Because IT Analytics is built on standard, proven technologies the process to combine data from IT Analytics and the Symantec CMDB 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 need the Computers Cube from the Client and Server Management Content Pack installed and processed, previously have run a custom inventory script that populates data in the Symantec CMDB and includes GUID information, 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.

  1. Within the Symantec Management Platform console, navigate to: Settings > Notification Server > IT Analytics, then click on Reports in the left menu tree.
  2. Click the Report Builder tab and then the Launch Report Builder button. 

  1. Allow a few minutes for the application to load. 

  1. From the Getting Started screen, select Table or Matrix Wizard

  1. 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.

  1. 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.

  1. 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.

  1. Verify that the data source you just browsed to is displayed on the next screen of the wizard.

  1. 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:

  1. Click Next and you will be prompted to design a query, which will make up the data set for the report. 

  1. We will first build the report with information from the Computers Cube and then add in the custom inventory data later. Click the  button toward the top of the window and select the Computers cube.

  1. Expand Measures and Computers, then drag the Computer Count into the main query window.

  1. Expand the Computer attribute and drag Computer – Name into the query window, just before Computer Count.

  1. Now drag Computer - Manufacturer into the query window, just before Computer - Name.

  1. Drag Computer – Model into the query window, in between Computer – Manufacturer and Computer - Name.

  1. Click Next to complete the creation of the data set.
  2. The next step will prompt you to arrange the fields to display properly in the table. Drag Computer_Count to the Values window and drag Computer__Name, Computer__Manufacturer and Computer__Model to the Row Groups window. When completed, click Next.

  1. The next step will prompt you to choose the layout of the report. Accept the default settings and click Next.

  1. The next step will prompt you to select a style for the report. Choose a color scheme you prefer and click Finish.

  1. 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 Custom Inventory Example, or a title relevant to your environment.

  1. Resize the font of the title and the cells, so that they fit 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.

  1. 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.

  1. Rename the dataset as “ComputerData” which will help to differentiate it from the new data set we will create next to pull in the custom inventory. Also notice the query for the dataset which has been written entirely in the background by going through the wizard.

  1. We will now create a new data source to pull in custom inventory data from the Symantec CMDB. Right-click on Data Source in the Report Data pane and select Add Data Source.

  1. In the Data Source Properties window, name the data source “CMDB” and select to Use a shared connection or report model, then click the Browse button.

  1. 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 CMDB. Select this as the data source for the report and click Open.

  1. Verify that the data source you just browsed to is displayed on the data source properties window.

  1. 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:

  1. Click OK and you should see this new data source listed in the Report Data pane on the left.

  1. We will now create a new dataset to pull in custom inventory data. Right-click on Datasets in the Report Data pane and select Add Dataset.

  1. In the Dataset Properties window, name the dataset “CustomInv” and select to Use a dataset embedded in my report, then select CMDB in the Data source dropdown.

  1. Click the Query Designer button and the query designer window will appear. This query designer window will look different than the one you worked with from the previous data set. This is because here we are creating a standard SQL query from the relational Symantec CMDB database, whereas before we created a data source off of the IT Analytics cubes themselves. While a bit different to navigate, it is important to note that we still do not need to know any SQL to create this query, we just need to know where the specific data resides.
  2. Expand the Tables folder and navigate to the table where your custom inventory resides. In our example, we created a custom inventory to pull specific details on how a machine was imaged (and named it Inv_Image_Details) but you can use any custom inventory here as long as it has specific Resource Guid information per computer. Check all the fields from the table you want to display in the final report and click Run Query. For the purposes of this example we only executed custom inventory on one computer so that is what displays in the result set below. 

  1. In the Database View pane on the left, scroll down and expand the Views folder, then open the vComputer view and check both Guid and Name fields.

  1. In the Relationships section in the middle of the Query Designer window, select the Add Relationship button. This is where we will pull in Computer Name to align with our custom inventory data. We will need this information later to link it with the IT Analytics cube data.

  1. Click under the Left Table header and select the vComputer view.

  1. Click under the Right Table header and select your table that houses your custom inventory.

  1. Double-click under the Join Fields header and you will be prompted to choose specific fields from each table/view that can be used as a common identifier. In our case we will use the Resource Guids to match on, however depending on the way your custom inventory is setup you can use other unique identifiers. Click under each Join Field and select the fields to match on, then click OK.

  1. Click Run Query to test the join and if the relationship is valid, you should see additional columns in the query result set. If the join produces an error, go back and edit the relationship fields to correct the join. When the query result displays as expected, click OK to close the Query Designer window.

  1. Notice that the underlying SQL query has been written for you in the Dataset Properties window. 

  1. Click OK to complete creation of the new data set.

  1. We now need to create three additional columns in our table to display the additional image details from our custom inventory. 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.

  1. Click into the new column header and type a name specific to your custom inventory data you want to display. For our example, we will use “Image Version.” Then right-click on the data cell below and select Expression

  1. 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.

  1. 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 Computers cube) and “Name” (from the custom inventory data set) will be used to signify the 1-to-1 relationship. Once that is established we can then add in the custom inventory data 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!Name.Value,Fields!Image_Version.Value, "CustomInv")

  1. 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.

  1. You should see an abbreviated place holder in that cell within the table, which represents the expression.

  1. We can add additional columns, based on the remaining data from custom inventory that we wish to display. To do so, repeat steps 42 – 46 above, being sure to name the columns and set the value of the lookup expression appropriately. In our example, we added two additional columns to display other fields from custom inventory and populated the values for each expression as follows:

=Lookup(Fields!Computer___Name.Value, Fields!Name.Value, Fields!Deployment_Date.Value, "CustomInv")

=Lookup(Fields!Computer___Name.Value, Fields!Name.Value, Fields!Deployment_Technician.Value, "CustomInv")

  1. After adding the additional columns and expression values, your table should look like something similar to this:

  1. 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.

  1. Depending on what custom inventory you have added to your report, you should see data from both data sets display together.

  1. Select the Design button to go back to the Design view and save the report.

  1. Click the  icon in the Report Builder toolbar to save this report to Reporting Services IT Analytics folder and name it appropriately for your environment. For our example, we simply named the report “Custom Inventory Example”.

  1. To link this report into the Symantec Management Platform console open the console then navigate to the Reports > IT Analytics > Reports folder.
  2. Right-click on the Reports folder and select New > IT Analytics Report.

  1. 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 report under the name you just saved. Then click the Add Report button.

  1. You should see a message saying that the report was added successfully.
  2. Refresh your browser and expand the Reports folder.
  3. 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 sources and expand reporting capabilities to meet various requirements, without needing to have advanced knowledge in developing SQL reports. Additionally, this also opens the door to additional reporting possibilities utilizing various out-of-the-box cube and CMDB combinations to help meet the business’ evolving report needs.