United Kingdom Endpoint Management User Group

 View Only
Back to Library

The SQL behind the Activity Center's "Manage > Software" console view 

Dec 23, 2013 01:33 PM

Have you ever wondered how the "Manage > Software" items information is obtained from within the database?

If you have, the following SQL queries will tell you (taken from a 7.1.2 MP1.1 v7RU ITMS system):

 

-- Newly Discovered Software
DECLARE @v1_TrusteeScope nvarchar(194)
   SET @v1_TrusteeScope = N'2e1f478a-4986-4223-9d1e-b5920a63ab41,582029e2-fc5b-4717-8808-b80d6ef0fd67,7091a13c-55c3-4e51-b164-8955ee25e1c2,b760e9a9-e4db-404c-a93f-aea51754aa4f'
SELECT
   [vri2_Software Component].[Guid] AS [Guid],
   [vri2_Software Component].[Name] AS [Name],
   [ajs6_ItemPresentation].[ImageUrl] AS [Image Url],
   [ajs3_vAC_InstalledSoftware].[IsManaged] AS [Managed],
   [vri2_Software Component].[ResourceTypeGuid] AS [ResourceTypeGuid],
   [ajs5_vRM_Company_Item].[Name] AS [Manufacturer]
FROM
   [vRM_Software_Component_Item] AS [vri2_Software Component]
      INNER JOIN [vAC_InstalledSoftware] AS [ajs3_vAC_InstalledSoftware]
         ON ([vri2_Software Component].[Guid] = [ajs3_vAC_InstalledSoftware].[Guid])
      LEFT OUTER JOIN ([ResourceAssociation] AS [ajs4_ra]
         LEFT OUTER JOIN [vRM_Company_Item] AS [ajs5_vRM_Company_Item]
            ON ([ajs4_ra].[ChildResourceGuid] = [ajs5_vRM_Company_Item].[Guid]))
         ON ([vri2_Software Component].[Guid] = [ajs4_ra].[ParentResourceGuid])
            AND
            (
               ([ajs4_ra].[ResourceAssociationTypeGuid] = '292dbd81-1526-423a-ae6d-f44eb46c5b16')
            )
      LEFT OUTER JOIN [ItemPresentation] AS [ajs6_ItemPresentation]
         ON ([vri2_Software Component].[Guid] = [ajs6_ItemPresentation].[Guid])
WHERE
   (
      (
         ([ajs3_vAC_InstalledSoftware].[IsManaged] = 0)
      )
   )
ORDER BY
   [Name] ASC
 

 

--Installed Products
DECLARE @v1_TrusteeScope nvarchar(194)
   SET @v1_TrusteeScope = N'2e1f478a-4986-4223-9d1e-b5920a63ab41,582029e2-fc5b-4717-8808-b80d6ef0fd67,7091a13c-55c3-4e51-b164-8955ee25e1c2,b760e9a9-e4db-404c-a93f-aea51754aa4f'
SELECT
   [vri2_Software Product].[Guid] AS [Guid],
   [vri2_Software Product].[Name] AS [Name],
   [ajs7_Inv_Software_Image].[ImageUrl] AS [Image Url],
   [dca3_Software Product State].[IsManaged] AS [Managed],
   [ajs6_vRM_Company_Item].[Name] AS [Manufacturer]
FROM
   [vRM_Software_Product_Item] AS [vri2_Software Product]
      INNER JOIN [Inv_Software_Product_State] AS [dca3_Software Product State]
         ON ([vri2_Software Product].[Guid] = [dca3_Software Product State].[_ResourceGuid])
      INNER JOIN [vAC_InstalledProducts] AS [ajs4_vAC_InstalledProducts]
         ON ([vri2_Software Product].[Guid] = [ajs4_vAC_InstalledProducts].[Guid])
      LEFT OUTER JOIN ([ResourceAssociation] AS [ajs5_ra]
         LEFT OUTER JOIN [vRM_Company_Item] AS [ajs6_vRM_Company_Item]
            ON ([ajs5_ra].[ChildResourceGuid] = [ajs6_vRM_Company_Item].[Guid]))
         ON ([vri2_Software Product].[Guid] = [ajs5_ra].[ParentResourceGuid])
            AND
            (
               ([ajs5_ra].[ResourceAssociationTypeGuid] = 'd5c66d5a-7686-4ca2-b7c1-ac980576ce1d') --Software Product to Company
            )
      LEFT OUTER JOIN [Inv_Software_Image] AS [ajs7_Inv_Software_Image]
         ON ([vri2_Software Product].[Guid] = [ajs7_Inv_Software_Image].[_ResourceGuid])
WHERE
   (
      (
         ([dca3_Software Product State].[IsManaged] = 1)
      )
   )
ORDER BY
   [Name] ASC

 

--Licensed
DECLARE @v1_TrusteeScope nvarchar(194)
   SET @v1_TrusteeScope = N'2e1f478a-4986-4223-9d1e-b5920a63ab41,582029e2-fc5b-4717-8808-b80d6ef0fd67,7091a13c-55c3-4e51-b164-8955ee25e1c2,b760e9a9-e4db-404c-a93f-aea51754aa4f'
SELECT
   [vri2_Software Product].[Guid] AS [Guid],
   [vri2_Software Product].[Name] AS [Name],
   [dca3_Software Product State].[IsManaged] AS [Managed],
   [ajs10_Inv_Software_Image].[ImageUrl] AS [Image Url],
   [ajs8_ScopeMembership].[ScopeCollectionGuid] AS [Organizational Group],
   [ajs9_FolderBaseFolder].[ParentFolderGuid] AS [ParentFolderGuid],
   [ajs7_vRM_Company_Item].[Name] AS [Manufacturer]
FROM
   [vRM_Software_Product_Item] AS [vri2_Software Product]
      INNER JOIN [Inv_Software_Product_State] AS [dca3_Software Product State]
         ON ([vri2_Software Product].[Guid] = [dca3_Software Product State].[_ResourceGuid])
      INNER JOIN [vAC_LicensedProducts] AS [ajs4_vAC_LicensedProducts]
         ON ([vri2_Software Product].[Guid] = [ajs4_vAC_LicensedProducts].[Guid])
      INNER JOIN [vAC_InstalledProducts] AS [ajs5_vAC_InstalledProducts]
         ON ([vri2_Software Product].[Guid] = [ajs5_vAC_InstalledProducts].[Guid])
      LEFT OUTER JOIN ([ResourceAssociation] AS [ajs6_ra]
         LEFT OUTER JOIN [vRM_Company_Item] AS [ajs7_vRM_Company_Item]
            ON ([ajs6_ra].[ChildResourceGuid] = [ajs7_vRM_Company_Item].[Guid]))
         ON ([vri2_Software Product].[Guid] = [ajs6_ra].[ParentResourceGuid])
            AND
            (
               ([ajs6_ra].[ResourceAssociationTypeGuid] = 'd5c66d5a-7686-4ca2-b7c1-ac980576ce1d') --Software Product to Company
            )
      LEFT OUTER JOIN ([ScopeMembership] AS [ajs8_ScopeMembership]
         LEFT OUTER JOIN [FolderBaseFolder] AS [ajs9_FolderBaseFolder]
            ON ([ajs8_ScopeMembership].[ScopeCollectionGuid] = [ajs9_FolderBaseFolder].[FolderGuid]))
         ON ([vri2_Software Product].[Guid] = [ajs8_ScopeMembership].[ResourceGuid])
      LEFT OUTER JOIN [Inv_Software_Image] AS [ajs10_Inv_Software_Image]
         ON ([vri2_Software Product].[Guid] = [ajs10_Inv_Software_Image].[_ResourceGuid])
GROUP BY
   [vri2_Software Product].[Guid],
   [dca3_Software Product State].[IsManaged],
   [vri2_Software Product].[Name],
   [ajs7_vRM_Company_Item].[Name],
   [ajs8_ScopeMembership].[ScopeCollectionGuid],
   [ajs9_FolderBaseFolder].[ParentFolderGuid],
   [ajs10_Inv_Software_Image].[ImageUrl]
ORDER BY
   [Name] ASC

 

--Adobe
DECLARE @v1_TrusteeScope nvarchar(194)
   SET @v1_TrusteeScope = N'2e1f478a-4986-4223-9d1e-b5920a63ab41,582029e2-fc5b-4717-8808-b80d6ef0fd67,7091a13c-55c3-4e51-b164-8955ee25e1c2,b760e9a9-e4db-404c-a93f-aea51754aa4f'
DECLARE @g3_SoftwareProductContainsSoftwareComponent uniqueidentifier
   SET @g3_SoftwareProductContainsSoftwareComponent = '9d67b0c6-beff-4fcd-86c1-4a40028fe483' --Software Product Contains Software Component
DECLARE @g7_SoftwareProducttoCompany uniqueidentifier
   SET @g7_SoftwareProducttoCompany = 'd5c66d5a-7686-4ca2-b7c1-ac980576ce1d' --Software Product to Company
SELECT
   [vri2_Software Product].[Guid] AS [Guid],
   [vri2_Software Product].[Name] AS [Name],
   [vri9_Company].[Name] AS [Manufacturer],
   [ajs11_ScopeMembership].[ScopeCollectionGuid] AS [Organizational Group],
   [ajs12_FolderBaseFolder].[ParentFolderGuid] AS [ParentFolderGuid],
   [ajs13_Inv_Software_Image].[ImageUrl] AS [Image Url]
FROM
   [vRM_Software_Product_Item] AS [vri2_Software Product]
      LEFT OUTER JOIN ([ResourceAssociation] AS [ra4_Software Product Contains Software Component]
         LEFT OUTER JOIN ([vRM_Software_Component_Item] AS [vri5_Software Component]
            INNER JOIN [Inv_InstalledSoftware] AS [ajs6_Inv_InstalledSoftware]
               ON ([vri5_Software Component].[Guid] = [ajs6_Inv_InstalledSoftware].[_SoftwareComponentGuid]))
            ON ([ra4_Software Product Contains Software Component].[ChildResourceGuid] = [vri5_SoftwareComponent].[Guid]))
         ON ([vri2_Software Product].[Guid] = [ra4_Software Product Contains Software Component].[ParentResourceGuid])
      LEFT OUTER JOIN ([ResourceAssociation] AS [ra8_Software Product to Company]
         LEFT OUTER JOIN [vRM_Company_Item] AS [vri9_Company]
            ON ([ra8_Software Product to Company].[ChildResourceGuid] = [vri9_Company].[Guid]))
         ON ([vri2_Software Product].[Guid] = [ra8_Software Product to Company].[ParentResourceGuid])
      INNER JOIN [vAC_LicensedProducts] AS [ajs10_vAC_LicensedProducts]
         ON ([vri2_Software Product].[Guid] = [ajs10_vAC_LicensedProducts].[Guid])
      LEFT OUTER JOIN ([ScopeMembership] AS [ajs11_ScopeMembership]
         LEFT OUTER JOIN [FolderBaseFolder] AS [ajs12_FolderBaseFolder]
            ON ([ajs11_ScopeMembership].[ScopeCollectionGuid] = [ajs12_FolderBaseFolder].[FolderGuid]))
         ON ([vri2_Software Product].[Guid] = [ajs11_ScopeMembership].[ResourceGuid])
      LEFT OUTER JOIN [Inv_Software_Image] AS [ajs13_Inv_Software_Image]
         ON ([vri2_Software Product].[Guid] = [ajs13_Inv_Software_Image].[_ResourceGuid])
WHERE
   (
      (
         ([ra4_Software Product Contains Software Component].[ResourceAssociationTypeGuid] = @g3_SoftwareProductContainsSoftwareComponent)
         OR
         ([ra4_Software Product Contains Software Component].[ResourceAssociationTypeGuid] IS NULL)
      )
      AND
      (
         ([ra8_Software Product to Company].[ResourceAssociationTypeGuid] = @g7_SoftwareProducttoCompany)
         OR
         ([ra8_Software Product to Company].[ResourceAssociationTypeGuid] IS NULL)
      )
      AND
      (
         (LOWER([vri9_Company].[Name]) LIKE '%' + N'adobe' + '%')
      )
   )
GROUP BY
   [vri2_Software Product].[Guid],
   [vri2_Software Product].[Name],
   [vri9_Company].[Name],
   [ajs11_ScopeMembership].[ScopeCollectionGuid],
   [ajs12_FolderBaseFolder].[ParentFolderGuid],
   [ajs13_Inv_Software_Image].[ImageUrl]
ORDER BY
   [Name] ASC

 

--Microsoft
DECLARE @v1_TrusteeScope nvarchar(194)
   SET @v1_TrusteeScope = N'2e1f478a-4986-4223-9d1e-b5920a63ab41,582029e2-fc5b-4717-8808-b80d6ef0fd67,7091a13c-55c3-4e51-b164-8955ee25e1c2,b760e9a9-e4db-404c-a93f-aea51754aa4f'
DECLARE @g3_SoftwareProductContainsSoftwareComponent uniqueidentifier
   SET @g3_SoftwareProductContainsSoftwareComponent = '9d67b0c6-beff-4fcd-86c1-4a40028fe483'  --Software Product Contains Software Component
DECLARE @g7_SoftwareProducttoCompany uniqueidentifier
   SET @g7_SoftwareProducttoCompany = 'd5c66d5a-7686-4ca2-b7c1-ac980576ce1d'  --Software Product to Company
SELECT
   [vri2_Software Product].[Guid] AS [Guid],
   [vri2_Software Product].[Name] AS [Name],
   [vri9_Company].[Name] AS [Manufacturer],
   [ajs11_ScopeMembership].[ScopeCollectionGuid] AS [Organizational Group],
   [ajs12_FolderBaseFolder].[ParentFolderGuid] AS [ParentFolderGuid],
   [ajs13_Inv_Software_Image].[ImageUrl] AS [Image Url]
FROM
   [vRM_Software_Product_Item] AS [vri2_Software Product]
      LEFT OUTER JOIN ([ResourceAssociation] AS [ra4_Software Product Contains Software Component]
         LEFT OUTER JOIN ([vRM_Software_Component_Item] AS [vri5_Software Component]
            INNER JOIN [Inv_InstalledSoftware] AS [ajs6_Inv_InstalledSoftware]
               ON ([vri5_Software Component].[Guid] = [ajs6_Inv_InstalledSoftware].[_SoftwareComponentGuid]))
            ON ([ra4_Software Product Contains Software Component].[ChildResourceGuid] = [vri5_SoftwareComponent].[Guid]))
         ON ([vri2_Software Product].[Guid] = [ra4_Software Product Contains Software Component].[ParentResourceGuid])
      LEFT OUTER JOIN ([ResourceAssociation] AS [ra8_Software Product to Company]
         LEFT OUTER JOIN [vRM_Company_Item] AS [vri9_Company]
            ON ([ra8_Software Product to Company].[ChildResourceGuid] = [vri9_Company].[Guid]))
         ON ([vri2_Software Product].[Guid] = [ra8_Software Product to Company].[ParentResourceGuid])
      INNER JOIN [vAC_LicensedProducts] AS [ajs10_vAC_LicensedProducts]
         ON ([vri2_Software Product].[Guid] = [ajs10_vAC_LicensedProducts].[Guid])
      LEFT OUTER JOIN ([ScopeMembership] AS [ajs11_ScopeMembership]
         LEFT OUTER JOIN [FolderBaseFolder] AS [ajs12_FolderBaseFolder]
            ON ([ajs11_ScopeMembership].[ScopeCollectionGuid] = [ajs12_FolderBaseFolder].[FolderGuid]))
         ON ([vri2_Software Product].[Guid] = [ajs11_ScopeMembership].[ResourceGuid])
      LEFT OUTER JOIN [Inv_Software_Image] AS [ajs13_Inv_Software_Image]
         ON ([vri2_Software Product].[Guid] = [ajs13_Inv_Software_Image].[_ResourceGuid])
WHERE
   (
      (
         ([ra4_Software Product Contains Software Component].[ResourceAssociationTypeGuid] = @g3_SoftwareProductContainsSoftwareComponent)
         OR
         ([ra4_Software Product Contains Software Component].[ResourceAssociationTypeGuid] IS NULL)
      )
      AND
      (
         ([ra8_Software Product to Company].[ResourceAssociationTypeGuid] = @g7_SoftwareProducttoCompany)
         OR
         ([ra8_Software Product to Company].[ResourceAssociationTypeGuid] IS NULL)
      )
      AND
      (
         (LOWER([vri9_Company].[Name]) LIKE '%' + N'microsoft' + '%')
      )
   )
GROUP BY
   [vri2_Software Product].[Guid],
   [vri2_Software Product].[Name],
   [vri9_Company].[Name],
   [ajs11_ScopeMembership].[ScopeCollectionGuid],
   [ajs12_FolderBaseFolder].[ParentFolderGuid],
   [ajs13_Inv_Software_Image].[ImageUrl]
ORDER BY
   [Name] ASC

 

 

--Usage Tracking
DECLARE @v1_TrusteeScope nvarchar(155)
   SET @v1_TrusteeScope = N'2e1f478a-4986-4223-9d1e-b5920a63ab41,582029e2-fc5b-4717-8808-b80d6ef0fd67,7091a13c-55c3-4e51-b164-8955ee25e1c2,b760e9a9-e4db-404c-a93f-aea51754aa4f'
DECLARE @g4_SoftwareProductContainsSoftwareComponent uniqueidentifier
   SET @g4_SoftwareProductContainsSoftwareComponent = '9d67b0c6-beff-4fcd-86c1-4a40028fe483'  --Software Product Contains Software Component
SELECT
   [vri2_Software Product].[Guid] AS [Guid],
   [vri2_Software Product].[Name] AS [Name],
   [ajs13_Inv_Software_Image].[ImageUrl] AS [Image Url],
   [dca3_Software Product State].[IsManaged] AS [Managed],
   [ajs8_Inv_Software_Product_Usage].[IsUsageTracked] AS [IsUsageTracked],
   [ajs9_ra].[ResourceAssociationTypeGuid] AS [ResourceAssociationTypeGuid],
   [ajs11_ScopeMembership].[ScopeCollectionGuid] AS [Organizational Group],
   [ajs12_FolderBaseFolder].[ParentFolderGuid] AS [ParentFolderGuid],
   [ajs10_vRM_Company_Item].[Name] AS [Manufacturer]
FROM
   [vRM_Software_Product_Item] AS [vri2_Software Product]
      INNER JOIN [Inv_Software_Product_State] AS [dca3_Software Product State]
         ON ([vri2_Software Product].[Guid] = [dca3_Software Product State].[_ResourceGuid])
      LEFT OUTER JOIN ([ResourceAssociation] AS [ra5_Software Product Contains Software Component]
         LEFT OUTER JOIN ([vRM_Software_Component_Item] AS [vri6_Software Component]
            INNER JOIN [Inv_InstalledSoftware] AS [ajs7_Inv_InstalledSoftware]
               ON ([vri6_Software Component].[Guid] = [ajs7_Inv_InstalledSoftware].[_SoftwareComponentGuid]))
            ON ([ra5_Software Product Contains Software Component].[ChildResourceGuid] = [vri6_SoftwareComponent].[Guid]))
         ON ([vri2_Software Product].[Guid] = [ra5_Software Product Contains Software Component].[ParentResourceGuid])
      LEFT OUTER JOIN [Inv_Software_Product_Usage] AS [ajs8_Inv_Software_Product_Usage]
         ON ([vri2_Software Product].[Guid] = [ajs8_Inv_Software_Product_Usage].[_ResourceGuid])
      LEFT OUTER JOIN ([ResourceAssociation] AS [ajs9_ra]
         LEFT OUTER JOIN [vRM_Company_Item] AS [ajs10_vRM_Company_Item]
            ON ([ajs9_ra].[ChildResourceGuid] = [ajs10_vRM_Company_Item].[Guid]))
         ON ([vri2_Software Product].[Guid] = [ajs9_ra].[ParentResourceGuid])
            AND
            (
               ([ajs9_ra].[ResourceAssociationTypeGuid] = 'd5c66d5a-7686-4ca2-b7c1-ac980576ce1d')  --Software Product to Company
            )
      LEFT OUTER JOIN ([ScopeMembership] AS [ajs11_ScopeMembership]
         LEFT OUTER JOIN [FolderBaseFolder] AS [ajs12_FolderBaseFolder]
            ON ([ajs11_ScopeMembership].[ScopeCollectionGuid] = [ajs12_FolderBaseFolder].[FolderGuid]))
         ON ([vri2_Software Product].[Guid] = [ajs11_ScopeMembership].[ResourceGuid])
      LEFT OUTER JOIN [Inv_Software_Image] AS [ajs13_Inv_Software_Image]
         ON ([vri2_Software Product].[Guid] = [ajs13_Inv_Software_Image].[_ResourceGuid])
WHERE
   (
      (
         ([ra5_Software Product Contains Software Component].[ResourceAssociationTypeGuid] = @g4_SoftwareProductContainsSoftwareComponent)
         OR
         ([ra5_Software Product Contains Software Component].[ResourceAssociationTypeGuid] IS NULL)
      )
      AND
      (
         ([dca3_Software Product State].[IsManaged] = 1)
         AND
         ([ajs8_Inv_Software_Product_Usage].[IsUsageTracked] = 1)
      )
   )
GROUP BY
   [vri2_Software Product].[Guid],
   [vri2_Software Product].[Name],
   [dca3_Software Product State].[IsManaged],
   [ajs8_Inv_Software_Product_Usage].[IsUsageTracked],
   [ajs9_ra].[ResourceAssociationTypeGuid],
   [ajs10_vRM_Company_Item].[Name],
   [ajs11_ScopeMembership].[ScopeCollectionGuid],
   [ajs12_FolderBaseFolder].[ParentFolderGuid],
   [ajs13_Inv_Software_Image].[ImageUrl]
ORDER BY
   [Name] ASC


 

--Software Products
DECLARE @v1_TrusteeScope nvarchar(155)
   SET @v1_TrusteeScope = N'2e1f478a-4986-4223-9d1e-b5920a63ab41,582029e2-fc5b-4717-8808-b80d6ef0fd67,7091a13c-55c3-4e51-b164-8955ee25e1c2,b760e9a9-e4db-404c-a93f-aea51754aa4f'
SELECT
   [vri2_Software Product].[Guid] AS [Guid],
   [vri2_Software Product].[Name] AS [Name],
   [ajs4_Inv_Software_Image].[ImageUrl] AS [Image Url],
   [ajs5_ra].[ResourceAssociationTypeGuid] AS [ResourceAssociationTypeGuid],
   [ajs6_vRM_Company_Item].[Name] AS [Manufacturer]
FROM
   [vRM_Software_Product_Item] AS [vri2_Software Product]
      INNER JOIN [vAC_DeliverableSoftwareProducts] AS [ajs3_vAC_DeliverableSoftwareProducts]
         ON ([vri2_Software Product].[Guid] = [ajs3_vAC_DeliverableSoftwareProducts].[Guid])
      LEFT OUTER JOIN [Inv_Software_Image] AS [ajs4_Inv_Software_Image]
         ON ([vri2_Software Product].[Guid] = [ajs4_Inv_Software_Image].[_ResourceGuid])
      LEFT OUTER JOIN ([ResourceAssociation] AS [ajs5_ra]
         LEFT OUTER JOIN [vRM_Company_Item] AS [ajs6_vRM_Company_Item]
            ON ([ajs5_ra].[ChildResourceGuid] = [ajs6_vRM_Company_Item].[Guid]))
         ON ([vri2_Software Product].[Guid] = [ajs5_ra].[ParentResourceGuid])
            AND
            (
               ([ajs5_ra].[ResourceAssociationTypeGuid] = 'd5c66d5a-7686-4ca2-b7c1-ac980576ce1d')  --Software Product to Company
            )
ORDER BY
   [Name] ASC


 

--Software Releases
DECLARE @v1_TrusteeScope nvarchar(155)
   SET @v1_TrusteeScope = N'2e1f478a-4986-4223-9d1e-b5920a63ab41,582029e2-fc5b-4717-8808-b80d6ef0fd67,7091a13c-55c3-4e51-b164-8955ee25e1c2,b760e9a9-e4db-404c-a93f-aea51754aa4f'
SELECT
   [vri2_Software Release].[Guid] AS [Guid],
   [vri2_Software Release].[Name] AS [Name],
   [ajs5_ItemPresentation].[ImageUrl] AS [Image Url],
   [dca3_Software Component State].[IsManaged] AS [Managed],
   [ajs6_ra].[ResourceAssociationTypeGuid] AS [ResourceAssociationTypeGuid],
   [ajs7_vRM_Company_Item].[Name] AS [Manufacturer]
FROM
   [vRM_Software_Release_Item] AS [vri2_Software Release]
      LEFT OUTER JOIN [Inv_Software_Component_State] AS [dca3_Software Component State]
         ON ([vri2_Software Release].[Guid] = [dca3_Software Component State].[_ResourceGuid])
      INNER JOIN [vAC_DeliverableSoftware] AS [ajs4_vAC_DeliverableSoftware]
         ON ([vri2_Software Release].[Guid] = [ajs4_vAC_DeliverableSoftware].[Guid])
      LEFT OUTER JOIN [ItemPresentation] AS [ajs5_ItemPresentation]
         ON ([vri2_Software Release].[Guid] = [ajs5_ItemPresentation].[Guid])
      LEFT OUTER JOIN ([ResourceAssociation] AS [ajs6_ra]
         LEFT OUTER JOIN [vRM_Company_Item] AS [ajs7_vRM_Company_Item]
            ON ([ajs6_ra].[ChildResourceGuid] = [ajs7_vRM_Company_Item].[Guid]))
         ON ([vri2_Software Release].[Guid] = [ajs6_ra].[ParentResourceGuid])
            AND
            (
               ([ajs6_ra].[ResourceAssociationTypeGuid] = 'd5c66d5a-7686-4ca2-b7c1-ac980576ce1d')  --Software Product to Company
            )
ORDER BY
   [Name] ASC


 

--Software Updates
DECLARE @v1_TrusteeScope nvarchar(155)
   SET @v1_TrusteeScope = N'2e1f478a-4986-4223-9d1e-b5920a63ab41,582029e2-fc5b-4717-8808-b80d6ef0fd67,7091a13c-55c3-4e51-b164-8955ee25e1c2,b760e9a9-e4db-404c-a93f-aea51754aa4f'
SELECT
   [vri2_Resource].[Guid] AS [Guid],
   [vri2_Resource].[Name] AS [Name],
   [ajs4_ItemPresentation].[ImageUrl] AS [Image Url],
   [ajs5_ra].[ResourceAssociationTypeGuid] AS [ResourceAssociationTypeGuid],
   [vri2_Resource].[ResourceTypeGuid] AS [ResourceTypeGuid],
   [ajs6_vRM_Company_Item].[Name] AS [Manufacturer]
FROM
   [vRM_Resource_Item] AS [vri2_Resource]
      INNER JOIN [vAC_DeliverableSoftwareUpdates] AS [ajs3_vAC_DeliverableSoftwareUpdates]
         ON ([vri2_Resource].[Guid] = [ajs3_vAC_DeliverableSoftwareUpdates].[Guid])
      LEFT OUTER JOIN [ItemPresentation] AS [ajs4_ItemPresentation]
         ON ([vri2_Resource].[Guid] = [ajs4_ItemPresentation].[Guid])
      LEFT OUTER JOIN ([ResourceAssociation] AS [ajs5_ra]
         LEFT OUTER JOIN [vRM_Company_Item] AS [ajs6_vRM_Company_Item]
            ON ([ajs5_ra].[ChildResourceGuid] = [ajs6_vRM_Company_Item].[Guid]))
         ON ([vri2_Resource].[Guid] = [ajs5_ra].[ParentResourceGuid])
            AND
            (
               ([ajs5_ra].[ResourceAssociationTypeGuid] = 'd5c66d5a-7686-4ca2-b7c1-ac980576ce1d')  --Software Product to Company
            )
ORDER BY
   [Name] ASC

Statistics
0 Favorited
1 Views
0 Files
0 Shares
0 Downloads

Tags and Keywords

Comments

Stefan S
Feb 24, 2014 04:46 AM

done :-) --> Connect Idea

SK
Feb 04, 2014 02:27 PM

That is correct, the AC queries do not isolate their data to only active devices.

Is event data is kept for retired computers, then I would expect to see their usage data.

If you only wish to see data related to active devices, I suggest that you submit a Connect Idea requesting this.

Stefan S
Feb 04, 2014 09:57 AM

@SK: What is reason behind the fact that also "retired" computers are shown in the enhanced view? Would it not make sense to filter out non-active computers from the view.

I would assume there is no "usage" to be seen on retired computers. Or if there is a good reason to show all computers maybe the status could be added in the view?

SK
Jan 17, 2014 06:17 AM

I believe this view has been created in order to identify those software products that have the "Turn on metering / usage tracking for this software product" optioned enabled, and that have one or more EXE's missing from the Programs column.

Migration User
Jan 17, 2014 06:10 AM

Hi SK,

 

Great job and post. One question. Does this new view show the sw resources linked to sw products, which have no EXE file associated? It could be great because a report I tried to launch freezed our database...

 

Regards!

SK
Jan 17, 2014 04:18 AM

ITMS 7.5 adds a new view to "Manage > Software" called "Missing program associations", which uses the following SQL:

 

DECLARE @v1_TrusteeScope nvarchar(155)
   SET @v1_TrusteeScope = N'{2E1F478A-4986-4223-9D1E-B5920A63AB41},{582029E2-FC5B-4717-8808-B80D6EF0FD67},{B760E9A9-E4DB-404C-A93F-AEA51754AA4F},{F83559BB-F812-4B90-8C04-E17BB1F57E01}'
SELECT
   [vri2_Software Product].[Guid] AS [Guid],
   [vri2_Software Product].[Name] AS [Name],
   [ajs9_Inv_Software_Image].[ImageUrl] AS [Image Url],
   [ajs7_ScopeMembership].[ScopeCollectionGuid] AS [Organizational Group],
   [ajs4_Inv_Software_Product_Usage].[IsUsageTracked] AS [Usage Tracked],
   [ajs5_ra].[ResourceAssociationTypeGuid] AS [ResourceAssociationTypeGuid],
   [ajs8_FolderBaseFolder].[ParentFolderGuid] AS [ParentFolderGuid],
   [ajs6_vRM_Company_Item].[Name] AS [Manufacturer]
FROM
   [vRM_Software_Product_Item] AS [vri2_Software Product]
      INNER JOIN [vAC_MeteredProductsMissingPrograms] AS [ajs3_vAC_MeteredProductsMissingPrograms]
         ON ([vri2_Software Product].[Guid] = [ajs3_vAC_MeteredProductsMissingPrograms].[ProductGuid])
      LEFT OUTER JOIN [Inv_Software_Product_Usage] AS [ajs4_Inv_Software_Product_Usage]
         ON ([vri2_Software Product].[Guid] = [ajs4_Inv_Software_Product_Usage].[_ResourceGuid])
      LEFT OUTER JOIN ([ResourceAssociation] AS [ajs5_ra]
         LEFT OUTER JOIN [vRM_Company_Item] AS [ajs6_vRM_Company_Item]
            ON ([ajs5_ra].[ChildResourceGuid] = [ajs6_vRM_Company_Item].[Guid]))
         ON ([vri2_Software Product].[Guid] = [ajs5_ra].[ParentResourceGuid])
            AND
            (
               ([ajs5_ra].[ResourceAssociationTypeGuid] = 'd5c66d5a-7686-4ca2-b7c1-ac980576ce1d')
            )
      LEFT OUTER JOIN ([ScopeMembership] AS [ajs7_ScopeMembership]
         LEFT OUTER JOIN [FolderBaseFolder] AS [ajs8_FolderBaseFolder]
            ON ([ajs7_ScopeMembership].[ScopeCollectionGuid] = [ajs8_FolderBaseFolder].[FolderGuid]))
         ON ([vri2_Software Product].[Guid] = [ajs7_ScopeMembership].[ResourceGuid])
      LEFT OUTER JOIN [Inv_Software_Image] AS [ajs9_Inv_Software_Image]
         ON ([vri2_Software Product].[Guid] = [ajs9_Inv_Software_Image].[_ResourceGuid])
WHERE
   (
      (
         ([ajs4_Inv_Software_Product_Usage].[IsUsageTracked] = 1)
      )
   )
GROUP BY
   [vri2_Software Product].[Guid],
   [vri2_Software Product].[Name],
   [ajs4_Inv_Software_Product_Usage].[IsUsageTracked],
   [ajs5_ra].[ResourceAssociationTypeGuid],
   [ajs6_vRM_Company_Item].[Name],
   [ajs7_ScopeMembership].[ScopeCollectionGuid],
   [ajs8_FolderBaseFolder].[ParentFolderGuid],
   [ajs9_Inv_Software_Image].[ImageUrl]
ORDER BY
   [Name] ASC

andykn101
Jan 02, 2014 09:59 AM

OK, found the answer.

If you select an "Installed Product" the "Computers with Software installed" list is based on vComputer, which only lists Active computers.

If you double click the selected Installed Product and look at the "Installs" column of the "Identify Inventory" tab, that comes directly from the Inv_InstalledSoftware table with no filtering for "Active" Status.

For some reason the "Computers with Software installed" list of a Software Release limits the results to the first few, it uses the spAC_GetComputersBySoftwareComponent Stored Procedure with @SelectTop=1.

SK
Jan 02, 2014 09:39 AM

The Software Catalog uses different SQL which is most likely why you see differences between its results and that of the Activity Center.

andykn101
Jan 02, 2014 08:37 AM

Thanks, now all we need is to find out why the "Computers with Software installed" list doesn't match the total of the "Installs" columns for a Software Product - even when there's only one Software Release in the Software Product.

skhs
Dec 23, 2013 01:45 PM

Thank you so much this is good info, I did had a post https://www-secure.symantec.com/connect/forums/query-behind-software-view-enhanced-console-views just wondering how the cost of savings or over utilization is calculated. 

Related Entries and Links

No Related Resource entered.