Video Screencast Help
Protect Your POS Environment Against Retail Data Breaches. Learn More.

count specific versions of the different products

Created: 21 May 2013 | 3 comments

Hi,

I am looking for a query that could be used as part of a report that could count specific versions of the different products as specified in the query? As an example I would count two versions of both Adobe Reader and Win Zip. I have an existing query, but I am not sure if it can be altered enough to accomplish what I am trying to do. I will post it anyways, but I suspect an entirely new twist would need to be done on it to accomplish what I am looking to do. By the way this query was provided to me and I can confirm it works in 7.0, but I am not sure with 7.1.

SELECT
    vCom.[Name] as [Computer Name]
    ,cs.[Model]
    ,cs.[Identifying Number] AS [Serial Number]
    ,cs.[Total Physical Memory (Bytes)]
    ,ac.[Last Logon User]
    ,[Sophos Installed] = (
CASE
WHEN sarp.[DisplayName] IS NULL THEN 'No'
ELSE 'Yes'
END
)
    ,[Sophos Version] = (
CASE
WHEN sarp.[DisplayVersion] IS NULL THEN ''
ELSE sarp.[DisplayVersion]
END
)
    ,vCom.[Domain] AS [Domain Name]
    ,vCom.[OS Name] AS [Operating System]
    ,vCom.[OS Revision] AS [Service Pack]
  
FROM
vComputer vCom -- 1-1
FULL JOIN CollectionMembership cm ON cm.[ResourceGuid] = vCom.[Guid] -- 1-Many
FULL JOIN vCollection vColl ON cm.[CollectionGuid] = vColl.[Guid] -- 1-Many
FULL JOIN Inv_AeX_AC_Identification ac on ac.[_ResourceGuid] = vCom.[Guid] -- 1-1
FULL JOIN vHWComputerSystem cs ON cs.[_ResourceGuid] = vCom.[Guid] --  1-1
    FULL JOIN
    (
        SELECT
            arp.*
        FROM
            Inv_AddRemoveProgram arp
        WHERE
            (
arp.[DisplayName] LIKE 'Sophos SafeGuard 6.00.0 Client'
OR
arp.[DisplayName] LIKE 'Sophos SafeGuard 6.00.1 Client'
)
    ) sarp ON sarp.[_ResourceGuid] = vCom.[Guid]
WHERE
vCom.[IsManaged] = 1
and
vColl.[Name] = 'Windows 2000/XP/2003/Vista/2008/7 Computers'
and
vCom.[Name] not like '%-%'
and
vCom.[IP Address] Not like '%10.4.254%'
and
vCom.[IP Address] Not like '%192.168%'
and
cs.[Identifying Number] is Not Null
ORDER BY vCom.[Name]

 

Any suggestions appreciated and thanks.

 

Operating Systems:

Comments 3 CommentsJump to latest comment

EugeneDisc's picture

Your query probably wont work in 7.1 as the table structure changed from 7.0 to 7.1, not much though...

Worth a shot non the less.

Have you managed to resolve this by now?

If not, would you like the report only to show counts of the different version?

OR Could you have a report that lists all versions and it can simply be exported to Excel for manipulation.

 

Eugene

KNP's picture

Hi,

Simply you can find in Reports-->Discovery and Inventory-->Inventory-->Cross-platform-->Software/Application-->Software-->Installed Software/Installed Software by Computer.

raju123's picture

Reports > Discovery and Inventory > Inventory > Cross-platform  Software/Applications > Software > Installed Software/Installed Software by Computer

You can find the particular software using % sign

eg -DisplayName LIKE 'Window%

https://www-secure.symantec.com/connect/forums/altiris-solution#comment-8699211