1
select `labtech`.`computers`.`ComputerID` AS `ComputerID`,ifnull((select count(0) from `labtech`.`software` where (`labtech`.`software`.`ComputerID` = `labtech`.`computers`.`ComputerID`)),0) AS `TotalApps`,ifnull((select sum(`labtech`.`software`.`Size`) from `labtech`.`software` where (`labtech`.`software`.`ComputerID` = `labtech`.`computers`.`ComputerID`)),0) AS `AppsSize`,ifnull((select sum((`labtech`.`hotfix`.`Installed` = 1)) from `labtech`.`hotfix` where (`labtech`.`hotfix`.`ComputerID` = `labtech`.`computers`.`ComputerID`)),0) AS `InstalledPatches`,ifnull((select sum((`labtech`.`hotfix`.`Installed` = 0)) from (`labtech`.`hotfix` join `labtech`.`hotfixdata` on((`labtech`.`hotfix`.`HotFixID` = `labtech`.`hotfixdata`.`HotFixID`))) where ((`labtech`.`hotfix`.`ComputerID` = `labtech`.`computers`.`ComputerID`) and ((`labtech`.`hotfixdata`.`Severity` <> '') or (`labtech`.`hotfix`.`Approved` = 1)))),0) AS `MissingPatches`,ifnull((select count(0) from `labtech`.`databasefails` where (`labtech`.`databasefails`.`ComputerID` = `labtech`.`computers`.`ComputerID`)),0) AS `Issues`,ifnull((select avg(`v_defragmentation`.`TotalFrag`) from `labtech`.`v_defragmentation` where (`v_defragmentation`.`ComputerID` = `labtech`.`computers`.`ComputerID`)),0) AS `FRAG`,ifnull((select avg(round(((`labtech`.`drives`.`Free` / `labtech`.`drives`.`Size`) * 100),0)) from `labtech`.`drives` where ((`labtech`.`drives`.`ComputerID` = `labtech`.`computers`.`ComputerID`) and (`labtech`.`drives`.`FileSystem` in ('NTFS','FAT32','FAT')) and (`labtech`.`drives`.`Size` > 8000))),0) AS `FREE`,(length(`labtech`.`computers`.`Ports`) - length(replace(`labtech`.`computers`.`Ports`,':',''))) AS `OpenPorts`,ifnull((select count(0) from `labtech`.`tickets` where ((`labtech`.`tickets`.`ComputerID` = `labtech`.`computers`.`ComputerID`) and (`labtech`.`tickets`.`Status` < 4))),0) AS `SupportTickets`,ifnull((select count(0) from `labtech`.`agents` where ((`labtech`.`agents`.`CheckAction` > 0) and (`labtech`.`agents`.`ComputerID` = `labtech`.`computers`.`ComputerID`))),0) AS `Monitors` from `labtech`.`computers`