Hello all. I've been tasked with adding a column which shows the number of outages for each device over the last 30 months and last year (two seperate reports) to an existing Availability Report.
Here is what I have so far, but I can't seem to get the results I want to display within the main report.
I'm close. Could you guys take a look at this and let me know why I can't bring these two queries together?
First query to get the number of outages experienced:
Select Nodes.Caption AS NodeName, COUNT(Events.EventType) AS num_outages
From nodes JOIN events on nodes.nodeid = events.networknode
Where
(EventType = '5000')
AND
(Message LIKE '%is down')
AND
(Nodes.MachineType = 'Cisco ASA 5505')
Group BY Nodes.Caption, EventType;
Second query which combines an availability query from Solarwinds with the one above, but shows the total count from the subquery rather than the individual outage count per device. I know I'm missing something here, but have changed this many different ways and think I'm just missing a trick or something I'm not familiar with like a rule.
SELECT TOP 10000 CONVERT(DateTime,
'01/01/' + LTRIM(YEAR(DateTime)),
101) AS SummaryYear,
Nodes.NodeID AS NodeID,
Nodes.Caption AS NodeName,
Nodes.IP_Address AS IP_Address,
AVG(ResponseTime.AvgResponseTime) AS AVERAGE_of_Average_Response_Time,
AVG(ResponseTime.Availability) AS AVERAGE_of_Availability,
AVG(ResponseTime.PercentLoss) AS AVERAGE_of_Percent_Loss,
(SELECT COUNT(Events.EventType)
From nodes JOIN events on nodes.nodeid = events.networknode
Where
((EventType = '5000') AND (Message LIKE '%is down'))
) AS num_outages
FROM
Nodes INNER JOIN ResponseTime ON (Nodes.NodeID = ResponseTime.NodeID)
WHERE
( DateTime BETWEEN 40362 AND 40393 )
AND
(
(Nodes.MachineType = 'Cisco ASA 5505') OR
(Nodes.MachineType = 'Cisco 2801')
)
GROUP BY CONVERT(DateTime, '01/01/' + LTRIM(YEAR(DateTime)), 101),
Nodes.NodeID, Nodes.Caption, Nodes.IP_Address
ORDER BY SummaryYear ASC, 3 ASC
Thanks again in advance.
