7 Replies Latest reply on Apr 14, 2009 10:48 AM by mtxpert

    Custom Report showing a total of times nodes went down for the last month


      I've created a report that works very well that allows me to view the total # of times a node has gone down for the past month. I can get the counts to work I'm just trying to utilize the compute command to give me a total # at the bottom of the report for the node down events.


      Here's my SQL - it works but doesn't show the total at the bottom:


      DECLARE @StartDate DateTime
      DECLARE @EndDate DateTime

      SET @StartDate = CAST((ROUND(CAST(GetDate() - 30 AS FLOAT), 0, 1)) as datetime)
      SET @EndDate = GetDate()

      SELECT Nodes.SysName, Interfaces.InterfaceAlias, Count(events.EventType) AS CountOfEventType

      FROM (Events LEFT JOIN Interfaces ON Events.NetObjectID = Interfaces.NodeID) LEFT JOIN Nodes ON Events.NetObjectID = Nodes.NodeID

      WHERE ((Interfaces.InterfaceAlias) Like '%cir%') AND ((Events.EventType)=1) AND Events.EventTime >= @StartDate AND Events.EventTime <=  @EndDate
      GROUP BY Nodes.SysName, Interfaces.InterfaceAlias
      ORDER BY CountOfEventType DESC
      Compute sum(Count(events.EventType))