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:
SELECT distinct
M.NAME as SYSTEM_NAME
FROM
MACHINE M,
PATCHLINK_SCHEDULE_MACHINE_STATUS PSMS,
PATCHLINK_SCHEDULE PS,
PATCHLINK_SCHEDULE_RUN_MACHINE PSRM,
PATCHLINK_SCHEDULE_RUN PSR
WHERE
M.ID=PSMS.MACHINE_ID
AND PS.ID=PSMS.PATCHLINK_SCHEDULE_ID
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.PATCHLINK_SCHEDULE_RUN_ID=PSR.ID
AND PSR.PATCHLINK_SCHEDULE_ID=PS.ID
AND PSRM.MACHINE_ID=M.ID
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
Many thanks
Answers (1)
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 :)
SELECT distinct
MACHINE.NAME as SYSTEM_NAME, MACHINE.ID as TOPIC_ID
FROM
MACHINE,
PATCHLINK_SCHEDULE_MACHINE_STATUS,
PATCHLINK_SCHEDULE,
PATCHLINK_SCHEDULE_RUN_MACHINE,
PATCHLINK_SCHEDULE_RUN
WHERE
MACHINE.ID=PATCHLINK_SCHEDULE_MACHINE_STATUS.MACHINE_ID
AND PATCHLINK_SCHEDULE.ID=PATCHLINK_SCHEDULE_MACHINE_STATUS.PATCHLINK_SCHEDULE_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.PATCHLINK_SCHEDULE_RUN_ID=PATCHLINK_SCHEDULE_RUN.ID
AND PATCHLINK_SCHEDULE_RUN.PATCHLINK_SCHEDULE_ID=PATCHLINK_SCHEDULE.ID
AND PATCHLINK_SCHEDULE_RUN_MACHINE.MACHINE_ID=MACHINE.ID
AND PATCHLINK_SCHEDULE_RUN_MACHINE.STATUS ='error'
ORDER BY PATCHLINK_SCHEDULE.DESCRIPTION, MACHINE.NAME ASC
Comments:
-
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 5 years ago