- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎01-29-2015 03:17 PM
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.
- Labels:
-
Finished Reports
-
Report Center
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎02-09-2021 02:18 PM
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎02-21-2019 10:36 AM
Thank you so much... that's what I was looking for.
Greetings
Sven
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎10-31-2019 03:05 PM
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎06-01-2016 09:35 PM
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎06-01-2016 05:00 PM
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎03-18-2016 01:26 PM
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎03-11-2016 04:17 PM
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎03-11-2016 10:19 AM
But when I run report
Select Top 1000000 *
From ComputerOnlineTime('3', 'mycomputername')
Order By Assetname
Lansweeper show error:
Invalid object name "ComputerOnlineTime"
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎03-11-2016 04:16 PM
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.