‎01-29-2015 03:17 PM
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
Select Top 1000000 *
From ComputerOnlineTime('3', 'PartOfAssetName')
Order By Assetname
‎03-07-2016 04:25 PM
Experience Lansweeper with your own data. Sign up now for a 14-day free trial.
Try Now