Home Print Show Topic URL Previous Next
IT Management Suite
Client Management Suite
Server Management Suite
Deployment Solution
Asset Management Suite

Integrating custom inventory data in IT Analytics

IT Analytics

When you author 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. IT Analytics is built on standard, proven technologies. Therefore, 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 following example illustrates how to achieve this goal, without needing to understand the underlying schema or implement complex queries.

To complete this example, first, you must have the Computers Cube from the Client and Server Management content pack installed and processed. Next, you must previously have run a custom inventory script that populates data in the Symantec CMDB and includes GUID information. Finally you must 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. 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. These features let you author a more robust custom report. For more information on using Report Builder, please see the Microsoft website.

To create a data set for computer data

  1. In the Symantec Management Console, on the Settings menu, click Notification Server > IT Analytics Settings.

  2. In the left pane, click Reports.

  3. Click the Report Builder tab and then the Launch Report Builder button.

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

  5. From the Getting Started window, select Table or Matrix Wizard.

  6. 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. This process is 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.

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

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

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

  10. 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"

  11. Click Next. You are prompted to design a query to make up the data set for the report.

  12. First we build the report with information from the Computers cube and then add in the custom inventory 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 Computers cube and click OK.

  13. Expand Measures > Computers, then drag the Computer Count into the main query window.

  14. Expand Computer and drag Computer - Name into the query window, before Computer Count.

  15. Drag Computer - Manufacturer into the query window, before Computer Name.

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

  17. Click Next to complete the creation of the data set.

  18. In the Arrange fields panel, you are prompted to arrange the fields to display properly in the table. Drag Computer_Count to the Values window and drag Computer__Name, Computer - Manufacturer, Computer - Model to the Row Groups window. Click Next.

  19. In the Choose the layout panel, you are prompted to choose the layout of the report. Accept the default settings and click Next.

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

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

  22. 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. Then when a gray bar appears at the top of the table, expand by dragging the columns.

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

  24. Rename the data set as "ComputerData" which helps to differentiate it from the new data that we create in the next procedure to pull in the custom inventory. Also notice the query for the data set which has been written entirely in the background by going through the wizard.

  25. Next we create the data set for custom inventory data.

To create data set for custom inventory data

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

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

  3. Navigate to the ReportServer/IT Analytics folder on the server that houses SQL Reporting Services. Within that folder there is a data source called CMDB. Select CMDB as the data source for the report and click Open.

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

  5. 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"

  6. Click OK.

    The new data source should be listed in the Report Data pane on the left.

  7. Now we create a new data set to pull in custom inventory data. In the Report Data pane, right-click on Datasets and select Add Dataset.

  8. In the Dataset Properties window, name the data set "Custominv" and select to Use a dataset embedded in my report, then select CMDB in the Data source drop-down.

  9. Click the Query Designer button.

    The query designer window that opens looks different than the one you worked with from the previous data set because here we create a standard SQL query from the relational Symantec CMDB database, whereas before we created a data source from 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 need to know where the specific data resides.

  10. 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 computer 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. The screen shot reflects the data for the same. CustInvn.png

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

  12. In the Relationships section in the middle of the Query Designer window, select the Add Relationship button. Here pull in Computer Name to align with our custom inventory data. We require this information later to link it with the IT Analytics cube data.

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

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

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

  16. In Query Designer, 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.

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

  18. Click OK to close the Dataset Properties window.

  19. 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 gray column header that appears when you click into the Computer Name field, then select: Insert Column > Right.

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

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

  22. 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 Computer cube) and Name (from the custom inventory data set) are 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 Set expression for: Value field :

    Fields!Image_Version.Value, "CustomInv")
  23. Note that you replace " - " with "___" because spaces and dashes are not allowed in the expression. Click OK to close the expression window.

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

  25. We can add additional columns, based on the remaining data from the custom inventory that we want to display.

    To do so, repeat steps 19 - 23, 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")
  26. Once you are done adding the additional columns and expression values, your table should look like something similar to the following screen shot:

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

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

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

  30. Click the save 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 named the report "Custom Inventory Example".

  31. To link this report into the Symantec Management Platform console open the console then navigate to the Reports > IT Analytics > Reports folder.

  32. Right-click on the Reports folder and select New > IT Analytics Report.

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

  34. You should see a message saying that the report was added successfully.

  35. Refresh your browser and expand the Reports folder.

  36. Locate and select the report you added.