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.

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
AS
(
SELECT
--DATEADD(M, DATEDIFF(M, 0, GETDATE()), 0) AS SummaryMonth,
CONVERT(DateTime,LTRIM(MONTH(EventTime)) + '/01/' + LTRIM(YEAR(EventTime)),101) AS SummaryMonth,
 
Nodes.VendorIcon,
 
Nodes.Region,
 
    Nodes.NetworkLayer,
 
    DATEDIFF(Mi, StartTime.EventTime,
 
 
    (SELECT TOP 1
        EventTime
 
        FROM Events AS Endtime
 
        WHERE EndTime.EventTime &gt; StartTime.EventTime AND EndTime.EventType = 5
 
            AND EndTime.NetObjectType = 'N'
 
            AND EndTime.NetworkNode = StartTime.NetworkNode
 
AND 
(
  (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
        EventTime
 
        FROM Events AS Endtime
 
        WHERE EndTime"></Field></Fields></Report>

Thank you for sharing this with the community.

I'm sure many will find it useful.