#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2012
    Posts
    2
    Rep Power
    0

    MS SQL - Query issue - Any Ideas?


    Hi All,

    I have a table with data simular to the following in MS SQL

    SysName DateTime Status
    SysA 01/01/2012 01:00 Up
    SysB 01/01/2012 01:00 Down
    SysC 01/01/2012 01:00 Up
    SysA 01/01/2012 01:10 Up
    SysB 01/01/2012 01:10 Down
    SysC 01/01/2012 01:10 Down
    SysA 01/01/2012 01:20 Down
    SysB 01/01/2012 01:20 Down
    SysC 01/01/2012 01:20 Down
    SysA 01/01/2012 01:30 Up
    SysB 01/01/2012 01:30 Down
    SysC 01/01/2012 01:30 Up
    SysA 01/01/2012 01:40 Up
    SysB 01/01/2012 01:40 Down
    SysC 01/01/2012 01:40 Up
    SysA 01/01/2012 01:50 Down
    SysB 01/01/2012 01:50 Down
    SysC 01/01/2012 01:50 Up
    SysA 01/01/2012 02:00 Down
    SysB 01/01/2012 02:00 Down
    SysC 01/01/2012 02:00 Up
    SysA 01/01/2012 02:10 Up
    SysB 01/01/2012 02:10 Down
    SysC 01/01/2012 02:10 Up

    I need to be able to run a query that will list the the down periods for each SysName in order - so the data from the above would look like the following

    SysName DownTime UpTime
    SysB 01/01/2012 01:00 STILL DOWN
    SysC 01/01/2012 01:10 01/01/2012 01:30
    SysA 01/01/2012 01:20 01/01/2012 01:30
    SysA 01/01/2012 01:50 01/01/2012 02:10

    The main issue I am having is allow the same device to be listed multiple times.

    Any help would be greatly appreciated, I'm sure there's a trick to making this work...

    Thanks!
  2. #2
  3. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2012
    Location
    Kansas City
    Posts
    13
    Rep Power
    0
    So you want the most recent down record returned for each server?

    Code:
    select sysname, max(datetime), status
    from tablename
    where status = 'down'
    group by sysname
    order by sysname
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2012
    Posts
    2
    Rep Power
    0
    Hi,

    Thanks for your response.

    I need a history of all the down events and the duration of the outage for lets say the last 10 outages, it could be the same node was down twice, this is where the difficulty lies as getting the last outage and duration for each is quite easy by selecting the last down time using max and then finding the max last uptime which is < the max last downtime.

    Thanks

IMN logo majestic logo threadwatch logo seochat tools logo