labtech Database

Parameters

Name	Type	Mode
CID	int	IN
StatusString	mediumtext	IN
SuccessMons	mediumtext	IN
FailedMons	mediumtext	IN
CheckInCPU	int	IN
CheckInMEM	int	IN
BtOUT	bigint	IN
BtIN	bigint	IN

Definition

BEGIN
 DECLARE tp VARCHAR(20000);
 DECLARE lu DATETIME;
 IF LEFT(StatusString,4) <> '' THEN
 SELECT CONCAT('Update computers SET LastContact=NOW()',IF(StatusString=',','',StatusString),' WHERE tempfiles<>-1 and ComputerID=',CID) INTO @tp;
  PREPARE stmt2 FROM @tp;
  EXECUTE stmt2;
  DEALLOCATE PREPARE stmt2;
 SET @tp='';
 ELSE
  UPDATE computers SET LastContact=NOW() WHERE tempfiles<>-1 and ComputerID=CID;
 END IF;
IF FailedMons <> '' THEN
  SELECT CONCAT('UPDATE h_agents JOIN agents USING(agentid) SET agents.LastScan=NOW(), h_agents.LastComputerID=',CID,',h_agents.FailureRate=ROUND((h_agents.Fails/(h_agents.Fails+h_agents.Checks))*100), h_agents.Fails=h_agents.Fails+1 WHERE h_agents.AgentID IN (',FailedMons,');') INTO @tp;
  PREPARE stmt2 FROM @tp;
  EXECUTE stmt2;
  DEALLOCATE PREPARE stmt2; 
  SET @tp='';
END IF;
 IF SuccessMons <> '' THEN
  SELECT CONCAT('UPDATE h_agents JOIN agents USING(agentid) SET agents.LastScan=NOW(), h_agents.LastComputerID=',CID,',h_agents.FailureRate=ROUND((h_agents.Fails/(h_agents.Fails+h_agents.Checks))*100), h_agents.Checks=h_agents.Checks+1 WHERE h_agents.AgentID IN (',SuccessMons,');') INTO @tp;
  PREPARE stmt2 FROM @tp;
  EXECUTE stmt2;
  DEALLOCATE PREPARE stmt2; 
  SET @tp='';
 END IF;
IF CheckInCPU >0 OR CheckInMEM >0 OR BtIN>0 OR BtOUT > 0 THEN
  INSERT INTO h_ComputerStats (ComputerID,CPU,MEM,DataIn,DataOut,EventDate,Samples) VALUES(CID,CheckInCPU,CheckInMEM,BtOUT,BtIN,DATE(NOW()),1) ON DUPLICATE KEY UPDATE `CPU`=ROUND(((`Samples` * `CPU`)+(VALUES(`CPU`) * VALUES(`Samples`)))/(`Samples` + VALUES(`Samples`))),`MEM`=ROUND(((`Samples` * `MEM`)+(VALUES(`MEM`) * VALUES(`Samples`)))/(`Samples` + VALUES(`Samples`))),`DataIn`=ROUND(((`Samples` * `DataIn`)+(VALUES(`DataIn`) * VALUES(`Samples`)))/(`Samples` + VALUES(`Samples`))),`DataOut`=ROUND(((`Samples` * `DataOut`)+(VALUES(`DataOut`) * VALUES(`Samples`)))/(`Samples` + VALUES(`Samples`))), Samples=Samples+1;
	SELECT lastupdate INTO lu FROM h_computerstatshourly WHERE computerid = CID ORDER BY eventdate DESC, eventhour DESC LIMIT 1;
	IF TIMESTAMPDIFF(MINUTE,lu,NOW()) >= 5 OR ISNULL(lu) THEN
		  INSERT INTO h_computerstatshourly (ComputerID,CPU,MaxCPU,MinCPU,MEM,MaxMEM,MinMEM,DataIn,DataOut,EventDate,EventHour,Samples) VALUES(CID,CheckInCPU,CheckInCPU,CheckInCPU,CheckInMEM,CheckInMEM,CheckInMEM,BtOUT,BtIN,DATE(NOW()),HOUR(NOW()),1) ON DUPLICATE KEY UPDATE 
		`CPU`=ROUND(((`Samples` * `CPU`)+(VALUES(`CPU`) * VALUES(`Samples`)))/(`Samples` + VALUES(`Samples`))),
		`MaxCPU`=IF(VALUES(CPU)>`MaxCPU`,VALUES(`CPU`),`MaxCPU`),
		`MinCPU`=IF(VALUES(CPU)<`MinCPU`,VALUES(`CPU`),`MinCPU`),
		`MEM`=ROUND(((`Samples` * `MEM`)+(VALUES(`MEM`) * VALUES(`Samples`)))/(`Samples` + VALUES(`Samples`))),
		`MaxMEM`=IF(VALUES(MEM)>`MaxMEM`,VALUES(`MEM`),`MaxMEM`),
		`MinMEM`=IF(VALUES(MEM)<`MinMEM`,VALUES(`MEM`),`MinMEM`),
		`DataIn`=ROUND(((`Samples` * `DataIn`)+(VALUES(`DataIn`) * VALUES(`Samples`)))/(`Samples` + VALUES(`Samples`))),
		Samples=Samples+1;
	END IF;
 END IF;
END

sp_CheckIn

Parameters

Definition