Hello, 

We have a canned SQL report that works great but we want to limit it to certain machine labels. The canned SQL that works is:

SELECT PP.TITLE AS DISPLAY_NAME,

M.NAME AS ComputerName, IP

FROM PATCHLINK_MACHINE_STATUS MS

JOIN KBSYS.PATCHLINK_PATCH PP ON PP.UID = MS.PATCHUID

JOIN MACHINE M ON M.ID = MS.MACHINE_ID

WHERE MS.STATUS = 'NOTPATCHED'

ORDER BY ComputerName

 What should I add to make it only query computers in a given label? I found this which tries to specify patch label but I want machine label:

 

SELECT PP.TITLE AS DISPLAY_NAME
     , M.NAME AS ComputerName
     , M.IP
     , M.MAC
     , M.USER_LOGGED AS USER_LOGGED
FROM
  ORG1.PATCHLINK_MACHINE_STATUS MS
INNER JOIN KBSYS.PATCHLINK_PATCH PP
ON PP.UID = MS.PATCHUID
INNER JOIN ORG1.MACHINE M
ON M.ID = MS.MACHINE_ID
INNER JOIN ORG1.PATCHLINK_PATCH_LABEL_JT
ON PATCHLINK_PATCH_LABEL_JT.PATCHUID = PP.UID
INNER JOIN ORG1.LABEL
ON PATCHLINK_PATCH_LABEL_JT.LABEL_ID = LABEL.ID
WHERE
  MS.STATUS = 'NOTPATCHED'
  AND LABEL.NAME = 'Patching - Approved Windows 7 Critical Patches'
ORDER BY
  DISPLAY_NAME
0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Answers

1

Try adding this to your WHERE statement:

AND ((1  in (select 1 from LABEL, MACHINE_LABEL_JT where MACHINE.ID = MACHINE_LABEL_JT.MACHINE_ID
AND MACHINE_LABEL_JT.LABEL_ID = LABEL.ID  AND LABEL.TYPE <> 'hidden' and LABEL.NAME = 'your machine label here')) ) 

If that doesn't work, we can look at doing joins, but this works in my reports, for example:

_________________________________

*Title*
Server Warranties

*Category:
Servers (Custom)

*Description*
Dan's server info report.

*SQL Select Statement*
SELECT MACHINE.NAME AS SYSTEM_NAME,IP,SYSTEM_DESCRIPTION,OS_NAME,
CS_MANUFACTURER,CS_MODEL,PROCESSORS,RAM_TOTAL,
GROUP_CONCAT(DISTINCT MACHINE_DISKS.DISK_FREE SEPARATOR '\n') AS MACHINE_DISKS_DISK_FREE_GROUPED,
SUM(MACHINE_DISKS.DISK_SIZE) AS MACHINE_DISKS_DISK_SIZE
FROM MACHINE 
LEFT JOIN MACHINE_DISKS ON (MACHINE_DISKS.ID = MACHINE.ID)
WHERE ((1  in (select 1 from LABEL, MACHINE_LABEL_JT where MACHINE.ID = MACHINE_LABEL_JT.MACHINE_ID
AND MACHINE_LABEL_JT.LABEL_ID = LABEL.ID  AND LABEL.TYPE <> 'hidden' and LABEL.NAME = 'server')) ) 
GROUP BY MACHINE.ID
ORDER BY SYSTEM_NAME

Show Line Number Column:    x
_________________________________

Hope that helps!

John

Answered 06/15/2012 by: jverbosk
Red Belt

Please log in to comment
Answer this question or Comment on this question for clarity