SQL - Machine Label Software Label
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
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
Answers (2)
Please log in to answer
Posted by:
GillySpy
12 years ago
this should go in the reports forum....
can you clarify what you are asking? I could interpret this a couple of ways:
can you clarify what you are asking? I could interpret this a couple of ways:
- List machines that are in a label X AND have software from label Y
- list machines that are in label X and list all the software they have in label Y
- list machines that are in label x and y that have software in x and y
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
Rating comments in this legacy AppDeploy message board thread won't reorder them,so that the conversation will remain readable.