2 Replies Latest reply on Jun 10, 2013 4:10 AM by allan1

    SQL help

    allan1

      Hi,

       

       

       

      I need help with a report. I'm not an SQL expert and need your help :-)

       

       

       

      Our system has this SQL report running. It shows the name of nodes that are down and for how long they have been down.

      How do I add the IP address to the list?

       

       

       

       

      SELECT

      ( STR(Events.EventType) + '.gif') AS EventTypeIcon,

      Nodes.Caption AS Evento,

      Nodes.NodeID AS NodeID,

      (

      CONVERT(VARCHAR(50),(datediff(dd, max(Events.EventTime), getdate()))) + 'd '+

      CONVERT(VARCHAR(50),(datediff(minute, max(Events.EventTime), getdate())%1440)/60 )+ 'h '+

      CONVERT(VARCHAR(50),((datediff(minute, max(Events.EventTime), getdate())%1440)%60)) + 'm'

      ) as LastTimeUP

      FROM

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

       

      where (

       

      (Events.NetworkNode = Nodes.NodeID) AND

      (Events.EventType = 1) AND

      (Nodes.Status = '2')

       

      )

      group by Nodes.Caption,Events.NetworkNode, Events.EventType, Nodes.NodeID

       

       

      Br,

        • Re: SQL help
          Leon Adato

          SELECT

          ( STR(Events.EventType) + '.gif') AS EventTypeIcon,

          Nodes.Caption AS Evento,

          Nodes.NodeID AS NodeID,

          Nodes.IP_Address,

          (

          CONVERT(VARCHAR(50),(datediff(dd, max(Events.EventTime), getdate()))) + 'd '+

          CONVERT(VARCHAR(50),(datediff(minute, max(Events.EventTime), getdate())%1440)/60 )+ 'h '+

          CONVERT(VARCHAR(50),((datediff(minute, max(Events.EventTime), getdate())%1440)%60)) + 'm'

          ) as LastTimeUP

          FROM

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

           

          where (

           

          (Events.NetworkNode = Nodes.NodeID) AND

          (Events.EventType = 1) AND

          (Nodes.Status = '2')

           

          )

          group by Nodes.Caption,Events.NetworkNode, Events.EventType, Nodes.NodeID, Nodes.IP_Address