SQL script to run a report for Average CPU and Memory Percent Utilization-business hours during week days?

You don't need an Advanced sql report for this. Simply add report filters on the date time field.

I am using NPM 10.7 by the way.

I am not seeing where I can do this. I can do the last XX days but I don't see where I can say I want the report to run for Monday thru Friday between 7AM and 5PM. Would you be able to give me some more direction?

You are looking at the time frame tab. You should be looking at the filter tab. You will find sample reports as attachments to this thread

Executing a Set of SQL Codes at a Particular Time; is it with Orion Report Scheduler or Something else?

I really appreciate you taking the time to respond.

Right now I have the following report and it gives me the past 5 days, which I run on Saturday so it gives me Monday-Friday results.

======================================================================

SELECT  TOP 10000 Nodes.NodeID AS NodeID,

Nodes.Caption AS NodeName,

AVG(CPULoad.AvgLoad) AS AVERAGE_of_AvgCPULoad,

AVG(CPULoad.AvgPercentMemoryUsed) AS AVERAGE_of_AvgPercentMemoryUsed,

AVG(CPULoad.AvgMemoryUsed) AS AVERAGE_of_AvgMemoryUsed,

AVG(CPULoad.TotalMemory) AS AVERAGE_of_TotalMemory

FROM

Nodes INNER JOIN CPULoad ON (Nodes.NodeID = CPULoad.NodeID)

WHERE

( DateTime BETWEEN 41913 AND 41918 )

GROUP BY Nodes.NodeID, Nodes.Caption

ORDER BY 2 ASC

==================================================================

I just don't know SQL to make the report also give me the data only between 7AM and 6PM.

If you can shed some light that would be really great.

Thanks again

Import the 2 report templates using report writer, then open them in the report writer and see all the tabs of the template

Thank you, I'll give it a try

Hi HolyGuacamole,

I added the following to the SQL field so I can get the time frame between 7AM and 6PM. Can you let me know if that is correct? The report is running without errors but I want to make sure I entered the times correctly at the right place. Is there a way for me to verify the information is correct?

Thanks again, you've been really helpful.

========================================================

DECLARE @StartDate DateTime

DECLARE @EndDate DateTime

SET @StartDate = FLOOR(CAST(DATEADD(WEEK, -1, GETDATE()) AS FLOAT));

SET @EndDate = CAST(GETDATE() AS FLOAT);

SELECT  TOP 10000 Nodes.NodeID AS NodeID,

Nodes.Caption AS NodeName,

AVG(CPULoad.AvgLoad) AS AVERAGE_of_AvgCPULoad,

AVG(CPULoad.AvgPercentMemoryUsed) AS AVERAGE_of_AvgPercentMemoryUsed,

AVG(CPULoad.AvgMemoryUsed) AS AVERAGE_of_AvgMemoryUsed,

AVG(CPULoad.TotalMemory) AS AVERAGE_of_TotalMemory

FROM

Nodes INNER JOIN CPULoad ON (Nodes.NodeID = CPULoad.NodeID)

WHERE

( DateTime BETWEEN @StartDate AND @EndDate)

AND

(

  (DATEPART(weekday, DateTime) >= 2) AND

  (DATEPART(weekday, DateTime) <= 6) AND

  (DatePart(Hour,DateTime) > 7) AND

  (Convert(Char,DateTime,108) <= '18')

)

GROUP BY Nodes.NodeID, Nodes.Caption

ORDER BY 2 ASC

========================================================

Looks right, but as I said you don't need an Advanced SQL report. Did you try the other report template? Did you try to Filter Results the following way first?

Yes, I tried it and I am going to use this version instead of the Advanced SQL report, but  I just wanted to know if I was editing the correct information.

Thanks again for all the help, I learned a few things and I appreciate it.