Notification

Icon
Error

Calculate computer online time (needs SQL Server) - Calculates a sum of online time per computer in the past X days

Posted: Thursday, January 29, 2015 3:17:05 PM(UTC)
Daniel.B

Daniel.B

Member Administration Original PosterPosts: 1,150
2
Like
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:

Code:

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
Code:
 
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
#1riceanny Member Posts: 7  
posted: 3/7/2016 4:25:31 PM(UTC)
How can modify the report to sum only the power on night hours (from 7pm to 8am for example)?
dangnhh@vn.fujikura.com
#2dangnhh@vn.fujikura.com Member Posts: 1  
posted: 3/11/2016 10:19:38 AM(UTC)
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
#3riceanny Member Posts: 7  
posted: 3/11/2016 4:16:00 PM(UTC)
Originally Posted by: dangnhh@vn.fujikura.com Go to Quoted Post
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.
riceanny
#4riceanny Member Posts: 7  
posted: 3/11/2016 4:17:49 PM(UTC)
Now I need to modify the report to sum only the power on night hours (from 7pm to 8am for example)...Think
Susan.A
#5Susan.A Member Administration Posts: 1,535  
posted: 3/18/2016 1:26:48 PM(UTC)
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.
sullivane
#6sullivane Member Posts: 190  
posted: 6/1/2016 4:00:10 PM(UTC)
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.
sullivane
#7sullivane Member Posts: 190  
posted: 6/1/2016 8:35:49 PM(UTC)
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.
Sven Thiele
#8Sven Thiele Member Posts: 4  
posted: 2/21/2019 10:36:10 AM(UTC)
Hi Daniel,

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

Greetings
Sven

Active Discussions

Lansweeper non-active computer report
by  Apaulcolypse   Go to last post Go to first unread
Last post: Yesterday at 5:16:21 PM(UTC)
Lansweeper Report for showing CPUs below or above
by  GBA Craig  
Go to last post Go to first unread
Last post: 7/18/2019 3:38:41 PM(UTC)
Lansweeper Hardware list
by  GBA Craig   Go to last post Go to first unread
Last post: 7/18/2019 3:34:43 PM(UTC)
Lansweeper Top 10 users submitting tickets
by  LGuth  
Go to last post Go to first unread
Last post: 7/17/2019 9:29:44 PM(UTC)
Lansweeper Microsoft Patch Tuesday Report - July 2019
by  Noobmode   Go to last post Go to first unread
Last post: 7/17/2019 8:48:08 PM(UTC)
Lansweeper Custom Scanning - file not existing
by  Bruce Garoutte  
Go to last post Go to first unread
Last post: 7/17/2019 8:19:50 PM(UTC)
Lansweeper MouseJack vulnerability affected devices report
by  Viper   Go to last post Go to first unread
Last post: 7/16/2019 5:19:14 PM(UTC)
Lansweeper TPM support
by  Sander Eerdekens  
Go to last post Go to first unread
Last post: 7/16/2019 1:38:17 PM(UTC)