Hi,
I´m using a sql script that I found here in Thwack:
SET NOCOUNT ON
set dateformat dmy
---!!!!!!!!!!!!!!!!!!!!!!!!!!
declare @period smallint
declare @periodbegin datetime
declare @periodend datetime
declare @Sensitivity smallint
--Add here Sensitivity (min), period(days) or periodbegin/periodend==========
set @Sensitivity=10
set @period=';'
set @periodbegin=';20-02-2009'
set @periodend=';01-03-2009'
--======================================================================
if @period!=''
begin
set @periodbegin=(GetDate()-@period)
set @periodend=getdate()
end
if @periodend=';' set @periodend=getdate()
SELECT
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 UpEventTime,
Nodes.Caption,
------------------------------Add here another column-------------------
--address,
------------------------------------------------------------------------
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) AND
(StartTime.NetObjectType = 'N') AND (
---------------------------------period---------------------
EventTime > @periodbegin
AND EventTime < @periodend
-----------------------------------------------------------------------------------
)and (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)))
--------------------------------------Sensitivity (min)-----------------------
> @Sensitivity
---------------------------------------Add here another filter---------------------
-- and caption like '%pix%'
-------------------------------------------------------------------------------------
ORDER BY StartTime.EventTime asc
--ORDER BY caption asc
--ORDER BY OutageDurationInMinutes desc
This script works fine in Report Writer, but I get "No activity to report" in NPM web console.
Can you help me ?
