Overview:
Choice of Monitoring (Trending) database:
- Using the embedded java database requiring minimal administration: Apache Derby (installed along with BI 4.x)
- Re-using the Audit data store for storing monitoring data
Connecting to Monitoring database (Apache Derby) with SQuirrel Client
- Trending DB is installed in BI 4.x in the following location:
- To shorten the path for defining Alias URL in SQuirrel, the path ‘<drive>:\<Install path of BI 4.x>\SAP BusinessObjects Enterprise XI 4.0/Data/TrendingDB’ can be shared with the network user who will be accessing it remotely via SQuirrel client.
- The path ‘<drive>:\<Install path of BI 4.x>\SAP BusinessObjects Enterprise XI 4.0/Data/TrendingDB’ also contains DDL for table creation for other database platforms like Oracle, SQL Server, DB2 etc.
Monitoring Data Model
below:
Monitoring Data Model in Apache Derby
Description of tables in Monitoring Database
| Table Name | Description |
|---|---|
| TREND_DETAILS | The table records information about metrics, probes and managed entities |
| TREND_DATA | The table records information on the metric values, timestamp (epoch time in milliseconds) when data was collected and error message key |
| MANAGED_ENTITY_STATUS_DETAILS | This table contains information of configured thresholds (caution & danger) – subscription breaches and alerts. Subscription check timestamp (epoch time in milliseconds) is also stored |
| MANAGED_ENTITY_STATUS_METRICS | This is a lookup table for watches |
Monitoring Data Model in Audit Data Store
Data Dictionary for Monitoring Database
For analyzing data dictionary in SQuirrel client, the create table scripts can be generated along with all constraints / indexes:
Refer to the attached file ‘create_table_trendingdb_derby.sql‘ for the generated DDL.
Alternatively following queries can be used to extract the data dictionary:
select t.TABLENAME, t.TABLETYPE, s.SCHEMANAME
from SYS.SYSTABLES t, SYS.SYSSCHEMAS s
where t.schemaid = s.schemaid
and s.schemaname='APP';
----
select t.TABLENAME, c.CONSTRAINTNAME, c.TYPE, s.SCHEMANAME
from SYS.SYSCONSTRAINTS c, SYS.SYSTABLES t, SYS.SYSSCHEMAS s
where c.schemaid = s.schemaid
and c.tableid = t.tableid
and s.schemaname='APP';
---
select s.SCHEMANAME, t.TABLENAME, g.conglomeratename, g.isindex, g.isconstraint
from SYS.SYSTABLES t, SYS.SYSSCHEMAS s, SYS.SYSCONGLOMERATES g
where g.schemaid = s.schemaid
and g.tableid = t.tableid
and s.schemaname='APP'
and (g.isindex = 'true' or g.isconstraint='true')
order by t.TABLENAME;
---**Note: Default row limit in SQuirrel client is 100. This limit is configurable or the setting can be turned off altogether (no limits). The setting is present in the
SQuirrel client on the SQL tab towards top right.
A clear trend which comes up based on the output of the above queries / script:
- Only tables, indexes and constraints are present in monitoring database. No views, procedures, materialized views etc. exists
- Auto-generated sequence keys are used as Primary Keys for all the four tables
- Enforced referential integrity i.e. PK-FK relationship exists between
- TREND_DETAILS (PK) and MANAGED_ENTITY_STATUS_DETAILS (FK)
- TREND_DETAILS (PK) and TREND_DATA (FK)
- Index type is either unique or non-unique
- Timestamp is stored in BIGINT format (epoch time) in TREND_DATA and MANAGED_ENTITY_STATUS_DETAILS table
Building Monitoring Report Queries
Some common monitoring reporting scenarios are listed below:
Example scenarios:
- List of different metrics available in the BOE system:
select distinct td.METRICNAME, td.TYPE
from TREND_DETAILS td
where td.TYPE=’Metric’;
——
- List of watches
select distinct w.CUID, w.NAME, td.METRICNAME, td.TYPE
from TREND_DETAILS td, MANAGED_ENTITY_STATUS_METRICS w
where td.CUID = w.CUID;
—-
- List of watches associated with metrics
select distinct w.NAME, td.METRICNAME, td.TYPE
from TREND_DETAILS td, MANAGED_ENTITY_STATUS_METRICS w
where td.DETAILSID = w.DETAILSID
–and td.TYPE=’Metric’ –Optional filter
order by w.NAME;
—-
- Trend values of metrics for a specific watch since 09-Feb-2015
select w.NAME, td.METRICNAME, t.MESSAGEKEY, t.TIME,
{fn TIMESTAMPADD( SQL_TSI_SECOND, t.TIME/1000, timestamp(‘1970-01-01-00.00.00.000000’))} UTC ,
t.VALUE
from TREND_DETAILS td, TREND_DATA t, MANAGED_ENTITY_STATUS_METRICS w
where td.DETAILSID = t.DETAILSID
and td.DETAILSID = w.DETAILSID
and w.NAME='<Node>. InputFileRepository Watch’ —This is an example
and t.TIME >= 1423440000000; —equivalent epoch time in milliseconds for 09-Feb-2015 00:00:00 UTC
–—
**The above query converts epoch time to regular time in UTC.
Concluding Remarks
The above write-up is not an exhaustive reference on monitoring database or monitoring functionality. The readers are encouraged to validate the above contents in line with standard BI Platform admin guide. Comments are welcome to further enhance the contents of this blog post. Thanks for your time 🙂
Great piece of work..thanks alot
Helpful note
Why SAP Host Agent metrics are not saved in Monitoring Trend DB ?
As per the BI Platform Administration guide, it should be possible to integrate monitoring application with SAP Solution Manager, IBM Tivoli, Wily etc. However, it is best to use fit for purpose monitoring solution offered by SAP. If you are looking for day to day health check monitoring of BOE, then default monitoring application in CMC and trending db should solve the purpose. For detailed troubleshooting, E2E monitoring of SAP ecosystem – solman and its integration with wily metrics are best suited.
Is it possible to identify when watches are in a yellow or red state?
Based on the current system metrics, you cannot simply capture the exact time when the watch status changes.There is a polling mechanisms which periodically polls the status of your object of interest in your watch and gets the snapshot of the objects current status and updates the same in monitoring application.
Thanks
Mani
Thanks. That’s too bad — seems it would be a basic function to report on the duration that a watch was in warning or danger status.
If the watch metrics are being stored in trending database (configured in settings), then the alert messages (for caution or danger thresholds) should get stored in MANAGED_ENTITY_STATUS_DETAILS and trend data of metrics included in the watch should get stored in TREND_DATA. Timestamp should also get captured. However as Manikandan indicated, it will be based on polling mechanism and exact time may not get captured. However if throttling criteria is lesser than the polling interval, chances of capturing a alert scenario is higher in trending database. Keeping the throttling criteria too low also has its disadvantages of creating false alerts (or threshold breaches lasting for very small interval).
Thank you, Saikat.
That would tell me when alerts are fired, but I’m looking for the start and end of the period when the watch was in caution or danger status.
I have the raw readings, as you mentioned, but I don’t see any way of determining if a particular reading was within the caution or danger range.
Ultimately, I’m trying to get to a total availability metric. So I want to get the percentage of readings that were normal, caution, or danger.
We have seen the Monitoring database running on the Audit space consume the table space and fill the trending. Â Auditing has a function to limit the # of days of retention. Â IS there a way to do this in the app or code, without creating a script to do it on a periodic basis to match auditing retention?
What is the best method for truncating the audit and monitoring tables  for 12 month data retention?
(suggested script would help)Â I’m not a DBA so I have to hunt and peck these solutions. Â (we are on Oracle)
It depends on your organisational usage pattern. In general we will archive the data from Audit db and  push it an archival database with a universe created on top of it which might be used only when you really need it.
Thanks
Mani
If you’re only interested in pruning and not archiving, then the following will do it: