Calculate computer online time (needs SQL Server)

[Daniel_B]

This report function can be executed only if the Lansweeper database is running on SQL Server.

Before creating and/or running the report, connect to your database with SQL Server Management Studio under the sa account and execute the following SQL code which creates a new function for all users:

CREATE FUNCTION ComputerOnlineTime
(@NumberOfDays INT, @Assetname NVARCHAR(255))
RETURNS @tblComputersOnlineTime TABLE (AssetID INT, Assetname NVARCHAR(200),
  [Hours active] INT, [Hours stand-by] INT, [Avg active per day] INT,
  [Avg stand-by per day] INT)
AS
BEGIN
/****** Calculation of computer Uptime based on tbl.Uptime  ******/
DECLARE @TableTemp TABLE (UptimeID INT, AssetID INT, EventTime DATETIME,
  Eventtype INT)
DECLARE @TableOff TABLE (AssetID INT, OffTime BIGINT, StbyTime BIGINT)
DECLARE @UptimeID INT
DECLARE @ID1 INT
DECLARE @EventTime1 DATETIME
DECLARE @Eventtype1 INT
DECLARE @ID2 INT
DECLARE @EventTime2 DATETIME
DECLARE @Eventtype2 INT

/*** CREATE TEMP TABLE WITH TBL.UPTIME DATA ***/
IF ISNULL(@Assetname,'') = ''
BEGIN
INSERT INTO @TableTemp (UptimeID, AssetID, EventTime, Eventtype) 
  SELECT TOP 100000 UptimeID, AssetId, eventtime, eventtype
  FROM tblUptime Where EventTime>GETDATE()-@NumberOfDays 
  ORDER BY AssetId, EventTime desc;
END
ELSE
BEGIN
  INSERT INTO @TableTemp (UptimeID, AssetID, EventTime, Eventtype) 
  SELECT TOP 100000 UptimeID, tblUptime.AssetId, eventtime, eventtype
  FROM tblUptime INNER JOIN tblAssets ON tblAssets.AssetID = tblUptime.AssetID
  Where EventTime>GETDATE()-@NumberOfDays AND
  tblAssets.Assetname LIKE '%' + @Assetname + '%'
  ORDER BY tblUptime.AssetId, EventTime desc;
END

/*** CHECK IF ASSET CURRENTLY OFF OR STANDBY, FILL BASE TABLE ***/
INSERT INTO @TableOff (AssetID, OffTime, StbyTime) SELECT Q1.AssetID,
CASE WHEN Q2.Eventtype = 2 THEN DATEDIFF(Minute,Q1.EventTime,GETDATE()) ELSE 0 END,
CASE WHEN Q2.Eventtype = 3 THEN DATEDIFF(Minute,Q1.EventTime,GETDATE()) ELSE 0 END FROM
(SELECT MAX(EventTime) AS EventTime, AssetID FROM @TableTemp GROUP BY AssetID) Q1
INNER JOIN (SELECT Eventtype, EventTime, AssetID FROM @TableTemp) Q2
  ON Q1.AssetID = Q2.AssetID And Q1.EventTime = Q2.EventTime;

/*** SUM UP ALL INTERVALS IN WHICH ASSET WAS OFF OR STANDBY ***/
WHILE EXISTS (SELECT * FROM @TableTemp)
  BEGIN
    SELECT TOP 1 @UptimeID = UptimeID, @ID1 = AssetID, @EventTime1 = EventTime, 
      @Eventtype1 = Eventtype 
      FROM @TableTemp ORDER BY AssetId, EventTime desc
    DELETE FROM @TableTemp WHERE UptimeID = @UptimeID 
    SELECT TOP 1 @ID2 = AssetID, @EventTime2 = EventTime, @Eventtype2 = Eventtype 
      FROM @TableTemp ORDER BY AssetId, EventTime desc
    IF (@Eventtype1 = 1 AND @Eventtype2 = 2) AND @ID1=@ID2 
      BEGIN 
	    UPDATE @TableOff SET OffTime=OffTime 
              + DATEDIFF(Minute,@eventtime2,@eventtime1)
	      WHERE AssetID=@ID1;
      END
    IF (@Eventtype1 = 4 AND @Eventtype2 = 3) AND @ID1=@ID2 
      BEGIN 
	    UPDATE @TableOff SET StbyTime=StbyTime 
              + DATEDIFF(Minute,@eventtime2,@eventtime1)
	      WHERE AssetID=@ID1;
      END
  END
  
INSERT INTO @tblComputersOnlineTime (AssetID, Assetname, [Hours active],
  [Hours stand-by], [Avg active per day], [Avg stand-by per day])
SELECT T1.AssetID, Assetname,
  CEILING(CAST((@NumberOfDays*24*60-OffTime-StbyTime) AS Float)/60),
  CEILING(CAST((StbyTime) AS Float)/60),
  CEILING(CAST((@NumberOfDays*24*60-OffTime-StbyTime) AS Float)/(60*@NumberOfDays)),
  CEILING(CAST((StbyTime) AS Float)/(60*@NumberOfDays))
FROM @TableOff T1 INNER JOIN tblAssets ON T1.AssetID = tblAssets.AssetID
ORDER BY Assetname

RETURN

END

Now create a report like the following under Reports\Create new report

 
Select Top 1000000 *
From ComputerOnlineTime('3', 'PartOfAssetName')
Order By Assetname

Note: This report function takes a long time to run! In the above example it will sum up the uptime of computers having an asset name like %PartOfAssetName% for the last 3 days. Modify the assetname filter to calculate it for a limited number of computers in your network.

[riceanny]

How can modify the report to sum only the power on night hours (from 7pm to 8am for example)?