Interface Availability Report

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.

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 ()

-- 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