[njams] getting a daily success/error rate per deployed service

17 September 2015

Often people ask for various reports which can be generated out of the nJAMS/BWPM data pool.

To start with a simple one, here the select for getting a success/error/warning rate of all deployed engines over time aggregated on daily basis.

select trunc(jobstart) datum, deployment_name,
    count(*) execution_count,
    sum(case when LASTEVENTSTATUS='1' then 1 else 0 end) success_count,
    sum(case when LASTEVENTSTATUS='2' then 1 else 0 end) warning_count,
    sum(case when LASTEVENTSTATUS='3' then 1 else 0 end) error_count,
    sum(case when LASTEVENTSTATUS='0' then 1 else 0 end) running_count
from (
  SELECT m.jobstart,o.deployment_name,m.LASTEVENTSTATUS
  FROM njams_t_monitor_main m
  INNER JOIN njams_t_domain_objects o ON m.DOMAIN_OBJECT_ID=o.OBJ_ID
) t
group by trunc(jobstart),deployment_name
order by trunc(jobstart),deployment_name

result:

datum	service	executions	success	warning	error
15-07-15	C1_Services	733	733	0	0
15-07-15	C2_Services	146	146	0	0
15-07-15	DWH_Services	236	236	0	0
15-07-15	ESB_Engine	504	384	120	0
15-07-15	SAP_Services	313	245	49	19

all the stuff shown here works with version 3.1, no clue about other versions of the software