I can't seem to get a report working that displays the software by software label and machine label. Any suggestions?

Thanks,


Select CONCAT(MACHINE.NAME, "\\", MACHINE.SYSTEM_DESCRIPTION) as MACHINE_NAME, DISPLAY_NAME, DISPLAY_VERSION, PUBLISHER from SOFTWARE, MACHINE_SOFTWARE_JT, MACHINE , MACHINE_LABEL_JT, LABEL, SOFTWARE_LABEL_JT
where
MACHINE.ID = MACHINE_SOFTWARE_JT.MACHINE_ID and
SOFTWARE.ID = MACHINE_SOFTWARE_JT.SOFTWARE_ID and
MACHINE.ID=MACHINE_LABEL_JT.MACHINE_ID and
MACHINE_LABEL_JT.LABEL_ID = LABEL.ID and
SOFTWARE.ID = SOFTWARE_LABEL_JT.SOFTWARE_ID and
SOFTWARE_LABEL_JT.LABEL_ID = LABEL.ID
and (LABEL.NAME in( 'Microsoft_Office_Standard_2010') and LABEL.NAME in( 'Colorado'))
and not IS_PATCH
order by MACHINE_NAME, DISPLAY_NAME
0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Rating comments in this legacy AppDeploy message board thread won't reorder them,
so that the conversation will remain readable.

Answers

0
this should go in the reports forum....

can you clarify what you are asking? I could interpret this a couple of ways:
  1. List machines that are in a label X AND have software from label Y
  2. list machines that are in label X and list all the software they have in label Y
  3. list machines that are in label x and y that have software in x and y
I think what you want is #1. That is here:

SELECT M.NAME MACHINE_NAME, DISPLAY_NAME, DISPLAY_VERSION, PUBLISHER
FROM
SOFTWARE S
JOIN SOFTWARE_LABEL_JT SLJ ON SLJ.SOFTWARE_ID=S.ID
JOIN LABEL SL ON SL.ID=SLJ.LABEL_ID
JOIN MACHINE_SOFTWARE_JT MS ON MS.SOFTWARE_ID = S.ID
JOIN MACHINE M ON M. ID = MS.MACHINE_ID
JOIN MACHINE_LABEL_JT MLJ ON MLJ.MACHINE_ID=M.ID
JOIN LABEL ML ON ML.ID=MLJ.LABEL_ID
WHERE NOT IS_PATCH and
ML.NAME='adobe machines'
and SL.NAME='SW Label 1'
ORDER BY M.NAME,1,2 asc
Answered 10/03/2011 by: GillySpy
Seventh Degree Black Belt

Please log in to comment
0
That is exactly what I needed.

Thanks,

Dave
Answered 10/03/2011 by: dav.a.anderson
Orange Belt

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