Database troubleshooting

This topic provides information on how to analyze and troubleshoot problems that you might experience when using TrueSight Operations Management Report Engine with Oracle or SQL Server database.

Data collection stops

When using the Microsoft SQL Server database, if you assign the sysadmin role to a Report Engine database user, you might encounter data collection failures.

To resolve this issue, remove the sysadmin role from the Report Engine database user and restart the Report Engine service.

Java NullPointer Exception with DB Authentication Failure

When using the Microsoft SQL Server database, if the database login password is not set according to the password complexity policy, Report Engine might throw the Java NullPointer exception with DB authentication failure. 

To avoid this issue, perform the following on SQL server: 

  1. Navigate to Start > Control Panel > Administrative Tools > Local Security Policy.
  2. In the Local Security Policy console, expand Account Policies and select Password Policy.
  3. Ensure that the Password must meet complexity requirements option is Enabled.

Database troubleshooting queries

  • To check data collection, run the following query which will display date, time, number of data points collected for that hour, number of distinct devices, and number of distinct attributes from the previous day:

    select reuniv.rpt_to_char(reuniv.rpt_trunc(TIMERECORDED,'HH24'),'yyyy-mm-dd hh24:mi:ss'),count(*), count(distinct MOTYPEID), count(distinct ATTRIBUTEID) from stats_data where timerecorded> reuniv.rpt_trunc(reuniv.rpt_sysdate()-1,'DD') group by reuniv.rpt_trunc(TIMERECORDED,'HH24') order by reuniv.rpt_trunc(TIMERECORDED,'HH24');
  • If you don't receive the daily_data and hourly_data in the database, check whether SQL Server Agent service or Oracle Scheduled job is running according to the database you are using.

  • The following query displays data from Hourly data table. If the number of devices and attributes are less than the above query, then it shows that the hourly summarization job is not working.

    select reuniv.rpt_to_char(TIMERECORDED,'dd-mm-yy'),count(*), count(distinct MOTYPEID), count(distinct ATTRIBUTEID) from daily_data where timerecorded> reuniv.rpt_trunc(reuniv.rpt_sysdate()-10,'DD') group by TIMERECORDED order by TIMERECORDED;

  • The following query lists all the attributes from which the Report Engine is collecting data. If you do not see attributes for which you want to collect data, then add them to TrueSight Infrastructure Management Server under external report attribute set.

    select reuniv.rpt_concat(reuniv.rpt_concat(ATTRIBUTESETNAME,','), ATTRIBUTENAME) from externalreportattributeset order by ATTRIBUTESETNAME, ATTRIBUTENAME;
  • To be able to get data in the reports in Oracle connection, perform the following steps:
    1. Login to the Report Engine Database using the reuniv DB user credentials.
    2. Execute following command where redbuser is the Report EngineDatabase user
      exec create_synonym_reuniv(‘redbuser’);
  • To get the errors related to Oracle jobs, run the get_re_log_table_message.sql script available at %BMC_PROACTIVENET_REPORTENGINE_HOME%\ReportEngine\schema\developer\oracle.

  • To get the error details related to the SQL Server job, run the following query and export the output in SQL Server Studio:

    select err_time ,err_msg from re_log with (nolock) order by id

Database jobs

The following table displays the various jobs that are scheduled in the Report Engine Database. You can use following schedules in case of any issues in job scheduling.


To check the status of scheduled Oracle jobs

Use the following query to:

  • Check the status of the Oracle scheduled jobs.
  • Ensure that no job is broken.
  • Ensure that the next scheduled run shows the correct value where some jobs are scheduled for an hourly run and some jobs are scheduled for a daily run.
select to_char(next_date,'dd-mm-yyyy hh24:mi:ss')||', '||broken||', '|| what from user_jobs order by next_Date;

To check the status of SQL jobs

Perform the following steps to check the status of SQL scheduled jobs:

  1. Access the Microsoft SQL Server Management Studio.
  2. Log in to the Report Engine database with valid credentials.
  3. In Object Explorer, expand SQL Server Agent.
  4. In SQL Server Agent, expand the Jobs folder. All jobs for the Report Engine database are displayed.
  5. Right click on the Jobs folder and select View History. The Log file viewer is displayed.
  6. In the Log file viewer, ensure that all Report Engine jobs are displayed as green. Ensure that the following message is displayed.
    The job succeeded.