Skip to Content
Technical Articles
Author's profile photo Sidharth VR

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.

Action Name Type Data Type Value
Create LogEndDate Expression java.lang.String ${date:now:yyyy-MM-dd’T’HH:mm:ss.SSS}
Create LogStartDate Global Variable RunDate
Create ManualStartDate Constant 2020-01-01T00:00:00.000

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

Operation Details Query(GET)
Resource Path MessageProcessingLogs
Query Options $select=MessageGuid,CorrelationId,ApplicationMessageId,ApplicationMessageType,LogStart,LogEnd,Sender,Receiver,IntegrationFlowName,Status,AlternateWebLink,IntegrationArtifact,LogLevel,CustomStatus,TransactionId,PreviousComponentName&$filter=LogStart ge datetime’${property.StartDate}’ and LogEnd le datetime’${property.EndDate}’
Content Type Atom
Timeout (in min) 1

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!!

Assigned Tags

      25 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Shikha Ghodeshwar
      Shikha Ghodeshwar

      This is very nice blog. can you please share the i -flow as well ?

      Author's profile photo RAMESH VARANGANTI
      RAMESH VARANGANTI

      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.

       

       

      for this , we can use Default value. If global variable value not available it will take default value and this will avoid groovy scripting.

       

       

      Author's profile photo Sidharth VR
      Sidharth VR
      Blog Post Author

      Hi Ramesh,

      Agreed. we can maintain Default Value. My intention is to keep Start date as externalized parameter so that we can just configure the value without editing the iflow.

      Sidharth VR

       

      Author's profile photo RAMESH VARANGANTI
      RAMESH VARANGANTI

      Can you try {{InputDefault}} at LogStartDate property default section then it will be configurable value.

      Author's profile photo nikhil gursal
      nikhil gursal

      Hi Sidharth,

      I am getting below error message when uploading source and target message in message mapping.

      "Upload of the resource failed. The content of WSDL file is invalid. cvc-elt.1: Cannot find the declaration of element 'xs:schema'."

      Can you please assist on this.

      Regards,

      Nikhil

      Author's profile photo Sidharth VR
      Sidharth VR
      Blog Post Author

      Hi Nikhil,

      Its a XSD structure. follow the below steps.

      1. Copy the code to notepad and store it as "source.xsd" in your desktop
      2. In CPI, navigate to resources tab, Add--->Schema---->XSD
      3. choose the source.xsd from your desktop and upload.

      let me know if it doesn't work.

      Thanks,

      Sidharth

      Author's profile photo nikhil gursal
      nikhil gursal

      Thanks Sidharth,

      Able to upload and can map after saving source and target code in XSD format.

      Nice blog!!!

      Regards,

      Nikhil

      Author's profile photo Yuvraj Karwar
      Yuvraj Karwar

      Hello Sidharth,

       

      Thanks for this blog. Can you please help us on below error at content modifier.

      'java.lang.IllegalArgumentException: Illegal pattern character 'T' '

       

      We have made config as you have mentioned for CM.

       

       

      Regards,

      Yuvraj Karwar

      Author's profile photo Sidharth VR
      Sidharth VR
      Blog Post Author

      Check if you are using an extra ' in your content modifier, else please share a screenshot.

      Thanks

      Sidharth VR

      Author's profile photo Shreyashri Kar
      Shreyashri Kar

      Hi Sidharth,

      Such a good informative blog !

      I have a query at this point --> Step 7: Calling the OData API via OData V2 adapter and configure with below parameters

      What credentials do I need to use & what would be the address in the Odata channel?

      It would be very kind of you to clarify the same.

      Thanks,

      Shreya

      Author's profile photo Sidharth VR
      Sidharth VR
      Blog Post Author

      Hi Shreyashri,

      URL: your CPI tenant URL,https://<<subaccount>>hana.ondemand.com/api/v1

      Credentials: deploy your S-USERID and Password in Security Material

       

      Thanks,

      Sidharth VR

      Author's profile photo Shreyashri Kar
      Shreyashri Kar

      Hi Sidharth,

      Thanks for the Clarification ! Now I can successfully fetch the details from Odata.
      Currently I am facing issue with the XSLT Mapping part !

      Post this conversion step, the output file generated does not look like the screenshot given here.
      Somehow the data doesn't get changed to the desired XLS format.

      I tried changing the Output Format of the XSLT mapping to both 'String' & 'Bytes' but the same result.

      Here I am using the mail adapter to deliver this report to my Outlook Mailbox & I have kept the Mail Body Type in the channel as 'Text/CSV'

      Am I missing something?
      Kindly let me know if you can spot the anomaly.

      Author's profile photo Sidharth VR
      Sidharth VR
      Blog Post Author

      Hi Shreyashri,

      Maintain Body Mime-Type as Text/Plain.

      after xslt, add a script to add output as attachment.

      //Script

      import com.sap.gateway.ip.core.customdev.util.Message;
      import org.apache.camel.impl.DefaultAttachment;
      import java.util.HashMap;
      import javax.mail.util.ByteArrayDataSource;

      def Message processData(Message message) {
      def body = message.getBody(java.lang.String) as String;

      // 2: Construct a ByteArrayDataSource object with the byte array and the content's MIME type
      def dataSource = new ByteArrayDataSource(body, 'text/plain');

      // 3: Construct a DefaultAttachment object
      def attachment = new DefaultAttachment(dataSource);

      // 4: Add the attachment to the message
      message.addAttachmentObject('MessageProcessing_Report.xls', attachment);

      return message;
      }

       

      Hope this works,

      Thanks,

      Sidharth VR

      Author's profile photo Aayush Aggarwal
      Aayush Aggarwal

      Hi Siddharth,

       

      Getting below error in Content Modifier:

      java.lang.IllegalArgumentException: Illegal pattern character 'T'
      Screenshot:
      image.png
      Author's profile photo Sunil Pharswan
      Sunil Pharswan

      Hello Siddharth,

      Thanks for this Blog.

      Regards,

      Sunil

      Author's profile photo Souvik Sinha
      Souvik Sinha

      Very useful blog... thanks for sharing...

       

      Regards,

      Souvik

      Author's profile photo Erigella NaveenReddy
      Erigella NaveenReddy

      Hi Sidharth,

      I am using the same above API to fetch the Last one month Monitoring report in CPI  I am getting Socket exception from ODATA because huge messages are processing in my tenant if i give last ten days or 15 days ODATA  is working fine and able to fetch the report. can any one give me the suggestion to overcome the issue.

      In adapter level i tried with the time out 120 min even its showing error.

      Thanks,

      Naveen

      Author's profile photo Sidharth VR
      Sidharth VR

      You can try in multiple methods.

      1. Reduce the number of fields
      2. try to pull the data as JSON.
      3. Try increasing tenure in steps.. 15 days to 20 days and try once, if success then try to increase to 25 days

      Regards,

      Sidharth

      Author's profile photo vijay kumar
      vijay kumar

      Hi Siddarth,

       

      • This query is not working for me $select=MessageGuid,CorrelationId,ApplicationMessageId,ApplicationMessageType,LogStart,LogEnd,Sender,Receiver,IntegrationFlowName,Status,AlternateWebLink,IntegrationArtifact,LogLevel,CustomStatus,TransactionId,PreviousComponentName,$top=20&$filter=LogStart ge datetime’${property.StartDate}’ and LogEnd le datetime’${property.EndDate}’

      we are try to give it dynamically it is not working .but it is working for ($select=MessageGuid,CorrelationId,ApplicationMessageId,ApplicationMessageType,LogStart,LogEnd,Sender,Receiver,IntegrationFlowName,Status,AlternateWebLink,IntegrationArtifact,LogLevel,CustomStatus,TransactionId,PreviousComponentName&$filter=LogStart ge datetime'2022-03-28T00:00:00' and LogEnd le datetime'2022-03-28T11:59:00')   .How to handle the the fetch data from dynamically.can you please help me out this.

       

      Thanks

      vijay

      Author's profile photo Munireddy Pulagam
      Munireddy Pulagam

      I am getting this error at content modifier property level, do i need change the format. i implemented the flow as per the blog correctly.

      Error Details
      java.lang.IllegalArgumentException: Illegal pattern character 'T'
      Author's profile photo Martin Pankraz
      Martin Pankraz

      Hi Sidharth VR,

      easy and straight forward approach. Thanks for sharing. How about putting it to the next level with automatic actions? At the moment you are exporting daily to act on the potential findings if you happen to look at the FTP server and actually spot something.

      Have a look here and here for some inspiration to add automatic actions and analytics to your approach.

      Looking forward for some more contributions from you 🙂

      KR
      Martin

      Author's profile photo Mohd Mukheed
      Mohd Mukheed

      hi Iam getting the below error,

       

      Error Details
      com.sap.gateway.core.ip.component.odata.exception.OsciException: An exception of type 'WstxUnexpectedCharException' occurred., cause: com.ctc.wstx.exc.WstxUnexpectedCharException: Unexpected character '=' (code 61); expected a semi-colon after the reference for entity 'client_id' at [row,col {unknown-source}]: [1,518]
      Author's profile photo Nagendra Kumar Tiwari
      Nagendra Kumar Tiwari

      I am also getting same error,

      Did anyone find the solution?

      Author's profile photo Mohd Mukheed
      Mohd Mukheed

      HI all,

       

      Iam trying to access MPL through iflow in Cloud Foundry environment and getting the above error, Kindly let me know the configurations to be made in BTP to access MPL api via iflows.

       

      Thanks

      Author's profile photo Filipe Rieger
      Filipe Rieger

      same error for me. you found a solution?