<Report Version="1.0" Group="Node Down Time" Title="Node Down Time Report" ModuleTitle="" Type="SQL" TypeDescription="" Icon="SQL" Schema="" SubTitle="" Description="" Footer="" Time_Frame="Named" Named_Time_Frame="Last 30 Days" Relative_Time_Frame="24 Hours" Starting_DateTime="3/10/2014 0:0:0" Ending_DateTime="4/10/2014 0:0:0" Grouping="Date" Group_Position="Beginning" SQL="SELECT * FROM (
SELECT
Nodes.StatusLED,
Nodes.Caption,
Nodes.NodeID,
StartTime.Message,
StartTime.EventTime AS DownEventTime,
(
SELECT TOP 1 EventTime
FROM Events AS EndTimeTable
where EndTimeTable.EventTime >= StartTime.EventTime
AND EndTimeTable.EventType = 5
AND EndTimeTable.NetObjectType = 'N'
AND EndTimeTable.NetworkNode = StartTime.NetworkNode
AND EventTime IS NOT NULL
ORDER BY EndTimeTable.EventTime
) AS UpEventTime,
DATEDIFF(Mi, StartTime.EventTime,(
SELECT TOP 1 EventTime FROM Events AS Endtime
where EndTime.EventTime > StartTime.EventTime AND EndTime.EventType = 5 AND EndTime.NetObjectType = 'N'
AND EndTime.NetworkNode = StartTime.NetworkNode ORDER BY EndTime.EventTime)
) AS OutageDurationInMinutes
FROM Events StartTime
INNER JOIN Nodes ON StartTime.NetworkNode = Nodes.NodeID
WHERE (StartTime.EventType = 1)
) AS UpTimeTable
where outageDurationInMinutes IS NOT NULL
ORDER BY Caption ASC, DownEventTime DESC" TopX="All" TopXCount="10" TopXPercent="10" Orientation="Landscape" CookedData="TRUE" Web="TRUE" GroupByDays="TRUE" ShowFolders=""><QueryBuilder><Fields></Fields><Filter></Filter></QueryBuilder><LayoutGroups><Group Field="Caption" GroupHeader="${Caption}" WEBURL="/Orion/View.aspx?View=NodeDetails&NetObject=N:${NodeID}" Alignment="0" ForeColor="0" BackColor="14671839" FontSize="8" FontName="Arial" FontBold="TRUE" FontItalic="FALSE" FontUnderline="FALSE" FontStrike="FALSE" Transparent="FALSE"></Group></LayoutGroups><Fields><Field Name="Caption" Header="Caption" Range="ALL" Parser="None" Format="" Alignment="Left" Width="4000" Units="" Hidden="FALSE" WebURL="/Orion/View.aspx?View=NodeDetails&NetObject=N:${NodeID}" BaseTableName="NodesData"></Field><Field Name="Message" Header="Message" Range="ALL" Parser="None" Format="" Width="0" Units="" Hidden="FALSE" WebURL="/Orion/View.aspx?View=NodeDetails&NetObject=N:${NodeID}" BaseTableName="Events"></Field><Field Name="DownEventTime" Header="Down Event Time" Range="ZeroAndAbove" Parser="DateTime" Format="" Alignment="Center" Width="2000" Units="" Hidden="FALSE" WebURL="" BaseTableName="Events"></Field><Field Name="UpEventTime" Header="Up Event Time" Range="ALL" Parser="None" Format="" Alignment="Center" Width="2000" Units="" Hidden="FALSE" WebURL="" BaseTableName="(
SELECT TOP 1 EventTime
FROM Events AS EndTimeTable
WHERE EndTimeTable"></Field><Field Name="NodeID" Header="" Range="Positive" Parser="None" Format="" Alignment="Center" Width="0" Units="" Hidden="TRUE" WebURL="/Orion/View.aspx?View=NodeDetails&NetObject=N:${NodeID}" BaseTableName="NodesData"></Field><Field Name="OutageDurationInMinutes" Header="Outage Duration In Minutes" Range="ALL" Parser="None" Format="" Alignment="Center" Width="0" Units="" Hidden="FALSE" WebURL="" BaseTableName="(
SELECT TOP 1 EventTime FROM Events AS Endtime
WHERE EndTime"></Field><Field Name="StatusLED" Header="" Range="ALL" Parser="Icon" Format="Status" Alignment="Center" Width="400" Units="" Hidden="FALSE" WebURL="" BaseTableName="NodesData"></Field></Fields></Report>
