Filter by events Yesterday

Hi All.

Writing a SWQL query.  Pulling some events from the Interfaces and InterfaceTraffic tables. 

Trying to filter for all entries YESTERDAY.  Not last 24 hours but 12:01 AM through 11:59 PM yesterday.

I know I have done that in the past with SQL queries.  So far I'm not able to make that work with SWQL.

Can anyone offer some tips on the best way to filter by a range of time within one date (yesterday)?

Thanks in advance for any assistance.

  • This is a fun one and there are a couple of ways to do it, and here's one of the ways I do it.

    SELECT [Interfaces].Node.Caption AS [Node]
         , CONCAT('/NetPerfMon/Images/Vendors/', [Interfaces].Node.VendorIcon ) AS [_IconFor_Node]
         , [Interfaces].Node.DetailsUrl AS [_LinkFor_Node]
         , [Interfaces].Name AS [Interface]
         , CONCAT('/NetPerfMon/images/Interfaces/', [Interfaces].Icon ) AS [_IconFor_Interface]
         , [Interfaces].DetailsUrl AS [_LinkFor_Interface]
         , [Interfaces].Traffic.Averagebps AS [AverageBps]
         , [Interfaces].Traffic.ObservationTimestamp AS [Traffic Time]
    FROM Orion.NPM.Interfaces AS [Interfaces]
    WHERE [Interfaces].HasObsoleteData = 0
    -- DATETRUNC truncates a date to a specific level (year, month, day, hour, etc.)
    -- see this:
    -- The first part:
    --   we ask for any time yesterday by taking the GETUTCDATE()
    --     GETUTCDATE() = '2022-07-20 17:00:18.1837'
    --   then we subtract 1 (day)
    --     GETUTCDATE() - 1 = '2022-07-19 17:00:18.1837'
    --   then we truncate the date information at the 'day' level (basically remove hour, minute, etc.)
    --     DATETRUNC('day', GETUTCDATE() - 1) = '2022-07-19 00:00:00.000' <-- this will appear different because of your time zone, but trust me
    -- for the second part, we do the same thing, but we use 'today' as (without the minus 1).
    AND [Interfaces].Traffic.ObservationTimestamp BETWEEN DATETRUNC('day', GETUTCDATE() - 1) AND DATETRUNC('day', GETUTCDATE())

    This is how it looks for me (when in a custom query widget):

    Remember that the time should be rendered by the local browser time and not the UTC time.  If you need to shift that, you may need to play with adding the TOLOCAL() function to change the output.