CODEWITHSUNDEEP
datetimereporting-services
tcox8
tcox8

Reputation: 3

SSRS report finding longest duration of computer idle time

I have a report I've created using SSRS. It currently is pulling the Date and Time for the last input received on a computer. My goal is to find the longest time a computer was idle. I have the data but just need to figure out how to logically extract it.

Here is a screenshot of example data: (https://i.sstatic.net/jRFs4.png)

Based off of this I would be able to say that the longest time duration that the computer has been idle would be 3 days on the 19th. Any help is greatly appreciated!

EDIT:

Below is my query for my original dataset:

Select 
  a.Name0 AS [Computer Name],
  b.LastKeyInput0 AS [Last Input Time]
From
  v_R_System a
    Inner Join
  v_HS_UHHG640 b
on b.ResourceID = a.ResourceID
where a.Name0 = @ComputerName

This query will return a computer and history of it's idle times as linked below:

Query Designer Results

Question: How do I tie in your solution with this query? THANK YOU FOR YOUR HELP!

Upvotes: 0

Views: 47

Answers (1)

Alan Schofield
Alan Schofield

Reputation: 21748

I've replicated your data for this example. I'm not sure what you want in your final output but this will give you enough to go on. This answer is rather long winded as I've tried to explain each step. Ultimately you could remove half the code but building it bit by bit helps understanding. If you want to just look at the final query, go to the bit labelled "Finally:" below

To create your data I simple did the following

DECLARE @t TABLE(LastInputTime datetime)

INSERT INTO @t VALUES 
('2020-09-18 11:13'),
('2020-09-19 12:58'),
('2020-09-19 12:58'),
('2020-09-19 12:58'),
('2020-09-22 11:32'),
('2020-09-23 10:12'),
('2020-09-24 11:51')

Next we can show this data and the previous rows data using the LAG function.

SELECT 
    *
    , PreviousDateTime = LAG(LastInputTime, 1, LastInputTime) OVER(ORDER BY LastInputTime)
FROM @t

The LAG() function here gets the LastInputTime column from the row that is LAG 1 (the previous row). If not row is found, the 3rd argument says, return the current row's value instead. This default just makes the next bit cleaner.

We get the following results enter image description here As you can see the PreviousDateTime shows the previous row (except on the first row where no previous row exists).

Now we have the previous row's data, we can use it to calculate the time between the two values with

IdleTimeInSeconds = DATEDIFF(s, LAG(LastInputTime, 1, LastInputTime) OVER(ORDER BY LastInputTime), LastInputTime)

Here we use DATEDIFF() and get the number of seconds (s), from the PreviousDateTime (out lag function), to the LastInputDateTime.

So our query now looks like this...

SELECT 
    *
    , PreviousDateTime = LAG(LastInputTime, 1, LastInputTime) OVER(ORDER BY LastInputTime)
    , IdleTimeInSeconds = DATEDIFF(s, LAG(LastInputTime, 1, LastInputTime) OVER(ORDER BY LastInputTime), LastInputTime)
FROM @t

And gives this result. enter image description here

Keeping it simple, we can now use this as a subquery and get the top 1 row ordered by IdleTimeInSeconds in descending order. We can wrap the result in a bit of text so it makes more sense in a report.

Finally:

This is the final query which you can run to test as it requires no actual data

DECLARE @t TABLE(LastInputTime datetime)

INSERT INTO @t VALUES 
('2020-09-18 11:13'),
('2020-09-19 12:58'),
('2020-09-19 12:58'),
('2020-09-19 12:58'),
('2020-09-22 11:32'),
('2020-09-23 10:12'),
('2020-09-24 11:51')

SELECT top 1 
        *
        , Message = 'Longest idle time was '
            + CAST(IdleTimeInSeconds as varchar(20))
            + ' seconds, from '
            + CAST(LAG(LastInputTime, 1, LastInputTime) OVER(ORDER BY LastInputTime) as varchar(20))
            + ' to '
            + CAST(LastInputTime as varchar(20))

    FROM (
        SELECT 
            *
            , PreviousDateTime = LAG(LastInputTime, 1, LastInputTime) OVER(ORDER BY LastInputTime)
            , IdleTimeInSeconds = DATEDIFF(s, LAG(LastInputTime, 1, LastInputTime) OVER(ORDER BY LastInputTime), LastInputTime)
        FROM @t
    ) x ORDER BY IdleTimeInSeconds DESC

This gives the following result.

enter image description here

UPDATE

Based on you supplied query, the final query will look like this...

SELECT TOP 1 
        *
        , Message = 'Longest idle time was '
            + CAST(IdleTimeInSeconds as varchar(20))
            + ' seconds, from '
            + CAST(LAG([Last Input Time], 1, [Last Input Time]) OVER(ORDER BY [Last Input Time]) as varchar(20))
            + ' to '
            + CAST([Last Input Time] as varchar(20))
    FROM (
          SELECT
            a.Name0 AS [Computer Name],
            b.LastKeyInput0 AS [Last Input Time],
            PreviousDateTime = LAG(b.LastKeyInput0, 1, b.LastKeyInput0) OVER(ORDER BY b.LastKeyInput0),
            , IdleTimeInSeconds = DATEDIFF(s, LAG(b.LastKeyInput0, 1, b.LastKeyInput0) OVER(ORDER BY b.LastKeyInput0), b.LastKeyInput0)
          FROM v_R_System a
              JOIN v_HS_UHHG640 b on b.ResourceID = a.ResourceID
          WHERE a.Name0 = @ComputerName
         ) x ORDER BY IdleTimeInSeconds DESC

Upvotes: 1

Related Questions