I need to track old versions of software for my job. I need to report total installed instances and up to date instances. Adobe Flash Player and Adobe Air are tracked together. Currently we check a report and manually add up the numbers of each version. I also pull the old version to attempt manual remote patching.
Here is my Query statement to list all instances
select SMS_R_System.Name, SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName, SMS_G_System_ADD_REMOVE_PROGRAMS.Version from SMS_R_System inner join SMS_G_System_ADD_REMOVE_PROGRAMS on SMS_G_System_ADD_REMOVE_PROGRAMS.ResourceID = SMS_R_System.ResourceId where SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName like "%Adobe AIR%" and SMS_G_System_ADD_REMOVE_PROGRAMS.InstallDate is not null or SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName like "%Adobe Flash Player%" order by SMS_R_System.Name
That was not an issue but when I try to pull all too old versions it was not as easy. Acceptable versions are Adobe Air 19.0.0.190, Adobe Flash Player 18.0.0.241, and Adobe Flash Player 19.0.0.190.
select SMS_R_System.Name, SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName, SMS_G_System_ADD_REMOVE_PROGRAMS.Version from SMS_R_System inner join SMS_G_System_ADD_REMOVE_PROGRAMS on SMS_G_System_ADD_REMOVE_PROGRAMS.ResourceID = SMS_R_System.ResourceId where SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName like "%Adobe AIR%" and SMS_G_System_ADD_REMOVE_PROGRAMS.InstallDate is not null and (SMS_G_System_ADD_REMOVE_PROGRAMS.Version like "[0-9].%" or SMS_G_System_ADD_REMOVE_PROGRAMS.Version like "1[0-8].%" or SMS_G_System_ADD_REMOVE_PROGRAMS.Version like "19.0.0.[0-9]" or SMS_G_System_ADD_REMOVE_PROGRAMS.Version like "19.0.0.[0-9][0-9]" or SMS_G_System_ADD_REMOVE_PROGRAMS.Version like "19.0.0.1[0-8][0-9]") or SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName like "%adobe flash player%" and (SMS_G_System_ADD_REMOVE_PROGRAMS.Version like "[0-9].%" or SMS_G_System_ADD_REMOVE_PROGRAMS.Version like "1[0-7].%" or SMS_G_System_ADD_REMOVE_PROGRAMS.Version like "18.0.0.[0-9]" or SMS_G_System_ADD_REMOVE_PROGRAMS.Version like "18.0.0.[0-9][0-9]" or SMS_G_System_ADD_REMOVE_PROGRAMS.Version like "18.0.0.1[0-9][0-9]" or SMS_G_System_ADD_REMOVE_PROGRAMS.Version like "18.0.0.2[0-3][0-9]" or SMS_G_System_ADD_REMOVE_PROGRAMS.Version like "18.0.0.240" or SMS_G_System_ADD_REMOVE_PROGRAMS.Version like "19.0.0.[0-9]" or SMS_G_System_ADD_REMOVE_PROGRAMS.Version like "19.0.0.[0-9][0-9]" or SMS_G_System_ADD_REMOVE_PROGRAMS.Version like "19.0.0.1[0-7][0-9]" or SMS_G_System_ADD_REMOVE_PROGRAMS.Version like "19.0.0.18[0-4]") order by SMS_R_System.Name
This works but when the versions change it will be almost impossible to update without messing up. I pull both lists and compared them 4 or 5 times to find what I was missing.
Is there anyway is SCCM/MS SQL to just compare version numbers?
WHERE Version < 19.0.0.190
I tried a query based on this but it seems to compare per a digit or something to that effect. e.g. (7.2 > 7.11 or 7.0.1.0 > 16.0.0.111) I have not been able to find an SCCM specific solution yet. I would be shocked if MS does not support a better way.
This is just one example. There are a lot of programs we track some with #.# versions, some with #.#.#.# versions, and that subversion more or less depending on the version. A flexible solution would be amazing.
Sorry for the extremely bad formatting. I self teach and have not touched SQL since a two week course is MS access 3 years ago.
This is just a reply at a glance, but could you make a statement...
(version like 18.#.#.# and version > 18.0.0.184) or (version like 19.#.#.# and version > 19.0.0.190)