Database troubleshooting

Data collection stops

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,'yyyy-mm-dd hh24:mi:ss'),count(*), count(distinct MOTYPEID), count(distinct ATTRIBUTEID) from hourly_data where timerecorded> reuniv.rpt_trunc(reuniv.rpt_sysdate()-1,'DD') group by TIMERECORDED order by TIMERECORDED;

• The following query displays data from Daily data table. If the number of devices and attributes are less than the above query, then it shows that the daily 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 BMC 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:
  • Login to the RE Database using the reuniv DB user credentials.

  • Execute following command where redbuser is the RE Database 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

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

• To check Oracle scheduled jobs, ensure that no job is broken, and the next scheduled run is showing correct value where some jobs are scheduled for hourly run and some jobs are for once-a-day run by running the following query: 
  
  select to_char(next_date,'dd-mm-yyyy hh24:mi:ss')||', '||broken||', '|| what from user_jobs order by next_Date;
• To check SQL Server scheduled jobs, perform the following:

1. 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 Log file viewer, ensure that all report engine jobs are displayed as green and message as The job succeeded.