Create a custom OEM uptime report for business hours only
There was a need to create a custom Information Publisher Report in Oracle Enterprise Manager 13c to provide uptime metrics. These uptime metrics were to be calculated weekly, from Monday to Friday, only for the hours of 8am to 5pm, and for specific targets. Any target status of Up, System Error, Agent Down, Blackout, Status Pending would be considered up. Only a target status of Down would be classified as down.
Yes, I do understand that Information Publisher is deprecated in lieu of BI Publisher, but it's just so easy to use.
Creating the Report
Here is the output of that report:
Unfortunately, creating this was more complicated than it looked.
The query used in the report is the following:
As you can see from the query, I am simply selecting from a function:
SELECT
TO_CHAR(oem_week, 'YYYY-MM-DD') "Week Of",
SUBSTR(oem_target,INSTR(oem_target,'/',-1)+1,LENGTH(oem_target)) "Target",
TO_CHAR(TRUNC(oem_uptime, 2), 'FM999.00') || '%' "Uptime"
FROM
TABLE("AHMED.ABOULNAGA".get_uptime_weekly())
ORDER BY
TO_CHAR(oem_week,'YYYYMMDD') DESC,
oem_target
This function simply returns exactly the output needed, allowing the query to be as simple as possible. Here is the output of the query when you run it manually:
Since I'm running the report as my own user, not SYSMAN, I have to provide a few grants. Run these as SYSTEM or SYSMAN on the database:
GRANT select ON sysman.mgmt$availability_history TO "AHMED.ABOULNAGA";
GRANT select ON sysman.MGMT$TARGET TO "AHMED.ABOULNAGA";
GRANT create type TO "AHMED.ABOULNAGA";Now, logging in as my database user AHMED.ABOULNAGA, I create a couple of types which are used by my function, which includes the exact output returned by the function:
CREATE TYPE custom_uptime_weekly_type AS OBJECT (
oem_target VARCHAR2(100),
oem_week DATE,
oem_uptime VARCHAR(10)
);
CREATE TYPE custom_uptime_weekly_table AS TABLE OF custom_uptime_weekly_type;
Now here is the dreaded query.
You can customize the list of targets, hours of operation, and start and end dates (query is hardcoded to start from the first day of the calendar year up to today's date).
CREATE OR REPLACE FUNCTION ati_ahmed_get_uptime_weekly RETURN ati_ahmed_uptime_weekly_table
PIPELINED
IS
vcurrday NUMBER;
vstarttime DATE;
vendtime DATE;
vuptime VARCHAR2(10);
TYPE vtargetarray_type IS
VARRAY(50) OF VARCHAR2(100);
vtarget vtargetarray_type;
vrecord ati_ahmed_uptime_weekly_type := ati_ahmed_uptime_weekly_type(NULL, NULL, NULL);
vuptime_weekly NUMBER;
vday_count INT;
BEGIN
-- ----------------------------------------
-- This is the list of targets
-- ----------------------------------------
vtarget := vtargetarray_type(
'/prod_soa_domain/soa_domain/osb_server1',
'/prod_soa_domain/soa_domain/osb_server2',
'/prod_soa_domain/soa_domain/soa_server1',
'/prod_soa_domain/soa_domain/soa_server2',
'/prod_soa_domain/soa_domain/ess_server1',
'/prod_soa_domain/soa_domain/ess_server2',
'/prod_soa_domain/soa_domain/bam_server1',
'/prod_soa_domain/soa_domain/bam_server2'
);
vcurrday := to_number(to_char(sysdate, 'DDD'));
-- ----------------------------------------
-- Start from the first day of the year
-- ----------------------------------------
FOR j IN 1..vtarget.count LOOP
vuptime_weekly := 0;
vday_count := 0;
FOR i IN 1..vcurrday LOOP IF to_char(sysdate - vcurrday + i, 'DY') NOT IN (
'SAT',
'SUN'
) THEN
-- ----------------------------------------
-- This is the hours of operation
-- ----------------------------------------
vstarttime := to_date(to_char(sysdate - vcurrday + i, 'DD-MON-YYYY')
|| ' 08:00:00', 'DD-MON-YYYY HH24:MI:SS');
vendtime := to_date(to_char(sysdate - vcurrday + i, 'DD-MON-YYYY')
|| ' 17:00:00', 'DD-MON-YYYY HH24:MI:SS');
SELECT
100 - SUM(a.value) "Uptime"
INTO vuptime
FROM
(
( SELECT
'Down' availability_status,
0 value,
2 order_col
FROM
dual
UNION ALL
SELECT
'Up' availability_status,
0 value,
1 order_col_txt_id
FROM
dual
UNION ALL
SELECT
'System Error' availability_status,
0 value,
5 order_col_txt_id
FROM
dual
UNION ALL
SELECT
'Agent Down' availability_status,
0 value,
4 order_col_txt_id
FROM
dual
UNION ALL
SELECT
'Blackout' availability_status,
0 value,
3 order_col_txt_id
FROM
dual
UNION ALL
SELECT
'Status Pending' availability_status,
0 value,
6 order_col_txt_id
FROM
dual
)
UNION ALL
SELECT
decode(lower(availability_status),
'target down', 'Down',
'target up', 'Up',
'metric error', 'System Error',
'agent down', 'Agent Down',
'unreachable', 'Unreachable',
'blackout', 'Blackout',
'pending/unknown', 'Status Pending')
availability_status_txt_id
,
round(SUM(least(nvl(end_timestamp, sysdate), vendtime) -
greatest(start_timestamp, vstarttime)) * 100 / vcurrday
, 2) AS value_txt_id,
decode(lower(availability_status),
'target down', 2,
'target up', 1,
'metric error', 5,
'agent down', 4,
'unreachable', 7,
'blackout', 3,
'pending/unknown', 6) order_col_txt_id
FROM
sysman.mgmt$availability_history b,
sysman.mgmt$target t
WHERE
b.target_name = vtarget(j)
AND b.target_guid = t.target_guid
AND lower(availability_status) != 'unreachable'
AND b.start_timestamp <= vendtime and ( b.end_timestamp>= vstarttime
OR b.end_timestamp IS NULL )
GROUP BY
lower(availability_status),
decode(lower(availability_status),
'target down', 2,
'target up', 1,
'metric error', 5,
'agent down', 4,
'unreachable', 7,
'blackout', 3,
'pending/unknown', 6)
) a
WHERE
-- ----------------------------------------
-- Only report target status 'Down' as down
-- ----------------------------------------
lower(availability_status) = 'down'
GROUP BY
a.availability_status,
a.order_col
ORDER BY
a.order_col;
IF to_char(sysdate - vcurrday + i, 'Dy') = 'Mon' THEN
vuptime_weekly := 0;
vday_count := 0;
END IF;
vuptime_weekly := vuptime_weekly + vuptime;
vday_count := vday_count + 1;
IF to_char(sysdate - vcurrday + i, 'Dy') = 'Fri' THEN
vrecord.oem_target := vtarget(j);
vrecord.oem_week := vstarttime - 4;
vrecord.oem_uptime := vuptime_weekly / vday_count;
PIPE ROW ( vrecord );
END IF;
END IF;
END LOOP;
END LOOP;
END;Now a final grant is needed:
GRANT execute ON "AHMED.ABOULNAGA".custom_get_uptime_weekly TO mgmt_view;
Keep in mind that you can run this report for practically any target type.
This was perhaps the messiest query I created for an OEM Information Publisher report, but only because the specific requirements became too complicated to return in a SQL query alone coupled with some limitations in Information Publisher.