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,510  
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 Deployment Java
by  mlachance   Go to last post Go to first unread
Last post: Today at 4:46:38 PM(UTC)
Lansweeper Custom OID Scanning Range
by  tcooper  
Go to last post Go to first unread
Last post: Today at 4:32:59 PM(UTC)
Lansweeper Approved Software
by  AZHockeyNut   Go to last post Go to first unread
Last post: Today at 4:07:58 PM(UTC)
Lansweeper Scanning targets - ignore assets with LSagent
by  AZHockeyNut  
Go to last post Go to first unread
Last post: Today at 4:01:44 PM(UTC)
Lansweeper LastLogon Data
by  ghelpdesk   Go to last post Go to first unread
Last post: Today at 3:53:04 PM(UTC)
Lansweeper Duplicate MAC address during asset rename
by  TJ  
Go to last post Go to first unread
Last post: Today at 3:48:49 PM(UTC)
Lansweeper Repeating Deployments
by  mlachance   Go to last post Go to first unread
Last post: Today at 1:25:48 PM(UTC)
Lansweeper Deployment - Ideas
by  mlachance  
Go to last post Go to first unread
Last post: Today at 1:22:40 PM(UTC)