SWQL for pulling yesterdays data between specific timestamps

I am creating a swql query to get the list of interfaces having high % utilization on the previous day between 2 timestamps. Below is the query that I am using for static date and time stamp. I need one with dynamic date where I can specify the timestamp.

SELECT n.Caption as [Node], i.DisplayName as [INTERFACE], i.InPercentUtil AS [Receive], i.OutPercentUtil AS [Transmit],i.DateTime AS [TIME] FROM Orion.NPM.InterfaceTraffic i INNER JOIN Orion.Nodes n ON (n.NodeID = i.NodeID) WHERE (i.InPercentUtil >= 80 OR i.OutPercentUtil >= 80) AND i.DateTime >='2/21/2023 10:50:00 AM' AND i.DateTime <= '2/21/2023 11:05:00 AM'

Find more posts tagged with

Accepted answers

ecmel

In this case, you can use AddDate function in SWQL to adjust the datetime for your requirement. The example below shows filter for yesterday between 10:50 and 11:05:

i.DateTime >= AddDate('hour', 10, AddDate('minute', 50, DATETRUNC('day', GETUTCDATE()-1)))

AND i.DateTime < AddDate('hour', 11, AddDate('minute', 05, DATETRUNC('day', GETUTCDATE()-1)))

All comments

ecmel

You can convert the last part of your query to the following statement, it should return the data for yesterday only:

i.DateTime >= DATETRUNC('day', GETUTCDATE()-1) AND i.DateTime < DATETRUNC('day', GETUTCDATE())

SumitPatil

Thanks ecmel. I need yesterday's data but between specific timestamps. For eg. data for yesterday between 1pm to 2pm.

ecmel

In this case, you can use AddDate function in SWQL to adjust the datetime for your requirement. The example below shows filter for yesterday between 10:50 and 11:05:

i.DateTime >= AddDate('hour', 10, AddDate('minute', 50, DATETRUNC('day', GETUTCDATE()-1)))

AND i.DateTime < AddDate('hour', 11, AddDate('minute', 05, DATETRUNC('day', GETUTCDATE()-1)))

SumitPatil

Thanks Ecmel. This worked for me.