1 Reply Latest reply on Aug 5, 2010 10:56 AM by William_Powley

    First SQL Query Assistance

    William_Powley

      Hello all.  I've been tasked with adding a column which shows the number of outages for each device over the last 30 months and last year (two seperate reports) to an existing Availability Report.

      Here is what I have so far, but I can't seem to get the results I want to display within the main report.

      I'm close.  Could you guys take a look at this and let me know why I can't bring these two queries together?

      First query to get the number of outages experienced:

      Select Nodes.Caption AS NodeName, COUNT(Events.EventType) AS num_outages
      From nodes JOIN events on nodes.nodeid = events.networknode

      Where
      (EventType = '5000')
      AND
      (Message LIKE '%is down')
      AND
      (Nodes.MachineType = 'Cisco ASA 5505')

      Group BY Nodes.Caption, EventType;

      Second query which combines an availability query from Solarwinds with the one above, but shows the total count from the subquery rather than the individual outage count per device.  I know I'm missing something here, but have changed this many different ways and think I'm just missing a trick or something I'm not familiar with like a rule.

      SELECT  TOP 10000 CONVERT(DateTime,
      '01/01/' + LTRIM(YEAR(DateTime)),
      101) AS SummaryYear,
      Nodes.NodeID AS NodeID,
      Nodes.Caption AS NodeName,
      Nodes.IP_Address AS IP_Address,
      AVG(ResponseTime.AvgResponseTime) AS AVERAGE_of_Average_Response_Time,
      AVG(ResponseTime.Availability) AS AVERAGE_of_Availability,
      AVG(ResponseTime.PercentLoss) AS AVERAGE_of_Percent_Loss,
      (SELECT COUNT(Events.EventType)
             From nodes JOIN events on nodes.nodeid = events.networknode
             Where
                ((EventType = '5000') AND (Message LIKE '%is down'))
      ) AS num_outages

      FROM
      Nodes INNER JOIN ResponseTime ON (Nodes.NodeID = ResponseTime.NodeID)

      WHERE
      ( DateTime BETWEEN 40362 AND 40393 )
       AND 
      (
        (Nodes.MachineType = 'Cisco ASA 5505') OR
        (Nodes.MachineType = 'Cisco 2801')
      )

      GROUP BY CONVERT(DateTime, '01/01/' + LTRIM(YEAR(DateTime)), 101),
      Nodes.NodeID, Nodes.Caption, Nodes.IP_Address

      ORDER BY SummaryYear ASC, 3 ASC

      Thanks again in advance.

        • Re: First SQL Query Assistance
          William_Powley

          Nevermind All.  I figured it out finally.  I think I was making it too complicated.

          If you could verify the code that would be awesome though!

          AVG(ResponseTime.AvgResponseTime) AS AVERAGE_of_Average_Response_Time,
          AVG(ResponseTime.Availability) AS AVERAGE_of_Availability,
          AVG(ResponseTime.PercentLoss) AS AVERAGE_of_Percent_Loss,
          (SELECT COUNT(Events.EventType)
                 From events
                 Where
                    ((nodes.nodeid = events.networknode)
                    AND
                    (EventType = '5000')
                    AND
                    (Message LIKE '%is down'))
          ) AS num_outages

          FROM
          Nodes INNER JOIN ResponseTime ON (Nodes.NodeID = ResponseTime.NodeID)

          WHERE
          ( DateTime BETWEEN 40362 AND 40393 )
           AND 
          (
            (Nodes.MachineType = 'Cisco ASA 5505') OR
            (Nodes.MachineType = 'Cisco 2801')
          )

          GROUP BY CONVERT(DateTime, '01/01/' + LTRIM(YEAR(DateTime)), 101),
          Nodes.NodeID, Nodes.Caption, Nodes.IP_Address

          ORDER BY SummaryYear ASC, 3 ASC