RE: EM10gr2 Grid Control Reporting Question

From: Newman, Christopher <>
Date: Wed, 4 Jun 2008 14:58:24 -0500
Message-ID: <>

Thanks Bradd. That clears things up for me. The 'CREATE LIKE' doesn't show the SQL, but I am under the assumption then that the remote agent is sending only a subset of the audit trail to EM, which is then queried to produce the 'Failed Login' reports. One fix would be to modify the agent scripts to grab the needed data from the remote audit trail, then fix EM to query it. Sounds simple, but I'm sure it's more complicated than that.

Has anyone successfully used EM to collate audit information?

Thanks- Chris

From: Bradd Piontek [] Sent: Wednesday, June 04, 2008 12:35 PM
To: Newman, Christopher
Subject: Re: EM10gr2 Grid Control Reporting Question

Ahh, a topic near and dear to me :) Oracle EM reporting. The thing to note about reports is that you are logging into the OMS Repository, not the source database. Therefore, dba_audit_trail (if you had access to it) would be the local copy on your OMS Repository, not the target database. If there is an existing, canned report int he Report Repository, your best bet is to do a 'CREATE LIKE' and see what the source of the data is. It may be a SQL query or could be an included metric (I haven't checked). As of, I don't think reporting supported reports outside of the OMS Repository.


Bradd Piontek
Twitter: Oracle Blog: Linked In: On Wed, Jun 4, 2008 at 12:15 PM, Newman, Christopher <> wrote: Hello,

I'm attempting to create a custom report in 10gr2 EM, looking for auditing information.  I know there is a separate report for failed logins (which runs successfully), but the actual SQL isn't visible, and I want to include this section in a comprehensive report.

I've set up my custom report using the element 'Table from SQL' and the SQL looks something like this:

select os_username, action_name, timestamp from dba_audit_trail where timestamp > sysdate-90;

However, when looking at the preview, I'm getting:

Error rendering element. Exception: ORA-00942: table or view does not exist

I'm logged into the grid as 'sysman', and I have preferred credentials set for the database target in question; both standard and sysdba credentials.  I can login manually with either of my preferred creds and successfully query dba_audit_trail (or sys.aud$ the underlying table, or course), also, the 'test' of preferred creds in EM returns successfully.

The only thing emoms.log shows is:

2008-06-04 12:12:11,162 [EMUI_12_12_11_/console/reports/render] ERROR elem.TableRenderBean prepareData.1033 - SQL Error Executing Query - select os_username, action_name, timestamp from dba_audit_trail where timestamp > sysdate-90 - Exception message: ORA-00942: table or view does not exist
java.sql.SQLException: ORA-00942: table or view does not exist

So I'm not sure where I'm going wrong in terms of permissions, or where to find the actual SQL that EM is using to generate the report.

Thanks- Chris


-- Received on Wed Jun 04 2008 - 14:58:24 CDT

Original text of this message