Good Afternoon All,
I received a request for an application availability report by Group Membership - this month.
Individually, I can run canned application availability and group membership reports but neither is enough so the only way is through Advanced SQL.
I'm hitting a wall because I think that I'm attempting to chain join views from the database but I don't know their respective columns and further more I'm not sure whether it'll be an inner or outer join.
I've reviewed this -
And it gets me closer but not really where I need to be.
Can anyone help with this?
I've gotten to here (But admittedly I'm way out of practice with my SQL):
SELECT
SummaryMonth,
NodeName,
Application_Name,
AVERAGE_of_ApplicationAvailability,
Cast(Application_Group As nvarchar(250)) as Application_Group From ( SELECT TOP 10000 CONVERT(DateTime, LTRIM(MONTH(DateTime)) + '/01/' + LTRIM(YEAR(DateTime)), 101) AS SummaryMonth,
Nodes.Caption AS NodeName,
APM_AlertsAndReportsData.ApplicationName AS Application_Name,
AVG(APM_DailyApplicationAvailability.PercentAvailability) AS AVERAGE_of_ApplicationAvailability,
Nodes.Application_Group AS Application_Group,
Containers_AlertsAndReportsData.GroupMemberName AS Group_Member_Name,
Containers_AlertsAndReportsData.GroupName AS Group_Name
FROM
(Nodes INNER JOIN APM_AlertsAndReportsData ON (Nodes.NodeID = APM_AlertsAndReportsData.NodeId))
INNER JOIN
(APM_DailyApplicationAvailability ON (APM_AlertsAndReportsData.ApplicationId = APM_DailyApplicationAvailability.ApplicationID))
INNER JOIN
(Containers_AlertsAndReportsData.GroupMemberName ON (Group_Member_Name = Containers_AlertsAndReportsData.GroupMemberName))
WHERE
( DateTime BETWEEN 41242 AND 41260 )
AND
(
(Nodes.UnManaged = 0) AND
(Nodes.Admin_Owner = 'Server')
)
GROUP BY
CONVERT
(DateTime, LTRIM(MONTH(DateTime)) + '/01/' + LTRIM(YEAR(DateTime)), 101),
Nodes.Caption, APM_AlertsAndReportsData.ApplicationName, Nodes.Application_Group
) As r ORDER BY Conainers_AlertsAndReportsData.GroupName 1 ASC, 2 ASC
Thanks!
