Code: Select all
SELECT `computers`.`ComputerID` AS 'ID',
`computers`.`name` AS `Name`,
`clients`.`name` AS 'Client',
SUBSTRING(`computers`.`OS`,11) AS 'OS',
`computers`.`LastContact` AS 'Last Contact Time',
`computers`.`WindowsUpdate` AS 'Last Update Attempt',
COUNT(hotfixid) AS 'Missing Patches'
FROM `hotfix`
JOIN `computers` ON ( `hotfix`.`ComputerID` = `computers`.`ComputerID` ) JOIN `clients` ON (`computers`.`clientid` = `clients`.`clientid`) JOIN `commands` ON (`computers`.`ComputerID` = `commands`.`computerid`) JOIN `v_extradatacomputers` ON (`computers`.`ComputerID` = `v_extradatacomputers`.`computerid` ) WHERE `Installed`=0 AND `Approved`=1 AND (((Computers.Flags & 1024) != 1024)) AND `computers`.`OS` NOT LIKE '%server%'
AND `LastContact` > DATE_SUB(NOW(), INTERVAL 5 MINUTE) AND `Username` LIKE '%not logged in%'
AND `commands`.`command` != 100 AND `commands`.`status` != 2 GROUP BY `computers`.`name` DESC HAVING `Missing Patches` >= 10 ORDER BY COUNT(hotfixid) DESC;