ServiceNow can fl exibly respond to SQL Monitor's webhook messages, allowing SQL Monitor's alerts to be handled as part of an organization's unifi ed incident management process. In this example, we'll build a Scripted REST API resource in ServiceNow to receive webhooks describing SQL Monitor alerts, using the information provided to create and update ServiceNow incidents. The key steps involved are: 1) Create a Scripted REST API endpoint in ServiceNow to receive webhook messages 2) Defi ne a script in ServiceNow to manage incidents in response to the webhooks 3) Confi gure SQL Monitor to send webhook messages to this new endpoint
.
Prerequisites for ServiceNow Integration with Redgate SQL Monitor
To successfully integrate Redgate SQL Monitor with ServiceNow, the following requirements must be met:
1) Redgate SQL Monitor: Version 12.1.37 or newer, along with an administrator account for the necessary confi guration.
2) ServiceNow Environment: A user with the web_service_admin role is required.
3) Network Connectivity: The SQL Monitor Base Monitor service(s) must have network access to the ServiceNow environment.
1) Scripted REST API Resource Setup
The fi rst step in ServiceNow is to create the Scripted REST API resource:
1. Search for "Scripted REST APIs" in ServiceNow.
2. Select New.
Make a note of the "Base API path" fi eld shown - this will be used later to form the endpoint url which SQL Monitor will send webhooks to. Within this new service, in the Resources section click New to create a new record.
Name the record "Alerts" or similar, change the HTTP method to "POST"
2) Script to handle the webhook:
(function process(/*RESTAPIRequest*/ request, /*RESTAPIResponse*/ response)
{
var event = request.body.data;
// Log incoming payload for troubleshooting gs.info("Redgate SQL Monitor webhook log: " + JSON.stringify(event));
var responseBody = {};
// Process only SQL Monitor Alert Notifications
if (event.messageType == 'AlertNotification')
{
// Generate unique Correlation ID
var sqmCorrelationId = 'SqlMonitor.' + event.baseMonitorGuid + '.' + event.id;
// Create Incident if (event.statusChange == 'Raised')
{
var alertTarget = event.monitoredEntity.name;
var inc = new GlideRecord('incident');
inc.initialize();
inc.short_description = 'SQL Monitor: ' + event.name + ' on ' + alertTarget;
var description = 'Raised against: ' + alertTarget + '\r\n\r\n'; description += event.description + "\r\n\r\n"; description += 'For more details view this alert in SQL Monitor at ' + event.detailsUrl; inc.description = description;
inc.correlation_id = sqmCorrelationId;
inc.state = 1;
// Map SQL Monitor Severity to Impact/Urgency
if (event.severity == 'High') { inc.impact = 1; inc.urgency = 2; }
if (event.severity == 'Medium') {
inc.impact = 2;
inc.urgency = 2;
}
if (event.severity == 'Low')
{
inc.impact = 3;
inc.urgency = 2;
}
inc.insert();
responseBody.message = 'Incident created';
response.setBody(responseBody);
}
else {
// Locate existing Incident
var existingIncident = new GlideRecord('incident');
existingIncident.addQuery('correlation_id', sqmCorrelationId);
existingIncident.query();
if (existingIncident.hasNext())
{ existingIncident.next();
// Resolve Incident
if (event.statusChange == 'Ended')
{
existingIncident.state = 6;
existingIncident.close_code = 3;
existingIncident.close_notes = 'Closed by SQL Monitor after alert ended.'; existingIncident.update();
responseBody.message = 'Incident closed.';
response.setBody(responseBody);
}
// Update Severity
if (event.statusChange == 'Escalated' || event.statusChange == 'DeEscalated')
{
if (event.severity == 'High')
{
existingIncident.impact = 1; existingIncident.urgency = 2;
}
if (event.severity == 'Medium')
{ existingIncident.impact = 2;
existingIncident.urgency = 2;
}
if (event.severity == 'Low')
{ existingIncident.impact = 3;
existingIncident.urgency = 2;
}
existingIncident.update();
responseBody.message = 'Incident severity updated.';
response.setBody(responseBody);
}
}
else
{
response.setError( new sn_ws_err.NotFoundError( 'Unable to find existing incident using correlation_id ' + sqmCorrelationId ) );
}
}
}
else
{
response.setError( new sn_ws_err.BadRequestError( 'MessageType not recognised.' ) );
}
})
(request, response);
Testing from ServiceNow - Click on “Explore Rest Api” related link (Optional)
Add following in Request Body Raw section:
1. TEST CASE: Create Incident (statusChange = Raised)
{
"messageType": "AlertNotifi cation",
"statusChange": "Raised",
"baseMonitorGuid": "BM-12345",
"id": "1001",
"name": "High CPU Usage",
"description": "CPU usage exceeded 90% for more than 5 minutes",
"severity": "High",
"monitoredEntity": {
"name": "SQLSERVER01"
}
}
2. TEST CASE: Close Incident (statusChange = Ended)
{
"messageType": "AlertNotifi cation",
"statusChange": "Ended",
"baseMonitorGuid": "BM-12345",
"id": "1001",
"name": "High CPU Usage",
"description": "CPU back to normal",
"severity": "High",
"monitoredEntity": {
"name": "SQLSERVER01"
}
}
3. TEST CASE: Escalate Severity
{
"messageType": "AlertNotifi cation",
"statusChange": "Escalated",
"baseMonitorGuid": "BM-12345",
"id": "1001",
"name": "High CPU Usage",
"description": "CPU now critical",
"severity": "High",
"monitoredEntity": {
"name": "SQLSERVER01"
}
}
