Email Alerts for GCP Events

In this article we will see how we can setup a framework to send email alerts in event of a failed query in BigQuery. However the framework can be extended for any of the events that needs to be monitored within Google Cloud Platform.

The high level flow and the corresponding Google Cloud Platform components will be as below:

Flow of Events

1. User fires an incorrect query within the BigQuery.
2. The corresponding event appears in Cloud Logging. In Cloud Logging, a sink is created for the failure events criteria and the sink destination is set as PubSub Topic.
3. Cloud Functions are created and the trigger is specified as PubSub Topic. The cloud function receives the BigQuery failure event data and sends out email as per required configuration.

Let’s first identify the event that we receive when a query fails within BigQuery. We will fire the below query which has an issue in the syntax and observe the corresponding failure event received in Cloud Logging.

SELECT 'Sourabh

On executing the above query, we receive the below error:

BigQuery Error

Now lets goto Cloud Logging and observe this event. We can apply the corresponding filter criteria to get our relevant events.

Cloud Logging Events

We have applied the filter criteria as below because in our case we want to monitor only the failed BigQuery events:

resource.type="bigquery_resource"
protoPayload.methodName="jobservice.jobcompleted"

Now let’s setup the complete flow. Within Cloud Logging, click on “CREATE SINK” at the top. Provide “Sink Name” and “Sink Description” appropriately.

Next, under Sink Destination select Sink Source as “Cloud Pub/Sub Topic”. Now in Select a Cloud Pub/Sub Topic, either create a new Topic or select an existing topic to pass on our events.

Next, under Choose logs to include in sink , copy paste the above filter criteria that we used to filter out log events.

Click Next and click on “CREATE SINK” button.

You will observe that a new log router gets created.

Now we have the sink created so that whenever any error occurs in execution of BigQuery query, an event would be published to the PubSub topic. Now we need to invoke a cloud function that will take the event payload and will send the email with appropriate content to the targeted audience.

Let’s goto Cloud Function and create a new function. Click on “CREATE FUNCTION”. Provide the appropriate function name and region name. Next, select the trigger type as “Cloud PubSub” and select the appropriate topic that we created in above steps which will get the Cloud Logging events data.

Click Save and Click on Next.

Let the runtime be NodeJS. Copy the below code in index.js file. Ensure that you replace the username ,password, from and to content appropriately. Also ensure that the exported function name and the entry point function name are same otherwise it will result into deployment errors.

Naming Conventions should be same.

exports.bq_alerts = (event, context) => {
const nodemailer = require('nodemailer');
const transporter = nodemailer.createTransport({
service: 'gmail',
auth: {
user: 'username@gmail.com',
pass: 'Password',
},
});
let buff = Buffer.from(event.data, 'base64');
let msg = JSON.parse(buff.toString('utf-8'));
err_msg = msg.protoPayload.status.message;
user = msg.protoPayload.authenticationInfo.principalEmail;
projectid = msg.resource.labels.project_id;
query = msg.protoPayload.serviceData.jobCompletedEvent.job.jobConfiguration.query.query;
transporter.sendMail({
from: '"FirstName LastName" <from@gmail.com>', // sender address
to: "to@gmail.com", // list of receivers
subject: "Error on BigQuery for Project " + projectid + " by user " + user + "", // Subject line
text: "The error message is : " + err_msg + " for query " + query, // plain text body
html: "The error message is : <b>" + err_msg + "</b> for query <b>" + query + ".</b>", // html body
}).then(info => {
console.log({info});
}).catch(console.error);
};

Copy the below code in package.json file

{
"name": "bq_alerts",
"version": "0.0.1",
"dependencies": {
"@google-cloud/pubsub": "^0.18.0",
"nodemailer": "^6.6.2",
"nodejs-base64": "^2.0.0"
}
}

Go ahead and deploy the code.

NOTE : We have used gmail SMTP server in this example but you can leverage any SMTP service for sending emails. For gmail SMTP , please enable the less secure apps by visiting this link and enable authentication by visiting this link.