Extracting data from sys_history or sys_audit
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎08-08-2012 01:53 AM
Hi,
We have a requirement to extract the Incident number, assignment group, field name, old value, new value, userid, description and timestamp of change for every available activity for an incident for the preceding 24 hour period in a csv format for each qualifying incident/activity.
On Service now this can be represented on the GUI with the sys_history_set list/screen (ie when looking at the history of an incident).
I'm looking for the best way of doing this - ODBC, cURL, reporting, metrics?
Has anybody successfully set this up?
Many thanks in advance for your help.
- Labels:
-
Performance Analytics
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎08-08-2012 07:39 AM
You might want to try one of the methods outlined in this wiki article - http://wiki.servicenow.com/index.php?title=Retrieving_A_Large_Number_Of_Records_From_ServiceNow#ServiceNow_Data_Pull
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎08-08-2012 09:00 AM
I've been extracting sys_audit data via ?XML web services in teh following way:
1) Get list of task sys_ids that were modified during extraction period
2) go one by one through those sys_ids getting ALL sys_audit data for that task as XML file
3) Load (thousands) of these XMLs to MS SQL Server.
Why so complex: document_id field in sys_audit is indexed while sys_created_on is not and thus in large environments you will timeout while querying data by sys_created_on. Alternative could be table rotation implementation but I did not try it.
Also I've done that before ODBC Driver was invented. Now it would be even more easier to do the same thing with Service-Now ODBC.