Interface created Today Database query

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 :

   

fullnamenodeidinterfaceidsearchdatesearchdateGfirstpolleddtfirstpolled
cur-3550 - FastEthernet0/2 · test 1416413/1/2018431583/1/201811:31:33.7
cur-3550 - Port-channel1 - Po11416423/1/2018431583/1/201811:31:33.7
cur-3550 - Port-channel2 · Aggregated ports to Server (LACP)1416433/1/2018431583/1/201811:31:33.7
SUPPORT-15 - vmxnet3 Ethernet Adapter · Ethernet02756443/1/2018431583/1/201811:35:05.9
tex-2901.lab.tex - Embedded-Service-Engine0/0 - Em0/0546363/1/2018431583/1/201811: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

Anonymous