codemonth.dk

One project every month - making stuff better ...

The power of combining API's (1+1 > 2)

Integrating plsql with different web applications, is extending the functionality of the database beyond just data management. But it is when we combine these API integrations, that we can really see the benefit of them. In this entry I will show how integrating just two different API's, we can create an easy monitoring solution, that can rival more expensive traditional choices.

What I will do, is I will combine the Airbrake API and the Pagerduty API, to not only track errors, but also implement a monitoring solution that can alert my DBA that something is wrong. I will show how we can use those API's to setup team based alerting and reporting on errors, and hint on how you could further imporve or extend the usage of this.

So if you have followed my series, you should already have the Airbrake integration and the Pagerduty integration installed somewhere in your demo database. If not, please look here for airbrake installation and setup and go here for pagerduty installation and setup.

So what do we want to do with this? Airbrake, we can use to report all sorts of errors into one integrated dashboard. Pagerduty, we can use to report incidents and alert people, based on services and schedules etc. So let us say that we have a database with 2 different applications running:

  • One App
  • Two App

And that each application, is completely indenpendent from one another, so they each have their own DBA and their own operations times where they need monitoring. So we want to enable the following:

  • Each application reports errors into Airbrake, with a way to easily filter errors from only one application at a time.
  • Each application can make new releases independently of each other without impacting Airbrake for the other application.
  • At the time we report an error, we need to be able to trigger the correct service on pagerduty, so we do not contact other people then those needed.
  • We want to be able to filter different errors for each application seperately, and choose how we want to alert on these.
To solve this, we will create a user, where this functionality will live.

We need to make sure that everyone has access to the airbrake api, but on the other hand, we don't want to allow everyone to access the pagerduty api. So we will grant access to both these packages to our new user, and then we will wrap these into a package that can be used by anyone. So let us start out by creating the new user:


SQL> create user err_monitor_usr identified by err_monitor_usr
  2  default tablespace users
  3  temporary tablespace temp
  4  quota 100M on users;

User created.

and make sure that we have the necessarry privileges


SQL> grant create session to err_monitor_usr;

Grant succeeded.

SQL> grant create public synonym to err_monitor_usr;

Grant succeeded.

SQL> grant create table to err_monitor_usr;

Grant succeeded.

SQL> grant create procedure to err_monitor_usr;

Grant succeeded.

We also need to make sure thah the user has access to the two packages needed for this demonstration:


SQL> connect pagerduty/pagerduty
Connected.
SQL> grant execute on pagerduty to err_monitor_usr;

Grant succeeded.

SQL> grant execute on pagerduty_events to err_monitor_usr;

Grant succeeded.

SQL> connect airbrake/airbrake
Connected.
SQL> grant execute on airbrake to err_monitor_usr;

Grant succeeded.

SQL> grant execute on airbrake_notification to err_monitor_usr;

Grant succeeded.

Now we have the basis ready for creating our little extended application funcitonality.

Let us connect as the err_monitor_usr


SQL> connect err_monitor_usr/err_monitor_usr
Connected.

First we need to create a table for the settings for the different apps and for the api. So both applications needs to log errors with a different environment setting, and both applications need to raise against different services on pagerduty. We will create the table as a prototype solution, but if we wanted to use this in production, we might want to split it into a bit more tables, and make the data structure a bit more flexible.


create table app_tracking_env (
	app_name					varchar2(40)
	, transport_protocol		varchar2(10)
	, wallet_location			varchar2(1024)
	, wallet_password			varchar2(1024)
	, airbrake_host				varchar2(1024)
	, airbrake_host_port		varchar2(10)
	, airbrake_api_name			varchar2(20)
	, airbrake_api_version		varchar2(20)
	, airbrake_project_id		varchar2(1024)
	, airbrake_project_key		varchar2(1024)
	, pagerduty_host			varchar2(1024)
	, pagerduty_host_port		varchar2(10)
	, pagerduty_api_name		varchar2(20)
	, pagerduty_api_version		varchar2(20)
	, pagerduty_service_key		varchar2(1024)
);

Insert the necesarry data, into the app tracking table. To do that we need to create a row for each application that we have


insert into app_tracking_env (
	app_name				
	, transport_protocol	
	, wallet_location		
	, wallet_password		
	, airbrake_host			
	, airbrake_host_port	
	, airbrake_api_name		
	, airbrake_api_version	
	, airbrake_project_id	
	, airbrake_project_key	
	, pagerduty_host		
	, pagerduty_host_port	
	, pagerduty_api_name	
	, pagerduty_api_version	
	, pagerduty_service_key	
) values (
	'One App'
	, 'https'
	, 'file:/home/oracle/wallet'
	, 'WalletPasswd123'
	, 'airbrake.io'
	, '443'
	, 'api'
	, 'v3'
	, '[oneapp_project_id]'
	, '[oneapp_project_key]'
	, 'pagerduty.com'
	, '443'
	, 'api'
	, 'v1'
	, '[oneapp_service_key]'
);

insert into app_tracking_env (
	app_name				
	, transport_protocol	
	, wallet_location		
	, wallet_password		
	, airbrake_host			
	, airbrake_host_port	
	, airbrake_api_name		
	, airbrake_api_version	
	, airbrake_project_id	
	, airbrake_project_key	
	, pagerduty_host		
	, pagerduty_host_port	
	, pagerduty_api_name	
	, pagerduty_api_version	
	, pagerduty_service_key	
) values (
	'Two App'
	, 'https'
	, 'file:/home/oracle/wallet'
	, 'WalletPasswd123'
	, 'airbrake.io'
	, '443'
	, 'api'
	, 'v3'
	, '[twoapp_project_id]'
	, '[twoapp_project_key]'
	, 'pagerduty.com'
	, '443'
	, 'api'
	, 'v1'
	, '[twoapp_service_key]'
);

Now we have the environment settings for the two different apps, so now we can create the procedure that each of them should call when they want to register an error, and raise an incident.


create or replace procedure raise_app_error(
	app_name			varchar2
	, error_number		number
	, error_text		varchar2
)

as

	app_env_r			app_tracking_env%rowtype;
	pragma				autonomous_transaction;

begin

	-- Get the environment settings
	select *
	into app_env_r
	from app_tracking_env ate
	where upper(ate.app_name) = upper(app_name);

	-- First set airbrake env
	airbrake.session_setup(
		transport_protocol => app_env_r.transport_protocol
		, airbrake_host => app_env_r.airbrake_host
		, airbrake_host_port => app_env_r.airbrake_host_port
		, airbrake_api_name => app_env_r.airbrake_api_name
		, airbrake_api_version => app_env_r.airbrake_api_version
		, wallet_location => app_env_r.wallet_location
		, wallet_password => app_env_r.wallet_password
		, airbrake_project_id => app_env_r.airbrake_project_id
		, airbrake_project_key => app_env_r.airbrake_project_key
	);

	-- Then set the pagerduty required env
	pagerduty.session_setup(
		transport_protocol => app_env_r.transport_protocol
		, pagerduty_host => app_env_r.pagerduty_host
		, pagerduty_host_port => app_env_r.pagerduty_host_port
		, pagerduty_api_name => app_env_r.pagerduty_api_name
		, pagerduty_api_version => app_env_r.pagerduty_api_version
		, wallet_location => app_env_r.wallet_location
		, wallet_password => app_env_r.wallet_password
		, pagerduty_project_key => app_env_r.pagerduty_service_key
	);

	-- Now register the error with airbrake
	airbrake_notification.error_notification(
		error_number => error_number
		, error_text => error_text
	);

	-- Then raise the event with pagerduty, to notify the correct people
	pagerduty_event.trigger_event (
		description => app_name || ' - ' || error_text
		, service_key => app_env_r.pagerduty_service_key
	);

end raise_app_error;
/

REM Grant execute to the app users
grant execute on raise_app_error to one_app_user;
grant execute on raise_app_error to two_app_user;

As you can see, the procedure is autonomous, so it will not interfere with normal execution of the application.

So with this small procedure and table, we combine two powerfull API's, and create a functionality that is better than the two alone. In the next couple of series, I will show how combining different API's can increase the productivity and usage of each of them.

Tagged in : UTL_HTTP