ServiceNow REST Integration - Configure ServiceNow

The following describes how to configure ServiceNow to perform a REST call against EJBCA that will gather metadata for each certificate and create a Generic Database Query Report containing all certificate metadata ( Username, Revocation Status, Expiration Date, Subject DN, and Issuer DN ).

Set up Mutual Authentication

The following lists the steps required to set up mutual authentication to establish trust.

To set up mutual authentication:

  1. Log into Service Now Management Console.

  2. In the Filter bar, type Certificates.

  3. Select Certificates under System Definitions.

Next, perform the steps in the following sections to set up trust stores.

PKCS12 Authentication Trust Store

To set up the PKCS12 Authentication Trust Store:

  1. On the System Definition > Certificates page, select New.

  2. Complete the following fields:

    • Name: Enter EJBCA-RA.

    • Type: Select PKCS12 Key Store from the drop-down.

    • Key store password: Enter the PKCS12 password.

    • Active: Selected.

    • Short description: EJBCA Registration Authority.

    • Select the Attachment icon under the search bar, select Choose file, and select the P12 file.

  3. Click Update.

Certificate Authority Trust Store (EJBCA RA Issuing CA)

To set up the Certificate Authority Trust Store (EJBCA RA Issuing CA):

  1. On the System Definition > Certificates page, select New.

  2. Complete the following fields:

    • Name: Enter EJBCA-RA-IssuingCA.

    • Format: PEM.

    • Type: Trust Cert Store.

    • Active: Selected.

    • Copy and paste the base64 certificate of the CA that issued the RA cert into the PEM Certificate field.

  3. Click Update.

Certificate Authority Trust Store (EJBCA TLS Cert Issuing CA)

These steps are only required if different from RA Issuing CA.

To set up the Certificate Authority Trust Store (EJBCA TLS Cert Issuing CA):

  1. On the System Definition > Certificates page, select New.

  2. Complete the following fields:

    • Name: Enter EJBCA-TLS-IssuingCA.

    • Format: PEM.

    • Type: Trust Cert Store.

    • Active: Selected.

    • Copy and paste the base64 certificate of the CA that issued the TLS cert into the PEM Certificate field.

  3. Click Update.

Configure REST Status Message

To configure and test the REST status message:

  1. In the Filter bar, type REST Message.

  2. Select REST Message under System Web Services.

  3. Click New.

  4. Complete the following fields:

    1. Name: EJBCA

    2. Endpoint: https://ejbcafqdn/ejbca/ejbca-rest-api

    3. Use mutual authentication: Selected

  5. Next to the Mutual authentication profile, click the Search icon.

  6. Click New next to HTTP Methods.

  7. Enter ejbcatls in the Protocol field.

  8. Select the Keystore field and select EJBCA-RA.

  9. Click Submit.

  10. Select EJBCA from the REST Message list.

  11. Click New.

  12. Complete the following fields:

  13. Click Submit.

  14. Select the Status Check REST Message.

  15. Click Test:

    • The screen will load with the response starting with {"status":"OK","version":"1.0","revision"}.

    • If a Status "OK" is not returned, repeat the previous steps until an OK is returned.

Status "OK" indicates the mutual authentication, Registration Authority access rules, and REST protocol initial configurations are correct.

Do not proceed to the remaining procedures until the previous OK status is returned from the REST test message.

Build Certificate Query Table

The following sections provide steps required to run scheduled jobs that generate reports on expiring certificates using the Certificate Search Rest Call:

  1. Create Certificate Search REST Call

  2. Create Query Table

  3. Create Staging Table

  4. Create Transform Mapping

  5. Create Script Includes

  6. Create Daily Calendar

  7. Create Scheduled Job

  8. Generate a Generic Report from the Database Query

Create Certificate Search REST Call

To create a certificate search REST call:

  1. In the Filter bar, type REST Message.

  2. Select REST Message under System Web Services.

  3. Click New.

  4. Complete the following fields:

    1. Name: EJBCA

    2. Endpoint: https://ejbcafqdn/ejbca/ejbca-rest-api/v2

    3. Use mutual authentication: Selected

  5. Next to the Mutual authentication profile, click the Search icon.

  6. Click New next to HTTP Methods.

  7. Enter ejbcatls in the Protocol field.

  8. Select the Keystore field and select EJBCA-RA.

  9. Click Submit.

  10. Select EJBCA from the REST Message list.

  11. Click New in the HTTP Methods list.

  12. Complete the following fields:

  13. Click Submit.

  14. Select the Certificate Search V2 HTTP Method.

  15. In the Variables Substitution section at the bottom, click New.

  16. Click the HTTP Request tab.

  17. Under HTTP Headers double-click on "Insert a new row...", enter Accept in the text box, and click the green checkmark.

  18. Double-click in the Value column on the same row, enter application/json in the text box, and click the green checkmark.

  19. On the next row enter Content-Type and application/json.

  20. Click Update.

  21. Select the Certificate Search V2 HTTP Method.

  22. Under HTTP Query Parameters enter the text in the box below in the Content box and click Test.

  23. The HTTP Status should be 200 and the Response box should have a success body from the REST call.

  24. Click Delete.

This REST call will return the most recent 10000 certificates that have an ACTIVE status. Increase the page_size to capture more certificates

Certificate Search
{
"pagination": {
"page_size": 10000,
"current_page": 1
},
"sort": {
"property": "UPDATE_TIME",
"operation": "ASC"
},
 
"criteria": [
{
"property": "CERT_STATUS",
"value": "ACTIVE",
"operation": "EQUAL"
}
]
}

Create Query Table

To create an EJBCA Certificate Query Table:

  1. In the Filter bar, type tables and select Tables under System Definition.

  2. Click New.

  3. Enter EJBCA Certificate Query Table in the Label field.

  4. Copy the Name that is generated based on the Label and paste it into a text editor. This name will be needed later.

  5. Click Submit.

  6. Change the Search field to Label and enter EJBCA Certificate Query Table in the text box.

  7. Click EJBCA Certificate Query Table.

  8. Click New in the Columns section.

  9. Enter string in the Type field and select String (Full UTF-8).

  10. Enter Revocation Date as the Column label.

  11. Click Submit.

  12. Repeat the previous steps for the remaining columns using the list below and click Update when finished:

    • Revocation Reason

    • Expiration Date

    • Issuer DN

    • Serial Number

    • Status

    • Subject Alternative Name(s)

    • Subject Key Identifier

    • Type

    • Last Updated

    • User

  13. Click Update.

Create Staging Table

The Certificate Search REST call needs to parse the returned JSON data from the response body and place it in a staging table to later be transformed. The transformation will occur and the data will be placed in the table used for reporting

  1. Create a CSV file with the headers below in Row 1 and save the file as data_set_table. The headers are case-sensitive and must match what is in the response body to be loaded.

    • revocationDate

    • revocationReason

    • expireDate

    • issuerDN

    • serialNumber

    • status

    • subjectAltName

    • subjectDN

    • subjectKeyId

    • type

    • updateTime

    • username

  2. In the Filter bar, type load data and select it

  3. Enter EJBCA Certificate Query in the Label field

  4. Copy the Name that is generated based on the Label and paste it into a text editor. This will be needed later.

  5. Select Choose File and select the data_set_table.

  6. Click Submit.

  7. A Progress page will display a state of Complete.

  8. Click the Create a transform map hyperlink.

Create Transform Mapping

To create an EJBCA query transform mapping:

  1. In the Filter bar, type transform maps and select it.

  2. Click New.

  3. Enter EJBCA Query Transform Mapping.

  4. Click the Source table drop-down, enter ejbca and select the staging table from the drop-down.

  5. Click Target table.

  6. Click Submit.

  7. Change the Search field to Label and enter EJBCA Query Transform Mapping in the text box.

  8. Click EJBCA Query Transform Mapping.

  9. Click the Mapping Assist link under Related Links.

  10. In the left column, select all the headers in the data_set_table file, and click Add.

  11. In the right column, click each item, click Add, and move the item up or down until the item is in line with the data_set_table header.

  12. Click Save.

  13. In the Field Maps, change the Coalesce value for serial_number to true.

  14. Click Update and click OK on the prompt to index the table due to the Coalesce feature.

Create Script Includes

Perform the steps in the sections below to create scripts:

Create getJSONObjectsViaRest

  1. In the Filter bar, type transform maps and select it.

  2. Click New.

  3. Enter getJSONObjectsViaRest in the Name field.

  4. Change Accessible from to All application scopes.

  5. Paste the code block below into the Script box and click Submit.

getJSONObjectsViaRest
function getJSONObjectsViaRest(endpoint, objects, api, request) {
var pagedR = new sn_ws.RESTMessageV2(api, request);
if (endpoint !== null) {
pagedR.setEndpoint(endpoint);
}
var pagedResponse = pagedR.execute();
var pagedResponseBody = pagedResponse.getBody();
var pagedhttpStatus = pagedResponse.getStatusCode();
var pagedObj = JSON.parse(pagedResponseBody);
objects = objects.concat(pagedObj.certificates);
 
return objects;
}

Create insertGlideRecord

  1. Click New.

  2. Enter insertglideRecord in the Name field.

  3. Change Accessible from to All application scopes.

  4. Paste the code block below into the Script box and click Submit.

insertGlideRecord
function insertGlideRecord(GlideReference, field, value) {
if (GlideReference.isValidField(field)) {
GlideReference.setValue(field, value);
}
}

Create loadRestInTable

  1. Click New.

  2. Enter loadRestInTable in the Name field.

  3. Change Accessible from to All application scopes.

  4. Paste the code block below into the Script box and click Submit.

loadRestInTable
function loadRestInTable(rest_message, http_method, target_table) {
try {
var objects = getJSONObjectsViaRest(null, [], rest_message, http_method);
 
for (var i=0; i<objects.length; i++ ) {
parseJSONObject(objects[i], target_table);
}
} catch (ex) {
var message = ex.message;
gs.info('JSON IMPORT ERROR: ' + message);
}
}

Create parseJSONObject

  1. Click New.

  2. Enter parseJSONObject in the Name field.

  3. Change Accessible from to All application scopes.

  4. Paste the code block below into the Script box and click Submit.

parseJSONObject
function parseJSONObject(object, target_table) {
var JSONImport = new GlideRecord(target_table);
JSONImport.initialize();
for (var key in object) {
if (object.hasOwnProperty(key)) {
var field = key.toLowerCase();
var actualField = "";
var value = "";
if (typeof(object[key]) === "object") {
if (!Array.isArray(object[key])) {
for (var inner_key in object[key]) {
actualField = ('u_' + field + "_" + inner_key).toLowerCase();
value = object[key][inner_key];
insertGlideRecord(JSONImport, actualField, value);
}
}
} else {
value = object[key].toString();
actualField = 'u_' + field;
}
insertGlideRecord(JSONImport, actualField, value);
}
}
JSONImport.insert();
}

Create Daily Calendar

To create a daily calendar:

  1. In the Filter bar, type calendar and select it.

  2. Click New.

  3. Enter Every day in the Name field.

  4. Click New in Days list.

  5. Select Monday from Day of the week drop-down and click Submit.

  6. Repeat step 5 for the remaining 6 days.

Create Scheduled Job

To create a scheduled job:

  1. In the Filter bar, type scheduled jobs and select Scheduled Jobs under System Definition.

  2. Click New.

  3. Select Automatically run a script of your choosing.

  4. Enter EJBCA - Daily Certificate Query in the Name field.

  5. Select Daily from the Run drop-down list.

  6. Enter 02 00 00 in the Time field.

  7. Enter the following in the Run this box and click Submit.

    • Update the query name to match the name in ServiceNow:

loadRestInTable
loadRestInTable('EJBCA', 'Certificate Search V2','x_138548_demo_ejbca_certificate_query');

1st var = REST Message, 2nd var = HTTP Method configured in the REST Message, 3rd var = Staging table.

Make sure to modify these values if different names are used for these three variables being called.

Generate a Generic Report from the Database Query

To generate a generic report from the database query:

  1. In the Filter bar, type reports and select Create New under Reports.

  2. Enter EJBCA - Generic Database Query.

  3. Click the Table drop-down and enter the table name generated when the EJBCA Certificate Query Table was created in step Create Query Table.

  4. Click Next.

  5. Select List under Other.

  6. Click Next.

  7. Click Choose Columns and select the desired fields.

  8. Click Save in the top right.

Test Query and Report

As the final step, you can now test the query and report.

A certificate needs to be generated in EJBCA before completing the test procedure.

To test the query and view the created report containing all certificate metadata:

  1. Navigate back to the EJBCA - Daily Certificate Query in System Definition > Scheduled Jobs.

  2. Click Execute Now.

  3. Return to the EJBCA - Generic Database Query and view the response data.