Converting SQL Queries to Smart Labels
I wrote a little query to find machine which were targeted in the last week, but received fewer than 10 patches.
This is so that I can create a smart Label to use for re-targeting the patch job.thing to find machines which would require re-patching:
M.NAME as SYSTEM_NAME
AND PS.PATCH_ACTION =3
AND DATEDIFF(NOW(),PSMS.LAST_RUN) < 7
AND PS.DESCRIPTION NOT LIKE ('AD-HOC%')
AND PSMS.PATCHED < 10
AND PSRM.STATUS ='error'
ORDER BY PS.DESCRIPTION, M.NAME ASC
Now this works beautifully in SQL Workbench, and also when I run it as a report, BUT when I try to create a smart label ( to retarget my patching), the smart label gets created, but nothing checks in, even though I know from the query results that there should be 200 odd devices in there.
I forced inventory on 5 machines to try and get them to apper in the label.
What is the magic trick into converting the working SQL query ( I even uses AS SYSTEM_NAME for consistency) into a WORKING smart label?
I must be missing a trick
I read elsewhere that you cannot use abbreviations for table names in the smart label, and you must include
MACHINE_ID as TOPIC_ID in the select clause:
Here is the modified SQL - and IT WORKS as a SMART LABEL :)
MACHINE.NAME as SYSTEM_NAME, MACHINE.ID as TOPIC_ID
AND PATCHLINK_SCHEDULE.PATCH_ACTION =3
AND DATEDIFF(NOW(),PATCHLINK_SCHEDULE_MACHINE_STATUS.LAST_RUN) < 7
AND PATCHLINK_SCHEDULE_MACHINE_STATUS.PATCHED < 10
AND PATCHLINK_SCHEDULE_RUN_MACHINE.STATUS ='error'
ORDER BY PATCHLINK_SCHEDULE.DESCRIPTION, MACHINE.NAME ASC
As a general piece of advice, I normally create a smart label using the wizard and then edit the SQL leaving the select clause alone and just changing the FROM and WHERE clauses. - chucksteel 4 years ago