Video Screencast Help

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

Created: 23 Dec 2013 | 10 comments
Language Translations
SK's picture
0 0 Votes
Login to vote

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

Comments 10 CommentsJump to latest comment

skhs's picture

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. 

0
Login to vote
andykn101's picture

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.

Authorised Symantec Consultant (ASC) with Endpoint Management Limited, an Authorised Symantec Delivery Provider based in the UK.

Connect Etiquette: Please "Mark as Solution" posts that fix your problem.

0
Login to vote
SK's picture

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

Connect Etiquette: "Mark as Solution" those posts which resolve your problem, and give a thumbs up to useful comments, articles and downloads.

0
Login to vote
andykn101's picture

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.

Authorised Symantec Consultant (ASC) with Endpoint Management Limited, an Authorised Symantec Delivery Provider based in the UK.

Connect Etiquette: Please "Mark as Solution" posts that fix your problem.

0
Login to vote
SK's picture

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

Connect Etiquette: "Mark as Solution" those posts which resolve your problem, and give a thumbs up to useful comments, articles and downloads.

0
Login to vote
Iker1234's picture

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!

0
Login to vote
SK's picture

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.

Connect Etiquette: "Mark as Solution" those posts which resolve your problem, and give a thumbs up to useful comments, articles and downloads.

0
Login to vote
Stefan S.'s picture

@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?

0
Login to vote
SK's picture

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.

Connect Etiquette: "Mark as Solution" those posts which resolve your problem, and give a thumbs up to useful comments, articles and downloads.

0
Login to vote