SQL -> Workstations that are ONLINE and ready to patch (& user NOT logged in!)

Useful script s and methods to resolve complicated issues in ConnectWise Automate
Post Reply
User avatar
Cubert
Posts: 2430
Joined: Tue Dec 29, 2015 7:57 pm
8
Contact:

SQL -> Workstations that are ONLINE and ready to patch (& user NOT logged in!)

Post by Cubert »

Workstations that are ONLINE and ready to patch (& user NOT logged in!)

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; 

Post Reply

Return to “Scripts”