1 Reply Latest reply on Dec 26, 2012 12:11 AM by Steven Klassen

    Application Availabilty by Group Membership View Report

    jeremyxmentzell

      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 - Re: Group Availability Reports

      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!