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

0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Community Chosen Answer

1

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

Answered 11/02/2012 by: jverbosk
Red Belt

Please log in to comment

Answers

0

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
Answered 11/01/2012 by: jverbosk
Red Belt

  • No Joy Im afraid.
    • This content is currently hidden from public view.
      Reason: Removed by user request
      For more information, visit our FAQ's.
Please log in to comment
Answer this question or Comment on this question for clarity