How to Create a Custom Report or Filter for Computers with Specific Software

Products

• Inventory Solution
• Software Management Solution
• IT Management Suite

Issue/Introduction

Introduction

Sometimes the Installed Software and Installed Software by Computer reports or Installed Software filters cannot be fine tuned enough to show which computers have certain software applications installed. In this case it is necessary to create an SQL query that gets the desired data from the database and then create a custom report or filter with the query. Please note that Symantec Support does not support custom reports so adaptations must be made by the user.

Environment

ITMS 8.x

Resolution

Building a SQL Query

To create the SQL query open SQL Management Studio on the SQL Server with the Altiris Database. Select the Altiris database (usually called Symantec_CMDB) in the Object Explorer pane, then click New Query. Paste the following query which lists every software component that is installed on every computer from the Inv_InstalledSoftware table and joins the vComputer and vSoftwareComponent tables to Inv_InstalledSoftware in order to display the names of the computers and software.

SELECT Inv_InstalledSoftware._ResourceGuid, vComputer.Name [Computer], vComputer.[User], vSoftwareComponent.Name [Software]
FROM Inv_InstalledSoftware
LEFT JOIN vComputer on vComputer.Guid = Inv_InstalledSoftware._ResourceGuid
LEFT JOIN vSoftwareComponent on vSoftwareComponent.Guid = Inv_InstalledSoftware._SoftwareComponentGuid

Then add the following line to the bottom of the query to filter out any software that does not contain the stated word(s) in its name ("Firefox" is used in this example and "%" symbols are wildcard characters in SQL):

WHERE vSoftwareComponent.Name like '%Firefox%'

Run the query and confirm that the results include all the desired data. If data for other software component is desired but is filtered out by the line above, add OR statements below the WHERE statement to include the other software components and use parenthesis around all the statements that are including software components (in this example "Google Chrome" is also included):

WHERE (vSoftwareComponent.Name like '%Firefox%'
OR vSoftwareComponent.Name like '%Google Chrome%')

Most of the time the query results will include many rows for unwanted software components with similar names to the desired software components. To eliminate these results add AND statements at the end of the query to exclude the unwanted software components based on parts of their names that are not in the desired software component names (in this example "plugin" and "Update" are excluded):

AND vSoftwareComponent.Name not like '%plugin%'
AND vSoftwareComponent.Name not like '%Update%'

Once the WHERE, OR, and AND statements have been tuned to show only the desired data, the query is ready to be copied into a custom filter or custom SQL report. Please note that the first column of the results must be the Computer GUID in order for the query to work in a filter. Additional columns do not matter in a filter and the GUID column will not be displayed in reports. Additional columns may be added to the query and be displayed in a custom report, but if the desired data is not contained in the vComputer, vSoftwareComponent, or Inv_InstalledSoftware tables then the user must locate the table containing the desired data and join it within the query (Symantec Support does not provide a database schema).

Creating a Custom SQL Report

To create a custom SQL report open the Altiris console and go to Reports>All Reports. Right-click on the folder where the custom report should be created and select New>Report>SQL Report. The new report's edit menu will appear in the right pane. Delete the default query under Parameterized Query and paste in the query that was created above. Give the report an appropriate name and then click Save Changes. The report results should match the query results from SQL Management Studio, minus the GUID column.

Creating a Custom Filter

To create a custom filter go to Manage>Filters open the Altiris console and go to Manage>Filters. Right-click on the folder where the custom filter should be created and select New>Filter. In the Filter Definition drop-down box select Query Mode: Raw SQL and then click OK on the popup window. Paste the SQL query created above in the Parameterized Query box. Give the filter an appropriate name and then click Save changes.

Please note that custom filters never update automatically. To make the filter update automatically on a schedule go to Manage>Jobs and Tasks, chose a folder to create an Update Filter Membership task under, right-click on the folder, select New>Task. In the Create New Task window scroll down and select Update Filter Membership. Next to Update filters (0): click None selected. In the Select Filters To Update window search for the custom filter that was created earlier. Move the custom filter to the Selected resources: pane by clicking the > button, then click OK.  Back in the Create New Task window give the task an appropriate name and then click OK. The new task will now open in the right pane. To give the Update Filter Task a schedule click on New Schedule. In the New Schedule window specify a desired time and then enable the Repeat every option and give the schedule a repeat frequency. Click Schedule to close the New Schedule window and then click Save Changes in the Task pane.

Reporting Computers that Do NOT Have the Software

Sometimes it is necessary to report which computers need a specific application to be installed. When working with filters the easiest approach is to create a filter for all the computers that have the software installed by following the steps above, then creating a second filter that explicitly excludes the first filter. This can be done in the filter edit page by expanding the Filter Definition section and then clicking on Select a filter next to Filters excluded in this filter (0):. The harder approach is to build the exclusion into the SQL query. A simple example query is provided below that excludes all computers that have Symantec Endpoint Protection installed.

SELECT DISTINCT Inv_InstalledSoftware._ResourceGuid, vComputer.Name [Computer], vComputer.[User] FROM Inv_InstalledSoftware
LEFT JOIN vComputer on vComputer.Guid = Inv_InstalledSoftware._ResourceGuid
LEFT JOIN vSoftwareComponent on vSoftwareComponent.Guid = Inv_InstalledSoftware._SoftwareComponentGuid
WHERE Inv_InstalledSoftware._ResourceGuid not in (
      SELECT Inv_InstalledSoftware._ResourceGuid FROM Inv_InstalledSoftware
      LEFT JOIN vSoftwareComponent on vSoftwareComponent.Guid = Inv_InstalledSoftware._SoftwareComponentGuid
      WHERE vSoftwareComponent.Name like 'Symantec Endpoint Protection%') --Your Software Here

Reporting Software Version

The Inv_AddRemoveProgram table contains version data but is limited to data reported by Software Inventory on Windows computers. The following query shows how to join the Inv_AddRemoveProgram table to the Inv_InstalledSoftware table:

SELECT Inv_InstalledSoftware._ResourceGuid, vComputer.Name [Computer], vComputer.[User], vSoftwareComponent.Name [Software], Inv_AddRemoveProgram.DisplayVersion
FROM Inv_InstalledSoftware
LEFT JOIN vComputer on vComputer.Guid = Inv_InstalledSoftware._ResourceGuid
LEFT JOIN vSoftwareComponent on vSoftwareComponent.Guid = Inv_InstalledSoftware._SoftwareComponentGuid
LEFT JOIN Inv_AddRemoveProgram on (Inv_AddRemoveProgram._ResourceGuid = Inv_InstalledSoftware._ResourceGuid
AND Inv_AddRemoveProgram._SoftwareComponentGuid = Inv_InstalledSoftware._SoftwareComponentGuid)