Need Interface IP Address details in Downtime Report

tt_2

Hi,

I am creating one report for 'Interface Downtime Report' in SolarWinds Orion NPM, which consist of when it went down, when it came up, total duration of downtime, interface name and respective node name. In this, I achieved to get the desire results by creating SQL query from ChatGpt. but unable to get the desire IP Address of that respective Interface.

Below is my SQL query for the same:

INTERFACE DOWNTIME REPORT:

SELECT
E1.EventTime AS [Down_Time],
E2.EventTime AS [Up_Time],
CONVERT(varchar(8), DATEADD(SECOND, DATEDIFF(SECOND, E1.EventTime, E2.EventTime), 0), 108) AS [Downtime],
I.FullName AS [Interface_Name],
ND.SysName AS [Node_Name]
FROM Events E1
CROSS APPLY (
SELECT TOP 1 E2.EventTime
FROM Events E2
WHERE
E2.NetObjectID = E1.NetObjectID
AND E2.EventType = 11 -- Interface Up
AND E2.EventTime > E1.EventTime
ORDER BY E2.EventTime ASC
) AS E2(EventTime)
JOIN Interfaces I
ON E1.NetObjectID = I.InterfaceID
JOIN NodesData ND
ON I.NodeID = ND.NodeID
WHERE
E1.NetObjectType = 'I'
AND E1.EventType = 10 -- Interface Down
ORDER BY E1.EventTime;

Can I get the additional column to get the IP Address of that respective Interface which got reported in the above query?

Thank you in Advance!

Regards,

Tushar Tawde

Lofstrand

Hi

We always suggest using SWQL instead of SQL where possible. But now that you almost have all the query I adjusted this on.

Joined in the table "NodeIPAddresses" on both interfaceindex and nodeid. Then if no IP was found bound to that interface I added the nodes IP instead, and added a * to mark it's the nodes IP.

SELECT
	E1.EventTime AS [Down_Time],
	E2.EventTime AS [Up_Time],
	CONVERT(varchar(8), DATEADD(SECOND, DATEDIFF(SECOND, E1.EventTime, E2.EventTime), 0), 108) AS [Downtime],
	I.FullName AS [Interface_Name],
	ND.SysName AS [Node_Name],
	ISNULL(IPA.IPAddress,CONCAT(ND.IP_Address,' *')) AS IPAddress
FROM Events E1
CROSS APPLY (
	SELECT TOP 1 E2.EventTime
	FROM Events E2
	WHERE
	E2.NetObjectID = E1.NetObjectID
	AND E2.EventType = 11 -- Interface Up
	AND E2.EventTime > E1.EventTime
	ORDER BY E2.EventTime ASC
) AS E2(EventTime)
INNER JOIN Interfaces I ON E1.NetObjectID = I.InterfaceID
INNER JOIN NodesData ND ON I.NodeID = ND.NodeID
LEFT OUTER JOIN NodeIPAddresses AS IPA ON I.InterfaceIndex=IPA.InterfaceIndex and I.NodeID=IPA.NodeID
WHERE
	E1.NetObjectType = 'I'
	AND E1.EventType = 10 -- Interface Down
ORDER BY E1.EventTime;