labtech Database

Parameters

Name	Type	Mode
CID	int(11)	IN
StatusString	varchar(20000)	IN
SuccessMons	varchar(20000)	IN
FailedMons	varchar(20000)	IN
CheckInCPU	int(11)	IN
CheckInMEM	int(11)	IN
BtOUT	bigint(20)	IN
BtIN	bigint(20)	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