SWQL Query Assistance

I wont write these right now because I'm leaving the office shortly, but also there's definitely some examples around

For both 1 and 2 the solution is to add and "AND ()" into your WHERE condition, and use the datetime functions here:

• GetUtcDate() - Returns the current date and time in UTC.
• Hour(d) - Returns the hour part of d (in 24 hour format).
• DateTrunc('datepart', 'd') - Where 'datepart' is one of the following strings: 'minute', 'hour', 'day', 'week', 'month', 'quarter', 'year'. Returns a date like d, but with all components more granular than 'datepart' set to
• WeekDay(d) - Returns the day of the week of d as a number, with Sunday = 0, Monday = 1, ..., Saturday = 6. Available in Orion Platform 2016.1 and later.

I think the usual approach is to compare DATETRUNC(x, GETUTCDATE()) to be > StartOfWindowInX and < EndofWindowInX

In SQL the BETWEEN function is available but not in SWQL yet as far as I'm aware, here's hoping.

I wont write these right now because I'm leaving the office shortly, but also there's definitely some examples around

For both 1 and 2 the solution is to add and "AND ()" into your WHERE condition, and use the datetime functions here:

• GetUtcDate() - Returns the current date and time in UTC.
• Hour(d) - Returns the hour part of d (in 24 hour format).
• DateTrunc('datepart', 'd') - Where 'datepart' is one of the following strings: 'minute', 'hour', 'day', 'week', 'month', 'quarter', 'year'. Returns a date like d, but with all components more granular than 'datepart' set to
• WeekDay(d) - Returns the day of the week of d as a number, with Sunday = 0, Monday = 1, ..., Saturday = 6. Available in Orion Platform 2016.1 and later.

I think the usual approach is to compare DATETRUNC(x, GETUTCDATE()) to be > StartOfWindowInX and < EndofWindowInX

In SQL the BETWEEN function is available but not in SWQL yet as far as I'm aware, here's hoping.

So support gave me this query, but the issue is it throws errors:

SELECT
[Nodes].Caption,
AVG([Nodes].ResponseTimeHistory.Availability) AS [AverageAvailability]
FROM Orion.Nodes AS [Nodes]
WHERE [Nodes].NodeName LIKE '%SOLARWINDSC%'
AND [Nodes].ResponseTimeHistory.DateTime BETWEEN DATEADD(DAY, -31, GETDATE()) AND GETDATE()
AND DATEPART(WEEKDAY, [Nodes].ResponseTimeHistory.DateTime) BETWEEN 2 AND 6 -- Monday to Friday
AND DATEPART(HOUR, [Nodes].ResponseTimeHistory.DateTime) BETWEEN 8 AND 16 -- 8 AM to 5 PM (5 PM included)
GROUP BY [Nodes].Caption
HAVING AVG([Nodes].ResponseTimeHistory.Availability) = 100

Any idea how to solve this?  It says "Cannot resolve property DAY".  Thoughts?

This query is not SWQL...it looks more like SQL that you'd run in Database Manager except it uses SWQL table names. Very odd that you'd get this "SWQL" from support...

Try this SWQL tested in SWQL Studio 3.2.0.50049:

SELECT
[Nodes].Caption
,AVG([Nodes].ResponseTimeHistory.Availability) AS [AverageAvailability]
FROM Orion.Nodes AS [Nodes]
WHERE 1=1 -- so I can comment out individual AND lines below and not break SWQL
AND [Nodes].NodeName LIKE '%SOLARWINDS%' -- removed 'SC' to match my node name
AND [Nodes].ResponseTimeHistory.DateTime BETWEEN AddDate('DAY', -31, GETDATE()) AND GETDATE()
AND WEEKDAY( [Nodes].ResponseTimeHistory.DateTime ) BETWEEN 2 AND 6 -- Monday to Friday
AND HOUR( [Nodes].ResponseTimeHistory.DateTime ) BETWEEN 8 AND 16 -- 8 AM to 5 PM (5 PM included)
GROUP BY [Nodes].Caption
--HAVING AVG([Nodes].ResponseTimeHistory.Availability) = 100

Also helpful --> https://github.com/solarwinds/OrionSDK/wiki/SWQL-Functions this is where adam.beedell  function info comes from.

Should get you closer...

Excellent response ty

This is great.  Thank you.  One last issue with this, i get thiis error when I attempt to use it...does this ring any bells on why?

"message": "An error has occurred.",
"exceptionMessage": "RunQuery failed, check fault information.\n'DateTime' is not a recognized built-in function name.",

How are you running it?

Great question.  I'm running it in a Dashboard View.

I get this error when i edit the dashboard containing this new code.

This shows the query:  It validates correctly, but then throws the error after saving it.  We are on the latest version of the software as well.

Also note I commented out the having so that I would get a result since nothing here had Availability = 100. Bottom line is you'll need to vet the final result but first you need a query that works...

Try

SELECT top 10
[Nodes].[Caption]
,AVG([Nodes].[ResponseTimeHistory].[Availability]) AS [AverageAvailability]
FROM Orion.Nodes AS [Nodes]
WHERE 1=1 -- so I can comment out individual AND lines below and not break SWQL
AND [Nodes].[NodeName] LIKE '%SOLARWINDS%' -- removed 'SC' to match my node name
AND [Nodes].[ResponseTimeHistory].[DateTime] BETWEEN AddDate('DAY', -31, GETDATE()) AND GETDATE()
AND WEEKDAY( [Nodes].[ResponseTimeHistory].[DateTime] ) BETWEEN 2 AND 6 -- Monday to Friday
AND HOUR( [Nodes].[ResponseTimeHistory].[DateTime] ) BETWEEN 8 AND 16 -- 8 AM to 5 PM (5 PM included)
GROUP BY [Nodes].[Caption]
--HAVING AVG([Nodes].ResponseTimeHistory.Availability) = 100

I created a simple table widget...the query I provided validated and the widget displays the same results as SWQL Studio.

I only have SAM license so not sure if network device uptime is part of another package or not....