SAP Cloud Platform Integration – Connection count beautified

Sometimes work is like cooking: topics tend to form lumps. And the topic of “Connection count details” is one lump right now.

If you have acquired and are using a “SAP Cloud Platform Integration – PI Edition” tenant, you should know that licensing is based on connections.

You can see the number of connections in your SAP Cloud Platform cockpit, or in your SAP Enterprise Support Reporting cockpit.

You can also download it using the method outlined here: https://blogs.sap.com/2021/01/27/cloud-integration-downloading-cpi-connections-report/ 

Also, we recently published a note explaining how to use a REST API to get more details about the connections.

Using the above mentioned API you can get information such as connection count, where the connection is used, sender & receiver systems, etc.. This OData REST API can hence be used by developers to build dashboards using the language of their choice, or it can be used by power-users in Excel directly as a datasource. But you can also use Postman, a very useful tool for calling, storing and visualising API calls. Indeed, a JSON or XML file may not be very readable to you (unless you are a computer).

This is what we’ll do in this little tutorial: format the result of the API calls for the human eye!

Install and configure Postman

If not already done, please install Postman.

Then create a new GET request and enter the right URL as described in the note.

https://<CPI Tenant URL Without ITSpaces>/api/v1/IntegrationConnections?$expand=IntegrationFlows&$filter=ResolvedConnection eq true&$inlinecount=allpages

In my case, the CPI NEO URL looks something like this:

https://XXX-tmn.hci.eu1.hana.ondemand.com/api/v1/IntegrationConnections?$expand=IntegrationFlows&$filter=ResolvedConnection eq true&$inlinecount=allpages&$format=json

In your Postman request, add the authentication (in my case basic authentication) using SAP Cloud Platform credentials.

Last step: add a parameter to get the result as JSON structure, not XML. The parameter is a standard OData parameter called “$format”.

Also (thanks Jon Prow for pointing this out), set the header “invalidateCache” to “true”.

Your request should look something like this (basic authentication is set in the “Authorization” tab):

Now click on “Send”.

If everything goes well, you will get the result in a JSON format.

Although JSON is quite readable, it is not really nice, so we’ll now “beautify” this answer using the visualisation feature of Postman.

Beautify the response

As said before, Postman provides means to visualise data using handlebar templates.

So I sat down for an hour and went back in time – 20 years – when I was writing ASP webpages :-).

The code below may not be state-of-the-art anymore – but it works.

var template = `
        <p style="font-size:18px">Total number of connections = {{response.d.__count}}</p>

        <p style="font-size:18px">Connection overview</p>
        <table style="background-color: #CCCCCC;width: 90%;padding:10px">
            <tr style="background-color: #F0AB00;">
                <th style="width:40%;padding:2pt;"><p style="font-size:14px">Id</p></th>
                <th style="width:40%;padding:2pt;"><p style="font-size:14px">Sender</p></th>
                <th style="width:40%;padding:2pt;"><p style="font-size:14px">Receiver</p></th>
            </tr>
        {{#each response.d.results}}
            <tr>
                <td style="width:40%;padding:2pt;"><p style="font-size:14px">{{Id}}</p></td>
                <td style="width:40%;padding:2pt;"><p style="font-size:14px">{{SenderHost}}</p></td>
                <td style="width:40%;padding:2pt;"><p style="font-size:14px">{{ReceiverHost}}</p></td>
            </tr>
        {{/each}}
        </table>

        <p style="font-size:18px">Details</p>
        {{#each response.d.results}}
            <p style="font-size:16px">Connection <b>#{{Id}}</b> between <b>{{SenderHost}}</b> and <b>{{ReceiverHost}}</b> found in following iFlows:</b><p>
                    <table style="background-color: #CCCCCC;width: 90%;padding:10px">
                        <tr style="background-color: #F0AB00;">
                            <th style="width:40%;padding:2pt;"><p style="font-size:14px">iFlow ID (iFlow Name)</p></th>
                            <th style="width:40%;padding:2pt;"><p style="font-size:14px">Sender Host Type</p></th>
                            <th style="width:30%;padding:2pt;"><p style="font-size:14px">Receiver Host Type</p></th>
                        </tr>
                        {{#each IntegrationFlows.results}}
                            <tr">
                                <td style="width:40%;padding:2pt;"><p style="font-size:14px">{{Id}}<br>({{Name}})</p></td>
                                <td style="width:40%;padding:2pt;"><p style="font-size:14px">{{SenderHostType}}</p></td>
                                <td style="width:40%;padding:2pt;"><p style="font-size:14px">{{ReceiverHostType}}</p></td>
                            </tr>
                            {{/each}}
                    </table>
        {{/each}}
`;

pm.visualizer.set(template, {
    response: pm.response.json()
});

This script can be used in Postman to interpret the JSON using the handlebars template.

Simply copy the complete script above and paste it into the “Tests” tab of your Postman:

Now click on “Send” again, and switch to “Visualize” in the Postman response.

You will now get a nicely formatted visualisation of the JSON response containing an overview of the connections and also the details of them.

Conclusion

As you can see, using handlebars in Postman to visualise data is quite easy and could be used to generate any other kind of data structure to nicely format the response of the CPI connection metering.

Addendum – Spreadsheet script

In order to structure the data so you can use it in your spreadsheet editor, you can also use the following script:

var template = `
        <p style="font-size:18px">Total number of connections = {{response.d.__count}}</p>
        <b>Connection description; iFlow ID; iFLow Name; Sender Host Type; Receiver Host Type</b><br>
     
        {{#each response.d.results}}
            {{#each IntegrationFlows.results}}
                {{../SenderHost}} to {{../ReceiverHost}};{{Id}};{{Name}};{{SenderHostType}};{{ReceiverHostType}}<br>
            {{/each}}
        {{/each}}
`;

pm.visualizer.set(template, {
    response: pm.response.json()
});

It creates a semi-column separated value text that you can copy and paste.

Tip: you can use the “Data” menu to convert “Text to column” once you have pasted the results in the first column of your sheet.

Hope this helps!