ProTop has the ability to monitor and alert on up to eight custom, user defined, numeric application metrics (Key Performance Indicators or KPIs) and trend the data on the dashboard web server.
Once set up KPI data is displayed in ProTop Real-Time:
and in the Advanced Dashboard of the graphical portal:
Use this feature to trend application metrics (number of boxes ships, number of widgets made, etc) alongside database performance metrics.
Alerting on user-defined metrics / custom KPIs
Receiving alerts on user-defined metrics is much like alerting for all other metrics, with only a metric name sleight of hand. In usermon.p below you assign your numeric values to output parameters named ufld1, ufld2, ... ufld8. These are passed along and assigned to numeric variables the alerting system recognizes, namely usrField1, usrField2 etc.
So, to alert on the value you assigned to the output parameter ufld2 (in this case the number of orders placed today), you set up an alert on usrField2 like this:
usrField2 num > 200 "" "300" "orders &2 &3, currently &1" alert
For more information on configuring alerts, see the Alert Configuration article.
Using lib/usermon.p
The key to this feature is PROTOP/lib/usermon.p. If it exists, the dashboard data collector dc/dashboard.p will execute it. A simple-to-follow sample is provided in lib/usermonx.p.
To use these user-defined metrics, follow these steps:
-
Locate the source version of lib/usermonx.p n in the $PROTOP/protop-[version]-src.zip that is delivered when you install or update ProTop. Copy the source usermonx.p to $PROTOP/lib/usermon.p, and edit it as required; see below. You can make your code specific to one database or common to all your monitored databases.
REPLICATION NOTE: If the monitored database is a replication source or target, use the "dictdb" prefix when accessing application tables; otherwise, you will get an error indicating that the table exists in both databases. In Replication Monitoring, the agent connects to the source and target databases with the same schema.
-
Assign these values to one of the 8 user-defined fields (usrField1 to usrField8)
-
Assign a label (usrLabel1 to userLabel8) to this field. The name of the label will be used in the dashboard as the legend for your metric.
Note: If you are in a multi-database environment, you must perform a couple extra steps since the databases might have different schemas and usermon.p cannot be compiled:
a. Create a specific program for every database or group of databases with compatible schema, and
b. Add specific code in usermon.p to run these different programs. -
If needed (no 4GL dev license present where you are running ProTop), compile and place the .r in $PROTOP/lib alongside usermon.p.
NOTE: $PROTOP/lib/usermon.p must also be present to trigger ProTop to run the .r !
The procedure usermon.p contains two sections:
1. Initialization section
This section at the top of the procedure is used to register procedures that will respond when the dashboard data collector calls for user-defined metrics. You can register a default internal procedure to process these calls, or register specific sub-programs per database, using ldbname(1) to get the name of the current database.
2. Subscription section (“Usermon” internal procedure)
This internal procedure must be defined in all procedures that need to collect and publish metrics. You can simply keep the one included in usermon.p, or include one in all of your database-specific procedures. It contains the logic to calculate your KPI and store them in the user-defined fields.
Example 1 - multi-database system, metrics unrelated to database tables
We have many databases, one for each region (East, Europe, West, Asia), and all have their own EDI process that records a log in a folder called /edi/logs. The log files are named after the database name (Ex.: east.log). We want to report the size of these logs for each database.
usermon.p:
--- Initialization section ---
session:add-super-procedure( this-procedure ).
subscribe to "usermon" anywhere run-procedure "userMon".
return.
--- Subscription section ---
procedure userMon:
define output parameter ufld1 as decimal no-undo.
define output parameter ulbl1 as character no-undo.
...
define output parameter ufld8 as decimal no-undo.
define output parameter ulbl8 as character no-undo.
/* get length of EDI log file for each database - we do not need to access to db tables */
/* We then put all the logic in this single procedure */
file-info:FILE-NAME = "/edi/logs/" + ldbname(1) + “.log”.
ASSIGN
ufld1 = FILE-INFO:FILE-SIZE /* field value */
ulbl1 = “EDI Log Size”. /* field label used in the dashboard */
return.
end.
Example 2 - multi-database with metrics related to database tables
We have the same databases per region, but this time, we want to collect the number of open orders in each database. North American databases have a different name for the order table.
usermon.p:
--- Initialization section ---
CASE ldbname(1):
WHEN “East” OR WHEN “West” THEN RUN VALUE(“lib/america.p”).
WHEN “Europe” THEN RUN VALUE(“lib/europe.p”).
WHEN “Asia” THEN RUN VALUE(“lib/asia.p”).
END CASE.
return.
--- Subscription section (unnecessary if all processing is done in special procedures) ---
america.p:
--- Initialization section ---
session:add-super-procedure( this-procedure ).
subscribe to "usermon" anywhere run-procedure "userMon".
return.
--- Subscription section ---
procedure userMon:
define output parameter ufld1 as decimal no-undo.
define output parameter ulbl1 as character no-undo.
...
define output parameter ufld8 as decimal no-undo.
define output parameter ulbl8 as character no-undo.
define variable cntOpenOrders as integer no-undo.
for each dictdb.order where status=”open” no-lock:
Assign cntOpenOrders = cntOpenOrders + 1.
End.
ASSIGN
ufld1 = cntOpenOrders /* field value */
ulbl1 = “Open Orders”. /* field label used in the dashboard */
return.
End.
europe.p:
--- Initialization section ---
session:add-super-procedure( this-procedure ).
subscribe to "usermon" anywhere run-procedure "userMon".
return.
--- Subscription section ---
procedure userMon:
define output parameter ufld1 as decimal no-undo.
define output parameter ulbl1 as character no-undo.
...
define output parameter ufld8 as decimal no-undo.
define output parameter ulbl8 as character no-undo.
define variable cntOpenOrders as integer no-undo.
for each dictdb.commandes where status=”open” no-lock: /* table name is different for this database */
Assign cntOpenOrders = cntOpenOrders + 1.
End.
ASSIGN
ufld1 = cntOpenOrders /* field value */
ulbl1 = “Open Orders”. /* field label used in the dashboard */
return.
end.