Trying to use Patching Report SQL Query as Smart Label
I am using KACE K1000 and there is a native Patching report for "Devices Needing Reboot' based off their patch schedules patch status saying "reboot pending".
SQL Query below:
select DISTINCT NAME, IP, LAST_SYNC, UPTIME from MACHINE join KBSYS.KONDUCTOR_TASK KT on KT.KUID = MACHINE.KUID and KT.TYPE like 'kpatch%' and KT.PHASE ='reboot pending' order by MACHINE.NAME
When copying and pasting this into a Smart Label SQL editor, the smart label no longer appears as a valid smart label capable of being filtered via the Device Inventory page. Additionally, it seems when a device checks in, they are automatically added to this "Test Label", I presume due to the first line in the SQL block.
Is this device smart label not possible due to the KT.PHASE criteria not existing within the same table, .dbo.MACHINE? I am still working with KACE support on getting SQL access for this very reason, however I was hoping there was an intuitive way of making this work.
Answer Chosen by the Author
Here is the query for my Needs Patching Restart smart label:
SELECT MACHINE.NAME AS SYSTEM_NAME, SYSTEM_DESCRIPTION, MACHINE.IP, MACHINE.MAC, MACHINE.ID as TOPIC_ID FROM MACHINE
join KBSYS.KONDUCTOR_TASK KT on KT.KUID = MACHINE.KUID and KT.TYPE like 'kpatch%'
WHERE KT.PHASE ='reboot pending'