1
select `cmp`.`ClientID` AS `ClientID`,`loc`.`LocationID` AS `LocationID`,`loc`.`Name` AS `LocationName`,`cmp`.`ComputerID` AS `ComputerID`,`cmp`.`Name` AS `ComputerName`,`os`.`Server` AS `ComputerIsServer`,`cmp`.`LocalAddress` AS `ComputerLocalIP`,if((locate('windows',`os`.`Name`) > 0),'Windows',if((locate('mac',`os`.`Name`) > 0),'Apple','Linux')) AS `ComputerOSType`,replace(replace(`os`.`Name`,'©',''),'Microsoft ','') AS `ComputerOS`,`sched`.`DefScheduleID` AS `DefScheduleID`,`cmp`.`DateAdded` AS `ComputerDateAgentAdded`,`cmp`.`DateAdded` AS `ComputerDateAddedLT`,`sched`.`Schedule` AS `BackupSchedulePattern`,if((sum(if(((`job`.`JobStatus` = 6) and ((to_days(now()) - to_days(`job`.`StartTime`)) <= 30)),1,0)) > 0),'Failed Backups',if((isnull(`sched`.`DefinitionGUID`) and (`os`.`Server` <> 0)),'No Scheduled Backups','Healthy')) AS `BackupRisk`,sum(if(((`job`.`JobStatus` = 6) and ((to_days(now()) - to_days(`job`.`StartTime`)) <= 30)),1,0)) AS `JobsFailedTotal`,sum(if(((`job`.`JobStatus` = 6) and ((to_days(now()) - to_days(`job`.`StartTime`)) < 1)),1,0)) AS `JobsFailed1d`,sum(if(((`job`.`JobStatus` = 6) and ((to_days(now()) - to_days(`job`.`StartTime`)) between 1 and 7)),1,0)) AS `JobsFailed7d`,sum(if(((`job`.`JobStatus` = 6) and ((to_days(now()) - to_days(`job`.`StartTime`)) between 7 and 15)),1,0)) AS `JobsFailed15d`,sum(if(((`job`.`JobStatus` = 6) and ((to_days(now()) - to_days(`job`.`StartTime`)) between 15 and 30)),1,0)) AS `JobsFailed30d`,sum(if(((`job`.`JobStatus` = 5) and ((to_days(now()) - to_days(`job`.`StartTime`)) <= 30)),1,0)) AS `JobsSucceededTotal`,sum(if(((`job`.`JobStatus` = 5) and ((to_days(now()) - to_days(`job`.`StartTime`)) < 1)),1,0)) AS `JobsSucceeded1d`,sum(if(((`job`.`JobStatus` = 5) and ((to_days(now()) - to_days(`job`.`StartTime`)) between 1 and 7)),1,0)) AS `JobsSucceeded7d`,sum(if(((`job`.`JobStatus` = 5) and ((to_days(now()) - to_days(`job`.`StartTime`)) between 7 and 15)),1,0)) AS `JobsSucceeded15d`,sum(if(((`job`.`JobStatus` = 5) and ((to_days(now()) - to_days(`job`.`StartTime`)) between 15 and 30)),1,0)) AS `JobsSucceeded30d`,if((max(if((`job`.`JobStatus` = 5),`job`.`StartTime`,NULL)) = max(`job`.`StartTime`)),1,0) AS `JobIsLastSucceeded` from (((`labtech`.`computers` `cmp` left join (`labtech`.`h_backupjobs` `job` join `labtech`.`backupdefinitionschedules` `sched` on(((`job`.`DefinitionGUID` = `sched`.`DefinitionGUID`) and (`job`.`JobTypeID` = `sched`.`JobTypeID`) and (`job`.`JobSubTypeID` = `sched`.`JobSubTypeID`)))) on((`job`.`ComputerID` = `cmp`.`ComputerID`))) join `labtech`.`inv_operatingsystem` `os` on((`cmp`.`ComputerID` = `os`.`ComputerID`))) join `labtech`.`locations` `loc` on((`cmp`.`LocationID` = `loc`.`LocationID`))) group by `cmp`.`ComputerID`