The Services Framework includes a SQL Stored Procedure that will extract repository records (for the list of designated attributes) from the Repository History table based on the designated date and time and the optional search criteria. The end result is essentially an export of the updates that would need to be submitted as an import to effectively roll back the records to some previous state. From the stored procedure:
-- The following stored procedure is used to extract historical data from a repository
-- for specified records and the specified "as of" date.
-- The following input parameters are expected:
-- repositoryName - name of the repository
-- projectionList - comma-delimited list of attributes for which the data is to be extracted
-- searchKey - comma-delimited list of attributes and values that are used for the search parameters
-- Each search parameter has the format <attribute>=<value> where <attribute> is the name of
-- the attribute exactly as it is spelled in the specified repository, and <value> is the
-- value to be found (the percent sign can be used as a wildcard).
-- searchSource - h to search against history, m to search against master repository (current values)
-- asOfDate - date and time in the format 'mm/dd/yyyy hh:mm:ss.mmm' specifying the date/time from which
-- to obtain the values
--
-- For example, to extract the Customer Specific Price And Status for Active (at the time) records
-- Home Depot from the Archbrook_CustomerItemSpecific_Staging repository as it was defined on 9/25/2009 at 12:00 noon,
-- for the purposes of submitting an import file to restore those prices, execute the following command:
--
-- ExtractHistoricalData N'PIM_Item_Staging','Product,User Desc,Memo Expires,Status',
-- 'Status=a','m','12/12/2010 20:00:00.000'
--
--
-- The procedure will perform a query against the Item History table and extract the specified data for the
-- specified fields
--
-- The results of executing this procedure can be copied/pasted to an Excel spreadsheet and saved to
-- be used as an import file. This effectively "rolls back" the specified non-primary key fields to their
-- values at the specified date and time.
-Brian
------------------------------
Brian Zupke | Senior Technical Support Engineer
Winshuttle North America | 9099009179
------------------------------
Original Message:
Sent: 10-20-2020 10:28
From: Natalie Scott
Subject: Audit Reports & Bulk Rollback
We took a SQL script EW had given us for an issue during our implementation and modified for our rollback issue. Based on what we did, I would think it would work for others. I am not a SQL expert so I will do my best to explain what I have below:
select
itm.attr_data.value('(/Item/F_1004926/node())[1]','varchar(max)') as 'Attribute_Name',
/*Repeat for all attributes you want returned*/
from b_repository_item_history itm
where master_repository_id=10199 and modify_action='update' and ENTRY_DATETIME between'MM/DD/YYYY TT:TT:TT' AND 'MM/DD/YYYY TT:TT:TT'
--------------------------------------------
itm.attr_data.value('(/
- Item/F_1004926/node())[1]','varchar(max)') as 'Attribute_Name',
- Item=Profile
- 1004926= Attribute ID found in profile
- /node())[1]','varchar(max)')--I am not 100% certain about the remainder of script, but all our attributes in the SQL code they gave us finished this way.
b_repository_item_history itm- I believe this should be the same for everyone. It is where the item changes for all repositories are stored from my understanding.
master_repository_id=10199 -ID of the repository you want item history from
modify_action='update'- What action took place. If it was a mass update, update should work. We have used with delete as well.
ENTRY_DATETIME between'MM/DD/YYYY TT:TT:TT' AND 'MM/DD/YYYY TT:TT:TT'- If you know when update occured. We have used with specific internal ids if we knew a specific group of records that were changed or deleted.
-------------------------------------
Hope that helps.
------------------------------
Natalie Scott | Data Governance & Business Process Manager
Gourmet Foods International | 40421 04367
Original Message:
Sent: 10-20-2020 09:24
From: Jonathan Varo
Subject: Audit Reports & Bulk Rollback
A bulk roll back feature in the UI would be really helpful. A couple weeks back, I had a 10K+ mistake that took a day to roll back between me and my team. :-(
@Brenda Maxwell... Any chance that you could post a mini-tutorial on how to perform a mass roll back records using SQL? I've only been using SQL for two years so far and, as much as it's helped unlock much more functionality for our system, I've only just scratched the surface.
Best regards,
------------------------------
Jonathan Varo | PIM Lead
Fender Musical Instruments | 480-845-5823
Original Message:
Sent: 10-19-2020 14:55
From: Sidd Shenoy
Subject: Audit Reports & Bulk Rollback
Much appreciated!
Original Message:
Sent: 10-19-2020 13:53
From: Natalie Scott
Subject: Audit Reports & Bulk Rollback
I do not do any usage auditing, however there is some options we have looked at where we would want to monitor specific attributes and when they were changed. My understanding is you can do that with Change Notifications. I uploaded this attachment that EW team shared with me about this tool.
https://higherlogicdownload.s3.amazonaws.com/WINSHUTTLE/UploadedImages/dcf2d7e2-afc1-4b5c-a649-9b0b6a3fbbd8/ChangeNotificationAdminGuide_2020-01-19.docx
------------------------------
Natalie Scott | Data Governance & Business Process Manager
Gourmet Foods International | 40421 04367
Original Message:
Sent: 10-19-2020 13:20
From: Sidd Shenoy
Subject: Audit Reports & Bulk Rollback
Very helpful! Thank you!
Do you do any kind of usage auditing?
------------------------------
Sidd Shenoy | Senior Director Enterprise Master Data
Thomson Reuters | (646) 540-2371
Original Message:
Sent: 10-19-2020 09:02
From: Natalie Scott
Subject: Audit Reports & Bulk Rollback
I am not aware of a report that details what the users edit.
For the roll back-we have used a SQL and item history table with the date /time right before the mass edit happened. I then imported data into the repository where the change was made.
I don't think there is a way to do a roll back for a group of records in the UI.
------------------------------
Natalie Scott | Data Governance & Business Process Manager
Gourmet Foods International | 40421 04367
Original Message:
Sent: 10-16-2020 16:22
From: Sidd Shenoy
Subject: Audit Reports & Bulk Rollback
Hi everyone. 2 questions for this forum:
1) Have you tried creating audit reports which detail the users that logged in over a period of time, what they edited in the system, etc.?
2) Have you tried rolling back a job? I did a bulk edit of multiple records and something bad occurred. My only option seemed to be going to each record individually and hitting rollback. Hard to do for 4k+ records!
Thanks in advance!
------------------------------
Sidd Shenoy | Senior Director Enterprise Master Data
Thomson Reuters | (646) 540-2371
------------------------------