Technical Articles
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.
- Start Timer (Timer)
- dateCapture (Content Modifier)
- setParam (Groovy Script)
- Request-reply
- ODataService(Receiver Participant)
- OData Adapter(V2)
- MessageMapping
- XSLT Mapping
- RunDate (Write Variable)
- Receiver Participant
- End message
- 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:
- Map both the root nodes.
- 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!!
This is very nice blog. can you please share the i -flow as well ?
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.
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
Can you try {{InputDefault}} at LogStartDate property default section then it will be configurable value.
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
Hi Nikhil,
Its a XSD structure. follow the below steps.
let me know if it doesn't work.
Thanks,
Sidharth
Thanks Sidharth,
Able to upload and can map after saving source and target code in XSD format.
Nice blog!!!
Regards,
Nikhil
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
Check if you are using an extra ' in your content modifier, else please share a screenshot.
Thanks
Sidharth VR
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
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
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.
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
Hi Siddharth,
Getting below error in Content Modifier:
Hello Siddharth,
Thanks for this Blog.
Regards,
Sunil
Very useful blog... thanks for sharing...
Regards,
Souvik
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
You can try in multiple methods.
Regards,
Sidharth
Hi Siddarth,
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
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.
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
hi Iam getting the below error,
I am also getting same error,
Did anyone find the solution?
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
same error for me. you found a solution?