This discussion has been locked. The information referenced herein may be inaccurate due to age, software updates, or external references.
You can no longer post new replies to this discussion. If you have a similar question you can start a new discussion in this forum.

Resource SQL Filter Question

Good Morning THWACK Community,

I need a little help with an SQL Query I use to filter the 'Down Interfaces' resource.  I have been asked to use the Down Interface resource to show interfaces that have gone down in the last 24 hours and a second 'Down Interfaces' resource to show the interfaces that have been down for the last 7 days.  This seemed pretty straight forward...

For the 'Down Interface' in the last 24 hours resource, I used this SQL filter
InterfaceLastChange >= DATEADD(dd,DATEDIFF(dd,0,GETDATE()),-1)

For the 'Down Interfaces' in the last 7 days resource, I used this SQL filter
InterfaceLastChange >=DATEADD(dd,DATEDIFF(dd,0,GETDATE()),-7)

Then I realized that this is kinda redundant for the first 24 hours.  The same interfaces will show up in both resources for the first 24 hours.  Is there a way to tweak my second filter to exclude the time frame from the first filter?

  • You could try something like the following. I can't remember if the column uses the server datetime or UTC datetime. If it is UTC, change GETDATE to GETUTCDATE.

    Last 24 Hours

    InterfaceLastChange >= DATEADD(hour, -24, GETDATE())

    If the last change datetime is newer than the current date minus 24 hours. 

    Last 7 Days

    InterfaceLastChange >= DATEADD(day, -7, GETDATE()) AND InterfaceLastChange <= DATEADD(hour, -24, GETDATE())

    If the last change datetime is newer than the current date minus 7 days, but older than 24 hours.