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.
Community Chosen Answer
OK, it appears that Smart labels don't like table aliases - try this version, working here.
SELECT MACHINE.NAME as Computer_Name, SYSTEM_DESCRIPTION, IP, MAC,
REPLACE(MACHINE.USER_LOGGED,'\\','\\\\') as USER_LOGGED, CS_DOMAIN
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