Querying the pending actions for agents in MOM 2005

Recently I came across a situation where I had patched our MOM2005 Management Servers with a MS hotfix to correct a problem we were experiencing and needed to then rollout the patch to all of our agents. As our environment is a combination of agents with either Control Levels Full or None I needed to determine and keep track of which agents I had patched.

To do this I wrote the following TSQL statement to query the OnePoint database and allow me to export the results to Excell to make it easier to keep track of what was still to be patched.

SELECT C.[Name] AS Name,
C.[Domain],
CM.[Name] AS ConfigManagerName,
(CASE Convert(varchar(20), C.ManagedType)
WHEN '0' THEN 'Unmanaged'
WHEN '2' THEN 'Agent-Managed'
ELSE Convert(varchar(20), C.ManagedType)
END) as 'Management Mode',
(CASE IsNull(dbo.fn_GetControlLevel(C.idComputer),'FUll')
WHEN 'Full' THEN 'Full'
WHEN 'Group' THEN 'None'
ELSE 'UNK'
END) As ControlLevel,
(CASE Convert(varchar(20),C.PendingAction)
WHEN '0' THEN ''
WHEN '1' THEN 'Approve Manual Agent Install'
WHEN '2' THEN 'Uninstall Agent'
WHEN '4' THEN 'Install Agent'
WHEN '5' THEN 'Requires Patching'
ELSE Convert(varchar(20),C.PendingAction)
END) AS PendingAction
FROM
[dbo].[Computer] C
LEFT OUTER JOIN [dbo].[Computer] CM ON C.[idConfigManager] = CM.[idComputer]
WHERE C.IsAddedByServiceDiscovery = 0
ORDER BY C.Name