Report Manager DB Schema

CA Spectrum Report Manager uses a MySQL database named "reporting" to store data. Another database "srmdbapi" provides views that are based on the reporting DB to query for data. This section provides an overview of all the database tables and views in 'reporting' and 'srmdbapi' databases so that the users can run their own queries and can use reporting tools of their own.
casp1032
Introduction
CA Spectrum Report Manager uses a MySQL database named "reporting" to store data. Another database "srmdbapi" provides views that are based on the reporting DB to query for data. This section provides an overview of all the database tables and views in 'reporting' and 'srmdbapi' databases so that the users can run their own queries and can use reporting tools of their own.
At startup, CA Spectrum Report Manager retrieves the data from the primary Archive Manager for each SpectroSERVER through OneClick and stores the data in the SRM databases.
Connecting to the database
Users can connect to the SRM database(which is on the OneClick server machine) using a ‘terminal window’ or a dos prompt (windows -> start -> Run -> cmd).
Use the below instructions to connect to the MySQL SRM DB instance running on the OneClick host
  1. change to your $SPECROOT/mysql/bin directory
  2. do a “mysql -uroot –proot” to connect to the mysql DB system
  3. do a “use reporting;” (don't forget the ; at the end!)
  4. Then user can run any of the select statements to fetch and see the data (don't forget the ; at the end of the statement)
  5. Once done, run “quit” to disconnect.
Users are also encouraged to use utilities like ‘MySQL Workbench’ to connect to the SRM database and run the required queries.
If user wants to connect to the SRM database remotely from another box through utilities then they have to run a grant command so that access to MySQL DB is allowed from that host.
  1. Go in to the $SPECROOT/mysql/bin directory Eg: /usr/Spectrum/mysql/bin
  2. Login to mysql with the command “mysql -uroot –proot”
  3. Once we logged in, use the command “grant all privileges on *.* to 'root'@'<ip address of the machine from where we are accessing the DB>' identified by 'root';”
  4. Example:- grant all privileges on *.* to 'root'@'10.132.15.160' identified by 'root'; (now user can access the reporting DB from MySQL workbench running on host 10.132.15.160)
 Once the access is granted then users can connect to the database and can run queries using the workbench .
This section contains information about 'reporting' database Tables and Views.
 to see how to write sample queries using reporting DB tables. 
This section contains information about 'srmdbapi' database Views.