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.

[dussan]

Hello Daniel

I think it is not working correct. I have computer which was power off last two days but it gets 72 active hours last two days and one hour at standbay.

lp, Dušan

[Sven_Thiele]

Hi Daniel,

Thank you so much... that's what I was looking for.

Greetings
Sven

[Keywan]

Sven Thiele wrote:
Hi Daniel,

Thank you so much... that's what I was looking for.

Greetings
Sven

Hi,

Could you tell me how you have resoled it?
Could you share your Report here`?

Thanks

[sullivane]

Just did this manually, opening each asset clicking monthly uptime and writing it down, for about 60 assets. I sure hope you guys are willing to try and officially support this report somehow. Took me almost 2 hours to get this info.

[sullivane]

I know you guys don't really support report much, but I am trying to use this, as this would be VERY helpful in our district. I have everything setup and it tested working by verifying on an asset looking at the uptime.

However, when I am putting up several machines, it's reporting that several machines are on for 24 hours a day and the uptime in the asset reports otherwise, by a lot.

Thanks for any help.

Edit: Found another one that shows in the report as an av of 24 hours a day, yet in the asset uptime it says it was on for 10m last month. I am running the report for the last 30 days. Also, I notice random assets are missing.

I know you guys don't support this report however, it's an AWESOME report that we can really use to find out if one of our labs are being used enough to justify replacing them when they are up for replacement.

[Susan_A]

We don't currently provide support for modifying this report further, unfortunately. As you can tell, this is quite a complicated query and not easily modifiable.

[riceanny]

Now I need to modify the report to sum only the power on night hours (from 7pm to 8am for example)...

[dangnhh1]

I execute function successfully and I also see this function in SQL database
But when I run report

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

Lansweeper show error:
Invalid object name "ComputerOnlineTime"

[riceanny]

dangnhh@vn.fujikura.com wrote:
I execute function successfully and I also see this function in SQL database
But when I run report

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

Lansweeper show error:
Invalid object name "ComputerOnlineTime"

To me too. I've the same error in LanSweeper web interface.
I've "solved" the problem calling the function directly from SQL Server Management Studio.