3 Replies Latest reply on Sep 11, 2013 2:28 PM by azabielski

    Question about Report Writer and SQL

    azabielski

      I have a 2 part question:

       

      1. Is there a way to color a row in a report based on the status
      2. I have a query i created for this report and I am trying to pull the last event that happened for each node its almost there however its pulling the first event and not the last one HELP PLEASSEEE

       

      select Nodes.GroupStatus as img,Nodes.VendorIcon as vimg,Nodes.NodeID,MIN(Nodes.Caption) as Node,MAX(Events.Message) as Issue,MAX(Events.EventTime) as ChangedTime, MAX(IP_Address) as IP from Events

      left join Nodes on Nodes.NodeID = Events.NetworkNode

      where (Status in (2,3,4,7,8,10,12,14,15,16,17,19,24,25)) OR (ChildStatus in (2,3,4,7,8,10,12,14,15,16,17,19,24,25))

      group by Nodes.NodeID, GroupStatus, VendorIcon

      order by ChangedTime DESC

       

      Thank you in advance!

        • Re: Question about Report Writer and SQL
          azabielski

          Ok so working it out more I almost have it completed however there are some bad messages coming in that shouldn't stating NULL in the message any Advance SQL people please help! Driving myself crazy here.

           

          SELECT Nodes.NodeID,Nodes.GroupStatus as img,Nodes.VendorIcon as vimg,Nodes.Caption AS Node,

             (select TOP 1 Message from Events

             where Message NOT LIKE '%Up%'

             AND NetworkNode = Nodes.NodeID

             AND (

          ( Events.EventType = 2) OR

          ( Events.EventType = 10) OR

          ( Events.EventType = 12) OR

          ( Events.EventType = 15) OR

          ( Events.EventType = 23) OR

          ( Events.EventType = 25) OR

          ( Events.EventType = 30) OR

          ( Events.EventType = 50) OR

          ( Events.EventType = 51) OR

          ( Events.EventType = 58) OR

          ( Events.EventType = 60) OR

          ( Events.EventType = 64) OR

          ( Events.EventType = 65) OR

          ( Events.EventType = 210) OR

          ( Events.EventType = 501) OR

          ( Events.EventType = 505) OR

          ( Events.EventType = 507) OR

          ( Events.EventType = 509) OR

          ( Events.EventType = 510) OR

          ( Events.EventType = 513) OR

          ( Events.EventType = 515) OR

          ( Events.EventType = 520) OR

          ( Events.EventType = 521) OR

          ( Events.EventType = 522) OR

          ( Events.EventType = 523) OR

          ( Events.EventType = 525) OR

          ( Events.EventType = 526) OR

          ( Events.EventType = 527) OR

          ( Events.EventType = 528) OR

          ( Events.EventType = 530) OR

          ( Events.EventType = 531) OR

          ( Events.EventType = 532) OR

          ( Events.EventType = 605) OR

          ( Events.EventType = 701) OR

          ( Events.EventType = 1001) OR

          ( Events.EventType = 1002) OR

          ( Events.EventType = 5000))

             order by EventID DESC) as Message,

          max(Events.EventTime) AS Event_Time, IP_Address

          FROM Nodes

          INNER JOIN (Events

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

          INNER JOIN AlertStatus ON (ActiveObject = Events.NetObjectID AND Events.NetworkNode = Nodes.NodeID)

          WHERE

          ((Nodes.Status in (0,3,4,7,8,10,12,14,15,16,17,19,24,25)) OR (Nodes.ChildStatus in (0,2,3,4,7,8,10,12,14,15,16,17,19,24,25))

            OR (AlertStatus.State = 2))

          group by Nodes.Caption,Nodes.GroupStatus,Nodes.VendorIcon,Nodes.NodeID,IP_Address

          ORDER BY 6 DESC

            • Re: Question about Report Writer and SQL
              azabielski

              OK SO I AM CLOSERRRRRR however there are still a few 1 offs that are reporting when i can't find any matching conditions in the tables that are in my where clause ANYONE out there can run some of this and verify it?

               

              SELECT Nodes.NodeID,Nodes.GroupStatus as img,Nodes.VendorIcon as vimg,Nodes.Caption AS Node,

                 (select TOP 1 Message from Events

                 where Message NOT LIKE '%Up%'

                 AND Message NOT LIKE '%added%' < ----- Not sure this does anything really.....

                 AND NetworkNode = Nodes.NodeID

                 AND (

              ( Events.EventType = 2) OR

              ( Events.EventType = 10) OR

              ( Events.EventType = 12) OR

              ( Events.EventType = 15) OR

              ( Events.EventType = 23) OR

              ( Events.EventType = 25) OR

              ( Events.EventType = 30) OR

              ( Events.EventType = 50) OR

              ( Events.EventType = 51) OR

              ( Events.EventType = 58) OR

              ( Events.EventType = 60) OR

              ( Events.EventType = 64) OR

              ( Events.EventType = 65) OR

              ( Events.EventType = 210) OR

              ( Events.EventType = 501) OR

              ( Events.EventType = 505) OR

              ( Events.EventType = 507) OR

              ( Events.EventType = 509) OR

              ( Events.EventType = 510) OR

              ( Events.EventType = 513) OR

              ( Events.EventType = 515) OR

              ( Events.EventType = 520) OR

              ( Events.EventType = 521) OR

              ( Events.EventType = 522) OR

              ( Events.EventType = 523) OR

              ( Events.EventType = 525) OR

              ( Events.EventType = 526) OR

              ( Events.EventType = 527) OR

              ( Events.EventType = 528) OR

              ( Events.EventType = 530) OR

              ( Events.EventType = 531) OR

              ( Events.EventType = 532) OR

              ( Events.EventType = 605) OR

              ( Events.EventType = 701) OR

              ( Events.EventType = 1001) OR

              ( Events.EventType = 1002) OR

              ( Events.EventType = 5000))

                 order by EventID DESC) as Message,

              max(Events.EventTime) AS Event_Time, IP_Address, MAX(EventID) as EventID

              FROM Nodes

              INNER JOIN (Events

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

              INNER JOIN AlertStatus ON (ActiveObject = Events.NetObjectID AND Events.NetworkNode = Nodes.NodeID)

              INNER JOIN Volumes ON (ActiveObject = VolumeID OR Volumes.NodeID = Nodes.NodeID)

              WHERE

              ((Nodes.Status in (0,3,4,7,8,10,12,14,15,16,17,19,24,25)) OR (Nodes.ChildStatus in (0,2,3,4,7,8,10,12,14,15,16,17,19,24,25))

                OR (AlertStatus.State = 2 AND (Events.NetObjectType = 'V' AND Volumes.VolumeID = Events.NetObjectID AND Volumes.NodeID = Nodes.NodeID)))

              group by Nodes.Caption,Nodes.GroupStatus,Nodes.VendorIcon,Nodes.NodeID,IP_Address

              ORDER BY 6 DESC

            • Re: Question about Report Writer and SQL
              azabielski

              GOT IT for anyone else that this might help

               

              select MAX(Nodes.NodeID) as NodeID,MAX(Nodes.GroupStatus) as IMG,MAX(VendorIcon) as vimg,MAX(EventID) AS EventID,Caption,MAX(Message) as Message,MAX(EventTime) as Event_Time,MAX(NetObjectID) as ObjectID,NetworkNode,MAX(Nodes.IP_Address) as IP_Address

              from Events

              left join AlertStatus on AlertStatus.ActiveObject = Events.NetObjectID

              left join Nodes on Events.NetworkNode = Nodes.NodeID

              ((Events.NetObjectType =

                CASE

                WHEN AlertStatus.ObjectType = 'Volume' THEN 'V'

                WHEN AlertStatus.ObjectType = 'Group' THEN 'C'

                WHEN AlertStatus.ObjectType = 'APM: Application' THEN 'AA'

                WHEN AlertStatus.ObjectType = 'Hardware Sensor' THEN 'HWHS'

                END

                )

              OR (Nodes.Status in (0,3,4,7,8,10,12,14,15,16,17,19,24,25) OR Nodes.ChildStatus in (0,2,3,4,7,8,10,12,14,15,16,17,19,24,25)

              AND Events.EventType IN (2,10,12,15,23,25,30,50,51,58,60,64,65,210,501,505,507,509,510,513,515,520,521,522,523,525,526,527,528,530,531,532,605,701,1001,1002,5000)

              )) AND EventID = (Select MAX(EventID) from Events WHERE ((NetObjectID = AlertStatus.ActiveObject) AND (NetworkNode = Nodes.NodeID)))

              AND Events.Message NOT LIKE '%Up%'

              group by NetworkNode,Caption

              order by MAX(Events.EventTime) DESC