Azure DevOps and ServiceNow reporting in Confluence

This template demonstrates how you can embed custom reporting into Confluence using HTML (iframe macro) or using ScriptRunner for Confluence custom macro feature. Get started to learn more.

What is ScriptRunner Connect?

ScriptRunner Connect is an AI assisted code-first (JavaScript/TypeScript) integration platform (iPaaS) for building complex integrations and automations.

Can I try it out for free?

Yes. ScriptRunner Connect comes with a forever free tier.

Can I customize the integration logic?

Absolutely. The main value proposition of ScriptRunner Connect is that you'll get full access to the code that is powering the integration, which means you can make any changes to the the integration logic yourself.

Can I change the integration to communicate with additional apps?

Yes. Since ScriptRunner Connect specializes in enabling complex integrations, you can easily change the integration logic to connect to as many additional apps as you need, no limitations.

What if I don't feel comfortable making changes to the code?

First you can try out our AI assistant which can help you understand what the code does, and also help you make changes to the code. Alternatively you can hire our professionals to make the changes you need or build new integrations from scratch.

Do I have to host it myself?

No. ScriptRunner Connect is a fully managed SaaS (Software-as-a-Service) product.

What about security?

ScriptRunner Connect is ISO 27001 and SOC 2 certified. Learn more about our security.

📋 Overview

This template provides you with 3 examples of how to create reports that can be used in Confluence to show data from external tools.

🖊️ Setup & Usage

• Configure API Connections for Azure DevOps or ServiceNow or both, depending on which reports you would like to try out.
• When trying out the Azure DevOps, also configure the necessary parameters.

AzureDevOpsReportHTML & ServiceNowReportHTML

These scripts demonstrate how to create reports in HTML, that can be used with Confluence's Iframe macro (you could also just use the URL, or embed the URL in any tool that supports Iframes).

AzureDevOpsReportMacro & ServiceNowReportMacro

These scripts demonstrate how to return Confluence Storage Format. These are designed to be used with ScriptRunner for Confluence's Custom Macro Feature. To set up the Custom Macro, go to the Macro section in your ScriptRunner Administration menu. In the top right, select the "Create Custom Macro" button. The basic script to use is:

def connectResponse = get("{UNIQUE_EVENT_LISTENER_URL}")
        .asObject(Map) 

return connectResponse.body.value

You can also add parameters to the macros for your users to use, which can be passed to ScriptRunner Connect to filter the data showed For example, ServiceNowReportMacro takes the a String as a parameter, to filter Statuses. You can add parameters to the custom macro at the bottom of the page.

To pass parameters the code looks like:

def paramString = parameters.Status.toString()
def encodedParam = java.net.URLEncoder.encode(paramString, "UTF-8")

def connectResponse = get("{UNIQUE_EVENT_LISTENER_URL}?statusParam=${encodedParam}")
        .asObject(Map) 


return connectResponse.body.value

ServiceNowReportMacro2

This script is designed to be used with the Custom Macro feature, however is more expansive to demonstrate the power & flexability of this reporting method. With this macro you can pass any ServiceNow Table, and any ServiceNow query, and you can report on any data from ServiceNow.

import AzureDevOps from './api/azure/devops';
import { HttpEventRequest, HttpEventResponse, buildHTMLResponse } from '@sr-connect/generic-app/events/http';

/**
 * This function retrieves all work items for configured organization and project from AzureDevOps and then sends back formatted HTML containing a table of Title, Priority and State,
 * which then can be embedded into Confluence.
 */
export default async function (event: HttpEventRequest, context: Context<EV>): Promise<HttpEventResponse> {
    const organization = context.environment.vars.AzureDevOps.ORGANIZATION;
    const project = context.environment.vars.AzureDevOps.PROJECT;

    if (!organization) {
        throw Error('Azure DevOps organization is not configured in parameters');
    }
    if (!project) {
        throw Error('Azure DevOps project is not configured in parameters');
    }

    const processedWorkItems: WorkItems[] = [];

    const workItemsResponse = await AzureDevOps.fetch(`https://dev.azure.com/${encodeURIComponent(organization)}/${project}/_apis/wit/wiql?api-version=7.2-preview.2`, {
        method: 'POST',
        headers: {
            "Content-Type": "application/json",
        },
        body: JSON.stringify({
            query: `SELECT [System.Id], [System.Title], [System.State] FROM WorkItems`
        })
    });

    if (!workItemsResponse.ok) {
        throw Error(`Unexpected response while getting work items: ${workItemsResponse.status} - ${await workItemsResponse.text()}`);
    }

    const workItems = await workItemsResponse.json();

    for (const item of workItems) {
        const workItemResponse = await AzureDevOps.fetch(`https://dev.azure.com/${encodeURIComponent(organization)}/${project}/_apis/wit/workitems/${item.id}?api-version=7.1`);

        if (!workItemResponse.ok) {
            throw Error(`Unespected response while getting work item (${item.id}): ${workItemResponse.status} - ${await workItemResponse.text()}`);
        }

        const workItem = await workItemResponse.json();

        processedWorkItems.push({
            title: workItem.fields?.["System.Title"],
            state: workItem.fields?.["System.State"],
            priority: workItem.fields?.["Microsoft.VSTS.Common.Priority"]
        });
    }

    let html = `<!DOCTYPE html>
                    <html lang="en">
                    <head>
                        <meta charset="UTF-8">
                        <meta name="viewport" content="width=device-width, initial-scale=1.0">
                        <title>Simple 3-Column Table</title>
                        <style>
                            table {
                                border-collapse: collapse;
                                width: 100%;
                                max-width: 800px;
                                margin: 20px auto;
                            }
                            th, td {
                                border: 1px solid #ddd;
                                padding: 8px;
                                text-align: left;
                            }
                            th {
                                background-color: #f2f2f2;
                            }
                        </style>
                    </head>
                    <body>
                        <table>
                            <thead>
                                <tr>
                                    <th>Title</th>
                                    <th>Priority</th>
                                    <th>State</th>
                                </tr>
                            </thead>
                            <tbody>`;

    for (const item of processedWorkItems) {
        html += `<tr>
                    <td><p>${item.title}</p></td>
                    <td><p>${item.priority}</p></td>
                    <td><p>${item.state}</p></td>
                 </tr>`;
    }
    
    html += `</tbody>
             </table>
             </body>
             </html>`;

    return buildHTMLResponse(html);
}

interface WorkItems {
    readonly title: string,
    readonly state: string,
    readonly priority: string
}

import AzureDevOps from './api/azure/devops';
import { HttpEventRequest, HttpEventResponse, buildJSONResponse } from '@sr-connect/generic-app/events/http';

/**
 * This function retrieves all work items for configured organization and project from AzureDevOps and then sends back formatted Confluence Macro containing a table of Title, Priority and State,
 * which then can be embedded into Confluence.
 */
export default async function (event: HttpEventRequest, context: Context<EV>): Promise<HttpEventResponse> {
    try {
        const organization = context.environment.vars.AzureDevOps.ORGANIZATION;
        const project = context.environment.vars.AzureDevOps.PROJECT;

        if (!organization) {
            throw Error('Azure DevOps organization is not configured in parameters');
        }
        if (!project) {
            throw Error('Azure DevOps project is not configured in parameters');
        }

        const processedWorkItems: WorkItems[] = [];

        const workItemsResponse = await AzureDevOps.fetch(`https://dev.azure.com/${encodeURIComponent(organization)}/${project}/_apis/wit/wiql?api-version=7.2-preview.2`, {
            method: 'POST',
            headers: {
                "Content-Type": "application/json",
            },
            body: JSON.stringify({
                query: `SELECT [System.Id], [System.Title], [System.State] FROM WorkItems`
            })
        });

        if (!workItemsResponse.ok) {
            throw Error(`Unexpected response while getting work items: ${workItemsResponse.status} - ${await workItemsResponse.text()}`);
        }

        const workItems = await workItemsResponse.json();

        for (const item of workItems) {
            const workItemResponse = await AzureDevOps.fetch(`https://dev.azure.com/${encodeURIComponent(organization)}/${project}/_apis/wit/workitems/${item.id}?api-version=7.1`);

            if (!workItemResponse.ok) {
                throw Error(`Unespected response while getting work item (${item.id}): ${workItemResponse.status} - ${await workItemResponse.text()}`);
            }

            const workItem = await workItemResponse.json();

            processedWorkItems.push({
                title: workItem.fields?.["System.Title"],
                state: workItem.fields?.["System.State"],
                priority: workItem.fields?.["Microsoft.VSTS.Common.Priority"]
            });
        }

        let macro = `<table data-table-width="760" data-layout="default" ac:local-id="4c56dca3-e5ce-4896-b4ba-0ee814c911fd">
                        <tbody>
                            <tr>
                                <th><p><strong>Title</strong></p></th>
                                <th><p><strong>Priority</strong></p></th>
                                <th><p><strong>State</strong></p></th>
                            </tr>`;

        for (const item of processedWorkItems) {
            macro += `<tr>
                    <td><p>${item.title}</p></td>
                    <td><p>${item.priority}</p></td>
                    <td><p>${item.state}</p></td>
                 </tr>`;
        }

        macro += `</tbody></table>`;

        return buildJSONResponse({
            value: macro
        });
    } catch (e) {
        console.log('Error while rendering report', e);

        return buildJSONResponse({
            value: `<ac:structured-macro ac:name="warning" ac:schema-version="1" ac:macro-id="7a310248-2194-4623-be8e-2285a857e99b"><ac:rich-text-body>
                    <p>Something went wrong while rendeing the report.</p></ac:rich-text-body></ac:structured-macro>`
        });
    }
}

interface WorkItems {
    readonly title: string,
    readonly state: string,
    readonly priority: string
}

import ServiceNow from './api/servicenow';
import { HttpEventRequest, HttpEventResponse, buildHTMLResponse } from '@sr-connect/generic-app/events/http';

/**
 * This function retrieves CI items from ServiceNow and then sends back formatted HTML containing a table of Name, Description and Number,
 * which then can be embedded into Confluence.
 **/
export default async function (event: HttpEventRequest, context: Context<EV>): Promise<HttpEventResponse> {
    const statusParam = event.queryStringParams.statusParam;

    const items = await getRecordsFromServiceNow(statusParam);

    let html = `<!DOCTYPE html>
                    <html lang="en">
                    <head>
                        <meta charset="UTF-8">
                        <meta name="viewport" content="width=device-width, initial-scale=1.0">
                        <title>Simple 3-Column Table</title>
                        <style>
                            table {
                                border-collapse: collapse;
                                width: 100%;
                                max-width: 800px;
                                margin: 20px auto;
                            }
                            th, td {
                                border: 1px solid #ddd;
                                padding: 8px;
                                text-align: left;
                            }
                            th {
                                background-color: #f2f2f2;
                            }
                        </style>
                    </head>
                    <body>
                        <table>
                            <thead>
                                <tr>
                                    <th>Name</th>
                                    <th>Description</th>
                                    <th>Number</th>
                                </tr>
                            </thead>
                            <tbody>`;

    for (const item of items.result) {
        html += `<tr>
                    <td><p>${item.name}</p></td>
                    <td><p>${item.short_description}</p></td>
                    <td><p>${item.number}</p></td>
                 </tr>`;
    }

    html += `</tbody>
             </table>
             </body>
             </html>`;

    return buildHTMLResponse(html);
}

async function getRecordsFromServiceNow(statusParam?: string) {
    if (statusParam) {
        const choices = await ServiceNow.Table.getRecords({
            tableName: 'sys_choice',
            sysparm_query: `element=install_status^label=${statusParam}`
        });

        const choiceId = choices.result[0]?.value;

        if (!choiceId) {
            throw Error('Choice was not found');
        }

        return await ServiceNow.Table.getRecords({
            tableName: 'cmdb_ci_business_app',
            sysparm_query: `install_status=${choiceId}`
        });
    } else {
        return await ServiceNow.Table.getRecords({
            tableName: 'cmdb_ci_business_app'
        })
    }
}

import ServiceNow from './api/servicenow';
import { HttpEventRequest, HttpEventResponse, buildJSONResponse } from '@sr-connect/generic-app/events/http';


/**
 * This function retrieves CI items from ServiceNow and then sends back formatted Confluence Macro containing a table of Name, Description and Number,
 * which then can be embedded into Confluence.
 **/
export default async function (event: HttpEventRequest, context: Context<EV>): Promise<HttpEventResponse> {
    try {
        const statusParam = event.queryStringParams.statusParam;

        const items = await getRecordsFromServiceNow(statusParam);

        let macro = `<table data-table-width="760" data-layout="default" ac:local-id="4c56dca3-e5ce-4896-b4ba-0ee814c911fd">
                     <tbody>
                        <tr>
                            <th><p><strong>Name</strong></p></th>
                            <th><p><strong>Description</strong></p></th>
                            <th><p><strong>Number</strong></p></th>
                        </tr>`;

        for (const item of items.result) {
            macro += `    <tr>
                            <td><p>${item.name}</p></td>
                            <td><p>${item.short_description}</p></td>
                            <td><p>${item.number}</p></td>
                        </tr>`;
        }

        macro += `</tbody></table>`;

        return buildJSONResponse({
            value: macro
        });
    } catch (e) {
        console.log('Error while rendering report', e);

        return buildJSONResponse({
            value: `<ac:structured-macro ac:name="warning" ac:schema-version="1" ac:macro-id="7a310248-2194-4623-be8e-2285a857e99b"><ac:rich-text-body>
                    <p>Something went wrong while rendeing the report.</p></ac:rich-text-body></ac:structured-macro>`
        });
    }
}

async function getRecordsFromServiceNow(statusParam?: string) {
    if (statusParam) {
        const choices = await ServiceNow.Table.getRecords({
            tableName: 'sys_choice',
            sysparm_query: `element=install_status^label=${statusParam}`
        });

        const choiceId = choices.result[0]?.value;

        if (!choiceId) {
            throw Error('Choice was not found');
        }

        return await ServiceNow.Table.getRecords({
            tableName: 'cmdb_ci_business_app',
            sysparm_query: `install_status=${choiceId}`
        });
    } else {
        return await ServiceNow.Table.getRecords({
            tableName: 'cmdb_ci_business_app'
        })
    }
}

import ServiceNow from './api/servicenow';
import { HttpEventRequest, HttpEventResponse, buildHTMLResponse, buildJSONResponse } from '@sr-connect/generic-app/events/http';

/**
 * This function retrieves table items from ServiceNow and then sends back formatted Confluence Macro containing a table of ID, Created Date and Created By,
 * which then can be embedded into Confluence.
 **/
export default async function (event: HttpEventRequest, context: Context<EV>): Promise<HttpEventResponse> {
    const tableParam = event.queryStringParams.tableParam;
    const queryParam = event.queryStringParams.queryParam;

    const tables = await ServiceNow.Table.getRecords<null>({
        tableName: 'sys_db_object',
        sysparm_query: `label=${tableParam}`,
        errorStrategy: {
            handleAnyError: () => null
        }
    });

    if (!tables || tables.result.length === 0) {
        return buildJSONResponse({
            value: `<ac:structured-macro ac:name="warning" ac:schema-version="1" ac:macro-id="7a310248-2194-4623-be8e-2285a857e99b"><ac:rich-text-body>
                    <p>ServiceNow General Report, invalid table passed!</p></ac:rich-text-body></ac:structured-macro>`
        });
    }

    const tableName = tables.result[0].name;

    const records = await ServiceNow.Table.getRecords<null>({
        tableName: tableName, 
        sysparm_query: queryParam, 
        errorStrategy:{
            handleAnyError: () => null
        }
    });

    if (!records) {
        return buildJSONResponse({
            value: `<ac:structured-macro ac:name="warning" ac:schema-version="1" ac:macro-id="7a310248-2194-4623-be8e-2285a857e99b"><ac:rich-text-body>
                    <p>ServiceNow General Report, invalid query passed!</p></ac:rich-text-body></ac:structured-macro>`
        });
    }

    let macro = `<table data-table-width="760" data-layout="default" ac:local-id="4c56dca3-e5ce-4896-b4ba-0ee814c911fd">
                    <tbody>
                        <tr>
                            <th><p><strong>ID</strong></p></th>
                            <th><p><strong>Created Date</strong></p></th>
                            <th><p><strong>Created By</strong></p></th>
                        </tr>`;

    for (const item of records.result) {
        macro +=     `<tr>
                        <td><p>${item.sys_id}</p></td>
                        <td><p>${item.sys_created_on}</p></td>
                        <td><p>${item.sys_created_by}</p></td>
                    </tr>`
    }

    macro += `</tbody></table>`;

    return buildJSONResponse({
        value: macro
    });
}