/build/static/layout/Breadcrumb_cap_w.png
12/04/2018 107 views

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

0 Comments   [ + ] Show comments

Comments


All Answers

2

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



Answered 12/04/2018 by: paul.davis
White Belt

  • 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.