K1000 Reports - Tracking Managed Installs
___________________________________________________________________________________

These four SQL reports allow the tracking of Managed Installs based on specified criteria.  Sometimes it's nice having a list of everything, but more frequently I need to check the status of things in progress or see what failed.  I've truncated example output so you can get a sample of all of the different statuses each report contains.

Just for the record, these are basically my tweaks on dchristian's Managed Installs report here:

http://www.itninja.com/question/managed-install-report

SELECT DISTINCT DATE_FORMAT(MI.CREATED,'%Y/%m/%d') as CREATED_DATE, S.DISPLAY_NAME, S.DISPLAY_VERSION,
CASE WHEN MS.MACHINE_ID > 0 THEN 'Installed'
WHEN MIA.ATTEMPT_COUNT > 0 THEN CONCAT('Not Installed (',MIA.ATTEMPT_COUNT,' of ', MI.MAX_ATTEMPT_COUNT,' attempts)')
ELSE 'Not Installed'
END  AS STATUS,
M.NAME, M.IP, M.LAST_SYNC, M.USER_NAME
FROM  SOFTWARE S
JOIN MI ON ( S.ID = MI.SOFTWARE_ID )
JOIN MI_LABEL_JT MIL ON ( MI.ID = MIL.MI_ID )
JOIN LABEL L ON ( MIL.LABEL_ID = L.ID )
JOIN MACHINE_LABEL_JT ML ON ( L.ID = ML.LABEL_ID )
JOIN MACHINE M ON ( ML.MACHINE_ID = M.ID )
JOIN SOFTWARE_OS_JT SO ON ( SO.SOFTWARE_ID = S.ID AND SO.OS_ID = M.OS_ID )
LEFT JOIN MACHINE_SOFTWARE_JT MS ON ( M.ID = MS.MACHINE_ID AND MS.SOFTWARE_ID = S.ID )
LEFT JOIN MI_ATTEMPT MIA ON ( MIA.MI_ID = MI.ID AND MIA.MACHINE_ID = M.ID )
ORDER  BY S.DISPLAY_NAME, S.DISPLAY_VERSION, STATUS
___________________________________________________________________________________

Notes on some of the CASE and WHERE statements I added to the reports:

MIA.ATTEMPT_COUNT = MI.MAX_ATTEMPT_COUNT means the maximum attempt count has been reached, which would indicate that the Managed Install failed.

MI.ENABLED != 0 means only enabled Managed Installs will be listed.

MI.UNINSTALL = 1 means the Managed Install is an uninstall-type Managed Install.
___________________________________________________________________________________
___________________________________________________________________________________

*Title*
Managed Installs - All Deployments

*Report Category*
Managed Installs (Custom)

*Description*
Lists all managed installs with status (Installed, Not Installed, Failed, Failed to Uninstall).  The 'Not Installed' status indicates the Managed Install has not started on the specified machines.

*SQL Select Statement*
select S.DISPLAY_NAME as MANAGED_INSTALL, S.DISPLAY_VERSION as SOFTWARE_VERSION, M.NAME as MACHINE,
case
when (MS.MACHINE_ID > 0 and MI.UNINSTALL = 0) then 'Installed'
when MIA.ATTEMPT_COUNT = MI.MAX_ATTEMPT_COUNT then 'Failed'
when (MS.MACHINE_ID > 0 and MI.UNINSTALL = 1) then 'Failed to Uninstall'
when MIA.ATTEMPT_COUNT > 0 then concat('Not Installed (',MIA.ATTEMPT_COUNT,' of ', MI.MAX_ATTEMPT_COUNT,' attempts)')
else 'Not Installed'
end as DEPLOYMENT_STATUS,
M.IP as 'IP Address', M.LAST_SYNC, M.USER_NAME as 'Last User'
from SOFTWARE S
join MI on (S.ID = MI.SOFTWARE_ID)
join MI_LABEL_JT MIL on (MI.ID = MIL.MI_ID)
join LABEL L on (MIL.LABEL_ID = L.ID)
join MACHINE_LABEL_JT ML on (L.ID = ML.LABEL_ID)
join MACHINE M on (ML.MACHINE_ID = M.ID)
join SOFTWARE_OS_JT SO on (SO.SOFTWARE_ID = S.ID and SO.OS_ID = M.OS_ID)
left join MACHINE_SOFTWARE_JT MS on (M.ID = MS.MACHINE_ID and MS.SOFTWARE_ID = S.ID)
left join MI_ATTEMPT MIA on (MIA.MI_ID = MI.ID and MIA.MACHINE_ID = M.ID)
order by MANAGED_INSTALL, SOFTWARE_VERSION, DEPLOYMENT_STATUS, MACHINE

Break on Columns:  MANAGED_INSTALL, SOFTWARE_VERSION
___________________________________________________________________________________

Title: Managed Installs - All Deployments
Description: Lists all managed installs with status (Installed, Not Installed, Failed, Failed to Uninstall). The 'Not Installed' status indicates the Managed Install has not started on the specified machines.
Category: Managed Installs (Custom)
Server Hostname: kbox.company.net
Generated: 2012/08/02 11:46:55

#   Machine    Deployment Status                Ip Address   Last Sync            Last User
1 of Managed Install: Adobe AIR, Software Version: 1.0.4990
 1  PPETERSON  Failed                           172.16.0.88  2012-08-02 10:03:36  ppeterso

3 of Managed Install: Adobe Flash Player 11 ActiveX, Software Version: 11.3.300.268
 2  AJONES     Installed                        172.16.1.42  2012-08-01 14:37:46  ajones
 3  CDANIELS   Not Installed                    172.16.3.77  2012-07-30 09:14:54  cdaniels
 4  DGREEN     Not Installed (1 of 3 attempts)  172.16.0.55  2012-08-02 09:05:49  dgreen

2 of Managed Install: Java(TM) 7 Update 5, Software Version: 7.0.50
 5  AJONES     Installed                        172.16.1.42  2012-08-01 14:37:46  ajones
 6  MWILLIAMS  Not Installed (1 of 3 attempts)  172.16.3.11  2012-08-02 09:58:52  mcwilliam

1 of Managed Install: Spelling Dictionaries Support For Adobe Reader 8, Software Version: 8.0.0
 7  jwhite     Failed to Uninstall              172.16.7.68  2012-08-02 10:26:13  jwhite
___________________________________________________________________________________
___________________________________________________________________________________

*Title*
Managed Installs - Enabled Deployments

*Report Category*
Managed Installs (Custom)

*Description*
Lists all enabled Managed Installs with status (Installed, Not Installed, Failed, Failed to Uninstall).  The 'Not Installed' status indicates the Managed Install has not started on the specified machines.

*SQL Select Statement*
select S.DISPLAY_NAME as MANAGED_INSTALL, S.DISPLAY_VERSION as SOFTWARE_VERSION, M.NAME as MACHINE,
case
when (MS.MACHINE_ID > 0 and MI.UNINSTALL = 0) then 'Installed'
when MIA.ATTEMPT_COUNT = MI.MAX_ATTEMPT_COUNT then 'Failed'
when (MS.MACHINE_ID > 0 and MI.UNINSTALL = 1) then 'Failed to Uninstall'
when MIA.ATTEMPT_COUNT > 0 then concat('Not Installed (',MIA.ATTEMPT_COUNT,' of ', MI.MAX_ATTEMPT_COUNT,' attempts)')
else 'Not Installed'
end as DEPLOYMENT_STATUS,
M.IP as 'IP Address', M.LAST_SYNC, M.USER_NAME as 'Last User'
from SOFTWARE S
join MI on (S.ID = MI.SOFTWARE_ID)
join MI_LABEL_JT MIL on (MI.ID = MIL.MI_ID)
join LABEL L on (MIL.LABEL_ID = L.ID)
join MACHINE_LABEL_JT ML on (L.ID = ML.LABEL_ID)
join MACHINE M on (ML.MACHINE_ID = M.ID)
join SOFTWARE_OS_JT SO on (SO.SOFTWARE_ID = S.ID and SO.OS_ID = M.OS_ID)
left join MACHINE_SOFTWARE_JT MS on (M.ID = MS.MACHINE_ID and MS.SOFTWARE_ID = S.ID)
left join MI_ATTEMPT MIA on (MIA.MI_ID = MI.ID and MIA.MACHINE_ID = M.ID)
where MI.ENABLED != 0
order by MANAGED_INSTALL, SOFTWARE_VERSION, DEPLOYMENT_STATUS, MACHINE

Break on Columns:  MANAGED_INSTALL, SOFTWARE_VERSION
___________________________________________________________________________________

Same output as Managed Installs - All Deployments report, but excludes disabled Managed Installs.
___________________________________________________________________________________
___________________________________________________________________________________

*Title*
Managed Installs - In Progress and Failed Deployments

*Report Category*
Managed Installs (Custom)

*Description*
Lists all enabled Managed Installs with deployments currently in progress, as well as those that failed (reached the maximum deployment attempt count) and failed to uninstall.

*SQL Select Statement*
select S.DISPLAY_NAME as MANAGED_INSTALL, S.DISPLAY_VERSION as SOFTWARE_VERSION, M.NAME as MACHINE,
case
when MIA.ATTEMPT_COUNT = MI.MAX_ATTEMPT_COUNT then 'Failed'
when (MS.MACHINE_ID > 0 and MI.UNINSTALL = 1) then 'Failed to Uninstall'
when MIA.ATTEMPT_COUNT > 0 then concat('Not Installed (',MIA.ATTEMPT_COUNT,' of ', MI.MAX_ATTEMPT_COUNT,' attempts)')
else 'Not Installed'
end as DEPLOYMENT_STATUS,
M.IP as 'IP Address', M.LAST_SYNC, M.USER_NAME as 'Last User'
from SOFTWARE S
join MI on (S.ID = MI.SOFTWARE_ID)
join MI_LABEL_JT MIL on (MI.ID = MIL.MI_ID)
join LABEL L on (MIL.LABEL_ID = L.ID)
join MACHINE_LABEL_JT ML on (L.ID = ML.LABEL_ID)
join MACHINE M on (ML.MACHINE_ID = M.ID)
join SOFTWARE_OS_JT SO on (SO.SOFTWARE_ID = S.ID and SO.OS_ID = M.OS_ID)
left join MACHINE_SOFTWARE_JT MS on (M.ID = MS.MACHINE_ID and MS.SOFTWARE_ID = S.ID)
left join MI_ATTEMPT MIA on (MIA.MI_ID = MI.ID and MIA.MACHINE_ID = M.ID)
where MI.ENABLED != 0
and MIA.ATTEMPT_COUNT != 0
order by MANAGED_INSTALL, SOFTWARE_VERSION, DEPLOYMENT_STATUS, MACHINE

Break on Columns:  MANAGED_INSTALL, SOFTWARE_VERSION
___________________________________________________________________________________

Title: Managed Installs - In Progress and Failed Deployments
Description: Lists all enabled Managed Installs with deployments currently in progress, as well as those that failed (reached the maximum deployment attempt count) and failed to uninstall.
Category: Managed Installs (Custom)
Server Hostname: kbox.company.net
Generated: 2012/08/02 11:39:31

#   Machine    Deployment Status                Ip Address   Last Sync            Last User
1 of Managed Install: Adobe AIR, Software Version: 1.0.4990
 1  PPETERSON  Failed                           172.16.0.88  2012-08-02 10:03:36  ppeterso

1 of Managed Install: Adobe Flash Player 11 ActiveX, Software Version: 11.3.300.268
 2  DGREEN     Not Installed (1 of 3 attempts)  172.16.0.55  2012-08-02 09:05:49  dgreen

1 of Managed Install: Java(TM) 7 Update 5, Software Version: 7.0.50
 3  MWILLIAMS  Not Installed (1 of 3 attempts)  172.16.3.11  2012-08-02 09:58:52  mcwilliam

1 of Managed Install: Spelling Dictionaries Support For Adobe Reader 8, Software Version: 8.0.0
 4  jwhite     Failed to Uninstall              172.16.7.68  2012-08-02 10:26:13  jwhite
___________________________________________________________________________________
___________________________________________________________________________________

*Title*
Managed Installs - Failed Deployments

*Report Category*
Managed Installs (Custom)

*Description*
Lists all enabled Managed Installs that failed (reached the maximum deployment attempt count) or failed to uninstall.

*SQL Select Statement*
select S.DISPLAY_NAME as MANAGED_INSTALL, S.DISPLAY_VERSION as SOFTWARE_VERSION, M.NAME as MACHINE,
case
when MIA.ATTEMPT_COUNT = MI.MAX_ATTEMPT_COUNT then 'Failed'
when (MS.MACHINE_ID > 0 and MI.UNINSTALL = 1) then 'Failed to Uninstall'
else 'Not Installed'
end as DEPLOYMENT_STATUS,
M.IP as 'IP Address', M.LAST_SYNC, M.USER_NAME as 'Last User'
from SOFTWARE S
join MI on (S.ID = MI.SOFTWARE_ID)
join MI_LABEL_JT MIL on (MI.ID = MIL.MI_ID)
join LABEL L on (MIL.LABEL_ID = L.ID)
join MACHINE_LABEL_JT ML on (L.ID = ML.LABEL_ID)
join MACHINE M on (ML.MACHINE_ID = M.ID)
join SOFTWARE_OS_JT SO on (SO.SOFTWARE_ID = S.ID and SO.OS_ID = M.OS_ID)
left join MACHINE_SOFTWARE_JT MS on (M.ID = MS.MACHINE_ID and MS.SOFTWARE_ID = S.ID)
left join MI_ATTEMPT MIA on (MIA.MI_ID = MI.ID and MIA.MACHINE_ID = M.ID)
where MI.ENABLED != 0
and (MIA.ATTEMPT_COUNT = MI.MAX_ATTEMPT_COUNT or MI.UNINSTALL = 1)
order by MANAGED_INSTALL, SOFTWARE_VERSION, DEPLOYMENT_STATUS, MACHINE

Break on Columns:  MANAGED_INSTALL, SOFTWARE_VERSION
___________________________________________________________________________________

Title: Managed Installs - Failed Deployments
Description: Lists all enabled Managed Installs that failed (reached the maximum deployment attempt count) or failed to uninstall.
Category: Managed Installs (Custom)
Server Hostname: kbox.company.net
Generated: 2012/08/02 11:36:46

#  Machine     Deployment Status    Ip Address   Last Sync            Last User
1 of Managed Install: Adobe AIR, Software Version: 1.0.4990
 1  PPETERSON  Failed               172.16.0.88  2012-08-02 10:03:36  ppeterso

1 of Managed Install: Spelling Dictionaries Support For Adobe Reader 8, Software Version: 8.0.0
 2  jwhite     Failed to Uninstall  172.16.7.68  2012-08-02 10:26:13  jwhite
___________________________________________________________________________________

Hope that helps!

John