Sample Audit Reports

[CognosPolzovatel]

In attempt to setting up the sample audit reports (following the Administration & Security Guide), after importing the sample audit reports, when going to Public folders and trying to run any of the sample audit reports, I receive the following error:

"An error occurred while performing operation 'sqlPrepareWithOptions' status='-201'."

Has anyone ever encountered such an error or have any idea on what the error may be about?
If I click on Details, I get lots of output, starting with:
UDA-SQL-0196 The table or view "Audit...COGIPF_USERLOGON" was not found in the dictionary. RSV-SRV-0042 Trace back: RSReportService.cpp(595):
etc...

Has anyone attempted to setup auditing and the audit db from start to finish & had success? Perhaps I missed a step?

[praveennb]

You will need to setup an audit database and audit datasource.

Open the cognos configuration
expand environment
then under Logging right click and create a new resource destination select the database option name this as Audit_C8 (Or any meaningful name), test the configuration then restart the services.

then go to Cognos connection create a new datasource named Audit, and choose the database name as Audit_C8, make sure you test the connection and save.

now the reports should work.

[CognosPolzovatel]

Hello user BI007:

The suggestions that you posted were all completed yesterday. After doing the complete setup (of the audit DB, the data source, the Cognos connection, etc.): this error gets generated when attempting to run any of the sample audit reports.

The only thing that I did not do was restart the services:
"then under Logging right click and create a new resource destination select the database option name this as Audit_C8 (Or any meaningful name), test the configuration then *restart the services*."
I was afraid to restart the services - since Cognos was & is being used at the moment. Hence, I will have to find an off-time to restart Cognos. I was hoping that the setup would work without the re-start. Looks like I was wrong, however. 

[CognosPolzovatel]

I decided to check my Audit database for the tables referenced (i.e., COGIPF_USERLOGON).

Via the Report Studio > Database View, I see the following Cognos tables referenced:
COGIPF_ACTION
COGIPF_EDITQUERY
COGIPF_NATIVEQUERY
COGIPF_PARAMETER
COGIPF_RUNJOB
COGIPF_RUNJOBSTEP
COGIPF_RUNREPORT
COGIPF_USERLOGON
COGIPF_VIEWREPORT
COGIPF_USERLOGON_FAILURES
COGIPF_USERLOGOFF
url_xml
etc...

However, when I logon to the DB via MS SQL Server Management, I see only System Tables in the schema. None of the tables that exist above.

Do you think this too has to do with the Cognos service restart? I'm hoping after our server gets restarted (over the weekend) - that my error will be resolved?

[MFGF]

Hi,

The audit tables will only get created when you do a restart of your C8 service.  Once the service has been stopped and restarted, you should see the tables have been created in your audit database - if not, it indicates you have a problem somewhere in the connection from C8 to the audit database.

Regards,

MF.

[CognosPolzovatel]

Yes: had to learn that the hard way.

Just found the following statement in the Installation & Configuration Guide:
"After you configure Cognos 8 components to send messages to a logging database, and restart the Cognos 8 service, Cognos 8 components create the required tables and table fields."
That makes sense why my DB is blank when I log on via MS SQL Server Manager.
Will have to check everything after a restart.

Thanks.

[jwilkinson]

Did that fix up because I have the same issue but have completed the steps as described. It is complex to set up as the set up information appears to be distributed across various documents on Cognos Insight and the installation documentation. I am working through the issue with Cognos support and will report back any progress. It seems likely that my error (as described in the initial post of this thread) is due to incorrect set up of the url_xml datasource connection and the various steps relating to java which I think allow Cognos to populate the Audit database.

[CognosPolzovatel]

Hello user jwilkinson:
I got my Audit DB and sample reports setup to work. I had to restart the Cognos service, which obviously had to be done during a time that users were not using Cognos. The audit DB then populated. My issue was that I missed a very important step: to restart the Cognos service after setup. What particular issue/error are you receiving? Perhaps I can assist?

[jwilkinson]

Hi, I got mine working too. The resolution was that I needed to amend the Catalog and Schema values in the properties of the Datasource "Audit" in Framework manager - in Cognos 8.3 it populates the Catalog and Schema values with "Audit" and "dbo" which of course will only work if those are the values you have set up for the database. I set my Catalog value to the database name and the Schema to a username with access to the database.

If anyone is having a similar issue with reports as good troubleshooting step is to create a simple package using one of the query items (db tables) referenced in the Audit framework manager model, then publish the package and see if you can create reports using it.

Getting the "Report usage" report to work was a little trickier, but basically if you collate the guidance and then follow the instructions carefully checking the details against your own system then it will work, just bear in mind not to use Java Development kit more recent than J2SDK 1.4.2_16 or it will give a "Bad version" error on trying to connect to http://localhost:9300/p2pd/cognos/DSServlet.jsp. A good test to save somne time is after restarting Cognos Connection try to browse to http://localhost:9300/p2pd/cognos/DSServlet.jsp in Internet Explorer, you want to see an XML page come up containing a bunch of XML script.