SAP Cloud Platform Integration – Message Monitoring Automated Report

Introduction:

This blog explains on how to send automated status report for the message processing via SAP CPI. The notification is sent by setting up a timer triggered integration flow (IFlow) on regular intervals.

Here is the how the over flow looks like:

Steps to be followed in creating the Iflow:

Step 1: Create a Package with Name: Cloud Platform Integration Reporting.

Step 2: Create an IFlow with Name: Reporting_IFlow

Step 3: Create an Integration flow with following Components.

1. Start Timer (Timer)
2. dateCapture (Content Modifier)
3. setParam (Groovy Script)
4. Request-reply
5. ODataService(Receiver Participant)
6. OData Adapter(V2)
7. MessageMapping
8. XSLT Mapping
9. RunDate (Write Variable)
10. Receiver Participant
11. End message
12. SFTP Adapter/Mail Adapter.

Step 4: Configure Timer.

Configure the timer to run for every day. I’ve done the configuration such that the interface will run every day twice (once in every 12 hours) and captures the data since the previous flow time.

Step 5: Configure dateCapture (Content Modifier)

In the content modifier, create three properties.

For the First run of the interface, we need to have a Start date, from the consecutive runs, Global variable will be considered for the start date, so that no message will be left uncaptured.

Step 6: Construct the setParam script based on start and end dates from content modifier.

import com.sap.gateway.ip.core.customdev.util.Message;
import java.util.HashMap;
def Message processData(Message message) {
       def body = message.getBody(java.lang.String);
     
       def map = message.getProperties();
       def ManualStartDate = map.get("ManualStartDate")
       def LogStartDate = map.get("LogStartDate")
       def LogEndDate = map.get("LogEndDate")
       def whereQuery
       if (LogStartDate ==''){
           message.setProperty ("StartDate", ManualStartDate)
       }else{
            message.setProperty ("StartDate", LogStartDate)
       }
       message.setProperty("EndDate", LogEndDate);
       return message;
}

Step 7: Calling the OData API via OData V2 adapter and configure with below parameters

Step 8: Message Mapping

Do the Message Mapping with following XSD as source:

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<xs:schema attributeFormDefault="unqualified" elementFormDefault="qualified" xmlns:xs="http://www.w3.org/2001/XMLSchema">
    <xs:element name="MessageProcessingLogs">
        <xs:complexType>
            <xs:sequence>
                <xs:element minOccurs="1" maxOccurs="unbounded" name="MessageProcessingLog">
                    <xs:complexType>
                        <xs:sequence>
                            <xs:element type="xs:string" nillable="false" maxOccurs="unbounded" name="MessageGuid" xmlns=""/>
                            <xs:element type="xs:string" nillable="true" minOccurs="0" maxOccurs="unbounded" name="CorrelationId" xmlns=""/>
                            <xs:element type="xs:string" nillable="true" minOccurs="0" maxOccurs="unbounded" name="ApplicationMessageId" xmlns=""/>
                            <xs:element type="xs:string" nillable="true" minOccurs="0" maxOccurs="unbounded" name="ApplicationMessageType" xmlns=""/>
                            <xs:element type="xs:dateTime" nillable="true" minOccurs="0" maxOccurs="unbounded" name="LogStart" xmlns=""/>
                            <xs:element type="xs:dateTime" nillable="true" minOccurs="0" maxOccurs="unbounded" name="LogEnd" xmlns=""/>
                            <xs:element type="xs:string" nillable="true" minOccurs="0" maxOccurs="unbounded" name="Sender" xmlns=""/>
                            <xs:element type="xs:string" nillable="true" minOccurs="0" maxOccurs="unbounded" name="Receiver" xmlns=""/>
                            <xs:element type="xs:string" nillable="true" minOccurs="0" maxOccurs="unbounded" name="IntegrationFlowName" xmlns=""/>
                            <xs:element type="xs:string" nillable="true" minOccurs="0" maxOccurs="unbounded" name="Status" xmlns=""/>
                            <xs:element type="xs:string" nillable="true" minOccurs="0" maxOccurs="unbounded" name="AlternateWebLink" xmlns=""/>
                            <xs:element nillable="false" maxOccurs="unbounded" name="IntegrationArtifact">
                                <xs:complexType>
                                    <xs:sequence>
                                        <xs:element type="xs:string" nillable="true" minOccurs="0" maxOccurs="unbounded" name="Id" xmlns=""/>
                                        <xs:element type="xs:string" nillable="true" minOccurs="0" maxOccurs="unbounded" name="Name" xmlns=""/>
                                        <xs:element type="xs:string" nillable="true" minOccurs="0" maxOccurs="unbounded" name="Type" xmlns=""/>
                                    </xs:sequence>
                                </xs:complexType>
                            </xs:element>
                            <xs:element type="xs:string" nillable="true" minOccurs="0" maxOccurs="unbounded" name="LogLevel" xmlns=""/>
                            <xs:element type="xs:string" nillable="true" minOccurs="0" maxOccurs="unbounded" name="CustomStatus" xmlns=""/>
                            <xs:element type="xs:string" nillable="true" minOccurs="0" maxOccurs="unbounded" name="TransactionId" xmlns=""/>
                            <xs:element type="xs:string" nillable="true" minOccurs="0" maxOccurs="unbounded" name="PreviousComponentName" xmlns=""/>
                        </xs:sequence>
                    </xs:complexType>
                </xs:element>
            </xs:sequence>
        </xs:complexType>
    </xs:element>
</xs:schema>

And the Below XSD as target.

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<xs:schema attributeFormDefault="unqualified" elementFormDefault="qualified" xmlns:xs="http://www.w3.org/2001/XMLSchema">
    <xs:element name="MessageProcessingLogs">
        <xs:complexType>
            <xs:sequence>
                <xs:element minOccurs="0" maxOccurs="unbounded" name="MessageProcessingLog">
                    <xs:complexType>
                        <xs:sequence>
                            <xs:element type="xs:string" nillable="false" maxOccurs="unbounded" name="IntegrationFlowName" xmlns=""/>
                            <xs:element type="xs:string" nillable="true" minOccurs="0" maxOccurs="unbounded" name="Completed" xmlns=""/>
                            <xs:element type="xs:string" nillable="true" minOccurs="0" maxOccurs="unbounded" name="Failed" xmlns=""/>
                            <xs:element type="xs:string" nillable="true" minOccurs="0" maxOccurs="unbounded" name="Processing" xmlns=""/>
                            <xs:element type="xs:string" nillable="true" minOccurs="0" maxOccurs="unbounded" name="Retry" xmlns=""/>
                            <xs:element type="xs:string" nillable="true" minOccurs="0" maxOccurs="unbounded" name="Escalated" xmlns=""/>
                        </xs:sequence>
                    </xs:complexType>
                </xs:element>
            </xs:sequence>
        </xs:complexType>
    </xs:element>
</xs:schema>

Mapping Rules:

1. Map both the root nodes.
2. Map the Target MessageProcessingLog(Subnode) with below logic.

Source Field: IntegrationFlowName To Target MessageProcessingLog

3. Map source IntegrationFlowName to  Target IntegrationFlowName with below logic

**Remember — First SplitBy Value must have Context change on Value change and second SplitBy Value must have Context change on Each value

4. Map the Source IntegrationFlow and Status from Source to Target Completed Field with below logic

This is the most important and tricky part.

IntegrationFlowName—removeContext—SplitByValue(ValueChange)—FormatByExample(Input-Status, Pattern-SplitByValue Output)—Equals(String1- FormayByExample, String2-Constant”COMPLETED”)—ifWithoutElse(Condition–Equalsoutput, trueValue–FormatByExample)—Count—Completed

repeat the same logic for Failed, Processing, Retry and Escalated by changing the constants.

Step 9: XSLT Mapping

Once done with Message Mapping, we will have the desired output in XML format. To convert the XML to XLS format, we need to do XSLT mapping. Use the below XSLT Code to convert the XML message in XLS format.

<?xml version="1.0" encoding="UTF-8"?>
<?mso-application progid="Excel.Sheet"?>

<xsl:stylesheet version="1.0" xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns="urn:schemas-microsoft-com:office:spreadsheet" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:ns0="urn:test.com" xmlns:html="http://www.w3.org/TR/REC-html40">
	<xsl:template match="/">
		<Workbook>
			<Styles>
				<Style ss:ID="Default" ss:Name="Normal">
					<Alignment ss:Vertical="Bottom"/>
					<Borders/>
					<Font/>
					<Interior/>
					<NumberFormat/>
					<Protection/>
				</Style>
				<Style ss:ID="s21">
					<Font ss:Size="22" ss:Bold="1"/>
				</Style>
				<Style ss:ID="s22">
					<Font ss:Size="14" ss:Bold="1"/>
				</Style>
				<Style ss:ID="s23">
					<Font ss:Size="12" ss:Bold="1"/>
				</Style>
				<Style ss:ID="s24">
					<Font ss:Size="10" ss:Bold="1"/>
				</Style>
			</Styles>
			<Worksheet ss:Name="Sheet1">
				<Table>
					<xsl:call-template name="Excel"/>
				</Table>
			</Worksheet>
		</Workbook>
    </xsl:template>
	<xsl:template name="Excel">
        <Row>
            <Cell>
            <Data ss:Type="String">IntegrationFlowName</Data>               
            </Cell>
            <Cell>
            <Data ss:Type="String">Completed</Data>
            </Cell>
            <Cell>
            <Data ss:Type="String">Failed</Data>                
            </Cell>
            <Cell>
            <Data ss:Type="String">Processing</Data>            
            </Cell>
            <Cell>
            <Data ss:Type="String">Retry</Data>                
            </Cell>
            <Cell>
            <Data ss:Type="String">Escalated</Data> 
            </Cell>
            </Row>
		<xsl:for-each select="//MessageProcessingLog">

            <Row>
                <Cell>
                    <Data ss:Type="String">
                        <xsl:value-of select="IntegrationFlowName" />
                    </Data>
                </Cell>
                <Cell>
                    <Data ss:Type="String">
                        <xsl:value-of select="Completed" />
                    </Data>
                </Cell>
                                <Cell>
                    <Data ss:Type="String">
                        <xsl:value-of select="Failed" />
                    </Data>
                </Cell>
                                <Cell>
                    <Data ss:Type="String">
                        <xsl:value-of select="Processing" />
                    </Data>
                </Cell>
                                <Cell>
                    <Data ss:Type="String">
                        <xsl:value-of select="Retry" />
                    </Data>
                </Cell>
                                <Cell>
                    <Data ss:Type="String">
                        <xsl:value-of select="Escalated" />
                    </Data>
                </Cell>
            </Row>
        </xsl:for-each>
    </xsl:template>
<xsl:template match="MessageProcessingLogs">
</xsl:template>
</xsl:stylesheet>

Step 10: Storing the RunDate for next trigger.

We need to store the run date of the flow so that there won’t be any duplicate counts displayed in the consecutive runs.

Step 11: Connect to SFTP/Mail Adapter.

For SFTP adapter Configuration —SFTP Receiver

For Mail adapter Configuration – Mail Receiver Adapter

Once the configuration is done, and triggered, the output file generated looks like the below

I’ve Masked the IFlow Names.

Conclusion:

This is one of the simplest ways of generating report for your Cloud Platform Integration. With the help of SAP API Business HUB, utilizing the new feature, we can further extend the look and feel of the reporting tool.

Thanks,

Happy Learning!!