codemonth.dk

One project every month - making stuff better ...

Paging dr. DBA

So I have been building an API in pl/sql for Airbrake, a web based service, where you can track your run-time errors. The only problem is, that you don't always sit at your computer with the Airbrake dashboard open. So what to do when there is a serious error and you want to get alerted, or there is something that goes wrong in a business process or a batch job?

Luckily there are several options available. One of them is pagerduty, and they have an api you can connect to as well. So I have created a small package that can connect to your pagerduty account, and trigger/acknowledge/resolve incidents. There are of course many more options in the api, such as creating Teams, Escalation policies and on-duty schedules, and I will be adding those options over the next 2 weeeks.

Just like the airbrake integration, there are a couple of things we need to do, to get the pagerduty API to work. The first thing of course, is to create an account on the website. They only offer a 14 day trial, but it's a full featured trial, so you have all the options available to test out. Once that has been created, you need to add the certificate chain to your oracle wallet. If you have not already created a wallet, you can read the instructions here.

Please note, that if you are using 12c, you should only install the root certificate and the SSL certificate and NOT the final client certificate.

So before we can install the initial code, we need to install a user that we can test this under and we need to create a network access that allows this user to connect to the pagerduty website.

So download the following scripts from the github repository:

  • user.sql
  • pagerduty_acl.sql
  • pljson/*
  • pagerduty.spec.sql
  • pagerduty.body.sql
  • pagerduty_events.spec.sql
  • pagerduty_events.body.sql
Login as sys and run the first two files as sys (user.sql and pagerduty_acl.sql). This creates a user called pagerduty with the password pagerduty. Connect as this new user, and then run the following scripts:


@pljson/install
@pagerduty.spec.sql
@pagerduty.body.sql
@pagerduty_events.spec.sql
@pagerduty_events.body.sql

After we have installed the scripts successfully, we also need to login to pagerduty, and create a service that we can register events against. To create a service, go to the website https://.pagerduty.com/services and create a service. Once the service is created (remember to set your cell number, and create an sms alert in the esclation policy if you want to test sms alerts as well), we can try out the packages, and raise an event from the database:

Log into sqlplus as pagerduty and first run the setup procedure, to setup the environment:


begin
  pagerduty.session_setup(
    transport_protocol => 'https'
    , pagerduty_host => '.pagerduty.com'
    , pagerduty_host_port => '443'
    , pagerduty_api_name => 'api'
    , pagerduty_api_version => 'v1'
    , wallet_location => 'file:/home/oracle/wallet'
    , wallet_password => 'WalletPasswd123'
    , pagerduty_project_id => '000000'
    , pagerduty_project_key => '[your_service_key]'
    , pagerduty_api_key => 'xxxxxxxxxxx'
  );
end;
/

At first we are not going to use the project id and the general api key, only the pagerduty_project_key, so we can put in whatever we want at first.

After we have setup the correct session parameters, we can raise our first event:


begin
	pagerduty_events.trigger_event (
		'This is the first test'
		, '[your_service_key]'
		, 'test_incident'
	);
end;
/
If you go to your incident screen in pagerduty, you should be able to see your very first incident. Next let us acknowledge the event. We used an event key "test_incident". With that event key, we can choose to acknowledge the event, and we can also close the event. So let us first see how you can use the api to acknowledge the event:

begin
	pagerduty_events.acknowledge_event (
		'test_incident'
		, '[your_service_key]'
		, 'We are acknowledging the event with this api call.'
	);
end;
/
With the event acknowledged, let us see how we can finally resolve the event:

begin
	pagerduty_events.resolve_event (
		'test_incident'
		, '[your_service_key]'
		, 'I fixed this.'
	);
end;
/

This was just a quick overview of what this can do. In the next entry I will show how the installation process can be a lot more smooth, and I will show how you can combine the airbrake api and the pagerduty api.

Tagged in : UTL_HTTP