Interface last status change

I have a simple custom table query to get all the physical interfaces in Down,Warning and Critical status, the problem is that I.LastChange column is not getting the correct time when the interface status last changed.

SELECT N.DisplayName as [Node Name],I.DisplayName as [Interface Name] ,I.StatusDescription AS [Status], I.LastChange,C.Comments,I.DetailsUrl as [Interface Details URL],N.DetailsUrl as [Node Details URL] FROM Orion.NPM.Interfaces I
INNER JOIN Orion.Nodes N on N.NodeID = I.NodeID
INNER join Orion.NPM.InterfacesCustomProperties C on I.InterfaceID = C.InterfaceID WHERE ((I.Status = 2 or I.Status = 3 or I.Status = 16) AND I.InterfaceType = 6 )

Is this field "LastChange" mean for status change, or configuration change?

Thanks.

Find more posts tagged with

swql query

last_changed

interface_status

npm

Accepted answers

All comments

daraka

I think InterfaceLastChange is the one that you have to use for status change. Better do some manual changes in the test interface and verify the same.

donrobert5

I think it saves the time in UTC.

sum_giais

@Adelazz most properties in the DB are stored in UTC, in SWQL queries you can wrap those fields in a TOLOCAL() function.

Most the time you can use SWQL navigational properties to eliminate joining data in, as is the case in the query below. Are the times still off when using the TOLOCAL() function?

SELECT
     i.Node.Caption AS [Node Name]
	,i.Caption AS [Interface Name] 
	,i.StatusDescription AS [Status]
	,TOLOCAL(i.LastChange) AS [LastChange]
    ,i.CustomProperties.Comments
	,i.DetailsUrl AS [Interface Details URL]
	,i.Node.DetailsUrl AS [Node Details URL]
FROM Orion.NPM.Interfaces AS i
WHERE ( ( i.Status = 2 OR i.Status = 3 OR i.Status = 16 ) AND i.InterfaceType = 6 )

https://github.com/solarwinds/OrionSDK/wiki/SWQL-Functions

Adelazz

Thank you @donrobert5 & @sum_giais for your replies, maybe my description was not clear. My concern is not regarding the timezone,Interfaces.LastChange and Interfaces.InterfaceLastChange are both showing the same time, when the interface "Administrative Status" was last changed, not the "Operational Status".

What am looking for is the time when the interface went "OPERATIONAL STATUS" changed to down.

Adelazz

Hi @daraka

I did shutdown and unshut an interfaces, both fields InterfaceLastChange and LastChange are showing the same time.

sum_giais

Could potentially use other sources for that data. Here's an attempt at using various other sources to gain more accurate data.

The idea was that the query (case statement) would look at Orion.NPM.InterfaceNetObjectDowntime, then Orion.Events, then Orion.NPM.InterfaceAvailability, then finally Orion.NPM.Interfaces for the LastChange as a fallback.

You may need to adjust the case statement to suit your requirements... as

Looking in Settings -> All Settings -> Polling Settings - the value of 21 on line 8 of the query in the is derived from the "DOWNTIME HISTORY RETENTION" setting. The value of 30 on lines 10 and 11 are derived from the "HOURLY INTERFACE AVAILABILITY STATISTICS RETENTION". You could tweak these values to be in line with the daily values if you're OK with it being accurate to just the day...

SELECT
     i.Node.Caption AS [Node Name]
	,i.Caption AS [Interface Name] 
	,i.StatusDescription AS [Status]
    ,i.CustomProperties.Comments
	,i.DetailsUrl AS [Interface Details URL]
	,i.Node.DetailsUrl AS [Node Details URL]
    ,CASE WHEN d.DateTimeFrom IS NOT NULL AND DAYDIFF(d.DateTimeFrom, GETUTCDATE()) < 21 THEN ADDMINUTE(-i.PollInterval, d.DateTimeFrom)
          WHEN e.EventTime IS NOT NULL THEN ADDMINUTE(-i.PollInterval, e.EventTime)
          WHEN a1.DateTime IS NOT NULL AND a2.DateTime IS NOT NULL AND DAYDIFF(a1.DateTime, GETUTCDATE()) < 30 AND DAYDIFF(a2.DateTime, GETUTCDATE()) < 30 AND a1.DateTime > a2.DateTime THEN a1.DateTime
          WHEN a1.DateTime IS NOT NULL AND a2.DateTime IS NOT NULL AND DAYDIFF(a1.DateTime, GETUTCDATE()) < 30 AND DAYDIFF(a2.DateTime, GETUTCDATE()) < 30 AND a2.DateTime > a1.DateTime THEN a2.DateTime
        ELSE i.LastChange
     END AS [LastChange]
FROM Orion.NPM.Interfaces AS i
LEFT JOIN ( SELECT d1.EntityId, d1.State, d1.DateTimeFrom, MAX(d1.DateTimeUntilNow) AS [DateTimeUntilNow] FROM Orion.NPM.InterfaceNetObjectDowntime AS d1 WHERE d1.EntityType = 'Orion.NPM.Interfaces' AND d1.DateTimeUntil IS NULL GROUP BY d1.EntityId, d1.State, d1.DateTimeFrom ) AS d ON d.EntityId = i.InterfaceID
LEFT JOIN ( SELECT e1.NetObjectID, MAX(e1.EventTime) AS [EventTime] FROM Orion.Events AS e1 WHERE e1.NetObjectType = 'I' AND e1.EventType IN ( 10, 11 ) GROUP BY e1.NetObjectID ) AS e ON e.NetObjectID = i.InterfaceID
LEFT JOIN ( SELECT ia.InterfaceID, MAX(ia.DateTime) AS [DateTime] FROM Orion.NPM.InterfaceAvailability AS ia WHERE ia.Availability != 100 GROUP BY ia.InterfaceID ) AS a1 ON a1.InterfaceID = i.InterfaceID
LEFT JOIN ( SELECT ia.InterfaceID, MAX(ia.DateTime) AS [DateTime] FROM Orion.NPM.InterfaceAvailability AS ia WHERE ia.Availability = 100 GROUP BY ia.InterfaceID ) AS a2 ON a2.InterfaceID = i.InterfaceID
WHERE ( i.Status IN ( 2, 3, 14, 16 ) AND i.InterfaceType = 6 )