Redshift to ServiceNow Integration

Overview

This Lambda function serves as a bridge between Amazon Redshift and ServiceNow. It receives data from Redshift and creates incidents in ServiceNow through their REST API.

Prerequisites

• ServiceNow instance with API access
• Environment variables configured in Lambda:
  • SERVICENOW_INSTANCE
  • SERVICENOW_USERNAME
  • SERVICENOW_PASSWORD
• Required Python packages:
  • requests
  • json

Code Breakdown

1. Import Required Libraries

import json
import os
import requests
import base64

• json: For handling JSON data
• os: For accessing environment variables
• requests: For making HTTP requests to ServiceNow
• base64: For handling encoded data (if needed)

2. Lambda Handler Setup

def lambda_handler(event, context):
    servicenow_instance = os.environ['SERVICENOW_INSTANCE']
    username = os.environ['SERVICENOW_USERNAME']
    password = os.environ['SERVICENOW_PASSWORD']
    
    base_url = f"https://{servicenow_instance}.service-now.com/api/now/v1"
    auth = (username, password)

• Function receives event data from Redshift
• Retrieves ServiceNow credentials from environment variables
• Constructs the base URL for ServiceNow API
• Sets up basic authentication

3. Processing Redshift Data

    redshift_data = json.loads(event['data'])

• Parses JSON data received from Redshift

4. Creating ServiceNow Incidents

    for record in redshift_data:
        payload = {
            "short_description": f"Incident from Redshift: {record.get('id', 'Unknown')}",
            "description": json.dumps(record),
            "category": "Redshift Alert",
            "urgency": "2"
        }

• Iterates through each record from Redshift
• Creates a payload for ServiceNow incident
• Customizable fields:
  • short_description: Brief description of the incident
  • description: Full record details
  • category: Incident category
  • urgency: Priority level (2 in this case)

5. API Call to ServiceNow

        response = requests.post(
            f"{base_url}/table/incident",
            auth=auth,
            headers={"Content-Type": "application/json"},
            json=payload
        )

• Makes POST request to ServiceNow's incident creation endpoint
• Uses basic authentication
• Sends JSON payload

6. Error Handling and Response

        if response.status_code != 201:
            return {
                'statusCode': response.status_code,
                'body': f"Failed to create ServiceNow incident: {response.text}"
            }
    
    return {
        'statusCode': 200,
        'body': f"Successfully created {len(redshift_data)} ServiceNow incidents"
    }

• Checks if incident creation was successful (status code 201)
• Returns error details if creation fails
• Returns success message with count of created incidents

Complete Lambda Function Code.

import json
import os
import time
import boto3
import requests

def lambda_handler(event, context):
    # Get ServiceNow credentials from environment variables
    servicenow_instance = os.environ['SERVICENOW_INSTANCE']
    servicenow_username = os.environ['SERVICENOW_USERNAME']
    servicenow_password = os.environ['SERVICENOW_PASSWORD']
    
    # Get Redshift Serverless connection details from environment variables
    workgroup_name = os.environ['REDSHIFT_SERVERLESS_WORKGROUP']
    database_name = os.environ['REDSHIFT_DATABASE']
    secret_arn = os.environ['REDSHIFT_SECRET_ARN']
    
    # Initialize Redshift Data API client
    redshift_client = boto3.client('redshift-data')
    
    # Execute SQL query to fetch data from Redshift Serverless
    sql_query = "SELECT incident_id, short_description, description, category, urgency, impact FROM servicenow.incidents LIMIT 10"
    
    try:
        # Execute the SQL statement using Redshift Serverless workgroup
        response = redshift_client.execute_statement(
            WorkgroupName=workgroup_name,  # Use WorkgroupName instead of ClusterIdentifier
            Database=database_name,
            SecretArn=secret_arn,
            Sql=sql_query,
            StatementName='fetch_incident_data'
        )
        
        statement_id = response['Id']
        
        # Wait for query execution to complete
        while True:
            status_response = redshift_client.describe_statement(Id=statement_id)
            status = status_response['Status']
            
            if status == 'FINISHED':
                break
            elif status in ['FAILED', 'ABORTED']:
                raise Exception(f"Query failed with status: {status}. Error: {status_response.get('Error', 'No error details')}")
            
            time.sleep(1)
        
        # Retrieve query results
        result = redshift_client.get_statement_result(Id=statement_id)
        
        # Process the results
        records = []
        columns = [column['name'] for column in result['ColumnMetadata']]
        
        for row in result['Records']:
            record = {}
            for i, column in enumerate(columns):
                # Handle different data types
                if 'stringValue' in row[i]:
                    record[column] = row[i]['stringValue']
                elif 'longValue' in row[i]:
                    record[column] = row[i]['longValue']
                elif 'doubleValue' in row[i]:
                    record[column] = row[i]['doubleValue']
                elif 'booleanValue' in row[i]:
                    record[column] = row[i]['booleanValue']
                else:
                    record[column] = None
            records.append(record)
        
        # ServiceNow API base URL
        base_url = f"https://{servicenow_instance}.service-now.com/api/now/v1"
        
        # Basic authentication for ServiceNow
        auth = (servicenow_username, servicenow_password)
        
        # Create incidents in ServiceNow for each record
        successful_incidents = 0
        failed_incidents = 0
        
        for record in records:
            # Prepare payload for ServiceNow
            payload = {
                "short_description": record.get('short_description', f"Incident from Redshift: {record.get('incident_id', 'Unknown')}"),
                "description": record.get('description', "Description not available"),
                "category": record.get('category', "Redshift Alert"),
                "urgency": record.get('urgency', "2"),
                "impact": record.get('impact', "2"),
                "caller_id": "system"
            }
            
            # Call ServiceNow API to create incident
            response = requests.post(
                f"{base_url}/table/incident",
                auth=auth,
                headers={"Content-Type": "application/json"},
                json=payload
            )
            
            if response.status_code == 201:
                successful_incidents += 1
            else:
                failed_incidents += 1
                print(f"Failed to create incident: {response.text}")
        
        return {
            'statusCode': 200,
            'body': json.dumps({
                'message': f"Successfully created {successful_incidents} ServiceNow incidents, failed to create {failed_incidents} incidents",
                'total_records': len(records)
            })
        }
    
    except Exception as e:
        print(f"Error: {str(e)}")
        return {
            'statusCode': 500,
            'body': json.dumps({
                'message': f"Error processing request: {str(e)}"
            })
        }

Security Considerations

• Credentials are stored as environment variables
• Uses HTTPS for API communication
• Implements basic authentication
• Monitor Lambda execution logs for errors