Recording User Defined Metrics in Oracle Enterprise Manager OEM
Did you know that OEM has a built in interface for executing simple ad-hoc queries and recording the results?
Here is how I set up a recurring query to find the number of user logins every 15 minutes and record the data.
1) Navigate to a database instance in OEM
2) Select the Related Links -> User Defined Metrics link from the bottom of the Database Instance Home tab.
3) Select the Create button to create a new User Defined Metric
a. Enter the Metric Name
b. Enter a SQL statement for the metric you would like to record (1 row, 1 column, use NVL (,0) for nulls or it may freak out)
SELECT COUNT(*) FROM V$SESSION WHERE USERNAME='INTERESTING_USER';
c. Enter Thresholds if you would like alerting
d. Enter scheduling information
e. Click Test
f. Click OK
4) Wait for some collections to occur. Keep in mind the scheduling information you originally entered and be patient, OEM likes to take its own sweet time.
5) View the data
a. Navigate to the database instance in OEM
b. Select the User Defined Metrics link from the Related Links section at the bottom of the page. You will be shown a list of User Defined Metrics for this database:
c. Select the name of the User Defined Metric. You will be shown