Trying to use Patching Report SQL Query as Smart Label
Hello,
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.
Any ideas?
Thanks all,
Answers (2)
Top Answer
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'
Comments:
-
Good man Chuck, I was on hols so didn’t have access to everything I would have wanted!! - Hobbsy 4 years ago
-
Hey Chuck! Thanks for this. I created a Smart Label with some basic parameters (Windows 10, >24 hr uptime) and edit the SQL to the block you provided. I had the same results in which the Smart Label was no longer able to give me a list view of devices that would meet the criteria. I do only have 1 machine that would meet this labels critera but has not had an inventory. This would explain why the label would still show 0 devices.
I had just figured it would have given me a similar view like in the "Advanced Search" section in the Devices section. Will be testing my theory with a test machine and reporting back with results. - jayddd 4 years ago-
Works flawlessly. Exactly what I was looking for. Thank you Chuck, - jayddd 4 years ago
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
