Video Screencast Help
Give us your opinion and win with Symantec! Please help us by taking this survey to tell us about your experience with Symantec Connect, so that we can continue to grow and improve.  Take the survey.

Installed software report to show list of computers (not summary)

Created: 18 Feb 2014 | 8 comments

Hi,

I realize this is one big query and may be a pain to tweak, but can it be tweaked to show me all computers with Adobe Acrobat and their version. Right now the report just give me a break down per product with a count.

DECLARE @v1_TrusteeScope nvarchar(max)
SET @v1_TrusteeScope = N'%TrusteeScope%'

IF ('%Type%' = 'Managed Software')
SELECT spi.Guid
      ,spi.Name
      ,spv.[Version]
      ,company.Name AS Company
      ,cnt.cnt AS [Count]
FROM vRM_Software_Product_Item spi
JOIN
        (SELECT COUNT(DISTINCT inst._ResourceGuid) AS cnt,ra.ParentResourceGuid
         FROM ResourceAssociation ra
         JOIN Inv_InstalledSoftware inst
            ON inst._SoftwareComponentGuid = ra.ChildResourceGuid and InstallFlag = 1
         WHERE ra.ResourceAssociationTypeGuid = '9D67B0C6-BEFF-4FCD-86C1-4A40028FE483' --product contains component
         AND inst._ResourceGuid IN (SELECT ResourceGuid from ScopeMembership WHERE ScopeCollectionGuid IN(SELECT ScopeCollectionGuid FROM fnGetTrusteeScopeCollections (@v1_TrusteeScope)))
         GROUP BY ra.ParentResourceGuid  
         )cnt
   ON cnt.ParentResourceGuid = spi.[Guid]
JOIN Inv_Software_Product_State sps
   ON sps._ResourceGuid = spi.[Guid]
   AND sps.IsManaged = 1
LEFT JOIN Inv_Software_Product_Version spv
   ON spv._ResourceGuid = spi.[Guid]
LEFT JOIN (SELECT rc.Name, ra.ParentResourceGuid AS softProdGuid
           FROM RM_ResourceCompany rc
           JOIN ResourceAssociation ra
              ON ra.ChildResourceGuid = rc.[Guid]
              AND ra.ResourceAssociationTypeGuid = 'D5C66D5A-7686-4CA2-B7C1-AC980576CE1D')company
   ON company.softProdGuid = spi.[Guid]

WHERE ('%Name%' = '%' OR LOWER (spi.Name) LIKE LOWER ('%Name%'))
AND ('%Version%' = '%' OR spv.[Version] LIKE '%Version%')
AND ('%Company%' = '%' OR LOWER (company.Name) LIKE LOWER ('%Company%'))
AND spi.[CreatedDate] >= '%Discovered since%'
ORDER BY spi.Name

ELSE
IF ('%Type%' = 'All Software')
SELECT sci.[Guid]
       ,sci.Name
       ,sc.[Version]
       ,company.Name AS [Company]
       ,inst.cnt AS [Count]
FROM vRM_Software_Component_Item sci
JOIN Inv_Software_Component sc
   ON sci.Guid = sc._ResourceGuid
JOIN (SELECT COUNT(DISTINCT _ResourceGuid) AS cnt, _SoftwareComponentGuid
      FROM Inv_InstalledSoftware
      WHERE InstallFlag = 1
      AND _ResourceGuid IN (SELECT ResourceGuid from ScopeMembership WHERE ScopeCollectionGuid IN(SELECT ScopeCollectionGuid FROM fnGetTrusteeScopeCollections (@v1_TrusteeScope)))
      GROUP BY _SoftwareComponentGuid)inst
   ON inst._SoftwareComponentGuid = sci.Guid
LEFT JOIN (SELECT ra.ParentResourceGuid AS sofCompGuid, rc.Name
           FROM RM_ResourceCompany rc
           JOIN ResourceAssociation ra
              ON ra.ChildResourceGuid = rc.Guid
              AND ra.ResourceAssociationTypeGuid = '292DBD81-1526-423A-AE6D-F44EB46C5B16')company
   ON company.sofCompGuid = sci.Guid
WHERE ('%Name%' = '%' OR LOWER (sci.Name) LIKE LOWER ('%Name%'))
AND ('%Version%' = '%' OR sc.[Version] LIKE '%Version%')
AND ('%Company%' = '%' OR LOWER (company.Name) LIKE LOWER ('%Company%'))
AND sci.[CreatedDate] >= '%Discovered since%'
ORDER BY sci.Name
 

Thanks.

Operating Systems:

Comments 8 CommentsJump to latest comment

Briandr88's picture

Hi,

This query works, but it is bringing back a whole lot of junk I don't need for the report.

SELECT DISTINCT
vc.[Name] as [Computer Name]
,ac.[Last Logon User]
,arp.[DisplayName] AS [Product Name]
,arp.[DisplayVersion] AS [Product Version]
,vc.[OS Name] AS [Operating System]
,vc.[OS Revision] AS [Service Pack]
FROM
Inv_AddRemoveProgram as arp
INNER JOIN
vComputer as vc
ON arp._ResourceGuid = vc.[Guid]
INNER JOIN
Inv_Aex_AC_TCPIP ip
ON vc.[Guid] = ip.[_ResourceGuid]
INNER JOIN
Inv_AeX_AC_Identification ac
on ac.[_ResourceGuid] = vc.[Guid]
LEFT JOIN
vHWComputerSystem cs
ON cs.[_ResourceGuid] = vc.[Guid]
WHERE
    IsManaged =1
    and
    vc.[OS Name] not like '%windows server%'
    and
    (
        ARP.DisplayName LIKE 'Adobe Acrobat%'
                       
    )
        ORDER BY vc.[Name]
    

I think the bolded line is causing the problems. I'd only like to see Adobe Acrobat 8, 9, 10 and so forth. I don't wanrt to see updates which is what is happening.

Help is appreciated.
 

SK's picture

Have you tried the built-in Installed Software report?

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

Briandr88's picture

The query on the first post is the installed software report, but it gives a summary like such:

Name                                    Version  Company               Count
     
Adobe Acrobat Professional     8     Adobe Systems, Inc.     4
Adobe Acrobat Professional     9     Adobe Systems, Inc.     11
Adobe Acrobat Standard         7     Adobe Systems, Inc.     9
Adobe Acrobat Standard         8     Adobe Systems, Inc.     18
Adobe Acrobat Standard         9     Adobe Systems, Inc.     37
Adobe Acrobat X Standard             Adobe Systems, Inc.     2

I want a detailed report of computer names and such.
 

Briandr88's picture

The first query I posted above is the built in software report. Problem is its giving me a summary and not a more detailed report.

SK's picture

I believe that report allows you to drilldown to see the computers.

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

Briandr88's picture

I have to right click each line individually which then allows me to view the computers. The counts appear to be off. I will check again later see if I am missing something. Thanks

Briandr88's picture

I keep messing around with this and getting closer:

SELECT DISTINCT
vc.[Name] as [Computer Name]
,ac.[Last Logon User]
,arp.[DisplayName] AS [Product Name]
,arp.[DisplayVersion] AS [Product Version]
,vc.[OS Name] AS [Operating System]
FROM
Inv_AddRemoveProgram as arp
INNER JOIN
vComputer as vc
ON arp._ResourceGuid = vc.[Guid]
INNER JOIN
Inv_Aex_AC_TCPIP ip
ON vc.[Guid] = ip.[_ResourceGuid]
INNER JOIN
Inv_AeX_AC_Identification ac
on ac.[_ResourceGuid] = vc.[Guid]
LEFT JOIN
vHWComputerSystem cs
ON cs.[_ResourceGuid] = vc.[Guid]
WHERE
    IsManaged =1
    and
    arp.[DisplayVersion] is not null   
    and
    arp.[DisplayName] not like '%update%'
    and
    vc.[OS Name] not like '%windows server%'
    and
    
   (
        ARP.[DisplayName] LIKE 'Adobe Acrobat%'
                       
    )
 
   ORDER BY vc.[Name]
  

But I will see stuff like this show up twice for same computer:

Adobe Acrobat 7.0 Standard
Adobe Acrobat 7.1.4 Standard

I don't need to see the minor version. Any other ideas to streamline this query?

SK's picture

If you only want to see 7, then try using this line: 

,LEFT (arp.[DisplayVersion],1) AS [Product Version]

instead of

,arp.[DisplayVersion] AS [Product Version]

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