1 Reply Latest reply on Jun 19, 2017 9:18 AM by ned1

    SLA AVAILABILITY REPORTS

    sagar.kupati

      Hi

       

      Looking for sql query for SLA monthly report.

       

      Below is query in SQL but i am not able to find the errors

       

      <Report Version="1.0" Group="Custom Reports" Title="SLA Availability - Last Month - Business Hours/Days" ModuleTitle="" Type="SQL" TypeDescription="" Icon="SQL" Schema="" SubTitle="Mon-Fri, 0900-1800 hrs" Description="" Footer="" Time_Frame="Named" Named_Time_Frame="Last 30 Days" Relative_Time_Frame="24 Hours" Starting_DateTime="11/5/2014 0:0:0" Ending_DateTime="12/6/2014 0:0:0" Grouping="Date" Group_Position="Beginning" SQL="DECLARE @StartDate DateTime

      DECLARE @EndDate DateTime

      SET @StartDate = DATEADD(d, DATEDIFF(d, 0, DATEADD(m, -1, DATEADD(d, 1 - day(getdate()), getdate()))), 0)

      SET @EndDate = DATEADD(ms, -2,DATEADD(d, DATEDIFF(d, 0, DATEADD(d, 1 - day(getdate()), getdate())), 0));

       

       

      Select SummaryMonth, NodeID, Vendor_Icon, NodeName, IP_Address, AVERAGE_of_Availability, SLA_Node,  SLA, SLA_Status From ( SELECT  TOP 10000 CONVERT(DateTime,

      LTRIM(MONTH(DateTime)) + '/01/' + LTRIM(YEAR(DateTime)),

      101) AS SummaryMonth,

      Nodes.NodeID AS NodeID,

      Nodes.VendorIcon AS Vendor_Icon,

      Nodes.Caption AS NodeName,

      Nodes.IP_Address AS IP_Address,

      AVG(ResponseTime.Availability) AS AVERAGE_of_Availability,

      Nodes.SLA_Node AS SLA_Node,

      CASE

      WHEN AVG(ResponseTime.Availability) &gt; Nodes.SLA_Node THEN 'PASS'

        ELSE 'FAIL'

      END AS SLA,

      CASE

      WHEN AVG(ResponseTime.Availability) &gt; Nodes.SLA_Node THEN 'Up.gif'

        ELSE  'Down.gif'

      END AS SLA_Status

       

       

      FROM

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

       

       

       

       

      WHERE

      ( DateTime BETWEEN @StartDate AND @EndDate )

      AND 

      (

        (DATEPART(weekday, DateTime) &gt;= 2) AND

        (DATEPART(weekday, DateTime) &lt;= 6) AND

        (DatePart(Hour,DateTime) &gt;= 9) AND

        (DatePart(Hour,DateTime) &lt;= 18)

      )

       

       

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

      Nodes.NodeID, Nodes.VendorIcon, Nodes.Caption, Nodes.IP_Address, Nodes.SLA_Node, Nodes.CustomerName

       

       

       

       

      ) As r ORDER BY SummaryMonth ASC, 4 ASC" TopX="All" TopXCount="10" TopXPercent="10" Orientation="Landscape" CookedData="TRUE" Web="TRUE" GroupByDays="TRUE" ShowFolders=""><QueryBuilder><Fields></Fields><Filter></Filter></QueryBuilder><LayoutGroups></LayoutGroups><Fields><Field Name="SummaryMonth" Header="Month" Range="Positive" Parser="Month" Format="" Alignment="Left" Width="2000" Units="" Hidden="FALSE" WebURL="" BaseTableName=""></Field><Field Name="NodeID" Header="Node ID" Range="Positive" Parser="None" Format="" Alignment="Center" Width="0" Units="" Hidden="TRUE" WebURL="/Orion/View.aspx?View=NodeDetails&amp;NetObject=N:${NodeID}" BaseTableName="Nodes"></Field><Field Name="Vendor_Icon" Header="Vendor" Range="ALL" Parser="Icon" Format="Vendors" Alignment="Center" Width="1000" Units="" Hidden="FALSE" WebURL="" BaseTableName="Nodes"></Field><Field Name="NodeName" Header="Node" Range="ALL" Parser="None" Format="" Alignment="Left" Width="0" Units="" Hidden="FALSE" WebURL="/Orion/View.aspx?View=NodeDetails&amp;NetObject=N:${NodeID}" BaseTableName="Nodes"></Field><Field Name="IP_Address" Header="IP Address" Range="ALL" Parser="None" Format="" Alignment="Left" Width="1100" Units="" Hidden="FALSE" WebURL="/Orion/View.aspx?View=NodeDetails&amp;NetObject=N:${NodeID}" BaseTableName="Nodes"></Field><Field Name="SLA_Node" Header="SLA_Node" Range="ZeroAndAbove" Parser="Numeric" Format="0.000" Width="2000" Units="%" Hidden="FALSE" WebURL="" BaseTableName="Nodes"></Field><Field Name="AVERAGE_of_Availability" Header="Availability" Range="ZeroAndAbove" Parser="Numeric" Format="0.000" Alignment="Center" Width="2000" Units="%" Hidden="FALSE" WebURL="/Orion/NetPerfMon/CustomChart.aspx?ChartName=AVAILABILITY&amp;NetObject=N:${NodeID}" BaseTableName="

      AVG(ResponseTime"></Field><Field Name="SLA_Status" Header="SLA_Status" Range="ALL" Parser="Icon" Format="Status" Alignment="Center" Width="1000" Units="" Hidden="FALSE" WebURL="" BaseTableName="

      CASE

      WHEN AVG(ResponseTime"></Field><Field Name="SLA" Header="SLA" Range="ALL" Parser="None" Format="" Width="1000" Units="" Hidden="FALSE" WebURL="" BaseTableName="

      Nodes"></Field></Fields></Report>