5 Replies Latest reply on Aug 24, 2014 6:47 AM by Jan Pelousek

# Help with SWQL - Day of the week?

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?  :-)

• ###### Re: Help with SWQL - Day of the week?

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

• ###### Re: Help with SWQL - Day of the week?

Can I get some clarification here? If I also want to create the business hours report cnorborg 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?

• ###### Re: Help with SWQL - Day of the week?

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?

1 of 1 people found this helpful
• ###### Re: Help with SWQL - Day of the week?

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

• ###### Re: Help with SWQL - Day of the week?

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

1 of 1 people found this helpful