HealthScore

Parameters

Name	Type	Mode
CID	int	IN

Definition

BEGIN
  Declare score int;
  Declare result int;
  Declare Total int;

Declare Totalscore int Default 0 ;
  Declare Totalresult int Default 0 ;
  Declare TotalTotal int Default 0 ;

SELECT IFNULL(SUM(IF(h_computerstatsdaily.MEM<MemoryLimit,1*(MemoryWeight*.01),0))+
SUM(IF(h_computerstatsdaily.CPU>CPULimit,1*(CPUWeight*.01),0)),0) As Result,
IFNULL((Count(*) * (1*(MemoryWeight *.01)))+
(Count(*) * (1*(CPUWeight *.01))),1) as Total,
1 as PerfScore
 FROM managementScore,h_computerstatsdaily Join Computers Using (ComputerID) Where h_computerstatsdaily.EventDate=DATE(DATE_ADD(CURDATE(),interval -1 day)) and Computers.ClientID=CID into @result,@total,@score;

set @TotalTotal =   @Total;
set @Totalresult =  @Total-@result;

SELECT IFNULL(SUM(IF(h_DriveStatsDaily.Free<DriveSpaceLevel,1*(DriveSpaceWeight *.01),0))+
SUM(IF(h_DriveStatsDaily.Frag>DriveFragLevel,1*(DriveFragWeight*.01),0)),0) As Result,
IFNULL((Count(*) * (1*(DriveSpaceWeight *.01)))+
(Count(*) * (1*(DriveFragWeight*.01))),1) as Total,1 as DriveScore
 FROM managementScore,h_DriveStatsDaily Join Drives Using (DriveID) Join Computers Using (ComputerID) Where h_DrivestatsDaily.EventDate=DATE(DATE_ADD(CURDATE(),interval -1 day)) and Computers.ClientID=CID into @result,@total,@score;

set @TotalTotal =  @TotalTotal + @Total;
set @Totalresult =  @Totalresult + (@Total-@result);

SELECT IFNULL((SUM(not h_stats.VirusScanEnabled) * (VirusDisabledWeight *.01)) +
(SUM(not h_stats.VirusScanUpToDate)*(VirusOutDateWeight*.01)) +
(SUM(h_stats.VirusScanMissing)*(VirusMissingWeight*.01)),0) as Result,IFNULL((Count(*) * (VirusDisabledWeight *.01)) +
(Count(*)*(VirusOutDateWeight*.01)) +
(Count(*)*(VirusMissingWeight*.01)),1) as Total,
1 as VirusScore
FROM managementScore,h_stats Join Computers Using (ComputerID) Where h_stats.StatDate>DATE_ADD(NOW(),interval -1 day) and Computers.ClientID=CID into @result,@total,@score;

set @TotalTotal =  @TotalTotal + @Total;
set @Totalresult =  @Totalresult + (@Total-@result);

SELECT IFNULL(SUM(IF(h_stats.EventLogSystemErrors>EventLogSystemCount,1*(EventLogSystemWeight*.01),0))+
SUM(IF(h_stats.EventLogSecurityErrors>EventLogSecurityCount,1*(EventLogSecurityWeight*.01),0))+
SUM(IF(h_stats.EventLogApplicationErrors>EventLogAppCount,1*(EventLogAppWeight*.01),0)),0) As Result,
IFNULL((1*(EventLogSystemWeight*.01))+(1*(EventLogSecurityWeight*.01))+(1*(EventLogAppWeight*.01)),1) as total,
1 as EventScore
 FROM managementScore,h_stats Join Computers Using (ComputerID) Where h_stats.StatDate>DATE_ADD(NOW(),interval -1 day) and Computers.ClientID=CID into @result,@total,@score;

set @TotalTotal =  @TotalTotal + @Total;
set @Totalresult =  @Totalresult + (@Total-@result);

Select IFNULL((Count(*)*(1*(OSBadWeight*.01)))+(Count(*)*(1*(OSWarningWeight*.01))),1) as Total,IFNULL((SUM(IF(OS like stringsplit(OSBadList,',',1) or OS like stringsplit(OSBadList,',',2) or OS like stringsplit(OSBadList,',',3),1*(OSBadWeight*.01),0)))+(SUM(IF(OS like stringsplit(OSWarningList,',',1) or stringsplit(OSWarningList,',',2) or stringsplit(OSWarningList,',',3),1*(OSWarningWeight*.01),0))),0) as Result,
100 as OSScore
 From Computers,ManagementScore Where Computers.ClientID=CID into @total,@result,@score;

set @TotalTotal =  @TotalTotal + @Total;
set @Totalresult =  @Totalresult + (@Total-@result);

SELECT IFNULL(Count(*),1) as Total,
IFNULL(SUM( NoneMissing )+(SUM(Missing1to2)*(PatchMissing12Weight*.01))+(
SUM(Missing3to5)*(PatchMissing35Weight*.01))+(
SUM(Missing5)*(PatchMissing6Weight*.01)),0) as Result, 
100 as PatchScore
from v_Patch,managementscore where ClientID=CID into @total,@result,@score;

set @TotalTotal =  @TotalTotal + @Total;
set @Totalresult =  @Totalresult + (@Total-@result);

SELECT IFNULL(Count(*),1) as Total,IFNULL((SUM(IF(Status=1 and TimestampDiff(hour,StartedDate,NOW())>TicketNewAgeLimit,1*(TicketNewAgeWeight*.01),0)))+ (SUM(IF(Status=2 and TimestampDiff(hour,StartedDate,NOW())>TicketOpenAgeLimit,1*(TicketOpenWeight*.01),0))),0) as Result,
1 as TicketScore
 from tickets,managementscore Where ClientID=CID into @total,@result,@score;

set @TotalTotal =  @TotalTotal + @Total;
set @Totalresult =  @Totalresult + (@Total-@result);

Select IFNULL(MAX(Reliablity),100) From AgentComputerData  Join Computers Using (ComputerID),managementscore  Where Computers.ClientID=CID into @result;

set @TotalTotal =  @TotalTotal + 100;
set @Totalresult =  @Totalresult + @result;

Set @TotalScore = 100*(@TotalResult/@TotalTotal) ;

return @TotalScore ;
END