Audit Report Error fixed in SP9
Symptoms of the Problem:
~tmp1af067603a9c5c2.rpt: Error in formula <Section_Visibility>. 'not isnull({Command.task_name}) ' This field name is not known. Details: errorKind
The above error was produced when running a Simple Audit Details report, even with a limited date range of one day or less.
To determine the number of audit records you have in your audit db, simply run the following query:
Select COUNT(*)
from imsauditevent12 ae,
imsauditeventobject12 aeo,
imsauditobjectattributes12 aoa,
imsaudittasksession12 ats
where aeo.parent_event_id = ae.id
and aoa.parent_object_id = aeo.id
and tasksession_id = ats.id
If you have millions of records in your audit database, it seems that your audit details error matches THIS KNOWN ISSUE.
Summary of the fix:
In the issue I reproduced, the audit data was filled with around1.67 million records, even though the query filtered by date range was returning a 100 or less records.
This was an issue where the BOXI server is running out of memory, when the full table scan was performed.
The main issue is the audit database, used by the customer, is too large (around 2 million records). The report in question is trying to load all the data into the report server and then tried to filter out. Fixed the issue to send the filter to the database server itself and let it do the heavy sorting.
However note one thing: even if the filter is done in the database server, if you're going to get 1 million records after the filtering done, the report will still fail because of the memory issues acknowledged by BOXI.
To workaround, the reports should have more filters, in addition to the date ranges... This will be enhanced in future release.
Fixed the issue for various audit reports... All the other audit reports now incorporate logic so the filtering will be done on the database server side. The changed report templates for SP9 are:
1) Audit Details Report
2) Audit-Assign Revoke Provisioning Roles Report
3) Audit-De-Provisioning Report
4) Audit-Pending Approval Tasks Report
5) Audit-Reset Password Report
1) Make sure you have tons of audit data. This can be achieved by enabling the auditing settings for an IM environment and making sure it is adding data into audit for some time.
(To artificially reproduce this you can take your production audit database and simply report off of it in test, or enable auditing and run TEWS or bulk loader on it with a million transactions. Bulk loader would probably be better because it can scale the submissions in chunks of 100.)
Start with 1,000,000 records in the audit database when you run this query:
[color=#0130ee]
select ae.id as event_id,
ae.event_name as event_name,
ae.event_description as event_description,
ae.audit_time as audit_time,
ae.admin_name as adminname,
ae.event_state as event_state,
ae.tasksession_id as tasksession_id,
ae.env_oid as env_oid,
ae.event_oid as event_oid,
aeo.parent_event_id as parent_event_id,
ats.task_name as task_name,
aeo.id as event_object_id,
aoa.parent_object_id as parent_event_object_id,
aoa.display_name as attribute_name,
aoa.attribute_oldvalue as attribute_oldvalue,
aoa.attribute_newvalue as attribute_newvalue
from imsauditevent12 ae, imsauditeventobject12 aeo,
imsauditobjectattributes12 aoa,
imsaudittasksession12 ats
where aeo.parent_event_id = ae.id
and aoa.parent_object_id = aeo.id
and tasksession_id = ats.id
and ae.event_name <> 'SetPrimaryObjectAuditEvent'
and ae.event_name <> 'SynchronizationUserEvent'
and ae.event_name <> 'SynchronizeAttributesWithAccountsEvent'
order by ats.task_name
[color]
2) Setup the report server connection, audit database connection as per the "Reporting" document in the IM installation and administration guide.
3) Run the reports that start with "Audit". Note: If you see empty report make sure you have performed all the tasks that are required by the auditing. If there is no data (for ex. pending tasks) in the auditing database, naturally your report will not show the data. The report is just a messenger. :rolleyes:
4) Veirfy the report is not failing.
Possible Workarounds:
- Simply creating a new audit db and configuring your IME and Reporting to use it, will immediately mask the error.
- Audit Settings XML should be meticulously tweaked to store minimum amount of information in the audit db.
- Audit Garbage collection should be more aggressively implemented and audit database size routinely monitored.
- Tune your audit database to accommodate full table scan, the audit report will request.
- Try using the audit report from the latest SP9/10 BIAR file.
- Review your report server to see if there are any timeout settings you can increase or if you can add additional memory.
Know Workarounds / exposes need for further enhancement:
A workaround to the above would also be to trim your audit repository.
I've already submitted an enhancement request on behalf of a number of large customers' to enhance the Audit Database clean-up procedure...
This procedure for audit should be enhanced and more closely match that of the task persistence garbage collection procedure, which was enhanced as a new feature with 12.5. Specifically, audit data may be covered under regulatory compliance guidelines and although the customers' production audit database may easily exceed twenty million records (without impacting general Identity Manager performance), this information may be necessary to archive for a period of time or possibly indefinitely (even though it might not be used). Identity Manager should provide a simple UI for archiving this information, rather than simply deleting it, which currently requires the customers' DBA to automate database backup for archival before running this procedure. Additionally, the recurrence scheduler should be incorporated into this IME enabled task, which should also be accessible via the IME. Making the processes solely dependent on executing at the database level, via stored procedure, puts additional pressure on customers' DBA to automate and schedule the execution of the job, but if this process more closely resembled the existing TP garbage collection process, this wouldn’t be the case. Additionally, the documentation should be enhanced to clearly indicate that any of these garbage clean-up tasks shouldn’t be executed during peak periods, else risk adversely affecting production system end-user performance, up to and including denial of service (if deleting millions of records, you could tax the database, which would prevent the IME from functioning until the purge is complete)
Please post any questions or concerns.
Thank you.
Regards,
Chris Thomas
CA Technologies
Principal Support Engineer
Identity Manager Reporting Expert
Tel: +1-631-342-4360
Chris.Thomas@ca.com