Searching For specific software within a Label
I am currently using the following script to find a list of machines with a certain
update installed on them.
select MACHINE.NAME as Computer_Name, SYSTEM_DESCRIPTION, IP, MAC, REPLACE(MACHINE.USER_LOGGED,'\\','\\\\') as USER_LOGGED, CS_DOMAIN from (SOFTWARE, MACHINE_SOFTWARE_JT, MACHINE) where MACHINE.ID = MACHINE_SOFTWARE_JT.MACHINE_ID and MACHINE_SOFTWARE_JT.SOFTWARE_ID = SOFTWARE.ID and SOFTWARE.DISPLAY_NAME like '%KB2661254%' order by DISPLAY_NAME
this works but I need to add a filter in so it just brings back results for the label EMEA.
Any suggestions?
Thanks,
Gary
Answers (2)
OK, it appears that Smart labels don't like table aliases - try this version, working here.
John
________________
SELECT MACHINE.NAME as Computer_Name, SYSTEM_DESCRIPTION, IP, MAC,
REPLACE(MACHINE.USER_LOGGED,'\\','\\\\') as USER_LOGGED, CS_DOMAIN
FROM MACHINE
JOIN MACHINE_SOFTWARE_JT ON (MACHINE_SOFTWARE_JT.MACHINE_ID = MACHINE.ID)
JOIN SOFTWARE ON (SOFTWARE.ID = MACHINE_SOFTWARE_JT.SOFTWARE_ID)
JOIN MACHINE_LABEL_JT ON (MACHINE_LABEL_JT.MACHINE_ID = MACHINE.ID)
JOIN LABEL ON (LABEL.ID = MACHINE_LABEL_JT.LABEL_ID)
WHERE DISPLAY_NAME like '%KB2661254%'
AND LABEL.NAME = 'EMEA'
ORDER BY DISPLAY_NAME
Try this.
John
______________
SELECT M.NAME as Computer_Name, SYSTEM_DESCRIPTION, IP, MAC,
REPLACE(M.USER_LOGGED,'\\','\\\\') as USER_LOGGED, CS_DOMAIN
FROM MACHINE M
JOIN MACHINE_SOFTWARE_JT JT ON (JT.MACHINE_ID = M.ID)
JOIN SOFTWARE S ON (S.ID = JT.SOFTWARE_ID)
JOIN MACHINE_LABEL_JT ML ON (ML.MACHINE_ID = M.ID)
JOIN LABEL L ON (L.ID = ML.LABEL_ID)
WHERE DISPLAY_NAME like '%KB2661254%'
AND L.NAME = 'EMEA'
ORDER BY DISPLAY_NAME
Comments:
-
No Joy Im afraid. - garycox 12 years ago
