Product:
Cognos Analytics Extended Audit 11
Microsoft Windows 2019 server
Issue:
Setup AuditExt as of this page, https://developer.ibm.com/technologies/analytics/tutorials/ibm-cognos-11-audit-extension/
but when run, it fails, check of log file (D:\Program Files\ibm\cognos\analytics\logs\c11AuditExtension.log) show a error message:
Violation of PRIMARY KEY constraint ‘PK_AE_CA_SEC_MEM’. Cannot insert duplicate key in object ‘dbo.AE_SECURITY_MEMBERS’
Solution:
The issue can be that you reach max.items limit, and get above error in the log file.
Stop the Cognos BI service.
Go to folder D:\Program Files\ibm\cognos\analytics\wlpdropins\AuditExt.war\WEB-INF\classes
Open c11AuditExtension.properties in Notepad++
Change this lines:
| false — will not save the report xml in the audit database. | option.ca.include.specifications: A Content Audit option that determines if the audit should record the specification XML of any reports/queries/analyses that it finds. Possible values are true and false. The default value is true. If this parameter is set to false, less database space will be used. |
| 0 | option.ca.max.duration: A Content Audit option that limits the maximum length of time, in seconds, that the audit should be run for. If this time is exceeded, the audit is terminated and recorded as a failure. If it is set to a value of zero, no time limit will be applied. The default value is 1800 (30 minutes). |
| 0 | option.aa.max.duration: An Account Audit option that limits the maximum length of time, in seconds, that the audit should be run for. If this time is exceeded, the audit is terminated and recorded as a failure. If it is set to a value of zero, no time limit will be applied. The default value is 1800 (30 minutes). |
| 0 | option.aa.max.items: An Account Audit option that limits the maximum number of items that will be processed by the audit. If the number is exceeded, the audit is terminated and recorded as a failure. If it is set to a value of zero, no limit will be applied. The default value is 10000. |
| 0 | option.ra.max.duration: A Role Audit option that limits the maximum length of time, in seconds, that the audit should be run for. If this time is exceeded, the audit is terminated and recorded as a failure. If it is set to a value of zero, no time limit will be applied. The default value is 1800 (30 minutes). |
| 0 | option.ra.max.items: A Role Audit option that limits the maximum number of items that will be processed by the audit. If the number is exceeded, the audit is terminated and recorded as a failure. If it is set to a value of zero, no limit will be applied. The default value is 30000. |
| false — will not check my folders | option.aa.include.content: An Account Audit option that determines if the audit should process the content of users’ My Folders. If set, this will cause a mini-Content Audit to be run for each user’s content where it exists. Possible values are true and false. The default value is true. |
Save the file.
Start IBM Cognos windows service.
Browse to http://servername.domain.com:9300/AuditExt/ to run the “collection event” again.
After you have loaded the Audit Extension report package (AuditExt_deployment_c11_20181003), you can can run a report called “Audit Run Report” to see if the collection of audit data was successful.
Of course you need to create a data source called “audit_extension” to your database where you store the audit data.
Under the teams folder – Cognos Audit Extension – Role Audit – Capabilites available to Users report, can be the one that give you a detail view of the license possibility for each user. You need to test your way forward.
More information:
https://www.ironsidegroup.com/video/bi-expert/cognos-audit-extension-your-secret-weapon/
https://www.envisn.com/envisn-cognos-blog/bid/102863/Using-IBM-Cognos-10-Audit-Extensions
https://www.wisdomjobs.com/e-university/ibm-cognos-tutorial-196/auditing-4398.html
https://www.bspsoftware.com/knowledgebase/how-does-license-auditor-work/