/build/static/layout/Breadcrumb_cap_w.png

Apply new OS platform to existing software inventory in the KACE K1000

I have been several times in the situation with customers asking me how to update the list of supported Operating Systems for existing Software inventory items having either a Managed Installation or a custom Inventory rule. There is no way to select multiple Software items in the K1000 Inventory and modify the list of supported Operating Systems when a new Operating System comes into Inventory.

An example would be an environment with Windows 7 / 8 clients that will be reimaged with Windows 10. In this case every single Software must be modified manually to have the reimaged computers install the same Software they had before.

For this scenario I have created a ticket rule that can be used to accomplish this. It filters Software inventory items by looking for at least one existing Managed Installation per Software or a Manual Software (which in general means that the Software has a Custom Inventory rule). All 32 bit Software products will be enabled for 32 and 64 bit, the 64 bit ones only for 64 bit.


It is important to create a database backup before creating and executing the ticket rule.


The following example is a ticket rule that will search for all Software entries having Windows 7 and Windows 8 as supported platforms enabled. It will the update the list and add Windows 10 as platform.


  • Open the K1000 Servicedesk  - Configuration - Rules
  • Click "Choose Action" - "New (SQL"
  • Choose a Name e.g. "Update OS for SOFTWARE TITLES"
  • Copy the following Select Query to the "Select SQL" section: select 1 AS 'TICKET.ID' 
  • Check "Run update query" and use the following "Update SQL" command:


insert into SOFTWARE_OS_JT
# x86 architecture
select distinct SOFTWAREID, OPERATING_SYSTEMS.ID from
(select SOFTWARE.ID AS SOFTWAREID from SOFTWARE JOIN SOFTWARE_OS_JT ON SOFTWARE.ID = SOFTWARE_OS_JT.SOFTWARE_ID
join OPERATING_SYSTEMS ON SOFTWARE_OS_JT.OS_ID = OPERATING_SYSTEMS.ID
# filter the 32 bit supported OSs to copy, 32 bit will be copied to 32 and 64 bit
and  (OPERATING_SYSTEMS.COMMON_NAME like 'Microsoft Windows 7%' or OPERATING_SYSTEMS.COMMON_NAME like 'Microsoft Windows 8%')
and OPERATING_SYSTEMS.ARCH = 'x86'
)
) dt, OPERATING_SYSTEMS
# filter the target OS by name
where OPERATING_SYSTEMS.COMMON_NAME like 'Microsoft Windows 10%'
and SOFTWAREID not in
(
select SOFTWARE_OS_JT.SOFTWARE_ID from SOFTWARE_OS_JT join OPERATING_SYSTEMS on SOFTWARE_OS_JT.OS_ID = OPERATING_SYSTEMS.ID
and SOFTWARE_OS_JT.OS_ID  IN

(select ID FROM OPERATING_SYSTEMS where OPERATING_SYSTEMS.COMMON_NAME like 'Microsoft Windows 10%')
)
and (SOFTWAREID IN (Select SOFTWARE_ID FROM MI) or SOFTWAREID IN (Select ID FROM SOFTWARE WHERE IS_MANUAL = 1))
union
# x64 architecture
select distinct SOFTWAREID, OPERATING_SYSTEMS.ID from
(select SOFTWARE.ID AS SOFTWAREID from SOFTWARE JOIN SOFTWARE_OS_JT ON SOFTWARE.ID = SOFTWARE_OS_JT.SOFTWARE_ID
join OPERATING_SYSTEMS ON SOFTWARE_OS_JT.OS_ID = OPERATING_SYSTEMS.ID
# filter the 32 bit supported OSs to copy, 64 bit will be copied to 64 bit only
and  (OPERATING_SYSTEMS.COMMON_NAME like 'Microsoft Windows 7%' or OPERATING_SYSTEMS.COMMON_NAME like 'Microsoft Windows 8%')
and OPERATING_SYSTEMS.ARCH = 'x64'
) dt, OPERATING_SYSTEMS where OPERATING_SYSTEMS.COMMON_NAME like 'Microsoft Windows 10%' and OPERATING_SYSTEMS.ARCH = 'x64'
and SOFTWAREID not in
(
select SOFTWARE_OS_JT.SOFTWARE_ID from SOFTWARE_OS_JT join OPERATING_SYSTEMS on SOFTWARE_OS_JT.OS_ID = OPERATING_SYSTEMS.ID
and SOFTWARE_OS_JT.OS_ID  IN (select ID FROM OPERATING_SYSTEMS
# filter the target OS by name and platform
where OPERATING_SYSTEMS.COMMON_NAME like 'Microsoft Windows 10%' and ARCH = 'x64')
)
and (SOFTWAREID IN (Select SOFTWARE_ID FROM MI) or SOFTWAREID IN (Select ID FROM SOFTWARE WHERE IS_MANUAL = 1))


Comments

  • When running this in 7.1 to get the new versions of Windows 10 selected I get "mysqli error: [1248: Every derived table must have its own alias]" - jmahoney 6 years ago
This post is locked

Don't be a Stranger!

Sign up today to participate, stay informed, earn points and establish a reputation for yourself!

Sign up! or login

Share

 
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