Report that shows the file information along with the computer name

Products

• IT Management Suite

Issue/Introduction

This article provides a query that can be used in a custom report that shows the consolidated information from the following tables/stored procedures:

• vRM_Computer_Item
• Inv_AddRemoveProgram
• Inv_Inventory_Results
• Inv_Installed_File_Details

Environment

ITMS 8.x

Resolution

The following query provides the needed information.  You can change the WHERE clause to best fit your necessity:

SELECT

       [vri2_Computer].[Guid]                   AS [_ItemGuid]

      ,[vri2_Computer].[Name]                   AS [Computer Name]

      ,[dca3_AddRemoveProgram].[DisplayName]    AS [Display Name]

      ,[dca4_Installed File Details].[Name]     AS [Executable Name]

      ,[dca3_AddRemoveProgram].[DisplayVersion] AS [Deisplay Version]

      ,[dca4_Installed File Details].[Path]     AS [Path]

  FROM [vRM_Computer_Item]               AS [vri2_Computer]

  LEFT JOIN [Inv_AddRemoveProgram]       AS [dca3_AddRemoveProgram]       ON ([vri2_Computer].[Guid] = [dca3_AddRemoveProgram].[_ResourceGuid])

  LEFT JOIN [ResourceAssociation]        AS [dca6_ResourceAssociation]    ON ([dca6_ResourceAssociation] .ParentResourceGuid =dca3_AddRemoveProgram._SoftwareComponentGuid 

AND [dca6_ResourceAssociation].ResourceAssociationTypeGuid = 'EABE86D3-AAFD-487A-AF63-5C95D7511AF6')

  LEFT JOIN [Inv_Inventory_Results]      AS [dca5_Inevntory Results]      ON ([vri2_Computer].[Guid] = [dca5_Inevntory Results].[_ResourceGuid]                             

AND [dca5_Inevntory Results].[Agent] = 'Inventory Agent')

  JOIN [Inv_Installed_File_Details] AS [dca4_Installed File Details] ON ([vri2_Computer].[Guid] = [dca4_Installed File Details].[_ResourceGuid]                        

AND [dca4_Installed File Details].FileResourceGuid = [dca6_ResourceAssociation].ChildResourceGuid)

 

WHERE [dca3_AddRemoveProgram].[DisplayName] like '%zip%'