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))