/build/static/layout/Breadcrumb_cap_w.png

Automatically enable new Windows 10 builds in KACE K1000 SMA

Hi everybody,

since Microsoft changed its Windows lifecycle policy in Windows 10 to release a new OS build about twice a year (more information here) you may get a mix of different builds in your network inventory after some time.
Quests KACE SMA (aka K1000) recognizes every Windows 10 build as a new, independent OS in most parts of the appliance software.
That's quite helpful in some usage scenarios and in some it is not.

When I recently added a new Windows 10 1703 machine for testing purposes, it did not receive any managed installs and custom inventory rules at all - because "Microsoft Windows 10 Pro x64 (10.0.15063)" as it is called in the OS list was not enabled yet for all the software installers and custom inventory objects in our KACE SMA.
So I had to edit all these items manually and add Windows 10 1703 to the list of supported operating systems - what took quite a while.
k8xjVD.jpeg

If you (like me) don't want to do that every time a new Windows 10 build appears on your network here is way to avoid it:

Create a ticket rule that automatically adds missing Windows 10 builds to all your software installers and custom inventory rules!

Note: the following instructions are provided without any warranty, make backups, test carefully and use this at your own risk!

1. Go to "Configuration" section of your Service Desk module and to the "Rules" then
Hint: you can create ticket rules like this even if you normally do not use the service desk module!
yfoPGT.jpeg

2. If you want, switch to the service desk queue where you want to create the rule in - but it does not really matter which one it is since this one does not change any tickets at all. In this example we stay in the default queue.
Now hit the "Choose action" button and select "New (SQL)" then.
E61KMr.jpeg

3. Enter a name for the rule like "Enable missing Windows 10 builds".
Be sure the check box "Enabled" is checked if you plan to run this scheduled - if you prefer manual execution, uncheck it!
Dakfob.jpeg
In the "Select SQL" section, write this:
SELECT 1 AS 'HD_TICKET.ID'
4. Leave all the following options unchecked except "Run update query". In this box, enter this:
INSERT INTO SOFTWARE_OS_JT 
SELECT
  softw.ID soID,
  ost.ID AS osID
FROM
  OPERATING_SYSTEMS ost,
  SOFTWARE softw
WHERE
  (softw.FILE_NAME <> '' OR softw.INVENTORY_RULE <> '') AND
  ost.NAME LIKE '%Windows 10 %' AND
  ost.ID NOT IN (SELECT
    softOSJT.OS_ID
  FROM
    SOFTWARE_OS_JT softOSJT
  WHERE
    softOSJT.SOFTWARE_ID = softw.ID
) AND
  ost.ID IN (SELECT
    machOS.OS_ID
  FROM
    MACHINE machOS
  GROUP BY
    machOS.OS_ID
) AND
  softw.ID IN (SELECT
    softOSJT.SOFTWARE_ID
  FROM
    SOFTWARE_OS_JT softOSJT
    INNER JOIN OPERATING_SYSTEMS osNAMES ON softOSJT.OS_ID = osNAMES.ID
  WHERE
    softOSJT.SOFTWARE_ID = softw.ID AND
    osNAMES.NAME LIKE '%Windows 10 %' AND
    osNAMES.ID IN
(SELECT
      machOS2.OS_ID
    FROM
      MACHINE machOS2
    GROUP BY
      machOS2.OS_ID
))

Some explanation:
Take care of software objects with file attachments (installers) or custom inventory rules
Only select OS that are not already in the list of enabled OS for this software
Only select OS that are currently present in your active inventory - we don't want abandoned builds
Only select software items that already have at least one Windows 10 build enabled and limit this list to active inventory builds as well

5. Now set your schedule in the last section below. "15 minutes" is the shortest interval to choose, I personally run this once every hour. This query should not cause much impact on your appliance database performance, but you should test this in your environment.
If you prefer to run this manually, leave the "Schedule" section and disable the rule. You can still run it by hitting the "Run Now" button on demand.
Don't forget to save your work by hitting the "Save" button!
FVw9rE.jpeg

That's it! Carefully test this (make a backup!!), the "Last run log" section in the ticket rule editor shows you the last query results with a number of all the newly inserted software/OS relations and any other output of the database engine.

Enjoy!

Comments

  • Great tip ! thank you ! - gwir 7 years ago
  • For those interested in testing this before putting it into production, you can change the two instances of softw.id to the ID of a particular software title and only that title will be changed.

    Also, this should work for macOS builds, too. - chucksteel 7 years ago
  • Any idea what could cause the error below in the log when I run this? Copied the syntax exactly, no changes.

    01/08/2018 11:53:48> Starting: 01/08/2018 11:53:48 01/08/2018 11:53:48> Executing Select Query... 01/08/2018 11:53:48> selected 1 rows 01/08/2018 11:53:48> Executing Update Query... 01/08/2018 11:53:48> mysqli error: [1064: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '.ID soID,   ost.ID AS osID FROM   OPERATING_SYSTEMS ost,   SOFTWARE softw WHE' at line 3] in EXECUTE("INSERT INTO SOFTWARE_OS_JT  SELECT   softw.ID soID,   ost.ID AS osID FROM   OPERATING_SYSTEMS ost,   SOFTWARE softw WHERE   (softw.FILE_NAME <> '' OR softw.INVENTORY_RULE <> '') AND   ost.NAME LIKE '%Windows 10 %' AND   ost.ID NOT IN (SELECT     softOSJT.OS_ID   FROM     SOFTWARE_OS_JT softOSJT   WHERE     softOSJT.SOFTWARE_ID = softw.ID) AND   ost.ID IN (SELECT     machOS.OS_ID   FROM     MACHINE machOS   GROUP BY     machOS.OS_ID) AND   softw.ID IN (SELECT     softOSJT.SOFTWARE_ID   FROM     SOFTWARE_OS_JT softOSJT     INNER JOIN OPERATING_SYSTEMS osNAMES ON softOSJT.OS_ID = osNAMES.ID   WHERE     softOSJT.SOFTWARE_ID = softw.ID AND     osNAMES.NAME LIKE '%Windows 10 %' AND     osNAMES.ID IN (SELECT       machOS2.OS_ID     FROM       MACHINE machOS2     GROUP BY       machOS2.OS_ID))") 01/08/2018 11:53:48> Ending: 01/08/2018 11:53:48 - erzeszut 6 years ago
    • I copy/pasted your query directly from your error message and it worked in my SMA (v7.2.102).
      What version of SMA are you using? - chrpetri 6 years ago
      • I'm on 7.2.101, which I assume is same as yours (there isn't a .102 as far as I know). - erzeszut 6 years ago
      • I think you need to install https://support.quest.com/kb/231874 to update your appliance to v7.2.102 - chrpetri 6 years ago
    • Could you try to copy and paste the source code from this post to a plain text editor like notepad and then copy it from there to your "Update SQL" text box in the ticket rule editor? Just to be sure there are no invisible chars ruining the query code. - chrpetri 6 years ago
      • A good thought - could have easily been a problem with the single quotes or something, but same result! So strange. - erzeszut 6 years ago
      • Unfortunately I do not have any others tangible ideas yet... but you could try the following:
        Get a SQL query builder/analyzer like FlySpeed SQL Query (https://www.activedbsoft.com/overview-querytool.html, free version is sufficient) you feel comfortable with, enable external database access on your SMA if you didn't already do so (http://www.itninja.com/question/connecting-to-back-end-sql-kace-1000) and connect to your SMA database with that SQL query builder/analyzer tool.
        Open a new query and insert the update SQL query from the post above *EXCEPT* the first line ("INSERT INTO SOFTWARE_OS_JT").
        This should a) provide a list of the IDs that should be inserted in the SOFTWARE_OS_JT table or b) at least not throw an error when executing.
        Don't worry, you cannot damage the SQL database of the SMA, its always write protected when accessing it by using the external database access. - chrpetri 6 years ago
  • Hi,
    You can change the resquest like this :

    INSERT INTO SOFTWARE_OS_JT
    SELECT
    null,
    softw.ID soID,
    'BUILD',
    ost.OS_FAMILY_ID,
    ost.OS_PRODUCT_ID,
    ost.ARCH,
    ost.ID AS osID
    FROM
    OPERATING_SYSTEMS ost,
    SOFTWARE softw
    WHERE
    (softw.FILE_NAME <> '' OR softw.INVENTORY_RULE <> '') AND
    ost.NAME LIKE '%Windows 10 %' AND
    ost.ID NOT IN (SELECT
    softOSJT.OS_ID
    FROM
    SOFTWARE_OS_JT softOSJT
    WHERE
    softOSJT.SOFTWARE_ID = softw.ID) AND
    ost.ID IN (SELECT
    machOS.OS_ID
    FROM
    MACHINE machOS
    GROUP BY
    machOS.OS_ID) AND
    softw.ID IN (SELECT
    softOSJT.SOFTWARE_ID
    FROM
    SOFTWARE_OS_JT softOSJT
    INNER JOIN OPERATING_SYSTEMS osNAMES ON softOSJT.OS_ID = osNAMES.ID
    WHERE
    softOSJT.SOFTWARE_ID = softw.ID AND
    osNAMES.NAME LIKE '%Windows 10 %' AND
    osNAMES.ID IN (SELECT
    machOS2.OS_ID
    FROM
    MACHINE machOS2
    GROUP BY
    machOS2.OS_ID))

    Regards. - lefeuvre 6 years ago
This post is locked
 
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