2 Replies Latest reply on Apr 21, 2009 9:12 PM by elsaonita

    Advanced SQL

    elsaonita

      How can I join this 2 scripts to create a report with Event time and the currently down nodes..

       

      SELECT
      Nodes.Caption AS NodeName, Nodes.IP_Address AS IP_Address, Nodes.Status AS Status, Interfaces.InterfaceName AS Interface_Name, Interfaces.Status AS Status_1, Nodes.StatusDescription AS Status_Description, Interfaces.InterfaceLastChange AS Interface_Last_Change
       FROM
      Nodes INNER JOIN Interfaces ON (Nodes.NodeID = Interfaces.NodeID)
       WHERE 
      (
        (Nodes.Status = '2') OR
        (Interfaces.Status = '2')
      )

       

      and

       

      SELECT Nodes.NodeID AS NodeID,
      Events.EventTime AS Event_Time,
      Nodes.Caption AS NodeName,
      Nodes.IP_Address AS IP_Address,
      ( STR(Events.EventType) + '.gif') AS EventTypeIcon,
      Events.Message AS Message

      FROM
      Nodes INNER JOIN (Events INNER JOIN EventTypes Events_EventTypes ON (Events.EventType = Events_EventTypes.EventType)) ON (Nodes.NodeID = Events.NetworkNode)


      WHERE
      ( EventTime BETWEEN 39552 AND 39918 )
       AND 
      (
        (Events.EventType = 1)
      )



      ORDER BY 2 DESC

        • Re: Advanced SQL
          Yann

          Hi,

          Is the following query returning what you need?

          ------------------------------------------

          SELECT Nodes.NodeID AS NodeID,
          Events.EventTime AS Event_Time,
          Nodes.Caption AS NodeName,
          Nodes.IP_Address AS IP_Address,
          ( STR(Events.EventType) + '.gif') AS EventTypeIcon,
          Events.Message AS Message

          FROM
          Nodes INNER JOIN (Events INNER JOIN EventTypes Events_EventTypes ON (Events.EventType = Events_EventTypes.EventType)) ON (Nodes.NodeID = Events.NetworkNode)
          INNER JOIN Interfaces ON Nodes.NodeID = Interfaces.NodeID


          WHERE
          ( EventTime BETWEEN 39552 AND 39918 )
           AND
          (
            (Events.EventType = 1)
          )
          AND Nodes.Status = 2 AND Interfaces.Status = 2


          ORDER BY 2 DESC

          ----------------------------------------

          Note that the EventTime BETWEEN 39552 AND 39918 is a constant in your query and means:

          39552 => 14 April 2008

          39918 => 15 April 2008

          (you can use a spreadsheet application to transform the values)

          Have a look at the following post and the DateDiff statements to replace your conditon:

          Re: PREVIEW and DESIGN views in Report Writer - is this a bug or user problem?


          HTH,

          Yann