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.
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.
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!
Hint: you can create ticket rules like this even if you normally do not use the service desk module!
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.
Now hit the "Choose action" button and select "New (SQL)" then.
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!
Be sure the check box "Enabled" is checked if you plan to run this scheduled - if you prefer manual execution, uncheck it!
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)) |
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 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.
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!
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!
Also, this should work for macOS builds, too. - chucksteel 7 years ago
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
What version of SMA are you using? - chrpetri 6 years ago
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
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