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'
, EndUtc datetime not null label 'Date End (UTC)' path 'EndUtc'
, 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 'User Logon Code with Domain' path 'UserLogonCodeWithDomain'
, UserName varchar2 not null label 'User Name' path 'UserName'
, ManagedThreadId int not null label 'ManagedThreadId' path 'ManagedThreadId'
, ProcessId int null label 'Process ID' path 'ProcessId'
, ExecutableName varchar2 not null label 'Executable Name' path 'ExecutableName'
, 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'
, MetadataType varchar2 null label 'Metadata Type' path 'MetadataType'
, SqlStepMetadataType varchar2 null label 'SQL Step Metadata Type' path 'SqlExecutionStep.MetadataType'
, SqlStepId int null label 'SQL Step ID' path 'SqlExecutionStep.Id'
, SqlStepParentId int null label 'SQL Step Parent ID' path 'SqlExecutionStep.ParentId'
, SqlStepAlias varchar2 null label 'SQL Step Alias' path 'SqlExecutionStep.Alias'
, SqlStepDepth int null label 'SQL Step Depth' path 'SqlExecutionStep.Depth'
, SqlStepManagedThreadId int null label 'SQL Step Managed Thread ID' path 'SqlExecutionStep.ManagedThreadId'
, SqlStepExecutionOptionsId varchar2 null label 'SQL Step Execution Options ID' path 'SqlExecutionStep.ExecutionOptionsId'
, SqlStepDescription varchar2 null label 'SQL Step Description' path 'SqlExecutionStep.Description'
, SqlStepStartedUtc datetime null label 'SQL Step Started (UTC)' path 'SqlExecutionStep.StartedUtc'
, ExceptionCode varchar2 null label 'Exception Code' path 'ExceptionMessageCode'
, ExceptionMessage varchar2 null label 'Exception Message' path 'ExceptionMessage'
, ExceptionNaturalKey varchar2 null label 'Exception Natural Key' path 'ExceptionNaturalKey'
, ExceptionData varchar2 null label 'Exception Data' path 'ExceptionData'
, ExceptionCallstack varchar2 null label 'Exception Callstack' path 'ExceptionCallstack'
)
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.