Client Management Suite

How to  create the filter expressins for computer report show the software later version .

 

e.g.

Software Name : ABC

Version : 12.02.01.0

Installed Date : 15/06/2012

 

Software Name : ABC

Version : 12.02.02.0

Installed Date : 16/06/2012

 

Software Name : ABC

Version : 12.02.03.0

Installed Date : 17/06/2012

 

I want report show the 12.02.03.0 version software only

Its pretty easy as this data is held in the Inv_AddRemoveProgram Table.

For a filter you would use something like:

select  sw.[_ResourceGuid]
from
Inv_AddRemoveProgram sw
where
sw.DisplayName like 'ABC'
AND sw.DisplayVersion = '12.02.03.0'
and sw.InstallFlag=1

For a report you would write something like:

SELECT
   [vri2_Computer].[Guid] AS [_ItemGuid],
   [vri2_Computer].[Name],
   [dca3_AddRemoveProgram].[DisplayName],
   [dca3_AddRemoveProgram].[InstallDate],
   [dca3_AddRemoveProgram].[DisplayVersion],
   [ajs5_vHWComputerSystem].[Model],
   [dca4_AeX AC Identification].[Hardware Serial Number]
FROM
   [vRM_Computer_Item] AS [vri2_Computer]
      LEFT OUTER JOIN [Inv_AddRemoveProgram] AS [dca3_AddRemoveProgram]
         ON ([vri2_Computer].[Guid] = [dca3_AddRemoveProgram].[_ResourceGuid])
      LEFT OUTER JOIN [Inv_AeX_AC_Identification] AS [dca4_AeX AC Identification]
         ON ([vri2_Computer].[Guid] = [dca4_AeX AC Identification].[_ResourceGuid])
      LEFT OUTER JOIN [vHWComputerSystem] AS [ajs5_vHWComputerSystem]
         ON ([vri2_Computer].[Guid] = [ajs5_vHWComputerSystem].[_ResourceGuid])
WHERE [dca3_AddRemoveProgram].[DisplayName] LIKE 'ABC'
AND [dca3_AddRemoveProgram].[DisplayVersion] = '12.02.03.0'

 

Try this:

SELECT T1.[Name] AS 'Name', T2.[ProductName] AS 'ProductName', T2.[ProductVersion] AS 'ProductVersion', T0.[Guid] FROM ( [vResourceEx] T0 INNER JOIN [Inv_AeX_AC_Identification] T1 ON T0.[Guid] = T1.[_ResourceGuid] ) INNER JOIN [Inv_AeX_SW_Audit_Software] T2 ON T1.[_ResourceGuid] = T2.[_ResourceGuid] WHERE T2.[ProductName] = 'ABC' AND T2.[ProductVersion] = '12.02.03.0' AND T0.[ResourceTypeGuid] = '493435f7-3b17-4c4c-b07f-c23e7ab7781f' ORDER BY T0.[Name] ASC

The above two reports aren't what the OP is looking for, from what I understand.  It looks like they want the maximum installed version of any program on any computer.  So the query needs to accommodate any software versioning.  The problem is that software versions are string (text) data in the registry, and therefore in the CMDB.  That means you can't use MAX(arp.DisplayVersion) or similar methods to determine the maximum version.

You can create a custom inventory for software that gathers VersionMajor and VersionMinor.  But often that wouldn't help in your example, since you would just see all of them as Major 12 and Minor 2.

What are you trying to do where you need to know the most recent version installed?

You could create a Count of Installed by displayVersion ordered by displayVersion report and then drill down in to a report that shows whatever you've selected to drill down to, but that still doesn't fit what he's asking for.

 

I cant look it up now, but ian_a tkin had an article with a nice sql function in it to compare two string-based version strings; I believe it was one if his "better software delivery" articles. Might work with a bit of tweaking in 7.1.

Excellent.  Yes, this is the link:

https://www-secure.symantec.com/connect/articles/building-better-collections-software-delivery-part-2

I'm at the end of my work day but I'm definitely reading through this tonight on the iPad.  I will play and report back tomorrow.

Here's a possible answer: