/build/static/layout/Breadcrumb_cap_w.png

Systems Management Question


Device Label not working when trying to filter for a software version

03/31/2020 366 views

I have some scripts for forcing my Office 365 ProPlus clients to update to the latest version.  I want to force them down to only the specific versions of Office 365 ProPlus that are less than 16.0.12527 by creating a Smart Label.

One of the challenges is how Microsoft does the versioning.  For instance 16.0.8625 is less than 16.0.12527 but in a SQL statement, 16.0.12527 is less than 16.0.8625.

I created a custom SQL Smart Label (See Below) to capture the 15.x and 16.x versions of Office that are below 16.0.12527.  If I run it a MySQL editor like FlySpeed query I see exactly the results I was expecting.  However KACE applies the label what appears to be every other device except the ones I am hoping to capture:

Select

  MACHINE.NAME As SYSTEM_NAME,

  MACHINE.SYSTEM_DESCRIPTION,

  MACHINE.IP,

  MACHINE.MAC,

  MACHINE.ID As TOPIC_ID

From

  MACHINE Inner Join

  MACHINE_SOFTWARE_JT On MACHINE.ID = MACHINE_SOFTWARE_JT.MACHINE_ID Inner Join

  SOFTWARE On MACHINE_SOFTWARE_JT.SOFTWARE_ID = SOFTWARE.ID

Where

  (SOFTWARE.DISPLAY_NAME Like '%Microsoft Office 365 ProPlus%' And

  SOFTWARE.DISPLAY_VERSION > 15 And

  SOFTWARE.DISPLAY_VERSION < '16.0.12527') Or

  (SOFTWARE.DISPLAY_NAME Like '%Microsoft Office 365 ProPlus%' And

  SOFTWARE.DISPLAY_VERSION < 17 And

  SOFTWARE.DISPLAY_VERSION > '16.0.2000')


I have also tried this by removing the highlighted section but I still get nearly all my computers picked up instead of the 65 out of 375 that should.  A lot of them are Servers that do not even meet the filter of having Office 365 ProPlus.


Answer Summary:
0 Comments   [ + ] Show comments

Comments


Answer Chosen by the Author

1

Here's what I came up with:

Select
  MACHINE.NAME As SYSTEM_NAME,
  MACHINE.SYSTEM_DESCRIPTION,
  MACHINE.IP,
  MACHINE.MAC,
  MACHINE.ID As TOPIC_ID,
  substring_index(SOFTWARE.DISPLAY_VERSION, '.', 1) as MAJORVERSION,
  substring_index(SOFTWARE.DISPLAY_VERSION, '.', -2) as MINORVERSION
From
  MACHINE 
  inner Join MACHINE_SOFTWARE_JT On MACHINE.ID = MACHINE_SOFTWARE_JT.MACHINE_ID 
  inner Join SOFTWARE On MACHINE_SOFTWARE_JT.SOFTWARE_ID = SOFTWARE.ID and SOFTWARE.DISPLAY_NAME like '%Microsoft Office 365 ProPlus%'
HAVING
MAJORVERSION = 16
and MINORVERSION < 12527


Answered 04/01/2020 by: chucksteel
Red Belt

 
This website uses cookies. By continuing to use this site and/or clicking the "Accept" button you are providing consent Quest Software and its affiliates do NOT sell the Personal Data you provide to us either when you register on our websites or when you do business with us. For more information about our Privacy Policy and our data protection efforts, please visit GDPR-HQ