The following query will check the and report which Interfaces where created base on earliest detailed record reported.
The 'daysback' variable will check a specific day minus today. ie; today - 1 = yesterday.
Here is a Query sample output shows Node+interface (fullname) date-searching for & converted to an Int, and firstPolled date & time :
fullname | nodeid | interfaceid | searchdate | searchdateG | firstpolleddt | firstpolled |
cur-3550 - FastEthernet0/2 · test | 141 | 641 | 3/1/2018 | 43158 | 3/1/2018 | 11:31:33.7 |
cur-3550 - Port-channel1 - Po1 | 141 | 642 | 3/1/2018 | 43158 | 3/1/2018 | 11:31:33.7 |
cur-3550 - Port-channel2 · Aggregated ports to Server (LACP) | 141 | 643 | 3/1/2018 | 43158 | 3/1/2018 | 11:31:33.7 |
SUPPORT-15 - vmxnet3 Ethernet Adapter · Ethernet0 | 275 | 644 | 3/1/2018 | 43158 | 3/1/2018 | 11:35:05.9 |
tex-2901.lab.tex - Embedded-Service-Engine0/0 - Em0/0 | 54 | 636 | 3/1/2018 | 43158 | 3/1/2018 | 11:27:12.0 |
-- SQL query
declare @daysback int
set @daysback = 0
select fullname , nodeid, interfaceid ,
convert(date,getdate()-1) as searchdate,
convert( int, cast( convert(date,getdate()) as datetime))-@daysback as searchdateG,
(
SELECT TOP 1 convert(varchar,i.datetime,101) as dt
FROM [dbo].[InterfaceTraffic_Detail] as i
where i3.InterfaceID = i.InterfaceID
and convert( int, cast( convert(date,getdate()) as datetime))-@daysback = (
SELECT top 1 min( convert( int, cast( convert(varchar,i2.datetime,101) as datetime) ) )
FROM [dbo].[InterfaceTraffic_Detail] as i2 where i.interfaceid = i2.InterfaceID
)
group by i.datetime
order by i.datetime
) as firstpolleddt,
(
SELECT TOP 1 i.datetime
FROM [dbo].[InterfaceTraffic_Detail] as i
where i3.InterfaceID = i.InterfaceID
and convert( int, cast( convert(date,getdate()) as datetime))-@daysback = (
SELECT top 1 min( convert( int, cast( convert(varchar,i2.datetime,101) as datetime) ) )
FROM [dbo].[InterfaceTraffic_Detail] as i2 where i.interfaceid = i2.InterfaceID
)
group by i.datetime
order by i.datetime
) as firstpolled
from [dbo].[Interfaces] i3
where
i3.InterfaceID = (
SELECT TOP 1 i.InterfaceID
FROM [dbo].[InterfaceTraffic_Detail] as i
where i3.InterfaceID = i.InterfaceID
and convert( int, cast( convert(date,getdate()) as datetime))-@daysback = (
SELECT top 1 min( convert( int, cast( convert(varchar,i2.datetime,101) as datetime) ) )
FROM [dbo].[InterfaceTraffic_Detail] as i2 where i.interfaceid = i2.InterfaceID
)
group by i.interfaceid
order by i.interfaceid
)
order by fullname