OnlineMachinesMissingPatches.sql

SELECT `computers`.`name` AS 'PC Name',
`computers`.`ComputerID` AS 'Agent ID',
`computers`.`OS` AS 'OS',
`computers`.`LastContact` AS 'Last Contact',
COUNT(hotfixid) AS 'Updates Pending'
FROM `hotfix`
JOIN `computers`ON ( `hotfix`.`ComputerID` = `computers`.`ComputerID` )
JOIN clients ON (computers.clientid=clients.clientid)
WHERE `hotfix`.`Installed`=0 AND `hotfix`.`Approved`=1 
AND clients.clientid NOT IN (33)
AND `computers`.`LastContact` > DATE_SUB(NOW(), INTERVAL 5 MINUTE)
AND computers.os NOT LIKE '%server%'
GROUP BY `computers`.`name` DESC
ORDER BY COUNT(hotfixid) DESC