Client Management Suite

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.

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.
 

Have you tried the built-in Installed Software report?

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.
 

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.

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

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?

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]