/build/static/layout/Breadcrumb_cap_w.png

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,


0 Comments   [ + ] Show comments

Answers (2)

Answer Summary:
Posted by: chucksteel 3 years ago
Red Belt
1

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 3 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 3 years ago
    • Works flawlessly. Exactly what I was looking for. Thank you Chuck, - jayddd 3 years ago
Posted by: Hobbsy 3 years ago
Red Belt
0

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

 
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