Systems Management

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