1
select sql_no_cache `c`.`ClientID` AS `ClientID`,`c`.`LocationID` AS `LocationID`,`c`.`ComputerID` AS `ComputerID`,`c`.`Name` AS `Name`,concat(max(`d`.`Letter`),':') AS `SysDrive`,round(((`d`.`Free` / `d`.`Size`) * 100),0) AS `Free`,`labtech`.`f_fragmentation`(`d`.`DriveID`) AS `Frag`,`c`.`TotalMemory` AS `TotalMemory`,(length(`c`.`Ports`) - length(replace(`c`.`Ports`,':',''))) AS `OpenPorts`,(select count(0) AS `count(*)` from `labtech`.`agents` where (`labtech`.`agents`.`ComputerID` = `c`.`ComputerID`)) AS `Agents`,(select count(0) AS `count(*)` from `labtech`.`scheduledscripts` where ((`labtech`.`scheduledscripts`.`ComputerID` = `c`.`ComputerID`) or (`labtech`.`scheduledscripts`.`LocationID` = `l`.`LocationID`) or (`labtech`.`scheduledscripts`.`ClientID` = `cl`.`ClientID`))) AS `SCmds`,cast(ifnull((select sum(timestampdiff(MINUTE,`labtech`.`h_commands`.`DateExecuted`,`labtech`.`h_commands`.`DateFinished`)) AS `sum(TIMESTAMPDIFF(MINUTE,DateExecuted,DateFinished))` from `labtech`.`h_commands` where ((`labtech`.`h_commands`.`ComputerID` = `c`.`ComputerID`) and (`labtech`.`h_commands`.`Command` = 209) and ((curdate() - interval 30 day) <= `labtech`.`h_commands`.`DateExecuted`))),0) as signed) AS `RedirMin`,((select count(0) AS `count(*)` from `labtech`.`v_usageperiods` where (`labtech`.`v_usageperiods`.`computerid` = `c`.`ComputerID`)) * 5) AS `PCUsage` from (((`labtech`.`computers` `c` join `labtech`.`locations` `l`) join `labtech`.`clients` `cl`) join `labtech`.`drives` `d`) where ((`c`.`LocationID` = `l`.`LocationID`) and (`c`.`ClientID` = `cl`.`ClientID`) and (`d`.`ComputerID` = `c`.`ComputerID`) and (left(`c`.`WinDir`,1) = `d`.`Letter`)) group by `c`.`ClientID`,`c`.`LocationID`,`c`.`ComputerID`