I am looking for a SQL statement to give me all of the details from a reconciliation. I can see all of the People brought in via the reconciliation in the PROCESS table. I can also see all of the messages written by  process.auditEvent() calls in the Person Modify (or Add) operation in the PROCESSLOG table. But I can't seem to find the list of attributes, previous value, new value for each person in any of the database tables. I'm certain the information is there, but I cannot figure out where it is.

The data is stored as a relatively simple XML document - depending on size - in the PROCESSLOG.NEW_DATA or  PROCESSLOG.SMALL_NEW_DATA.

Now - xml data is not easy to handle in SQL - but luckily SQL has some functionality to convert the data - I am not able at this moment to go into depth on how this works in details - but the query unfolds the stored data into a view - you have to look at the xml document to understand what it does (the document has sections for existing, old and new data - the example here shows the new entries) : 

SELECT id, process_id, object, new_attr, new_value 
FROM itimuser.processlog , XMLTABLE ('$d/Object/Attribute/Object/Attribute/Scalar' passing xmlparse(document coalesce(new_data,small_new_data)) as "d" 
	COLUMNS 
	new_value VARCHAR(2004) PATH 'substring(.,0,1999)' , 
	new_attr VARCHAR(254) PATH '../@name' , 
	object VARCHAR(254) PATH '../../../../@name' 
) 
where data_id in ('Entity','person','account')

It is necessary to use relative XPATH to get to the data correctly as you will else get duplicate data.

What I have done is to create views for both new/old/unchanged data so that I can work with the data in e.g. Cognos Reports.

I have used this "unfolding" to perform an analysis of attributes changes in an HRFeed interface to get an overview of which attributes was driving the changes on identities - this illustrates the power of the XML processing :  

SELECT new_attr, substring(a.submitted,1,7) AS MONTH, count(distinct b.id)
FROM itimuser.process A, 
	itimuser.processlog B , 
	XMLTABLE (
		'$d/Object/Attribute/Object[@name="AttributeChangeOperation.operation.replace"]/Attribute/Scalar|$d/Object/Attribute/Object[@name="AttributeChangeOperation.operation.add"]/Attribute/Scalar' 
			passing xmlparse(document replace(coalesce(b.new_data,b.small_new_data),',',' ')) as "d"
		COLUMNS
			new_value VARCHAR(32672) PATH '.'
			, new_attr VARCHAR(254) PATH '../@name'
			, object VARCHAR(254) PATH '../../../../@name'
			, operation VARCHAR(254) PATH '../../@name'
	) 
where data_id in ('Entity','person','account')
and coalesce(b.new_data,b.small_new_data) is not NULL 
and a.id = b.process_id
and a.name='personModifyProcessName'
and a.id = a.root_process_id
and b.activity_id = 0
and a.submitted like '2023%'
group by new_attr, substring(a.submitted,1,7)
order by month, new_attr

I hope this gives you a starting point - I know XML handling is not the easiest thing to work with....

PS. The "coalesce" function basically collapses the 2 data columns into one - this is a nifty trick to avoid unnecessary special handling for the columns depending on size....

HTH 

I am looking for a SQL statement to give me all of the details from a reconciliation. I can see all of the People brought in via the reconciliation in the PROCESS table. I can also see all of the messages written by  process.auditEvent() calls in the Person Modify (or Add) operation in the PROCESSLOG table. But I can't seem to find the list of attributes, previous value, new value for each person in any of the database tables. I'm certain the information is there, but I cannot figure out where it is.

The data is stored as a relatively simple XML document - depending on size - in the PROCESSLOG.NEW_DATA or  PROCESSLOG.SMALL_NEW_DATA.

Now - xml data is not easy to handle in SQL - but luckily SQL has some functionality to convert the data - I am not able at this moment to go into depth on how this works in details - but the query unfolds the stored data into a view - you have to look at the xml document to understand what it does (the document has sections for existing, old and new data - the example here shows the new entries) : 

SELECT id, process_id, object, new_attr, new_value FROM itimuser.processlog , XMLTABLE ('$d/Object/Attribute/Object/Attribute/Scalar' passing xmlparse(document coalesce(new_data,small_new_data)) as "d" 	COLUMNS 	new_value VARCHAR(2004) PATH 'substring(.,0,1999)' , 	new_attr VARCHAR(254) PATH '../@name' , 	object VARCHAR(254) PATH '../../../../@name' ) where data_id in ('Entity','person','account')

It is necessary to use relative XPATH to get to the data correctly as you will else get duplicate data.

What I have done is to create views for both new/old/unchanged data so that I can work with the data in e.g. Cognos Reports.

I have used this "unfolding" to perform an analysis of attributes changes in an HRFeed interface to get an overview of which attributes was driving the changes on identities - this illustrates the power of the XML processing :  

SELECT new_attr, substring(a.submitted,1,7) AS MONTH, count(distinct b.id)FROM itimuser.process A, 	itimuser.processlog B , 	XMLTABLE (		'$d/Object/Attribute/Object[@name="AttributeChangeOperation.operation.replace"]/Attribute/Scalar|$d/Object/Attribute/Object[@name="AttributeChangeOperation.operation.add"]/Attribute/Scalar' 			passing xmlparse(document replace(coalesce(b.new_data,b.small_new_data),',',' ')) as "d"		COLUMNS			new_value VARCHAR(32672) PATH '.'			, new_attr VARCHAR(254) PATH '../@name'			, object VARCHAR(254) PATH '../../../../@name'			, operation VARCHAR(254) PATH '../../@name'	) where data_id in ('Entity','person','account')and coalesce(b.new_data,b.small_new_data) is not NULL and a.id = b.process_idand a.name='personModifyProcessName'and a.id = a.root_process_idand b.activity_id = 0and a.submitted like '2023%'group by new_attr, substring(a.submitted,1,7)order by month, new_attr

I hope this gives you a starting point - I know XML handling is not the easiest thing to work with....

PS. The "coalesce" function basically collapses the 2 data columns into one - this is a nifty trick to avoid unnecessary special handling for the columns depending on size....

HTH 

I am looking for a SQL statement to give me all of the details from a reconciliation. I can see all of the People brought in via the reconciliation in the PROCESS table. I can also see all of the messages written by  process.auditEvent() calls in the Person Modify (or Add) operation in the PROCESSLOG table. But I can't seem to find the list of attributes, previous value, new value for each person in any of the database tables. I'm certain the information is there, but I cannot figure out where it is.

I have been playing with the idea developing a Cognos package using this that would give a better overview of the the requests that the builtin.

There are a couple of challenges to that such as the dates in the PROCESS family tables being strings and not timestamps  - but as DB2 V11 added some builtin conversion functions it should now be possible to convert these to real timestamps either in Cognos or as views in the database.

Another challenge is of course that on top of Oracle and Db2 also Postgresql is now an option (currently IIRC only in the container version) but I believe all needed XML and date functions are SQL standards so they should be available on all platforms - Oracle I will not be able to test as I have no access to that - but Postgresql will be part of my test setup at some time in near future.

In general I have a weak point for Cognos - it is so immense powerful - but we are not really making this obvious and the installation/setup/configuration instructions are not that good - in older times a redbook would have helped - but we are not doing those anymore for Verify products.

I would be very interested in feedback and work on the queries I included above - so please comment if there is questions etc.   

The data is stored as a relatively simple XML document - depending on size - in the PROCESSLOG.NEW_DATA or  PROCESSLOG.SMALL_NEW_DATA.

Now - xml data is not easy to handle in SQL - but luckily SQL has some functionality to convert the data - I am not able at this moment to go into depth on how this works in details - but the query unfolds the stored data into a view - you have to look at the xml document to understand what it does (the document has sections for existing, old and new data - the example here shows the new entries) : 

SELECT id, process_id, object, new_attr, new_value FROM itimuser.processlog , XMLTABLE ('$d/Object/Attribute/Object/Attribute/Scalar' passing xmlparse(document coalesce(new_data,small_new_data)) as "d" 	COLUMNS 	new_value VARCHAR(2004) PATH 'substring(.,0,1999)' , 	new_attr VARCHAR(254) PATH '../@name' , 	object VARCHAR(254) PATH '../../../../@name' ) where data_id in ('Entity','person','account')

It is necessary to use relative XPATH to get to the data correctly as you will else get duplicate data.

What I have done is to create views for both new/old/unchanged data so that I can work with the data in e.g. Cognos Reports.

I have used this "unfolding" to perform an analysis of attributes changes in an HRFeed interface to get an overview of which attributes was driving the changes on identities - this illustrates the power of the XML processing :  

SELECT new_attr, substring(a.submitted,1,7) AS MONTH, count(distinct b.id)FROM itimuser.process A, 	itimuser.processlog B , 	XMLTABLE (		'$d/Object/Attribute/Object[@name="AttributeChangeOperation.operation.replace"]/Attribute/Scalar|$d/Object/Attribute/Object[@name="AttributeChangeOperation.operation.add"]/Attribute/Scalar' 			passing xmlparse(document replace(coalesce(b.new_data,b.small_new_data),',',' ')) as "d"		COLUMNS			new_value VARCHAR(32672) PATH '.'			, new_attr VARCHAR(254) PATH '../@name'			, object VARCHAR(254) PATH '../../../../@name'			, operation VARCHAR(254) PATH '../../@name'	) where data_id in ('Entity','person','account')and coalesce(b.new_data,b.small_new_data) is not NULL and a.id = b.process_idand a.name='personModifyProcessName'and a.id = a.root_process_idand b.activity_id = 0and a.submitted like '2023%'group by new_attr, substring(a.submitted,1,7)order by month, new_attr

I hope this gives you a starting point - I know XML handling is not the easiest thing to work with....

PS. The "coalesce" function basically collapses the 2 data columns into one - this is a nifty trick to avoid unnecessary special handling for the columns depending on size....

HTH 

I am looking for a SQL statement to give me all of the details from a reconciliation. I can see all of the People brought in via the reconciliation in the PROCESS table. I can also see all of the messages written by  process.auditEvent() calls in the Person Modify (or Add) operation in the PROCESSLOG table. But I can't seem to find the list of attributes, previous value, new value for each person in any of the database tables. I'm certain the information is there, but I cannot figure out where it is.

I have been playing with the idea developing a Cognos package using this that would give a better overview of the the requests that the builtin.

There are a couple of challenges to that such as the dates in the PROCESS family tables being strings and not timestamps  - but as DB2 V11 added some builtin conversion functions it should now be possible to convert these to real timestamps either in Cognos or as views in the database.

Another challenge is of course that on top of Oracle and Db2 also Postgresql is now an option (currently IIRC only in the container version) but I believe all needed XML and date functions are SQL standards so they should be available on all platforms - Oracle I will not be able to test as I have no access to that - but Postgresql will be part of my test setup at some time in near future.

In general I have a weak point for Cognos - it is so immense powerful - but we are not really making this obvious and the installation/setup/configuration instructions are not that good - in older times a redbook would have helped - but we are not doing those anymore for Verify products.

I would be very interested in feedback and work on the queries I included above - so please comment if there is questions etc.   

The data is stored as a relatively simple XML document - depending on size - in the PROCESSLOG.NEW_DATA or  PROCESSLOG.SMALL_NEW_DATA.

Now - xml data is not easy to handle in SQL - but luckily SQL has some functionality to convert the data - I am not able at this moment to go into depth on how this works in details - but the query unfolds the stored data into a view - you have to look at the xml document to understand what it does (the document has sections for existing, old and new data - the example here shows the new entries) : 

SELECT id, process_id, object, new_attr, new_value FROM itimuser.processlog , XMLTABLE ('$d/Object/Attribute/Object/Attribute/Scalar' passing xmlparse(document coalesce(new_data,small_new_data)) as "d" 	COLUMNS 	new_value VARCHAR(2004) PATH 'substring(.,0,1999)' , 	new_attr VARCHAR(254) PATH '../@name' , 	object VARCHAR(254) PATH '../../../../@name' ) where data_id in ('Entity','person','account')

It is necessary to use relative XPATH to get to the data correctly as you will else get duplicate data.

What I have done is to create views for both new/old/unchanged data so that I can work with the data in e.g. Cognos Reports.

I have used this "unfolding" to perform an analysis of attributes changes in an HRFeed interface to get an overview of which attributes was driving the changes on identities - this illustrates the power of the XML processing :  

SELECT new_attr, substring(a.submitted,1,7) AS MONTH, count(distinct b.id)FROM itimuser.process A, 	itimuser.processlog B , 	XMLTABLE (		'$d/Object/Attribute/Object[@name="AttributeChangeOperation.operation.replace"]/Attribute/Scalar|$d/Object/Attribute/Object[@name="AttributeChangeOperation.operation.add"]/Attribute/Scalar' 			passing xmlparse(document replace(coalesce(b.new_data,b.small_new_data),',',' ')) as "d"		COLUMNS			new_value VARCHAR(32672) PATH '.'			, new_attr VARCHAR(254) PATH '../@name'			, object VARCHAR(254) PATH '../../../../@name'			, operation VARCHAR(254) PATH '../../@name'	) where data_id in ('Entity','person','account')and coalesce(b.new_data,b.small_new_data) is not NULL and a.id = b.process_idand a.name='personModifyProcessName'and a.id = a.root_process_idand b.activity_id = 0and a.submitted like '2023%'group by new_attr, substring(a.submitted,1,7)order by month, new_attr

I hope this gives you a starting point - I know XML handling is not the easiest thing to work with....

PS. The "coalesce" function basically collapses the 2 data columns into one - this is a nifty trick to avoid unnecessary special handling for the columns depending on size....

HTH 

I am looking for a SQL statement to give me all of the details from a reconciliation. I can see all of the People brought in via the reconciliation in the PROCESS table. I can also see all of the messages written by  process.auditEvent() calls in the Person Modify (or Add) operation in the PROCESSLOG table. But I can't seem to find the list of attributes, previous value, new value for each person in any of the database tables. I'm certain the information is there, but I cannot figure out where it is.

As an SDI Jedi I have to object to the choice of SQL/PowerShell - in SDI this is VERY easy....

But - of course - we use the tools we are feeling comfortable with - so I will not start a fight on that :-) 

On Oracle SQL I found this : https://stackoverflow.com/questions/55092802/oracle-xmltable-path-get-node-ancestor it looks like it requires some special handling in Oracle...

HTH

I have been playing with the idea developing a Cognos package using this that would give a better overview of the the requests that the builtin.

There are a couple of challenges to that such as the dates in the PROCESS family tables being strings and not timestamps  - but as DB2 V11 added some builtin conversion functions it should now be possible to convert these to real timestamps either in Cognos or as views in the database.

Another challenge is of course that on top of Oracle and Db2 also Postgresql is now an option (currently IIRC only in the container version) but I believe all needed XML and date functions are SQL standards so they should be available on all platforms - Oracle I will not be able to test as I have no access to that - but Postgresql will be part of my test setup at some time in near future.

In general I have a weak point for Cognos - it is so immense powerful - but we are not really making this obvious and the installation/setup/configuration instructions are not that good - in older times a redbook would have helped - but we are not doing those anymore for Verify products.

I would be very interested in feedback and work on the queries I included above - so please comment if there is questions etc.   

The data is stored as a relatively simple XML document - depending on size - in the PROCESSLOG.NEW_DATA or  PROCESSLOG.SMALL_NEW_DATA.

Now - xml data is not easy to handle in SQL - but luckily SQL has some functionality to convert the data - I am not able at this moment to go into depth on how this works in details - but the query unfolds the stored data into a view - you have to look at the xml document to understand what it does (the document has sections for existing, old and new data - the example here shows the new entries) : 

SELECT id, process_id, object, new_attr, new_value FROM itimuser.processlog , XMLTABLE ('$d/Object/Attribute/Object/Attribute/Scalar' passing xmlparse(document coalesce(new_data,small_new_data)) as "d" 	COLUMNS 	new_value VARCHAR(2004) PATH 'substring(.,0,1999)' , 	new_attr VARCHAR(254) PATH '../@name' , 	object VARCHAR(254) PATH '../../../../@name' ) where data_id in ('Entity','person','account')

It is necessary to use relative XPATH to get to the data correctly as you will else get duplicate data.

What I have done is to create views for both new/old/unchanged data so that I can work with the data in e.g. Cognos Reports.

I have used this "unfolding" to perform an analysis of attributes changes in an HRFeed interface to get an overview of which attributes was driving the changes on identities - this illustrates the power of the XML processing :  

SELECT new_attr, substring(a.submitted,1,7) AS MONTH, count(distinct b.id)FROM itimuser.process A, 	itimuser.processlog B , 	XMLTABLE (		'$d/Object/Attribute/Object[@name="AttributeChangeOperation.operation.replace"]/Attribute/Scalar|$d/Object/Attribute/Object[@name="AttributeChangeOperation.operation.add"]/Attribute/Scalar' 			passing xmlparse(document replace(coalesce(b.new_data,b.small_new_data),',',' ')) as "d"		COLUMNS			new_value VARCHAR(32672) PATH '.'			, new_attr VARCHAR(254) PATH '../@name'			, object VARCHAR(254) PATH '../../../../@name'			, operation VARCHAR(254) PATH '../../@name'	) where data_id in ('Entity','person','account')and coalesce(b.new_data,b.small_new_data) is not NULL and a.id = b.process_idand a.name='personModifyProcessName'and a.id = a.root_process_idand b.activity_id = 0and a.submitted like '2023%'group by new_attr, substring(a.submitted,1,7)order by month, new_attr

I hope this gives you a starting point - I know XML handling is not the easiest thing to work with....

PS. The "coalesce" function basically collapses the 2 data columns into one - this is a nifty trick to avoid unnecessary special handling for the columns depending on size....

HTH 

I am looking for a SQL statement to give me all of the details from a reconciliation. I can see all of the People brought in via the reconciliation in the PROCESS table. I can also see all of the messages written by  process.auditEvent() calls in the Person Modify (or Add) operation in the PROCESSLOG table. But I can't seem to find the list of attributes, previous value, new value for each person in any of the database tables. I'm certain the information is there, but I cannot figure out where it is.

As an SDI Jedi I have to object to the choice of SQL/PowerShell - in SDI this is VERY easy....

But - of course - we use the tools we are feeling comfortable with - so I will not start a fight on that :-) 

On Oracle SQL I found this : https://stackoverflow.com/questions/55092802/oracle-xmltable-path-get-node-ancestor it looks like it requires some special handling in Oracle...

HTH

I have been playing with the idea developing a Cognos package using this that would give a better overview of the the requests that the builtin.

There are a couple of challenges to that such as the dates in the PROCESS family tables being strings and not timestamps  - but as DB2 V11 added some builtin conversion functions it should now be possible to convert these to real timestamps either in Cognos or as views in the database.

Another challenge is of course that on top of Oracle and Db2 also Postgresql is now an option (currently IIRC only in the container version) but I believe all needed XML and date functions are SQL standards so they should be available on all platforms - Oracle I will not be able to test as I have no access to that - but Postgresql will be part of my test setup at some time in near future.

In general I have a weak point for Cognos - it is so immense powerful - but we are not really making this obvious and the installation/setup/configuration instructions are not that good - in older times a redbook would have helped - but we are not doing those anymore for Verify products.

I would be very interested in feedback and work on the queries I included above - so please comment if there is questions etc.   

The data is stored as a relatively simple XML document - depending on size - in the PROCESSLOG.NEW_DATA or  PROCESSLOG.SMALL_NEW_DATA.

Now - xml data is not easy to handle in SQL - but luckily SQL has some functionality to convert the data - I am not able at this moment to go into depth on how this works in details - but the query unfolds the stored data into a view - you have to look at the xml document to understand what it does (the document has sections for existing, old and new data - the example here shows the new entries) : 

SELECT id, process_id, object, new_attr, new_value FROM itimuser.processlog , XMLTABLE ('$d/Object/Attribute/Object/Attribute/Scalar' passing xmlparse(document coalesce(new_data,small_new_data)) as "d" 	COLUMNS 	new_value VARCHAR(2004) PATH 'substring(.,0,1999)' , 	new_attr VARCHAR(254) PATH '../@name' , 	object VARCHAR(254) PATH '../../../../@name' ) where data_id in ('Entity','person','account')

It is necessary to use relative XPATH to get to the data correctly as you will else get duplicate data.

What I have done is to create views for both new/old/unchanged data so that I can work with the data in e.g. Cognos Reports.

I have used this "unfolding" to perform an analysis of attributes changes in an HRFeed interface to get an overview of which attributes was driving the changes on identities - this illustrates the power of the XML processing :  

SELECT new_attr, substring(a.submitted,1,7) AS MONTH, count(distinct b.id)FROM itimuser.process A, 	itimuser.processlog B , 	XMLTABLE (		'$d/Object/Attribute/Object[@name="AttributeChangeOperation.operation.replace"]/Attribute/Scalar|$d/Object/Attribute/Object[@name="AttributeChangeOperation.operation.add"]/Attribute/Scalar' 			passing xmlparse(document replace(coalesce(b.new_data,b.small_new_data),',',' ')) as "d"		COLUMNS			new_value VARCHAR(32672) PATH '.'			, new_attr VARCHAR(254) PATH '../@name'			, object VARCHAR(254) PATH '../../../../@name'			, operation VARCHAR(254) PATH '../../@name'	) where data_id in ('Entity','person','account')and coalesce(b.new_data,b.small_new_data) is not NULL and a.id = b.process_idand a.name='personModifyProcessName'and a.id = a.root_process_idand b.activity_id = 0and a.submitted like '2023%'group by new_attr, substring(a.submitted,1,7)order by month, new_attr

I hope this gives you a starting point - I know XML handling is not the easiest thing to work with....

PS. The "coalesce" function basically collapses the 2 data columns into one - this is a nifty trick to avoid unnecessary special handling for the columns depending on size....

HTH 

I am looking for a SQL statement to give me all of the details from a reconciliation. I can see all of the People brought in via the reconciliation in the PROCESS table. I can also see all of the messages written by  process.auditEvent() calls in the Person Modify (or Add) operation in the PROCESSLOG table. But I can't seem to find the list of attributes, previous value, new value for each person in any of the database tables. I'm certain the information is there, but I cannot figure out where it is.

Update: I've been trying to get the SQL query from @Franz Wolfhagen working in Oracle, and I have run into a wall that I can't get past, and I haven't really even gotten to the problem with relateve paths in XQuery. The problem I've hit is that the PROCESSLOG.NEW_DATA column is type LONG. This is a legacy datatype that has lots of restrictions. The list of restrictions is pretty extensive, but doesn't seem to be complete. The big restriction I found is that a LONG column can't be used in an XMLTABLE function. I found suggestions to use the TO_LOB() function to convert the column to an LOB, but you can't do that in-line. Basically, I've gotten to a "can't get there from here" point with this datatype. So something external to Oracle needs to be used if you need this data to be formatted nicely. Franz recommended IDI to do this, which is a good option.

As an SDI Jedi I have to object to the choice of SQL/PowerShell - in SDI this is VERY easy....

But - of course - we use the tools we are feeling comfortable with - so I will not start a fight on that :-) 

On Oracle SQL I found this : https://stackoverflow.com/questions/55092802/oracle-xmltable-path-get-node-ancestor it looks like it requires some special handling in Oracle...

HTH

I have been playing with the idea developing a Cognos package using this that would give a better overview of the the requests that the builtin.

There are a couple of challenges to that such as the dates in the PROCESS family tables being strings and not timestamps  - but as DB2 V11 added some builtin conversion functions it should now be possible to convert these to real timestamps either in Cognos or as views in the database.

Another challenge is of course that on top of Oracle and Db2 also Postgresql is now an option (currently IIRC only in the container version) but I believe all needed XML and date functions are SQL standards so they should be available on all platforms - Oracle I will not be able to test as I have no access to that - but Postgresql will be part of my test setup at some time in near future.

In general I have a weak point for Cognos - it is so immense powerful - but we are not really making this obvious and the installation/setup/configuration instructions are not that good - in older times a redbook would have helped - but we are not doing those anymore for Verify products.

I would be very interested in feedback and work on the queries I included above - so please comment if there is questions etc.   

The data is stored as a relatively simple XML document - depending on size - in the PROCESSLOG.NEW_DATA or  PROCESSLOG.SMALL_NEW_DATA.

Now - xml data is not easy to handle in SQL - but luckily SQL has some functionality to convert the data - I am not able at this moment to go into depth on how this works in details - but the query unfolds the stored data into a view - you have to look at the xml document to understand what it does (the document has sections for existing, old and new data - the example here shows the new entries) : 

SELECT id, process_id, object, new_attr, new_value FROM itimuser.processlog , XMLTABLE ('$d/Object/Attribute/Object/Attribute/Scalar' passing xmlparse(document coalesce(new_data,small_new_data)) as "d" 	COLUMNS 	new_value VARCHAR(2004) PATH 'substring(.,0,1999)' , 	new_attr VARCHAR(254) PATH '../@name' , 	object VARCHAR(254) PATH '../../../../@name' ) where data_id in ('Entity','person','account')

It is necessary to use relative XPATH to get to the data correctly as you will else get duplicate data.

What I have done is to create views for both new/old/unchanged data so that I can work with the data in e.g. Cognos Reports.

I have used this "unfolding" to perform an analysis of attributes changes in an HRFeed interface to get an overview of which attributes was driving the changes on identities - this illustrates the power of the XML processing :  

SELECT new_attr, substring(a.submitted,1,7) AS MONTH, count(distinct b.id)FROM itimuser.process A, 	itimuser.processlog B , 	XMLTABLE (		'$d/Object/Attribute/Object[@name="AttributeChangeOperation.operation.replace"]/Attribute/Scalar|$d/Object/Attribute/Object[@name="AttributeChangeOperation.operation.add"]/Attribute/Scalar' 			passing xmlparse(document replace(coalesce(b.new_data,b.small_new_data),',',' ')) as "d"		COLUMNS			new_value VARCHAR(32672) PATH '.'			, new_attr VARCHAR(254) PATH '../@name'			, object VARCHAR(254) PATH '../../../../@name'			, operation VARCHAR(254) PATH '../../@name'	) where data_id in ('Entity','person','account')and coalesce(b.new_data,b.small_new_data) is not NULL and a.id = b.process_idand a.name='personModifyProcessName'and a.id = a.root_process_idand b.activity_id = 0and a.submitted like '2023%'group by new_attr, substring(a.submitted,1,7)order by month, new_attr

I hope this gives you a starting point - I know XML handling is not the easiest thing to work with....

PS. The "coalesce" function basically collapses the 2 data columns into one - this is a nifty trick to avoid unnecessary special handling for the columns depending on size....

HTH 

I am looking for a SQL statement to give me all of the details from a reconciliation. I can see all of the People brought in via the reconciliation in the PROCESS table. I can also see all of the messages written by  process.auditEvent() calls in the Person Modify (or Add) operation in the PROCESSLOG table. But I can't seem to find the list of attributes, previous value, new value for each person in any of the database tables. I'm certain the information is there, but I cannot figure out where it is.

I have never really worked with ISVG/ISIM on Oracle (and be aware that IVIG 11 is only supporting Db2 and Postgresql) - but I can see there is a TO_LOB sql function that may solve you challenge of the restrictions that LONG imposes ?  

Update: I've been trying to get the SQL query from @Franz Wolfhagen working in Oracle, and I have run into a wall that I can't get past, and I haven't really even gotten to the problem with relateve paths in XQuery. The problem I've hit is that the PROCESSLOG.NEW_DATA column is type LONG. This is a legacy datatype that has lots of restrictions. The list of restrictions is pretty extensive, but doesn't seem to be complete. The big restriction I found is that a LONG column can't be used in an XMLTABLE function. I found suggestions to use the TO_LOB() function to convert the column to an LOB, but you can't do that in-line. Basically, I've gotten to a "can't get there from here" point with this datatype. So something external to Oracle needs to be used if you need this data to be formatted nicely. Franz recommended IDI to do this, which is a good option.

As an SDI Jedi I have to object to the choice of SQL/PowerShell - in SDI this is VERY easy....

But - of course - we use the tools we are feeling comfortable with - so I will not start a fight on that :-) 

On Oracle SQL I found this : https://stackoverflow.com/questions/55092802/oracle-xmltable-path-get-node-ancestor it looks like it requires some special handling in Oracle...

HTH

I have been playing with the idea developing a Cognos package using this that would give a better overview of the the requests that the builtin.

There are a couple of challenges to that such as the dates in the PROCESS family tables being strings and not timestamps  - but as DB2 V11 added some builtin conversion functions it should now be possible to convert these to real timestamps either in Cognos or as views in the database.

Another challenge is of course that on top of Oracle and Db2 also Postgresql is now an option (currently IIRC only in the container version) but I believe all needed XML and date functions are SQL standards so they should be available on all platforms - Oracle I will not be able to test as I have no access to that - but Postgresql will be part of my test setup at some time in near future.

In general I have a weak point for Cognos - it is so immense powerful - but we are not really making this obvious and the installation/setup/configuration instructions are not that good - in older times a redbook would have helped - but we are not doing those anymore for Verify products.

I would be very interested in feedback and work on the queries I included above - so please comment if there is questions etc.   

The data is stored as a relatively simple XML document - depending on size - in the PROCESSLOG.NEW_DATA or  PROCESSLOG.SMALL_NEW_DATA.

Now - xml data is not easy to handle in SQL - but luckily SQL has some functionality to convert the data - I am not able at this moment to go into depth on how this works in details - but the query unfolds the stored data into a view - you have to look at the xml document to understand what it does (the document has sections for existing, old and new data - the example here shows the new entries) : 

SELECT id, process_id, object, new_attr, new_value FROM itimuser.processlog , XMLTABLE ('$d/Object/Attribute/Object/Attribute/Scalar' passing xmlparse(document coalesce(new_data,small_new_data)) as "d" 	COLUMNS 	new_value VARCHAR(2004) PATH 'substring(.,0,1999)' , 	new_attr VARCHAR(254) PATH '../@name' , 	object VARCHAR(254) PATH '../../../../@name' ) where data_id in ('Entity','person','account')

It is necessary to use relative XPATH to get to the data correctly as you will else get duplicate data.

What I have done is to create views for both new/old/unchanged data so that I can work with the data in e.g. Cognos Reports.

I have used this "unfolding" to perform an analysis of attributes changes in an HRFeed interface to get an overview of which attributes was driving the changes on identities - this illustrates the power of the XML processing :  

SELECT new_attr, substring(a.submitted,1,7) AS MONTH, count(distinct b.id)FROM itimuser.process A, 	itimuser.processlog B , 	XMLTABLE (		'$d/Object/Attribute/Object[@name="AttributeChangeOperation.operation.replace"]/Attribute/Scalar|$d/Object/Attribute/Object[@name="AttributeChangeOperation.operation.add"]/Attribute/Scalar' 			passing xmlparse(document replace(coalesce(b.new_data,b.small_new_data),',',' ')) as "d"		COLUMNS			new_value VARCHAR(32672) PATH '.'			, new_attr VARCHAR(254) PATH '../@name'			, object VARCHAR(254) PATH '../../../../@name'			, operation VARCHAR(254) PATH '../../@name'	) where data_id in ('Entity','person','account')and coalesce(b.new_data,b.small_new_data) is not NULL and a.id = b.process_idand a.name='personModifyProcessName'and a.id = a.root_process_idand b.activity_id = 0and a.submitted like '2023%'group by new_attr, substring(a.submitted,1,7)order by month, new_attr

I hope this gives you a starting point - I know XML handling is not the easiest thing to work with....

PS. The "coalesce" function basically collapses the 2 data columns into one - this is a nifty trick to avoid unnecessary special handling for the columns depending on size....

HTH 

I am looking for a SQL statement to give me all of the details from a reconciliation. I can see all of the People brought in via the reconciliation in the PROCESS table. I can also see all of the messages written by  process.auditEvent() calls in the Person Modify (or Add) operation in the PROCESSLOG table. But I can't seem to find the list of attributes, previous value, new value for each person in any of the database tables. I'm certain the information is there, but I cannot figure out where it is.

Thanks Franz. Unfortunately, TO_LOB() can only be used in very specific situations, and this isn't one of them. Specifically, from the docs:

You can apply this function only to a LONG or LONG RAW column, and only in the select list of a subquery in an INSERT statement.

I have never really worked with ISVG/ISIM on Oracle (and be aware that IVIG 11 is only supporting Db2 and Postgresql) - but I can see there is a TO_LOB sql function that may solve you challenge of the restrictions that LONG imposes ?  

Update: I've been trying to get the SQL query from @Franz Wolfhagen working in Oracle, and I have run into a wall that I can't get past, and I haven't really even gotten to the problem with relateve paths in XQuery. The problem I've hit is that the PROCESSLOG.NEW_DATA column is type LONG. This is a legacy datatype that has lots of restrictions. The list of restrictions is pretty extensive, but doesn't seem to be complete. The big restriction I found is that a LONG column can't be used in an XMLTABLE function. I found suggestions to use the TO_LOB() function to convert the column to an LOB, but you can't do that in-line. Basically, I've gotten to a "can't get there from here" point with this datatype. So something external to Oracle needs to be used if you need this data to be formatted nicely. Franz recommended IDI to do this, which is a good option.

As an SDI Jedi I have to object to the choice of SQL/PowerShell - in SDI this is VERY easy....

But - of course - we use the tools we are feeling comfortable with - so I will not start a fight on that :-) 

On Oracle SQL I found this : https://stackoverflow.com/questions/55092802/oracle-xmltable-path-get-node-ancestor it looks like it requires some special handling in Oracle...

HTH

I have been playing with the idea developing a Cognos package using this that would give a better overview of the the requests that the builtin.

There are a couple of challenges to that such as the dates in the PROCESS family tables being strings and not timestamps  - but as DB2 V11 added some builtin conversion functions it should now be possible to convert these to real timestamps either in Cognos or as views in the database.

Another challenge is of course that on top of Oracle and Db2 also Postgresql is now an option (currently IIRC only in the container version) but I believe all needed XML and date functions are SQL standards so they should be available on all platforms - Oracle I will not be able to test as I have no access to that - but Postgresql will be part of my test setup at some time in near future.

In general I have a weak point for Cognos - it is so immense powerful - but we are not really making this obvious and the installation/setup/configuration instructions are not that good - in older times a redbook would have helped - but we are not doing those anymore for Verify products.

I would be very interested in feedback and work on the queries I included above - so please comment if there is questions etc.   

The data is stored as a relatively simple XML document - depending on size - in the PROCESSLOG.NEW_DATA or  PROCESSLOG.SMALL_NEW_DATA.

Now - xml data is not easy to handle in SQL - but luckily SQL has some functionality to convert the data - I am not able at this moment to go into depth on how this works in details - but the query unfolds the stored data into a view - you have to look at the xml document to understand what it does (the document has sections for existing, old and new data - the example here shows the new entries) : 

SELECT id, process_id, object, new_attr, new_value FROM itimuser.processlog , XMLTABLE ('$d/Object/Attribute/Object/Attribute/Scalar' passing xmlparse(document coalesce(new_data,small_new_data)) as "d" 	COLUMNS 	new_value VARCHAR(2004) PATH 'substring(.,0,1999)' , 	new_attr VARCHAR(254) PATH '../@name' , 	object VARCHAR(254) PATH '../../../../@name' ) where data_id in ('Entity','person','account')

It is necessary to use relative XPATH to get to the data correctly as you will else get duplicate data.

What I have done is to create views for both new/old/unchanged data so that I can work with the data in e.g. Cognos Reports.

I have used this "unfolding" to perform an analysis of attributes changes in an HRFeed interface to get an overview of which attributes was driving the changes on identities - this illustrates the power of the XML processing :  

SELECT new_attr, substring(a.submitted,1,7) AS MONTH, count(distinct b.id)FROM itimuser.process A, 	itimuser.processlog B , 	XMLTABLE (		'$d/Object/Attribute/Object[@name="AttributeChangeOperation.operation.replace"]/Attribute/Scalar|$d/Object/Attribute/Object[@name="AttributeChangeOperation.operation.add"]/Attribute/Scalar' 			passing xmlparse(document replace(coalesce(b.new_data,b.small_new_data),',',' ')) as "d"		COLUMNS			new_value VARCHAR(32672) PATH '.'			, new_attr VARCHAR(254) PATH '../@name'			, object VARCHAR(254) PATH '../../../../@name'			, operation VARCHAR(254) PATH '../../@name'	) where data_id in ('Entity','person','account')and coalesce(b.new_data,b.small_new_data) is not NULL and a.id = b.process_idand a.name='personModifyProcessName'and a.id = a.root_process_idand b.activity_id = 0and a.submitted like '2023%'group by new_attr, substring(a.submitted,1,7)order by month, new_attr

I hope this gives you a starting point - I know XML handling is not the easiest thing to work with....

PS. The "coalesce" function basically collapses the 2 data columns into one - this is a nifty trick to avoid unnecessary special handling for the columns depending on size....

HTH 

I am looking for a SQL statement to give me all of the details from a reconciliation. I can see all of the People brought in via the reconciliation in the PROCESS table. I can also see all of the messages written by  process.auditEvent() calls in the Person Modify (or Add) operation in the PROCESSLOG table. But I can't seem to find the list of attributes, previous value, new value for each person in any of the database tables. I'm certain the information is there, but I cannot figure out where it is.

I am looking for a SQL statement to give me all of the details from a reconciliation. I can see all of the People brought in via the reconciliation in the PROCESS table. I can also see all of the messages written by  process.auditEvent() calls in the Person Modify (or Add) operation in the PROCESSLOG table. But I can't seem to find the list of attributes, previous value, new value for each person in any of the database tables. I'm certain the information is there, but I cannot figure out where it is.

@Franz Wolfhagen, based on your suggestion, and the realization that I needed to mess with XML and XSLT again, I came up with an assemblyline to turn the audit data into HTML. A huge help was your reply on Narchive (https://ibm.software.network.directory-integrator.narkive.com/08ISRCSS/tdi-7-0-in-parsing-an-xml-file) describing the use of the XML Parser on a simple string, rather than having to read from a file.

Using IDI for this was easier than my proposed PowerShell solution. Plus, I have the stubs in place in the AL to make it available from a REST API call in the future.

I made a video of it here: 

The link to the project containing the assemblyline is: https://github.com/franktate/IBM-Directory-Integrator. I also made a video covering the use of XSLT to convert the XML to HTML, and it's here: https://youtu.be/7yEkGZGN1Lg.

Thanks again for your contributions, Franz.

I am looking for a SQL statement to give me all of the details from a reconciliation. I can see all of the People brought in via the reconciliation in the PROCESS table. I can also see all of the messages written by  process.auditEvent() calls in the Person Modify (or Add) operation in the PROCESSLOG table. But I can't seem to find the list of attributes, previous value, new value for each person in any of the database tables. I'm certain the information is there, but I cannot figure out where it is.

OK, just one terminology objection:

When talking about reconciliation, I was thinking about services accounts - we do reconcile services, right?

For the users, we talk about data feeds, or HR feeds, which do user data changes and new users.

Of course, you may reconcile data feed as well, so you may be right here, but when I saw initial post, I was confused - like, there is OOB report "Reconciliation statistics", what is this all about?

After looking video, I understand now what was your question about, at the first place.

Nice work, BTW! :-)

Mita

@Franz Wolfhagen, based on your suggestion, and the realization that I needed to mess with XML and XSLT again, I came up with an assemblyline to turn the audit data into HTML. A huge help was your reply on Narchive (https://ibm.software.network.directory-integrator.narkive.com/08ISRCSS/tdi-7-0-in-parsing-an-xml-file) describing the use of the XML Parser on a simple string, rather than having to read from a file.

Using IDI for this was easier than my proposed PowerShell solution. Plus, I have the stubs in place in the AL to make it available from a REST API call in the future.

I made a video of it here: 

The link to the project containing the assemblyline is: https://github.com/franktate/IBM-Directory-Integrator. I also made a video covering the use of XSLT to convert the XML to HTML, and it's here: https://youtu.be/7yEkGZGN1Lg.

Thanks again for your contributions, Franz.

I am looking for a SQL statement to give me all of the details from a reconciliation. I can see all of the People brought in via the reconciliation in the PROCESS table. I can also see all of the messages written by  process.auditEvent() calls in the Person Modify (or Add) operation in the PROCESSLOG table. But I can't seem to find the list of attributes, previous value, new value for each person in any of the database tables. I'm certain the information is there, but I cannot figure out where it is.

@Franz Wolfhagen, based on your suggestion, and the realization that I needed to mess with XML and XSLT again, I came up with an assemblyline to turn the audit data into HTML. A huge help was your reply on Narchive (https://ibm.software.network.directory-integrator.narkive.com/08ISRCSS/tdi-7-0-in-parsing-an-xml-file) describing the use of the XML Parser on a simple string, rather than having to read from a file.

Using IDI for this was easier than my proposed PowerShell solution. Plus, I have the stubs in place in the AL to make it available from a REST API call in the future.

I made a video of it here: 

The link to the project containing the assemblyline is: https://github.com/franktate/IBM-Directory-Integrator. I also made a video covering the use of XSLT to convert the XML to HTML, and it's here: https://youtu.be/7yEkGZGN1Lg.

Thanks again for your contributions, Franz.

I am looking for a SQL statement to give me all of the details from a reconciliation. I can see all of the People brought in via the reconciliation in the PROCESS table. I can also see all of the messages written by  process.auditEvent() calls in the Person Modify (or Add) operation in the PROCESSLOG table. But I can't seem to find the list of attributes, previous value, new value for each person in any of the database tables. I'm certain the information is there, but I cannot figure out where it is.