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.
The report sql is over complicated, not least because the select distinct line will only select a single record.
you have to think what the purpose of the smart label is, in this case , to add a machine to a group, so maybe
SELECT ID from MACHINE
i suggest you create a basic device smart label, check what field the select statement is using and then adjust you report sql to match the smart label sql format
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'