3 Replies Latest reply on Mar 16, 2012 11:17 AM by MTorok

    Orion NPM MTTR Report


      There are several post related to creating a Summary MTTR report that includes all devices but have not found a report that will show this data.  We are looking for an MTTR report that will show the total downtime per device group (not per device) for the last month.  i.e. 3.5 hours of downtime for Group X.

      Does anyone have a working example?

        • Re: Orion NPM MTTR Report

          This is exactly what I'm looking for.  Not being a SQL person I'm trying to script in report writer but not having much success, you can edit the pre-defined reports by selecting 'show SQL' from the report pull-down menu but then again you have to do it in SQL.  Logged a ticket with Solarwinds to see if I could get some direction, to be honest none, go & look on thwack.  For some reason the group availability reports are designed to show only 0% or 100%, no use to anyone.  At least they raised a feature request.

          I've looked through report-lab & content-exchange and to me this seems a problem for a number of people, unless you know your SQL, I still can't find anything.

          I'm looking for a report that simply shows the actual percentage time that each of my groups we're down last month summarized & this can be display all in the 1 report, not seperate reports.  Any help would be appreciated.

            • Re: Orion NPM MTTR Report

              I meant to updated this thread.  Though a combination of a few other reports on this site and a few DBA's we came up with the report.  Ours includes 2 custom attributes Region and Network Layer so you will need to updated with your criteria but this sums and averages the total outage time.  This is the whole report so save it as MTTR.OrionReport.

              <Report Version="1.0" Group="Availability" Title="MTTR" ModuleTitle="" Type="SQL" TypeDescription="" Icon="SQL" Schema="" SubTitle="Mean Time To Repair for Last Month" Description="Average downtime for all Events per Region Per Network Layer" Footer="" Time_Frame="Named" Named_Time_Frame="Last 30 Days" Relative_Time_Frame="24 Hours" Starting_DateTime="1/24/2012 0:0:0" Ending_DateTime="2/24/2012 0:0:0" Grouping="Date" Group_Position="Beginning" SQL=";WITH cteTemp
              --DATEADD(M, DATEDIFF(M, 0, GETDATE()), 0) AS SummaryMonth,
              CONVERT(DateTime,LTRIM(MONTH(EventTime)) + '/01/' + LTRIM(YEAR(EventTime)),101) AS SummaryMonth,
                  DATEDIFF(Mi, StartTime.EventTime,
                  (SELECT TOP 1
                      FROM Events AS Endtime
                      WHERE EndTime.EventTime &gt; StartTime.EventTime AND EndTime.EventType = 5
                          AND EndTime.NetObjectType = 'N'
                          AND EndTime.NetworkNode = StartTime.NetworkNode
                (Nodes.Region = 'NA') OR   (Nodes.Region = 'SA') OR (Nodes.Region = 'EAME')) 
                          ORDER BY EndTime.EventTime))/60.0 AS MTTR
              FROM Events StartTime INNER JOIN Nodes ON StartTime.NetworkNode = Nodes.NodeID
              WHERE (StartTime.EventType = 1) AND (StartTime.NetObjectType = 'N') AND
              datepart(yy,eventtime) = datepart(yy,dateadd(m,-1,getdate())) and datepart(m,eventtime) = datepart(m,dateadd(m,-1,getdate()))
              --ORDER BY Nodes.Region ASC, Nodes.NetworkLayer ASC, Nodes.VendorIcon ASC
              SELECT SummaryMonth, Region, NetworkLayer, AVG(MTTR) AS MTTR from cteTemp
              GROUP BY SummaryMonth, Region, NetworkLayer;
              " TopX="All" TopXCount="10" TopXPercent="10" Orientation="Landscape" CookedData="TRUE" Web="TRUE" GroupByDays="TRUE" ShowFolders=""><QueryBuilder><Fields></Fields><Filter></Filter></QueryBuilder><LayoutGroups><Group Field="SummaryMonth" GroupHeader="${SummaryMonth}" WEBURL="" Alignment="0" ForeColor="0" BackColor="16777215" FontSize="8" FontName="Arial" FontBold="TRUE" FontItalic="FALSE" FontUnderline="FALSE" FontStrike="FALSE" Transparent="FALSE"></Group><Group Field="Region" GroupHeader="${Region}" WEBURL="" Alignment="0" ForeColor="0" BackColor="16777215" FontSize="8" FontName="Arial" FontBold="TRUE" FontItalic="FALSE" FontUnderline="FALSE" FontStrike="FALSE" Transparent="FALSE"></Group></LayoutGroups><Fields><Field Name="SummaryMonth" Header="Month" Range="Positive" Parser="Month" Format="" Alignment="Left" Width="1365" Units="" Hidden="FALSE" WebURL="" BaseTableName=""></Field><Field Name="Region" Header="Region" Range="ALL" Parser="None" Format="" Alignment="Left" Width="1275" Units="" Hidden="FALSE" WebURL="" BaseTableName="Nodes"></Field><Field Name="NetworkLayer" Header="NetworkLayer" Range="ALL" Parser="None" Format="0" Alignment="Left" Width="1950" Units="" Hidden="FALSE" WebURL="" BaseTableName="Nodes"></Field><Field Name="MTTR" Header="MTTR" Range="ALL" Parser="Numeric" Format="0.00" Width="0" Units="Hours" Hidden="FALSE" WebURL="" BaseTableName="
                  (SELECT TOP 1
                      FROM Events AS Endtime
                      WHERE EndTime"></Field></Fields></Report>