Get message payload out of BC_MSG table

[Dimitri]

Hi All,

I want to get the payload out of table BC_MSG (SAP PO 7.4 system) and column MSG_BYTES.

When I take a SQL tool to do it, I can only see a lot of characters, but not the actual payload. it is completely not readable.

Is there something that I need to do to get the actual payload of out the table using a SELECT statement?

Thanks a lot.

Dimitri

[aditinayak27]

which SQL Tool are you using to fetch the details of table BC_MSG_AUDIT?

[vadimklimov · Accepted solution]

Hi Dimitri,

Content of BC_MSG-MSG_BYTES is a byte array holding XI message (not only payload, but the whole XI message). Which means, after you get result set for your query against BC_MSG, you need to read BC_MSG-MSG_BYTES as a binary input stream and then convert it to text readable and parsable XI message.

When converting retrieved byte array, message compression shall be taken into consideration. By default, message compression (controlled by property 'messaging.compression.enabled' of Java service 'XPI Service: Messaging System') is disabled and content of MSG_BYTES is byte array representing uncompressed XI message content (for each message, you can also see if its content is persisted compressed or uncompressed by checking value of BC_MSG-BYTES_COMPRESSED: 'N' - uncompressed (default), 'Y' - compressed).

If compression is enabled, then expected uncompressed byte array size for every processed and persisted message shall be available in BC_MSG-UNCOMPRESSED_SIZE - which means, when unzipping the retrieved content of BC_MSG-MSG_BYTES into byte array of XI message content, the target uncompressed byte array size shall be taken into consideration.

Otherwise, convertion of byte array of BC_MSG-MSG_BYTES shall be based on bytes length specified in BC_MSG-BYTES_LENGTH.

Having all this written, let me ask you if you really need to retrieve raw message data from database layer and implement its conversion in custom logic, or can the requirement be converted to something allowing involvement of PI application layer. If latter is allowed, then there are several helpful functionalities that can reduce custom development effort - for example, SAP standard web service AdapterMessageMonitoringVi provides operations getMessageBytesJavaLangStringBoolean and getMessageBytesJavaLangStringIntBoolean that can be used to retrieve content of XI message. If you need to invoke something lower level from APIs, then you may have a look at Message Store accessor classes - for example, com.sap.engine.messaging.impl.core.store.MessageStore (particularly, its methods getMessage() and getMessageData()).

Regards,

Vadim

[Dimitri · Accepted solution]

Hi Vadim,

FYI: we wanted to test the sensitive data indicator on Service Interface level.

Therefore, I asked a DBA to access the database via an external tool to actually see the data has been protected.

There is no requirement to use this within an integration scenario.

Kind regards,

Dimitri

[vadimklimov · Accepted solution]

Dimitri,

Assuming you also executed preparatory steps (encryption key is created in / imported to key storage view, enabled message content encryption in AAE using properties 'messaging.security.persistenceLayerEncryption.*' of Messaging System service), then after you set sensitive data flag in the service interface, future processed messages shall get non-null value in BC_MSG-PCI_KEY_NAME when they are looked in BC_MSG table: BC_MSG-PCI_KEY_NAME is null for messages with non-encrypted content and is value of the used encryption key when message is processed for sensitive data interface. In this way, observation of BC_MSG-PCI_KEY_NAME shall be another good evidence about message content encryption in persistence layer. Still, this does not prevent the encryption key being compromised (e.g. retrieved from NWA) and then used to decrypt retrieved message content's byte array from BC_MSG-MSG_BYTES. Good point: at least, this will require some efforts and is not something that is likely to be achieved by means of only DBA tools.

Regards,

Vadim