My addition to Managed Insatll status
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
[ + ] Show comments
Answers (1)
Please log in to answer
Posted by:
dtuttle
14 years ago
oh, change your S.ID to whatever the software, not MI id is. There is 3 of them
Rating comments in this legacy AppDeploy message board thread won't reorder them,so that the conversation will remain readable.