Oracle report returning empty results / an error

Versions / Builds Affected

EventsManager 2011 Report Pack build 20110401

Status

Resolved

Problem Summary

The SQL query built when running a "Generic Oracle Audit" report respectively a custom report based on it holds 2 problems resulting in and error respectively empty reports.

TT / JIRAID

ESM-24

How to Identify

Problem #1: The "Generic Oracle Audit" returns "No events were found matching the filtering criteria". The debug logs show a similar query to: SELECT [ID], [EVENT_DATE], [EVENT_TIME], [LOG],[COMPUTER], [USERNAME], [OS_USERNAME],[HOST_NAME],[RULE_NAME], [OBJECT], [OWNER], [ACTION_NAME], [OS_PROCESS], [SQL_TEXT] FROM (SELECT [ID], [DATE] as [EVENT_DATE], [TIME] as [EVENT_TIME],'7' as [LOG], case when [SOURCE_COMPUTER]='' then 'N/A' else [SOURCE_COMPUTER] end as [COMPUTER], case when [USERNAME]='' then 'N/A' else [USERNAME] end as [USERNAME], case when [OS_USERNAME]='' then 'N/A' else [OS_USERNAME] end as [OS_USERNAME], case when [USERHOST]='' then 'N/A' else [USERHOST] end as [HOST_NAME], case when [RULE_NAME]='' then 'N/A' else [RULE_NAME] end as [RULE_NAME], [OWNER] +'.'+[OBJ_NAME] as [OBJECT], [ACTION_NAME], [OWNER], case when [OS_PROCESS]='' then 'N/A' else [OS_PROCESS] end as [OS_PROCESS], case when [SQL_TEXT]='' then 'N/A' else [SQL_TEXT] end as [SQL_TEXT], FROM Oracle_Audit ) AS [EVENTS] WHERE CAST([EVENTS].[EVENT_TIME] AS DECIMAL(20,7)) >= 29219.0000000 AND CAST([EVENTS].[EVENT_TIME] AS DECIMAL(20,7)) <= 29219.9999884 AND CAST([EVENTS].[EVENT_DATE] AS DECIMAL(20,7)) >= 40748.0000000 AND CAST([EVENTS].[EVENT_DATE] AS DECIMAL(20,7)) <= 40748.9999884 AND [EVENTS].[LOG] IN ('1') ORDER BY [EVENT_DATE] ASC,[EVENT_TIME] ASC The subquery contains "SELECT ... '7' as [LOG]". In combination with the WHERE condition "[EVENTS].[LOG] IN ('1')" this will never return any result. Problem #2: When customizing this report and restricting it to a certain Classification (e.g. to Critical events only) the following SQL query is built: SELECT [ID], [EVENT_DATE], [EVENT_TIME], [LOG],[COMPUTER], [USERNAME], [OS_USERNAME],[HOST_NAME],[RULE_NAME], [OBJECT], [OWNER], [ACTION_NAME], [OS_PROCESS], [SQL_TEXT] FROM (SELECT [ID], [DATE] as [EVENT_DATE], [TIME] as [EVENT_TIME],'7' as [LOG], case when [SOURCE_COMPUTER]='' then 'N/A' else [SOURCE_COMPUTER] end as [COMPUTER], case when [USERNAME]='' then 'N/A' else [USERNAME] end as [USERNAME], case when [OS_USERNAME]='' then 'N/A' else [OS_USERNAME] end as [OS_USERNAME], case when [USERHOST]='' then 'N/A' else [USERHOST] end as [HOST_NAME], case when [RULE_NAME]='' then 'N/A' else [RULE_NAME] end as [RULE_NAME], [OWNER] +'.'+[OBJ_NAME] as [OBJECT], [ACTION_NAME], [OWNER], case when [OS_PROCESS]='' then 'N/A' else [OS_PROCESS] end as [OS_PROCESS], case when [SQL_TEXT]='' then 'N/A' else [SQL_TEXT] end as [SQL_TEXT] FROM Oracle_Audit ) AS [EVENTS] WHERE ([PC_ALERT_LEVEL]=N'4') AND CAST([EVENTS].[EVENT_TIME] AS DECIMAL(20,7)) >= 29219.0000000 AND CAST([EVENTS].[EVENT_TIME] AS DECIMAL(20,7)) <= 29219.9999884 AND CAST([EVENTS].[EVENT_DATE] AS DECIMAL(20,7)) >= 40748.0000000 AND CAST([EVENTS].[EVENT_DATE] AS DECIMAL(20,7)) <= 40748.9999884 AND [EVENTS].[LOG] IN ('1') ORDER BY [EVENT_DATE] ASC,[EVENT_TIME] ASC The WHERE condition contains PC_ALERT_LEVEL]=N'4', but the SELECT query does not pull this column from the database. When running this report it will result in an error. The debug logs shows: 2011-06-22,14:43:07,981,0,"#00003A88","#00000D28","error ","ReportPlugIn","CrystalUserControl.LoadReport:Invalid column name 'PC_ALERT_LEVEL'."

Workaround / Fix Details

A temporary patch is available via PSG.

Required Actions

1. Please confirm that Oracle data is stored in the data base 2. Confirm logs above apply 3. Escalate to PSG