Wrox Programmer Forums
Go Back   Wrox Programmer Forums > SQL Server > SQL Server 2005 > SQL Server 2005
| Search | Today's Posts | Mark Forums Read
SQL Server 2005 General discussion of SQL Server *2005* version only.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server 2005 section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
  #1 (permalink)  
Old July 26th, 2007, 09:37 AM
Registered User
 
Join Date: Jul 2007
Location: San Jose, San Jose, Costa Rica.
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default Query from Solarwinds Database Help.

Hi all I'm a beginner at SQL and understand the principals. We have a network monitoring database, with a reports creator that's powerful to a certain extent. However when i try this query, i get total times of the events in our network monitor. I'm looking for not total times from all nodes, but anything in the last 4 months that is over 239minutes in outage duration. Here is a sample of the query perhaps someone can point me in the right direction.

Thanks,

Kevin

SELECT
    StartTime.EventTime,
    Nodes.Caption,
    Nodes.Location,
    StartTime.Message,
    DATEDIFF(Mi, StartTime.EventTime,
    (SELECT TOP 1
        EventTime
        FROM Events AS Endtime
        WHERE EndTime.EventTime > StartTime.EventTime AND EndTime.EventType = 5
            AND EndTime.NetObjectType = 'N'
            AND EndTime.NetworkNode = StartTime.NetworkNode
        ORDER BY EndTime.EventTime)) AS OutageDurationInMinutes

FROM Events StartTime INNER JOIN Nodes ON StartTime.NetworkNode = Nodes.NodeID
WHERE (StartTime.EventType = 1) AND (StartTime.NetObjectType = 'N') AND
eventtime between dateadd(month, -1, getdate()) and getdate() AAND 'OutageDurationInMinutes' >= '239'
ORDER BY OutageDurationInMinutes ASC

  #2 (permalink)  
Old July 27th, 2007, 03:57 PM
Friend of Wrox
 
Join Date: May 2006
Location: Helsingborg, , Sweden.
Posts: 246
Thanks: 0
Thanked 0 Times in 0 Posts
Default

SELECT EventTime,
        Caption,
        Location,
        Message,
        OutageDurationInMinutes
FROM (
            <your query here without last order by>
        ) AS d
WHERE OutageDurationInMinutes >= 239
ORDER BY OutageDurationInMinutes



Similar Threads
Thread Thread Starter Forum Replies Last Post
database query problem naveed77 Beginning VB 6 2 February 12th, 2008 06:47 AM
Database Update Query kgriffin ASP.NET 1.0 and 1.1 Basics 3 May 6th, 2005 12:54 PM
Problem with my database query jmurrayhead Classic ASP Databases 2 March 12th, 2004 03:48 AM
Multiple Database query nlicata SQL Language 1 August 15th, 2003 03:34 AM
database query Darren JSP Basics 0 July 12th, 2003 03:57 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.