UnDP transform historical data

Question

Hi

I have a UnDP that collects data for more than a year. In this case the data is a table of two values.

For a new graph I need the data of the table summarized. So I defined a transform UnDP (columnsum( {OutputPower})).

This works like expected, but only transforms new polled data.

Is there a way to run the transformation on exsiting data (so that I have the sum for all values collected in the past)?

Thomas

FormerMember · Answer

I solved the problem by creating the missing historical data directly in the database.

See the SQL script below to summarize the values of two instances (rows) of a UnDP named OutputPower into a Transform named TotalPower.

Manipulations on the database are on your own risk. Always create a backup in advance!

DECLARE @id_src AS UNIQUEIDENTIFIER
DECLARE @id_dst AS UNIQUEIDENTIFIER

DECLARE @poller_src AS NVARCHAR(100)
DECLARE @poller_dst AS NVARCHAR(100)
DECLARE @node AS NVARCHAR(100)

SET @poller_src = 'OutputPower' -- name of the source UnDP
SET @poller_dst = 'TotalPower'  -- name of the destination UnDP
SET @node = 'PDU 11'            -- name of the node

-- lookup assignment IDs for source and destination pollers
SELECT @id_src = dbo.CustomPollerAssignment.CustomPollerAssignmentID
 FROM dbo.CustomPollerAssignment INNER JOIN
                      dbo.CustomPollers ON dbo.CustomPollerAssignment.CustomPollerID = dbo.CustomPollers.CustomPollerID INNER JOIN
                      dbo.Nodes ON dbo.CustomPollerAssignment.NodeID = dbo.Nodes.NodeID
 WHERE (dbo.CustomPollers.UniqueName = @poller_src) AND (dbo.Nodes.Caption = @node)

SELECT @id_dst = dbo.CustomPollerAssignment.CustomPollerAssignmentID
 FROM dbo.CustomPollerAssignment INNER JOIN
                      dbo.CustomPollers ON dbo.CustomPollerAssignment.CustomPollerID = dbo.CustomPollers.CustomPollerID INNER JOIN
                      dbo.Nodes ON dbo.CustomPollerAssignment.NodeID = dbo.Nodes.NodeID
 WHERE (dbo.CustomPollers.UniqueName = @poller_dst) AND (dbo.Nodes.Caption = @node)

PRINT 'poller assignement id src: ' + CONVERT(varchar(100), @id_src)
PRINT 'poller assignement id dst: ' + CONVERT(varchar(100), @id_dst)

DECLARE @oldest_src AS DATETIME
DECLARE @oldest_dst AS DATETIME

-- look for the oldest data of both datasets
SELECT TOP 1 @oldest_src = [DateTime]
  FROM [NetPerfMon].[dbo].[CustomPollerStatistics_Daily]
   WHERE CustomPollerAssignmentID = @id_src
   ORDER BY [DateTime] ASC

SELECT TOP 1 @oldest_dst = [DateTime]
  FROM [NetPerfMon].[dbo].[CustomPollerStatistics_Daily]
   WHERE CustomPollerAssignmentID = @id_dst
   ORDER BY [DateTime] ASC
   
PRINT 'oldest entrie of src: ' + CONVERT(varchar(100), @oldest_src)
PRINT 'oldest entrie of dst: ' + CONVERT(varchar(100), @oldest_dst)

-- in this example we will summarize all instances of the source poller and
-- store it in the destination poller
-- remark: if you create a poller that uses transformation there will be a result row for every source row
-- also if the results in all rows are identical
 
DECLARE @row_id AS VARCHAR(800)

-- we use a cursur to traverse all rows
DECLARE cursor1 CURSOR FOR

SELECT DISTINCT RowID 
 FROM [NetPerfMon].[dbo].[CustomPollerStatistics_Daily]
 WHERE CustomPollerAssignmentID = @id_src

OPEN cursor1;

-- Perform the first fetch.
FETCH NEXT FROM cursor1 INTO @row_id;

@FETCH_STATUS to see if there are any more rows to fetch.
@FETCH_STATUS = 0
 BEGIN
     -- COMMENT OUT THE FOLLOWING LINE TO INSERT THE DATA TO THE DATABASE - ALLWAYS CHECK BEFORE EXECUTE
  -- INSERT INTO [NetPerfMon].[dbo].[CustomPollerStatistics_Daily]
  SELECT @id_dst, 2, [DateTime], sum(minRate), SUM(AvgRate), SUM(MaxRate), NULL, SUM(RawStatus), CONVERT(varchar(1000), SUM(RawStatus)), @row_id
    FROM [NetPerfMon].[dbo].[CustomPollerStatistics_Daily]
    WHERE CustomPollerAssignmentID = @id_src
   AND [DateTime] < @oldest_dst
    Group by DateTime   
    
  FETCH NEXT FROM cursor1 INTO @row_id;
 END

CLOSE cursor1;
DEALLOCATE cursor1;

Nandish · Answer

Thomas this cant be done on the UNDP transform results wizard.Because there is no option available in the formula to add a date or any customizable way.

May be you can use Orion report Writer tool and create a new report with custom SQL query.