Monthly Report Exceptions

r0berth1

I have some reports that pull the average availability for my MPLS circuits on Monday-Friday between the hours of 7 AM until 7 PM. I need to create and exceptions report that shows comments on the circuits that have availability of less than 98%. The reports have 2 custom fields, the first one is Branch which contains the actual branch name instead of the router name, the second is Comments which contains the reason the circuit went down, IE power failure, Circuit cut, etc. When i create the exceptions report to pull only circuits that have less than 98% average availability, filter it for only Monday-Friday between the hours of 7 AM - 7 PM, where the comment box it not empty, i will get 1 field with the branch name, and then 30 or so entries under that listing the same comment for every min of that hour. It will not let me group the data by Date ony. Can someone help me figure this out? The monthly report shows:

Brance Name Availability

Baker 99%

the exceptions report should show something like:

Baker 98% T1 was cut by construction crew.

but when i create the report, it shows:

Baker 98% T1 was cut by construction crew

                  T1 was cut by construction crew ...

 

Monthly Report:

SELECT  TOP 10000 CONVERT(DateTime,
LTRIM(MONTH(DateTime)) + '/01/' + LTRIM(YEAR(DateTime)),
101) AS SummaryMonth,
AVG(ResponseTime.Availability) AS AVERAGE_of_Availability,
Nodes.Branch AS Branch

FROM
Nodes INNER JOIN ResponseTime ON (Nodes.NodeID = ResponseTime.NodeID)

WHERE
( DateTime BETWEEN 39537 AND 39566.9999884259 )
 AND 
(
  (Nodes.State = 'LA') AND
  (DATEPART(weekday, DateTime) <> 7) AND
  (DATEPART(weekday, DateTime) <> 1) AND
  (Convert(Char,DateTime,108) >= '07:00') AND
  (Convert(Char,DateTime,108) <= '19:00')
)

GROUP BY CONVERT(DateTime, LTRIM(MONTH(DateTime)) + '/01/' + LTRIM(YEAR(DateTime)), 101),
Nodes.Branch

ORDER BY SummaryMonth ASC, 3 ASC

 

Exceptions Report:

SELECT  TOP 10000 ResponseTime.DateTime AS Date,
Nodes.Branch AS Branch,
Nodes.Comments AS Comments

FROM
Nodes INNER JOIN ResponseTime ON (Nodes.NodeID = ResponseTime.NodeID)

WHERE
( DateTime BETWEEN 39963 AND 39992.9999884259 )
 AND 
(
  (DATEPART(weekday, DateTime) <> 1) AND
  (DATEPART(weekday, DateTime) <> 7) AND
  (Convert(Char,DateTime,108) >= '07:00') AND
  (Convert(Char,DateTime,108) <= '19:00') AND
  (ResponseTime.Availability <= 98) AND
  (Nodes.Comments IS NOT NULL) AND
  (Nodes.Comments <> '')
)

r0berth1

Nevermind, I figured it out. For those of you who want to do the same thing. Here is what I did.

This is the original report that looks at a few custom node properites to pull % uptime. but i wanted a report that showed a list of every site that was under 98% uptime and show why it was down.

Below is the code from the original report:

SELECT  TOP 10000 CONVERT(DateTime,
LTRIM(MONTH(DateTime)) + '/01/' + LTRIM(YEAR(DateTime)),
101) AS SummaryMonth,
AVG(ResponseTime.Availability) AS AVERAGE_of_Availability,
Nodes.Branch AS Branch

FROM
Nodes INNER JOIN ResponseTime ON (Nodes.NodeID = ResponseTime.NodeID)

WHERE
( DateTime BETWEEN 39537 AND 39566.9999884259 )
 AND 
(
  (Nodes.State = 'LA') AND
  (DATEPART(weekday, DateTime) <> 7) AND
  (DATEPART(weekday, DateTime) <> 1) AND
  (Convert(Char,DateTime,108) >= '07:00') AND
  (Convert(Char,DateTime,108) <= '19:00')
)

GROUP BY CONVERT(DateTime, LTRIM(MONTH(DateTime)) + '/01/' + LTRIM(YEAR(DateTime)), 101),
Nodes.Branch

ORDER BY SummaryMonth ASC, 3 ASC

I was able to modify it a little to get the report that I wanted. here is the code:

SELECT * FROM (

SELECT  TOP 10000 CONVERT(DateTime,
LTRIM(MONTH(DateTime)) + '/01/' + LTRIM(YEAR(DateTime)),
101) AS SummaryMonth,
AVG(ResponseTime.Availability) AS AVERAGE_of_Availability,
Nodes.Branch AS Branch,
Nodes.Comments AS Comments

FROM
Nodes INNER JOIN ResponseTime ON (Nodes.NodeID = ResponseTime.NodeID)

WHERE
( DateTime BETWEEN 39993 AND 40023.9999884259 )
 AND 
(
  (DATEPART(weekday, DateTime) <> 7) AND
  (DATEPART(weekday, DateTime) <> 1) AND
  (Convert(Char,DateTime,108) >= '07:00') AND
  (Convert(Char,DateTime,108) <= '19:00') AND
  (Nodes.Comments IS NOT NULL)
)

GROUP BY CONVERT(DateTime, LTRIM(MONTH(DateTime)) + '/01/' + LTRIM(YEAR(DateTime)), 101),
Nodes.Branch, Nodes.Comments

ORDER BY SummaryMonth ASC, 3 ASC) AS X WHERE AVERAGE_of_Availability <= '98';