2 Replies Latest reply on Apr 11, 2011 11:30 AM by hehe7nic

    Interface Availability Report

      I'd like to create an interface availability report.

      I don't know how to modify the SQL report from the thwack. 

      My filter condition is: Node Name = A (list all A's interfaces' availability)

      Time Frame is: January, February, March.......(Monthly)

      Can you help me?

        • Re: Interface Availability Report
          rschroeder

          Start out by looking at how an existing report is built, then modify it to meet your needs.  For example, open Report Writer, open Daily Node Availability, and choose Node Availability - Last Month.

          When you click on that report, the Report Designer will open.  Review the tabs and adjust/add/remove items to make it work for your needs.  In the Select Fields tab you might want to right-click on one of the rows and choose "Add a new field."  Then click on the underscored asterisk links to choose the items you want to track.

          Review the other tabs to see if there are areas you'd like to change, and when you're done, click the Preview button.  If the report shows what you want, save it with a new name.  If it's not quite right, keep adjusting it until you get what you need.

          Keep in mind that tracking interface availability may not be what you want.  In my environment, interfaces come up and down as PC's & servers are rebooted.  It makes more sense for me to track node availability instead of Interface availability, but your environment is likely different than mine.

          warning: I've seen some flakiness with Report Writer.  It can unexpected close and you'll lose your work.  Forewarned is forearmed--be smart and save your changes frequently, with a name you'll recognize, so you don't have to start over.

            • Re: Interface Availability Report

              It's a SQL report.

              I mark the time frame condition and nodes.caption condition. The nodes.caption filter is work. But the time frame does not. No matter how i change the time frame, the result always shows last month's data

               

               

               

              -- Modified query originally submitted by mgibson on Thwack (Re: Interface Availability Report)

              -- Modified date to report last month's data

              -- Sotherland 04/09/2010

              use netperfmon

              IF exists (SELECT * FROM dbo.sysobjects where id = object_id(N'dbo.SUMTABLE'))

               

              DROP TABLE SUMTABLE

               

              SELECT 

              EventTime,

              Interfaces.Caption,

              NetObjectID,

              NodeID,

               

              DATEDIFF(MINUTE, StartTime.EventTime,

              (SELECT TOP 1 EventTime

              FROM Events AS Endtime

              WHERE 

              EndTime.EventTime > StartTime.EventTime 

              AND EndTime.EventType = 11

              AND EndTime.NetObjectType = 'I'

              AND EndTime.NetObjectID = StartTime.NetObjectID

              ORDER BY EndTime.EventTime)) 

              AS OutageDurationInMinutes

              INTO SUMTABLE

              FROM Events AS StartTime 

              INNER JOIN Interfaces ON StartTime.NetObjectID = Interfaces.InterfaceID

              WHERE

              (StartTime.EventType = 10) 

              AND (StartTime.NetObjectType = 'I')

              AND (eventtime between dateadd(month, -1, getdate()) and getdate())

              GROUP BY 

              Interfaces.NodeID,

              NetObjectID,

              Interfaces.Caption, 

              Interfaces.InterfaceID,

              StartTime.EventTime,

              StartTime.NetObjectID

               

               

              Select DISTINCT 

              Nodes.Caption AS HostName, 

              interfaces.caption as Interface,

               

              COALESCE(CONVERT(char(10),100-Round(SUM(Convert(float,OutageDurationInMinutes)/(DATEDIFF(Minute, '03/01/2011 00:00:00', '03/31/2011 23:59:59') + 1 )*100),2)),'100') + '%' AS "Avalibility This Month",

              COALESCE(SUM(OutageDurationInMinutes), 0) AS OutageTimeInMinute

              FROM (Nodes INNER JOIN Interfaces ON Interfaces.NodeID = Nodes.NodeID) LEFT JOIN SUMTABLE ON SUMTABLE.NetObjectID = Interfaces.InterfaceID

              Where Nodes.Caption like '%mpls%'

              GROUP BY Nodes.Caption, interfaces.caption

               

              ORDER BY Nodes.Caption

               

              DROP TABLE SUMTABLE