Specifying a label in a SQL report
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
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
