cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
Daniel_B
Lansweeper Alumni
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.
10 REPLIES 10
dussan
Engaged Sweeper II
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
Engaged Sweeper
Hi Daniel,

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

Greetings
Sven
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
Champion Sweeper III
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
Champion Sweeper III
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
Lansweeper Alumni
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
Engaged Sweeper
Now I need to modify the report to sum only the power on night hours (from 7pm to 8am for example)...
dangnhh1
Engaged Sweeper
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"
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.