cancel
Showing results for 
Search instead for 
Did you mean: 
Create Post

Help with SWQL - Day of the week?

Jump to solution

Ok, just starting off with SWQL and I am trying to replicate some of the things that have been done with report-writer in the past here.   One of the things is a report that gives availability for a group of sites during business hours (ie: M-F 8am-5pm).    Curious how this can be done?  I've got it to the point where I'm getting availability reports, and I'm getting them for between the hours of 8am to 5pm (I think).  But I'm stuck on how to get the Day-of-the-week (DOTW) done?

Was hoping for a function that might return that, but I don't see any in the documentation I've been able to dig up.   I was thinking of using the DayDiff function to find the # of days since a given Monday lets say, and then wanted to mod it by 7 and figure out the DOTW based on the remainder, but I don't think SWQL has that kind of math ability?

Anyone have any good ideas for getting this done?   Or am I SOL on getting it done with SWQL?  🙂

0 Kudos
1 Solution
Level 13

We currently don't expose the day of the week function.  Writing the equation you are talking is not possible in SWQL as it would would require lookup tables for some of the variables

View solution in original post

0 Kudos
8 Replies
Level 12

Old post, but I stumbled across it.

  • 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.

SWQL Functions · solarwinds/OrionSDK Wiki · GitHub

@mwbThanks!  This pointed me in the right direction.

For any future people: I was able to make a query to get statistic for business hours and business days using the following:

 

 

SELECT TOP 1000
IT.NodeID,
N.Caption,
TOLOCAL(IT.DateTime) AS Date_Time,
WEEKDAY(TOLOCAL(IT.DateTime)) AS DayOfWeek,
IT.InterfaceID,
IT.OutPercentUtil,
IT.InPercentUtil,
IT.PercentUtil,
NCP.business_hours_start,
NCP.business_hours_end
FROM Orion.NPM.InterfaceTraffic IT
INNER JOIN Orion.NPM.InterfacesCustomProperties ICP
ON IT.InterfaceID = ICP.InterfaceID
INNER JOIN Orion.Nodes N
ON IT.NodeID=N.NodeID
INNER JOIN Orion.NodesCustomProperties NCP
ON N.NodeID=NCP.NodeID
WHERE IT.InterfaceID = 1
AND 
(
    (HOUR(TOLOCAL(IT.DateTime)) >= HOUR(NCP.business_hours_start_eastern))
	AND
	(HOUR(TOLOCAL(IT.DateTime)) <= HOUR(NCP.business_hours_end_eastern))
)
AND
(
    (WEEKDAY(TOLOCAL(IT.DateTime)) != 0)
    AND
    (WEEKDAY(TOLOCAL(IT.DateTime)) != 7)
)
AND TOLOCAL(IT.DateTime) > DATETIME('2020-03-15 00:00:00.000')

 

 

*syntax highlighting is wrong as there isn't currently a code option for SQL/SWQL.

Hot dang, just what I was lookin' for.

Level 13

Hello, I'm thinking about possibility to acomplish what you need by usage of combination of daydiff and datetrunc functions.

Example of usage: SELECT DayDiff(DateTrunc('week', keepalive), keepalive) as dd FROM Orion.Engines

Would this be suitable?

Jan Pelousek - I think you're reference SQL, not SWQL.    While this is easily accomplished in SQL, I don't believe it is possible in SWQL.  You can't embed SQL in a webpage without doing it as a Report Writer resource.   SWQL stuff tends to run much quicker and I like the results better on a page.

0 Kudos

Hi Craig,

the example I gave is working SWQL query based on SWIS built in functions. As far as I remember both DayDiff and DateTrunc functions should be available since NPM 10.5 ( or 10.6, not 100% sure now).

This can be used also in the filter conditions.

Example for getting the max cpuload:

Select c.node.caption as [caption], max(MaxLoad) as [Max] from orion.cpuload c where DayDiff(DateTrunc('week', datetime ), datetime ) in (0,1,2,3,4) group by c.node.caption

Regards,

Honza

Level 13

We currently don't expose the day of the week function.  Writing the equation you are talking is not possible in SWQL as it would would require lookup tables for some of the variables

View solution in original post

0 Kudos

Can I get some clarification here? If I also want to create the business hours report Craig Norborg is speaking about in the new Web Report setup of the reporting tool, I could not use a SWQL query for my Custom table, I would need to use a SQL query. Is that correct?

0 Kudos