HealthScore

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