SAP Cloud Platform Integration – Message Monitoring Automated Report

[former_member598593]

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

[ShikhaG]

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

[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.

 



 

[former_member598593]

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

 

[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

[former_member598593]

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

[Nikhil_Gursal]

Thanks Sidharth,

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

Nice blog!!!

Regards,

Nikhil

[YuvrajKarwar]

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

[former_member598593]

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

Thanks

Sidharth VR

[shreya91]

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

[former_member598593]

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

[shreya91]

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.

[former_member598593]

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

[Varanganti]

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

[aayushaggarwal]

Hi Siddharth,

 

Getting below error in Content Modifier:

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

Screenshot:

[former_member672551]

Hello Siddharth,

Thanks for this Blog.

Regards,

Sunil

[sinhasouvik]

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

 

Regards,

Souvik

[former_member33436]

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

[cloudintegration_sid81]

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

[former_member774698]

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

[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'

[Martin-Pankraz]

Hi sidharthramasamy,

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

[former_member227811]

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]

[former_member227811]

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

[filipe_rieger]

same error for me. you found a solution?

[former_member871128]

I am also getting same error,

Did anyone find the solution?

[maryame]

when using the automated monitoring report to get"COMPLETED" flows, I'm getting mismatched values when comparing some of the report values and the actual values, this problem only occurs when the status= COMPLETED, the others are on point, any idea what may be the cause of this mismatch?