0 Replies Latest reply on Feb 26, 2012 6:43 PM by contracer

    No activity to report

    contracer

      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 ?