Property change history

Hello

We have some properties which we watch for changes. In Manager, I can right click and show history, and it nicely shows old value and new value, and the date on which the value changed

How can I get the same from the backend? I tried using the DialogWatchOperation and DialogWatchProperty, and that does work (sort of), but it doesnt neatly show me old and new value, it just shows the date on which a particular value was set, to get the old value I need to look in the next row, and using this in a complex query is tough.

Any built-in views/sps/functions which display the data with old and new values for all records?

Thanks

Kin

  • FYI - I'm doing it this way for now, but I was hoping there is an easier built-in way:

    with x as
    (select ROW_NUMBER() OVER (ORDER BY OperationDate desc) AS RowNumber,
    dwo.ObjectKeyOfRow,
    dc.ColumnName,dwp.ContentShort,dwo.OperationDate from DialogWatchOperation dwo
    join DialogWatchProperty dwp on dwp.UID_DialogWatchOperation = dwo.UID_DialogWatchOperation
    join DialogColumn dc on dc.UID_DialogColumn = dwp.UID_DialogColumn
    where dwo.ObjectKeyOfRow='<Key><T>Person</T><P>a104ada6-c32d-4ec5-8d03-0eea467dd625</P></Key>'
    and dc.ColumnName='CCC_MyColumn'
    ),
    y as
    (
    select x.RowNumber+1 as RowNumber,
    x.ObjectKeyOfRow,x.ColumnName,x.ContentShort,x.OperationDate
    from x
    )
    select x.ColumnName,x.ContentShort as OldValue,y.ContentShort as NewValue, x.OperationDate from y
    left join x on x.ObjectKeyOfRow = y.ObjectKeyOfRow and x.RowNumber = y.RowNumber

  • Hi Troy

    No, we already have colums enabled for tracking. I'm tring to see if there is some query to see oldvalue and newvalue along with the date changed. 

  • Hello All,

    I need same information as mentioned here. In IAM Managaer we can see history of changes but I need SQL Query to display easly in backend. Any table or view to see historical changes in column? or SQL query..

    Not: History information for Column already enabled in Designer and we can see history in Manager

    IAM Version: 9.1.1

    Thanks a lot

  • If you enable TRACE logging (globallog.config) you can see the SQL queries the Time Trace feature of the Manager Tooling is using.
    Then you can make your own SQL query or function based on those queries.

    The DialogWatchProperty does not store the current value of a field. I'm not a SQL expert but you could use UNPIVOT operator and flip the columnnames and values of a table into rows of "Columnname, Value" CAST all the value types to string or text. and make it dynamic if you want to use it for every table (see code).

    -- Pulled from the manager.log
    select
    	op.UID_DialogWatchOperation, op.GenProcId, op.OperationDate, op.OperationUser,
    	op.OperationType, op.ObjectKeyOfRow, c.Columnname, p.HasContentFull, p.ContentShort, p.ContentFull
    from DialogWatchOperation op
    left outer join DialogWatchProperty p
    	on p.UID_DialogWatchOperation = op.UID_DialogWatchOperation
    left outer join DialogColumn c
    	on c.UID_DialogColumn = p.UID_DialogColumn
    where (op.ObjectKeyOfRow in ('<Key><T>Person</T><P>...</P></Key>')) and (isnull(op.OperationDate, '1899-12-30 00:00:00.000') >= '2023-11-06 13:52:05.484')
    order by op.OperationDate desc, op.UID_DialogWatchOperation, c.Columnname
    
    -- Joined with person table
    select
    	op.UID_DialogWatchOperation, op.GenProcId, op.OperationDate, op.OperationUser,
    	op.OperationType, op.ObjectKeyOfRow, ps.CentralAccount, c.Columnname, p.HasContentFull, p.ContentShort, p.ContentFull
    from DialogWatchOperation op
    left outer join DialogWatchProperty p
    	on p.UID_DialogWatchOperation = op.UID_DialogWatchOperation
    left outer join DialogColumn c
    	on c.UID_DialogColumn = p.UID_DialogColumn
    join Person ps on ps.XObjectKey = op.ObjectKeyOfRow
    where (isnull(op.OperationDate, '1899-12-30 00:00:00.000') >= '2023-11-06 13:52:05.484')
    order by op.OperationDate desc, op.UID_DialogWatchOperation, c.Columnname
    
    -- Into a function
    
    CREATE FUNCTION CCC_FTTimeTraceObjectKey (
    	@OBJECTKEY AS VARCHAR(138)
    	,@DATEAFTER AS DATETIME
    	)
    RETURNS TABLE
    AS
    RETURN
    
    SELECT op.UID_DialogWatchOperation
    	,op.GenProcId
    	,op.OperationDate
    	,op.OperationUser
    	,op.OperationType
    	,op.ObjectKeyOfRow
    	,c.Columnname
    	,p.HasContentFull
    	,p.ContentShort
    	,p.ContentFull
    FROM DialogWatchOperation op
    LEFT OUTER JOIN DialogWatchProperty p ON p.UID_DialogWatchOperation = op.UID_DialogWatchOperation
    LEFT OUTER JOIN DialogColumn c ON c.UID_DialogColumn = p.UID_DialogColumn
    WHERE (op.ObjectKeyOfRow IN (@OBJECTKEY))
    	AND (isnull(op.OperationDate, '1899-12-30 00:00:00.000') >= @DATEAFTER)
    
    -- UNPIVOT example: don't use this in production without testing first!
    
    DECLARE @TABLENAME AS NVARCHAR(80)
    SET @TABLENAME = 'Person'
    DECLARE @DATEAFTER AS DATETIME
    SET @DATEAFTER = '2023-11-15'
    DECLARE @OBJECTKEY AS NVARCHAR(138)
    --SET @OBJECTKEY = '<Key><T>Person</T><P>6462cf04-cfcb-4136-9005-8da61358c004</P></Key>'
    SET @OBJECTKEY = '%'
    
    DECLARE @UnPivotList NVARCHAR(4000) = (
    		SELECT STRING_AGG(QUOTENAME(ColumnName), ',')
    		FROM DialogColumn
    		WHERE UID_DialogTable IN (
    				SELECT UID_DialogTable
    				FROM DialogTable
    				WHERE TableName = @TABLENAME
    				)
    			AND IsToWatch = 1
    		);
    		
    DECLARE @CastedCols NVARCHAR(MAX);
    
    SELECT @CastedCols = COALESCE(@CastedCols + N',', N'') + N'CAST(ISNULL(' + QUOTENAME(ColumnName) + N', '' '') AS NVARCHAR(400)) AS ' + QUOTENAME(ColumnName)
    FROM DialogColumn
    WHERE UID_DialogTable IN (
    		SELECT UID_DialogTable
    		FROM DialogTable
    		WHERE TableName = @TABLENAME
    		)
    	AND IsToWatch = 1
    	AND SchemaDataType <> 'datetime'
    
    DECLARE @CastedDateCols NVARCHAR(MAX);
    
    SELECT @CastedDateCols = COALESCE(@CastedDateCols + N',', N'') + N'CAST(CONVERT(VARCHAR,' + QUOTENAME(ColumnName) + N', 121) AS NVARCHAR(400)) AS ' + QUOTENAME(ColumnName)
    FROM DialogColumn
    WHERE UID_DialogTable IN (
    		SELECT UID_DialogTable
    		FROM DialogTable
    		WHERE TableName = @TABLENAME
    		)
    	AND IsToWatch = 1
    	AND SchemaDataType = 'datetime'
    
    IF @CastedDateCols IS NULL
    	SELECT @CastedDateCols = ''
    ELSE
    	SELECT @CastedDateCols = ',' + @CastedDateCols
    
    DECLARE @UnPivotSQL NVARCHAR(MAX) = N'
    SELECT * FROM (
    SELECT ''00000000-0000-0000-0000-000000000000'' As UID_DialogWatchOperation
    	,''00000000-0000-0000-0000-000000000000'' As GenProcId
    	,t.XObjectKey As ObjectKeyOfRow
    	,''Current object'' As OperationUser
    	,''C'' As OperationType
    	, DATEADD(ms, 10, o.OperationDate) AS OperationDate
    	,t.Columnname
    	,t.Value As ContentShort
    	,'''' As ContentFull
    	,0 As HasContentFull
    FROM (
    SELECT XObjectKey, Columnname, Value
    FROM (
    Select
    	' + @CastedCols + @CastedDateCols + '
    	,t.XObjectKey
    FROM ' + @TABLENAME + ' t
    WHERE t.XObjectKey IN (SELECT ObjectKeyOfRow FROM DialogWatchOperation WHERE (isnull(OperationDate, ''1899-12-30 00:00:00.000'') >= ''' + CONVERT(VARCHAR, @DATEAFTER, 126) + '''))
    ) AS t
    UNPIVOT(Value For ' + 'ColumnName' + ' IN (
        ' + @UnPivotList + 
    	'
                )) AS Unpivoted_Table
    ) t
    JOIN (
    SELECT op.ObjectKeyOfRow
    	,MAX(op.OperationDate) AS OperationDate
    	,c.Columnname
    	FROM DialogWatchOperation op
    	LEFT OUTER JOIN DialogWatchProperty p ON p.UID_DialogWatchOperation = op.UID_DialogWatchOperation
    	LEFT OUTER JOIN DialogColumn c ON c.UID_DialogColumn = p.UID_DialogColumn
    	GROUP BY op.ObjectKeyOfRow, c.Columnname
    ) o ON o.ObjectKeyOfRow = t.XObjectKey AND (isnull(o.OperationDate, ''1899-12-30 00:00:00.000'') >= ''' + CONVERT(VARCHAR, @DATEAFTER, 126) + ''')
    AND o.Columnname = t.Columnname
    
    UNION ALL
    
    SELECT op.UID_DialogWatchOperation
    	,op.GenProcId
    	,op.ObjectKeyOfRow
    	,op.OperationUser
    	,op.OperationType
    	,op.OperationDate
    	,c.Columnname
    	,p.ContentShort
    	,p.ContentFull
    	,p.HasContentFull
    FROM DialogWatchOperation op
    LEFT OUTER JOIN DialogWatchProperty p ON p.UID_DialogWatchOperation = op.UID_DialogWatchOperation
    LEFT OUTER JOIN DialogColumn c ON c.UID_DialogColumn = p.UID_DialogColumn
    JOIN ' + @TABLENAME + 
    	' t ON t.XObjectKey = op.ObjectKeyOfRow
    WHERE (isnull(op.OperationDate, ''1899-12-30 00:00:00.000'') >= ''' + CONVERT(VARCHAR, @DATEAFTER, 126) + ''')
    ) ChangedProperties
    WHERE ObjectKeyOfRow LIKE ''' + @OBJECTKEY + '''
    Order By ObjectKeyOfRow, ColumnName, OperationDate DESC
    ';
    
    --SELECT(@UnPivotSQL) AS UnPivotSQL;
    EXEC (@UnPivotSQL)