Query sys_audit and syslog_transaction table

[spiegel]

Hello everyone, 

I am trying to query the sys_audit and syslog_transaction table to get all the records. I am well aware of the massive size of these tables and the performance overhead of querying them. Nevertheless, ignoring the overehead, How would one go about achieving this? My aim is to query these tables to get all the records, not all at once but periodically works. I have tried to paginate using the sysparm_limit and sysparm_offset parameters but that also returns max execution time reached error. The other requirement is to achieve this using the REST API without using javascript or python.

Help would be highly appreciated. Thank you.

[DrewW]

Add an index to the sys_updated_on field, it will take some time to complete.  Then query the data to get one day at a time.  Don't ask for all of the records and then page it, just ask for all of the records for 1 day.  Process those then ask for all of the records for the next day.  Its really the only way you are going to be able to get it all.

You may also want to consider only getting the sys_audit data for the tables that you replicated out and only the records that you replicated out.

What is your end goal?  What is going to be making the rest call?

[Mark Roethof]

Hi there,

What would be the use case to do so? Its a known bad practice to report on these tables, or also API no no. Though maybe you have a certain use case for this?

Some customers have Table extension on sys_audit (this is not out-of-the-box, though I have seen this multiple times). If so, you could query the most recent sys_audit0* tables.

Do note: Often sys_audit is indeed one of the largest or largest tables on instances and manually quering or even using background script takes ages or times out. Though actually... why? Because sys_audit is full of garbage 🙂 Almost every customer out there can at least cut sys_audit in half. See my recent blogs on this subject. That should help having a better performance on the table in the first place.