6 Replies Latest reply on Jan 3, 2011 9:47 AM by MrSampsonite

# Build report based on 7pm to 5am Average (our peak time)?

Anyone have an idea on how to build a report where the time range is nightly from 7pm to 5am?

Basically for a given perfmon stat we're collecting, the key period for us is at night. With perfmon we can schedule collections and then see the average, but it's a manual process of going to each server and clicking on the data. We're trying to do that with NPM and APM.

What we've got so far in the report is it filtering where time is either greater than 7pm or less than 5am. But when we say "show data from last 7 days" to see how the averages conform, what it ends up doing is taking midnight to 5am on one day and combining it with the 7pm to midnight later on and then averaging all of that. Instead it needs to take 7pm to midnight of day 1 and add on midnight to 5am on day 2 and then average all of that.

Hope that wasn't too confusing. We can't be the only ones trying to do this.

• ###### Re: Build report based on 7pm to 5am Average (our peak time)?

I am also looking for this...  need to do interface, memory and CPU utilisation within core hours...

• ###### Re: Build report based on 7pm to 5am Average (our peak time)?

I will work on this (next week) if nobody gets it before hand.  do me a favor, send me a reminder like monday or tuesday or call my cell.

• ###### Re: Build report based on 7pm to 5am Average (our peak time)?

here is the format for the selects to get yesterday at 1700 and today at 0700.  use between clause.

--This gives you yesterday @ 17:00
(select
convert(datetime, floor( convert(float,getdate()) ) ))
)
)

--This gives you today @ 07:00
(select
convert(datetime, floor( convert(float,getdate()) ) ))
)

• ###### Re: Build report based on 7pm to 5am Average (our peak time)?

Larry--

Thanks for remembering to do this!! I'm going to move the post to the Report Lab forum.

M

• ###### Re: Build report based on 7pm to 5am Average (our peak time)?

Hi everyone, I got a response from support and via Experts-Exchange.com that has worked pretty well. See response below:

http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/Q_26684240.html

I'm out on vacation until the new year but when I get back I'll find our advanced sql report and paste the query I'm using.

• ###### Re: Build report based on 7pm to 5am Average (our peak time)?

Below is the query we're using for pages per second during our peak time (7pm to 5am) for the last 10 days). Adjust accordingly.

SELECT  TOP 10000 Nodes.NodeID AS NodeID,
Nodes.Caption AS NodeName,
AVG(APM_ResponseTime.StatisticData) AS AVERAGE_of_StatisticsData,

Convert(datetime, datediff(d, 0, dateadd(hh, -5, DateTime))) AS SummaryDate

FROM
(
(
Nodes
INNER JOIN
ON
)
INNER JOIN
APM_ResponseTime
ON
(
)
)
WHERE
(
Nodes.Status<>9 -- ignore unmanaged nodes
AND
AND
DateTime >= dateadd(hh, -5, datediff(d, 9, getdate())) -- 10 days ago through...
AND
DateTime < dateadd(hh, -5, datediff(d, 0, 1+getdate())) -- today
AND
datepart(hh, DateTime) in (19,20,21,22,23,0,1,2,3,4) -- from 7:00pm to 4:59am
)
GROUP BY
CONVERT(datetime, datediff(d, 0, dateadd(hh, -5, DateTime))),
Nodes.NodeID,
Nodes.Caption,