/build/static/layout/Breadcrumb_cap_w.png

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


0 Comments   [ + ] Show comments

Answers (1)

Posted by: paul.davis 5 years ago
White Belt
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




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

Don't be a Stranger!

Sign up today to participate, stay informed, earn points and establish a reputation for yourself!

Sign up! or login

Share

 
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