CODEWITHSUNDEEP
sqlsql-serversql-server-2008
user2168435
user2168435

Reputation: 762

SQL query to calculate uptime of application

I have an application that logs information like connections and disconnections in the same table. I would like to try and calculate the total application uptime.

The table structure is like this 

status     time 
connect    2015-09-28 12:05:45.783
disconnect 2015-09-28 12:09:45.783
connect    2015-09-28 12:35:15.783
disconnect 2015-09-28 14:45:35.783

EDIT:

How would I write a query to calculate this? I was trying to use the total time, some thing like this 

/*
status     time 
connect    2015-09-28 12:05:45.783
disconnect 2015-09-28 12:09:45.783
connect    2015-09-28 12:35:15.783
disconnect 2015-09-28 14:45:35.783
*/
declare @0 datetime;
set @0 = '2015-09-28 12:00:0.000'

declare @1 datetime;
set @1 = '2015-09-28 12:05:45.783'

declare @2 datetime;
set @2 =  '2015-09-28 12:09:45.783'

declare @3 datetime;
set @3 =  '2015-09-28 12:35:45.783'

declare @4 datetime;
set @4 = '2015-09-28 14:45:45.783'

select DATEDIFF(MS, @1, @3)
select DATEDIFF(MS, @2, @4)

Upvotes: 0

Views: 2233

Answers (3)

Thorsten Kettner
Thorsten Kettner

Reputation: 94939

This question seems to be abandoned. However in order to help future readers with a similar problem, here is how to approach it:

The table contains alternating records: connect, disconnect, connect, disconnect, etc. We can use LAG to look into a previous row. Doing so, we only have to take the disconnect records then with their previous records' time, which is their connect time of course.

Only, it's important, to first get the previous times and then filter for 'disconnect'. If we would filter directly with WHERE status = 'disconnect, then LAG would give us the previous disconnect record's time, of course. So we first write the inner lag query, then use this to filter the desired records outside.

select sum(time_used) as total_seconds
from
(
  select 
    datediff(second, log_time, lag(log_time) over (order by log_time)) as time_used,
    status
  from connection_logs
) times_used
where status = 'disconnect';

SQL fiddle: http://www.sqlfiddle.com/#!3/dfba6a/1

We should usually also think about the case of a missing connect or disconnect record, so we'd have two consecutive connects or disconnects. One solution would be to ignore the record with the missing partner, so we'd look at lag(status) and only use it if that status really is 'connect':

select sum(time_used) as total_seconds
from
(
  select 
    datediff(second, log_time, lag(log_time) over (order by log_time)) as time_used,
    status,
    lag(status) over (order by log_time) as previous_status
  from connection_logs
) times_used
where status = 'disconnect'
and previous_status = 'connect';

SQL fiddle http://www.sqlfiddle.com/#!3/dfba6a/2

Upvotes: 2

Kendall
Kendall

Reputation: 381

Using a common table expression, you can arrange your status history into a view with a start time and end time for each record, like this: http://sqlfiddle.com/#!3/18438/1

with t1 (stat, ts, ndx) as (
  select stat, ts, row_number() over(order by ts)
  from connected
)
,
t2 (stat, startts, endts) as (
  select t1.stat, t1.ts, coalesce(t2.ts, getdate())
  from t1
  left join t1 t2
  on t2.ndx = t1.ndx + 1
)
select * from t2
order by startts

I used the coalesce(t2.ts, getdate()) to get the current date time for the last record in your range.

If you want to find the amount of time that each status was held, use the datediff() function, like this: http://sqlfiddle.com/#!3/18438/2

with t1 (stat, ts, ndx) as (
  select stat, ts, row_number() over(order by ts)
  from connected
)
,
t2 (stat, startts, endts) as (
  select t1.stat, t1.ts, coalesce(t2.ts, getdate())
  from t1
  left join t1 t2
  on t2.ndx = t1.ndx + 1
)
select stat, startts, endts, datediff(second, startts, endts) as elapsed
from t2
order by startts

And if you want total up and down time, that's just math and formatting: http://sqlfiddle.com/#!3/e90a3/33

with t1 (stat, ts, ndx) as (
  select stat, ts, row_number() over(order by ts)
  from connected
)
,
t2 (stat, startts, endts) as (
  select t1.stat, t1.ts, coalesce(t2.ts, getdate())
  from t1
  left join t1 t2
  on t2.ndx = t1.ndx + 1
)
,
t3 (stat, totaltime) as (
  select stat, sum(datediff(second, startts, endts))
  from t2
  group by stat
)
select stat,
  cast(totaltime / 86400 as varchar) + ' ' + 
  right('0' + cast((totaltime % 86400) / 3600 as varchar),2) + ':' +
  right('0' + cast((totaltime % 3600) / 60 as varchar),2)  + ':' +
  right('0' + cast(totaltime % 60 as varchar),2)
  as [Days HH:MM:SS]
from t3

Upvotes: 4

user4622594
user4622594

Reputation:

this is quick and dirt but it works - using a common table expression (cte):

;WITH cte
AS
(SELECT t.statustext, logtime, ROW_NUMBER() OVER(PARTITION BY statustext ORDER BY logtime) AS id
FROM    dbo.NameOfYourTable t
)

SELECT  con.logtime AS LoginTime, 
        discon.logtime AS LogoutTime, 
        DATEDIFF(MINUTE,con.logtime,discon.logtime) AS SessionTimeInMinutes
FROM    cte AS con
        INNER JOIN cte AS discon ON con.id = discon.id
WHERE   con.statustext = 'connect'
        AND discon.statustext = 'disconnect'

Note: for my example i changed column names to statustext and logtime. statusand timeare SQL Keywords and should not be used as column names!

Upvotes: 0

Related Questions