/bundles/itninjaweb/img/Breadcrumb_cap_w.png
Because we are a multi office IT group, and we push software out by office, I need to track the progress of the install. So I added a "not assigned" sub-query to this. So it will list out computers that have not been assigned this MI - Not for use if your not planning to role out to everyone.
select *
from
(
select ST.DISPLAY_NAME as SOFTWARE_TITLE,
ST.NAME as MACHINE_NAME,
if (MA.ATTEMPT_COUNT = ST.MAX_ATTEMPT_COUNT, concat('FAILED (', MA.ATTEMPT_COUNT,' of ',ST.MAX_ATTEMPT_COUNT,')'),
if(MSJ.MACHINE_ID is null, concat('NOT INSTALLED (', if(MA.ATTEMPT_COUNT is null,0,MA.ATTEMPT_COUNT),' of ',ST.MAX_ATTEMPT_COUNT,')'),'INSTALLED')) as STATUS
from (select MI.ID as MI_ID, MI.MAX_ATTEMPT_COUNT, S.DISPLAY_NAME, S.ID as SOFTWARE_ID, M.NAME, M.ID as MACHINE_ID from MI, SOFTWARE S, LABEL L, MI_LABEL_JT MIJ, MACHINE M, MACHINE_LABEL_JT MJ, SOFTWARE_OS_JT SOJ
where S.ID = MI.SOFTWARE_ID
and L.ID = MIJ.LABEL_ID
and MI.ID = MIJ.MI_ID
and MJ.LABEL_ID = L.ID
and MJ.MACHINE_ID = M.ID
and TARGET_ALL_MACHINES = 0
and S.ID = MI.SOFTWARE_ID
and M.OS_ID = SOJ.OS_ID
and SOJ.SOFTWARE_ID = S.ID
and S.ID = 5619
union
select MI.ID, MI.MAX_ATTEMPT_COUNT, S.DISPLAY_NAME, S.ID as SOFTWARE_ID, M.NAME, M.ID from MI, SOFTWARE S, SOFTWARE_OS_JT SOJ
right outer join MACHINE M on M.ID is not null
where MI.TARGET_ALL_MACHINES = 1
and S.ID = MI.SOFTWARE_ID
and M.OS_ID = SOJ.OS_ID
and SOJ.SOFTWARE_ID = S.ID) as ST
left outer join MI_ATTEMPT MA on MA.MI_ID = ST.MI_ID and MA.MACHINE_ID = ST.MACHINE_ID
left outer join MACHINE_SOFTWARE_JT MSJ on MSJ.MACHINE_ID = ST.MACHINE_ID and MSJ.SOFTWARE_ID = ST.SOFTWARE_ID
order by STATUS, ST.MI_ID, ST.NAME
)as LABELYES
UNION ALL
SELECT * FROM (

select (SELECT S.DISPLAY_NAME FROM SOFTWARE S WHERE S.ID = 5619) as SOFTWARE_TITLE,
ST.NAME as MACHINE_NAME,
concat('Not Assigned') as STATUS
FROM(
SELECT MACHINE.NAME, SOFTWARE.DISPLAY_NAME
FROM MACHINE
JOIN MACHINE_SOFTWARE_JT on MACHINE.ID = MACHINE_SOFTWARE_JT.MACHINE_ID
JOIN SOFTWARE on MACHINE_SOFTWARE_JT.SOFTWARE_ID = SOFTWARE.ID
WHERE MACHINE.ID NOT IN(select M.ID
from MI, SOFTWARE S, LABEL L, MI_LABEL_JT MIJ, MACHINE M, MACHINE_LABEL_JT MJ, SOFTWARE_OS_JT SOJ
where S.ID = MI.SOFTWARE_ID
and L.ID = MIJ.LABEL_ID
and MI.ID = MIJ.MI_ID
and MJ.LABEL_ID = L.ID
and MJ.MACHINE_ID = M.ID
and S.ID = MI.SOFTWARE_ID
and M.OS_ID = SOJ.OS_ID
and SOJ.SOFTWARE_ID = S.ID
AND S.ID = 5619
)
GROUP BY MACHINE.ID
) AS ST
)AS LABELNO

ORDER BY STATUS, MACHINE_NAME
0 Comments   [ - ] Hide 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.
Answer this question or Comment on this question for clarity

Answers

0
oh, change your S.ID to whatever the software, not MI id is. There is 3 of them
Answered 01/18/2010 by: dtuttle
Purple Belt

Please log in to comment