Analyzing large volumes of native call logs on cloud platform APIs

Wiki (en)
1

Native call logging provides unique facilities for in-depth analysis of cloud platform connectivity issues. It is available on all Invantive SQL products. For more details, refer to Collect Native Platform Call Data.

Up to release 20.2.71, native call logging created one file in text format per request and response. Learn from this post how release 20.2.72 and up provides new facilities to handle millions of native call logs.

Limitations to Analyzing Large Volumes of Native Call Logs

Given the increasing popularity of Invantive SQL-based solutions, it was not uncommon for users to registers hundreds of thousands files during analysis sessions. In 20.2.42 we introduced differentiated logging to finetune native call logging.

Starting release 20.2.72, native call logging will change in a number of ways to enable further up-scaling:

  • The default storage format will change from text to NDJSON to ease querying large volumes of native call logs.
  • Multiple API calls will be stored per log file. A native call log file will be created per day, user and data container.

Query Native Call Log

Native call logs previously were in a text-only format intended for human consumption. By switching to the NDJSON-format and with the recent addition of the NDJSONTABLE table function in Invantive SQL, it now becomes easy to analyse millions of API requests.

For example, the following query runs across all API requests in a single native call log file:

select msg.*
from   ndjsontable
       ( passing file 'PATH\NativeLog\native-something.ndjson'
         columns Id                         int64    not null label 'Id'                       path 'Id'
         ,       DateRegisteredUtc          datetime not null label 'Date Registered (UTC)'    path 'DateRegisteredUtc'
         ,       StartUtc                   datetime not null label 'Date Start (UTC)'         path 'StartUtc'
         ,       PoolidentityId             varchar2     null label 'Pool Identity ID'         path 'PoolIdentityId'
         ,       Uid                        guid     not null label 'UID'                      path 'Uid'
         ,       CallUid                    guid     not null label 'Call UID'                 path 'CallUid'
         ,       Url                        varchar2     null label 'URL'                      path 'Url'
         ,       Direction                  varchar2     null label 'Direction'                path 'CallDirection'
         ,       TableFullQualifiedName     varchar2     null label 'Table Name'               path 'TableFullQualifiedName'
         ,       TableNameOverrule          varchar2     null label 'Table Name (Overrule)'    path 'TableNameOverrule'
         ,       MessageText                varchar2     null label 'Message Text'             path 'MessageText'
         ,       ResponseStatus             varchar2     null label 'Response Status'          path 'ResponseStatus'
         ,       PartitionCode              varchar2     null label 'Partition Code'           path 'PartitionCode'
         ,       DataContainerId            varchar2     null label 'Data Container ID'        path 'DataContainerId'
         ,       AdditionalContext          varchar2     null label 'Additional Context'       path 'AdditionalContext'
         ,       RequestBodyAsString        varchar2     null label 'Body'                     path 'RequestBodyAsString'
         ,       RequestContents            varchar2     null label 'Request Contents'         path 'RequestContents'
         ,       ResponseBodyAsString       varchar2     null label 'Body'                     path 'ResponseBodyAsString'
         ,       ResponseContents           varchar2     null label 'Response Contents'        path 'ResponseContents'
         --
         ,       GuiAction                  varchar2     null label 'GUI Action'               path 'GuiAction'
         ,       GuiExternalIpAddress       varchar2     null label 'GUI Action'               path 'GuiExternalIpAddress'
         ,       GuiLanguage                varchar2     null label 'GUI Action'               path 'GuiLanguage'
         ,       GuiModuleName              varchar2     null label 'GUI Action'               path 'GuiModuleName'
         ,       GuiModuleVersion           varchar2     null label 'GUI Action'               path 'GuiModuleVersion'
         ,       GuiOsUser                  varchar2     null label 'GUI Action'               path 'GuiOsUser'
         ,       GuiUser                    varchar2     null label 'GUI Action'               path 'GuiUser'
         ,       MachineNameFull            varchar2 not null label 'MachineNameFull'          path 'MachineNameFull'
         ,       UserLogonCodeWithDomain    varchar2 not null label 'UserLogonCodeWithDomain'  path 'UserLogonCodeWithDomain'
         ,       ManagedThreadId            int      not null label 'ManagedThreadId'          path 'ManagedThreadId'
         ,       ProcessId                  int          null label 'Process ID'               path 'ProcessId'
         ,       MessageCode                varchar2     null label 'Message Code'             path 'MessageCode'
         ,       Parameters                 varchar2     null label 'Parameters'               path 'Parameters'
         ,       Success                    boolean  not null label 'Success'                  path 'Success'
         ,       UserLogOnCode              varchar2     null label 'User Logon Code'          path 'UserLogOnCode'
         ,       RequestHttpHeaders         varchar2     null label 'Request HTTP Headers'     path 'RequestHttpHeaders'
         ,       ResponseHttpHeaders        varchar2     null label 'Response HTTP Headers'    path 'ResponseHttpHeaders'
       )
       msg
order
by     msg.Id

The result can be exported to Excel, such as using the following Invantive Script statement for on-premises products to the file dump-native-call-log.xlsx in the user’s Documents folder:

local export results as "${system:userdocumentsdirectory}\dump-native-call-log.xlsx" format xlsx include headers

Please note that the export can and will contain confidential information, such as internal business data, but also credentials such as a “Bearer” in the request.

Remember to consider removing confidential information when storing and/or distributing.