About CData Connect Cloud log files
CData Connect Cloud offers a detailed logging system that is essential for monitoring and troubleshooting the interactions with the cloud data connectivity service. The logs are divided into Query Logs, which provide detailed information about the SQL queries executed, including their text, timestamp, user ID, and execution status, and Audit Logs, which track administrative actions within the platform. To aid in diagnosing issues, users can adjust the level of detail captured by the logs.
It is important to note that logs are retained for only seven days before automatic deletion. For any troubleshooting needs, users can download the log files, which can be particularly useful when encountering errors related to query execution.
Types of log files in CData Connect Cloud
CData Connect Cloud generates two categories of log files depending on the tasks it carries out. 
- Query Logs: This includes information about the queries made to your CData Connect Cloud account. The Query Log tab shows a list of executed queries along with the following details for each one:
- Timestamp: The date and time when the query is executed, formatted as YYYY-MM-DD HH:MM:SS AM/PM UTC
- Query Type: The category of the query
- User: The login ID of the CData Connect Cloud user who executes the query
- Status: The outcome of the query, indicating whether it succeeded or failed
- Query Text: The text and destination of the query
At the bottom of the query table, you can choose the number of queries to display per page and navigate between different result pages.
- Query Details: Clicking on any query in the log will expand it and display detailed information. The fields and contents may differ between queries of various types.
You can click on the Logs button in the query details section to download a log file containing detailed information about the query. These log files are helpful for troubleshooting errors related to issuing queries to CData Connect.
Notes:
- You can adjust the logging verbosity for a connection on its Advanced Settings page, setting the default level to 3 (so the logs have enough details to troubleshoot adequately). Check out the Logging Section described below to know more.
- CData Connect retains log files for seven days, after which they are automatically deleted.
- Filtering: The section above the query table allows for filtering queries. By default, only Start Time and End Time are listed as filtering criteria. Click the + icon to expand the field and reveal additional categories.
| Filter Name | Description |
| Start Time and End Time | Displays only queries run between the selected start and end times. |
| Query Type(s) | Displays only queries of the types that you select. |
| All Queries/Failed/Successful | Enables you to toggle between allowing all queries or only those that fail or succeed. |
| Username | Displays only queries that are performed by the specified username. |
| Query ID(s) | Display only queries that match the entered IDs. |
| Query Text | Displays only queries that include the specified text. |
Click Search to apply the selected filters to the query table.
- Audit Logs: The Audit Logs tab displays a list of audit events and the following information for each event:
- Timestamp: The date and time when the event occurs, formatted as YYYY-MM-DD HH:MM:SS AM/PM UTC
- Event Type: The category of the audit event
- User: The login ID of the CData Connect Cloud user who performs the administrative task
- Target Resource: The category and the name of the targeted resource
At the bottom of the audit table, you can choose the number of entries to display per page and navigate through the pages of results.
- Audit Details: Clicking on any entry in the audit log will expand it to show detailed information. The fields and content displayed can differ between various types of entries. Some entries may not have any additional details to display.
- Filtering: The section above the audit log allows you to filter entries. Click the + icon to expand the field, which enables you to filter by the following categories:
- Start Time and End Time: Shows only the entries that fall within the specified time range.
- Audit Event Type: Shows only the entries of the selected event types.
Click on Search to apply the selected filters to the query table.
Logging Verbosity
In the Advanced Settings tab for each connection, there is a setting in the Logging section called Verbosity. This setting controls how much information CData Connect logs for that data source on the Logs page.
The verbosity level can range from 1 to 5, with 1 logging the least information and 5 logging the most. Each data source is set to log at verbosity level 2 by default. It is important to note that higher verbosity levels can negatively impact query performance. To maintain optimal performance, only increase the verbosity level when troubleshooting.
Protecting Sensitive Information in Logs
Verbosity levels 3 and higher include sensitive information from the connection, as detailed below:
- Level 3 includes the bodies of requests and responses.
- Level 4 includes SSL certificates.
- Level 5 includes additional low-level transfer data not included at the previous levels.
Note: Sensitive connection properties, including passwords, are always masked in connection strings and requests/responses, regardless of the verbosity level.
To safeguard your sensitive information, increase logging verbosity only when necessary. If logging verbosity is set to level 3 or higher, review log files thoroughly before sharing them outside your organization.
Understanding the Query log files
In this segment, we aim to comprehend the Query logs generated by CData Connect Cloud. As indicated previously, you can click on the Logs button in the query details section to download a detailed log file. A Query log typically consists of two files – Query Federation and <DataSourceConnectionName> (Driver Log). Let’s explore what constitutes these log files.
- Query Federation log file
(A) First, check the version number of the driver used in the log. Please note that the Data Source connection name in this example is NetSuite5:
2024-05-24T17:32:46.634+00:00 2 [4432|Q-Id]
[INFO|Connec] Connection Property: maxlogfilesize = -1
2024-05-24T17:32:46.634+00:00 2
[4432|Q-Id] [INFO|Connec] Connection Property: usefederationfactory = True
2024-05-24T17:32:46.634+00:00 2
[4432|Q-Id] [INFO|Connec] Connection Property: verbosity = 5
2024-05-24T17:32:46.634+00:00 2 [4432|Q-Id]
[INFO|Connec] Connection Property: queryoverconnections = TRUE
2024-05-24T17:32:46.634+00:00 2 [4432|Q-Id]
[INFO|Connec] Connection Property: other = userviewsschemaname=DerivedViews
2024-05-24T17:32:46.634+00:00 4 [4432|Q-Id]
[INFO|Connec] Success: (0 ms) 2024-05-24T17:32:46.634+00:00 1
[4432|Q-Id] [INFO|Connec] Opened QueryFederation connection. Version: 23.0.8893.0. Git: 7e1b22947698774ce9e71042fa987dcc11ef87fb. Edition: [ADO.NET Provider .NET Standard 2.0].
(B) Next, check the connection string in the log, if it is properly defined.
2024-05-24T17:32:46.634+00:00 1 [4432|Q-Id]
[INFO|Connec] Connection String: autocache=False;batchsize=0;browsableschemas='';cachemetadata=False;cacheprovider='';cachetolerance=600;connectionlifetime=0;databaseconfiguration='';defaultcatalog='';defaultschema='';logmodules='';maxlogfilecount=-1;maxlogfilesize=-1;maxrows=-1;notablenamedrivers="SQL Server,OracleOCI,PostgreSQL,Amazon Redshift";offline=False;other="userviewsschemaname=DerivedViews";poolidletimeout=60;poolmaxsize=100;poolminsize=1;poolwaittime=60;queryoverconnections=TRUE;readonly=False;savelastresult=true;tables='';useconnectionpooling=False;usefederationfactory=True;userviewsschemaname=DerivedViews;usesinglecharpattern=false;verbosity=5;views=''
(C) Now, compare the query run to the one logged in the log file.
2024-05-24T17:32:46.635+00:00 2 [4432|Q-Id]
[EXEC|Parsed] Executing query: [SELECT * FROM (SELECT[NetSuite5].[SuiteQL].[TransactionAccountingLine].[account] AS [account],[NetSuite5].[SuiteQL].[TransactionAccountingLine].[accountingbook] AS[accountingbook],[NetSuite5].[SuiteQL].[TransactionAccountingLine].[amount]AS [amount],[NetSuite5].[SuiteQL].[TransactionAccountingLine].[amountpaid] AS[amountpaid],[NetSuite5].[SuiteQL].[TransactionAccountingLine].[amountunpaid] AS [amountunpaid],[NetSuite5].[SuiteQL].[TransactionAccountingLine]. [credit] AS [credit],[NetSuite5].[SuiteQL].[TransactionAccountingLine]. [debit] AS [debit],[NetSuite5].[SuiteQL].[TransactionAccountingLine]. [exchangerate] AS [exchangerate],[NetSuite5].[SuiteQL]. [TransactionAccountingLine].[netamount] AS [netamount],[NetSuite5]. [SuiteQL].[TransactionAccountingLine].[overheadparentitem] AS [overheadparentitem],[NetSuite5].[SuiteQL].[TransactionAccountingLine].[paymentamountunused] AS [paymentamountunused],[NetSuite5].[SuiteQL]. [TransactionAccountingLine].[paymentamountused] AS [paymentamountused], [NetSuite5].[SuiteQL].[TransactionAccountingLine].[posting] AS [posting],[NetSuite5].[SuiteQL].[TransactionAccountingLine].[transaction] AS [transaction],[NetSuite5].[SuiteQL].[TransactionAccountingLine].[transactionline] AS [transactionline],[NetSuite5].[SuiteQL]. [TransactionAccountingLine].[amountlinked] AS [amountlinked],[NetSuite5]. [SuiteQL].[TransactionAccountingLine].[lastmodifieddate] AS [lastmodifieddate],[NetSuite5].[SuiteQL].[TransactionAccountingLine].[processedbyrevcommit] AS [processedbyrevcommit] FROM [NetSuite5].[SuiteQL].[TransactionAccountingLine] LIMIT 1000)]
The above-logged query retrieves data from the "TransactionAccountingLine" table within the "SuiteQL" schema, using the “NetSuite5” connection name, which is linked to the NetSuite driver in CData Connect Cloud.
(D) Next, examine the "Sending Query" section of the QF log to see what query the Query Federation sends to the driver. Additionally, we need to check whether the query execution is successful. In the example log given below, the query was executed in 2836 ms.
2024-05-24T17:32:46.664+00:00 3
[QF] Sending query: SELECT [NESTED_QUERY1582].[account] AS [account], [NESTED_QUERY1582].[accountingbook] AS [accountingbook], [NESTED_QUERY1582].[amount] AS [amount], [NESTED_QUERY1582].[amountpaid] AS [amountpaid], [NESTED_QUERY1582].[amountunpaid] AS [amountunpaid], [NESTED_QUERY1582].[credit] AS [credit], [NESTED_QUERY1582].[debit] AS [debit], [NESTED_QUERY1582].[exchangerate] AS [exchangerate], [NESTED_QUERY1582].[netamount] AS [netamount], [NESTED_QUERY1582].[overheadparentitem] AS [overheadparentitem], [NESTED_QUERY1582].[paymentamountunused] AS [paymentamountunused], [NESTED_QUERY1582].[paymentamountused] AS [paymentamountused], [NESTED_QUERY1582].[posting] AS [posting], [NESTED_QUERY1582].[transaction] AS [transaction], [NESTED_QUERY1582].[transactionline] AS [transactionline], [NESTED_QUERY1582].[amountlinked] AS [amountlinked], [NESTED_QUERY1582].[lastmodifieddate] AS [lastmodifieddate], [NESTED_QUERY1582].[processedbyrevcommit] AS [processedbyrevcommit] FROM [CData].[SuiteQL].[TransactionAccountingLine] AS [NESTED_QUERY1582] LIMIT 1000
It is important to note that when a query is written in the ‘Data Explorer’ section of Connect Cloud to retrieve data from a specified data source (as mentioned in section III), the driver will not recognize the connection name (e.g., NetSuite5 in this instance), causing the entire query to fail. To address this, QF re-frames the query so that the driver can understand it by using the default catalog name "CData," which the drivers recognize. The rest of the query remains unchanged.
2024-05-24T17:32:49.471+00:00 2 [4432|Q-Id]
[EXEC|Messag] Executed query: [SELECT * FROM (SELECT [NetSuite5].[SuiteQL]. [TransactionAccountingLine].[account] AS [account],[NetSuite5].[SuiteQL]. [TransactionAccountingLine].[accountingbook] AS[accountingbook],[NetSuite5]. [SuiteQL].[TransactionAccountingLine].[amount] AS [amount],[NetSuite5].[SuiteQL].[TransactionAccountingLine].[amountpaid] AS[amountpaid],[NetSuite5].[SuiteQL].[TransactionAccountingLine].[amountunpaid] AS [amountunpaid],[NetSuite5].[SuiteQL].[TransactionAccountingLine]. [credit] AS [credit],[NetSuite5].[SuiteQL].[TransactionAccountingLine]. [debit] AS [debit],[NetSuite5].[SuiteQL].[TransactionAccountingLine]. [exchangerate] AS [exchangerate],[NetSuite5].[SuiteQL]. [TransactionAccountingLine].[netamount] AS [netamount],[NetSuite5]. [SuiteQL].[TransactionAccountingLine].[overheadparentitem] AS [overheadparentitem],[NetSuite5].[SuiteQL]. [TransactionAccountingLine] .[paymentamountunused] AS [paymentamountunused],[NetSuite5]. [SuiteQL].[TransactionAccountingLine].[paymentamountused] AS [paymentamountused],[NetSuite5].[SuiteQL]. [TransactionAccountingLine]. [posting] AS [posting],[NetSuite5].[SuiteQL] .[TransactionAccountingLine] .[transaction] AS [transaction], [NetSuite5].[SuiteQL]. [TransactionAccountingLine].[transactionline] AS [transactionline], [NetSuite5].[SuiteQL].[TransactionAccountingLine].[amountlinked] AS [amountlinked],[NetSuite5].[SuiteQL]. [TransactionAccountingLine]. [lastmodifieddate] AS [lastmodifieddate],[NetSuite5].[SuiteQL]. [TransactionAccountingLine].[processedbyrevcommit] AS [processedbyrevcommit] FROM [NetSuite5].[SuiteQL].[TransactionAccountingLine] LIMIT 1000)] Success: (2836 ms)
- Data Source Connection log file
(A) To troubleshoot any issue, we first need to determine if the driver being used is old or new by checking its version number in the log.
NetSuite connection. Version: 23.0.8865.0. Edition: [ADO.NET Provider .NET Standard 2.0].
(B) Next, we need to check the connection string logged, to determine if the expected fields are included for the connection to succeed.
2024-05-24T17:32:46.663+00:00 1 [1222|Q-Id]
[INFO|Connec] Connection String: accountid=*****;accountsupportssuiteanalyticsconnect=False;aggregatecolumnmode=Ignore;applicationid='';authkey='';authscheme=Token;authtoken='';autocache=false;browsableschemas='';cachedbhaspoolingenabled=True;cachemetadata=true;cacheprovider=Microsoft.Data.SqlClient;cacheschema=xxxxxx-xxxxxx-bd87-6xxx36;cachetolerance=600;callbackurl='';catalog=CData; connectionlifetime=0;customfieldpermissions='';fetchresultsetmetadata=true;firewallpassword='';firewallport=0;firewallserver='';firewalltype=NONE;firewalluser='';includechildtables=False;includecustomfieldcolumns=True;includecustomlisttables=False;includecustomrecordtables=True;includereferencecolumns="InternalId, Name";initiateoauth=REFRESH;logmodules=''; maximumconcurrentsessions=0;maxlogfilecount=2;maxlogfilesize=500MB;maxrows=-1;netsuitedateformat=MM-dd-yyyy;netsuitelongdateformat="MMM dd, yyyy";netsuitemetadatafolder='';oauthaccesstoken=*****;oauthaccessxxxxxxx5aa125d4937303122f45f2b*********546856d67371ddc;oauthclientsecret=*****;oauthexpiresin='';oauthrefreshtoken='';oauthtokentimestamp='';oauthverifier='';oauthversion=Auto;offline=False;other="fetchresultsetmetadata=true;preferpersistedtokens=true;schemacacheprefix=3dcdsslservercert='';tables='';timeout=300;useasyncservices=False;useconnectionpooling=False;useinternalnamesforcustomizations=False;userdefinedviews='';usertimezoneoffset='';usesimplenames=False;useupserts=False;verbosity=2;version=2022_2;views='';webservicehost=''
(C) Now, we verify if the query sent by QF to the driver has been logged in this file and executed as well.
2024-05-24T17:32:46.664+00:00 2 [1222|Q-Id]
[EXEC|Parsed] Executing query: [SELECT [NESTED_QUERY1582].[account] AS [account], [NESTED_QUERY1582].[accountingbook] AS [accountingbook], [NESTED_QUERY1582].[amount] AS [amount], [NESTED_QUERY1582].[amountpaid] AS [amountpaid], [NESTED_QUERY1582].[amountunpaid] AS [amountunpaid], [NESTED_QUERY1582].[credit] AS [credit], [NESTED_QUERY1582].[debit] AS [debit], [NESTED_QUERY1582].[exchangerate] AS [exchangerate], [NESTED_QUERY1582].[netamount] AS [netamount], [NESTED_QUERY1582].[overheadparentitem] AS [overheadparentitem], [NESTED_QUERY1582].[paymentamountunused] AS [paymentamountunused], [NESTED_QUERY1582].[paymentamountused] AS [paymentamountused], [NESTED_QUERY1582].[posting] AS [posting], [NESTED_QUERY1582].[transaction] AS [transaction], [NESTED_QUERY1582].[transactionline] AS [transactionline], [NESTED_QUERY1582].[amountlinked] AS [amountlinked], [NESTED_QUERY1582].[lastmodifieddate] AS [lastmodifieddate], [NESTED_QUERY1582].[processedbyrevcommit] AS [processedbyrevcommit] FROM [CData].[SuiteQL].[TransactionAccountingLine] AS [NESTED_QUERY1582] LIMIT 1000]
(D) Next, RealDB will cache all the metadata received from the source, storing it in the cloud. CData Connect Cloud has recently introduced a new "Jobs" feature that allows users to store metadata in their preferred database.
2024-05-24T17:32:46.672+00:00 2 [1222|Q-Id]
[RealDB:SQLServer]ColumnInfo Catalog:metadatacache, Schema:xxxxxxx-81ed-4419-bd87-xxxxxxxx, Name:Schema, TypeName:string, OriginalTypeName:nvarchar, TypeCode:16, Size:512, Precision:512, Scale:-1, IsKey:False
2024-05-24T17:32:46.672+00:00 2 [1222|Q-Id]
[RealDB:SQLServer]ColumnInfo Catalog:metadatacache, Schema:xxxxxxx-81ed-4419-bd87-xxxxxxxx, Name:Name, TypeName:string, OriginalTypeName:nvarchar, TypeCode:16, Size:512, Precision:512, Scale:-1, IsKey:False
……
2024-05-24T17:32:46.703+00:00 2 [1222|Q-Id]
[META|Schema] Retrieved column schema from MetaCache: [TransactionAccountingLine] Success: 18 results
(E) The HTTP request is then sent by the driver.
2024-05-24T17:32:46.765+00:00 2 [1222|Q-Id]
[HTTP|Req: 162] POST https://tstdrvxxxxx.suitetalk.api.netsuite.com/services/rest/query/v1/suiteql?limit=1&offset=0
2024-05-24T17:32:47.391+00:00 2 [1222|Q-Id]
[HTTP|Res: 162] HTTP/1.1 200 OK, 189 Bytes Transferred
2024-05-24T17:32:47.391+00:00 2 [1222|Q-Id]
[HTTP|Res: 162] Request completed in 686 ms.
(F) Once the HTTP request is successful, the log will display the number of bytes transferred, the total time taken to execute the query, and the number of results processed.
2024-05-24T17:32:47.391+00:00 2 [1222|Q-Id]
[EXEC|Page ] Page successful: [SELECT "NESTED_QUERY1582"."account" AS "account", "NESTED_QUERY1582"."accountingbook" AS "accountingbook", "NESTED_QUERY1582"."amount" AS "amount", "NESTED_QUERY1582"."amountpaid" AS "amountpaid", "NESTED_QUERY1582"."amountunpaid" AS "amountunpaid", "NESTED_QUERY1582"."credit" AS "credit", "NESTED_QUERY1582"."debit" AS "debit", "NESTED_QUERY1582"."exchangerate" AS "exchangerate", "NESTED_QUERY1582"."netamount" AS "netamount", "NESTED_QUERY1582"."overheadparentitem" AS "overheadparentitem", "NESTED_QUERY1582"."paymentamountunused" AS "paymentamountunused", "NESTED_QUERY1582"."paymentamountused" AS "paymentamountused", "NESTED_QUERY1582"."posting" AS "posting", "NESTED_QUERY1582"."transaction" AS "transaction", "NESTED_QUERY1582"."transactionline" AS "transactionline", "NESTED_QUERY1582"."amountlinked" AS "amountlinked", TO_CHAR("NESTED_QUERY1582"."lastmodifieddate", 'YYYY-MM-DD HH24:MI:SS TZH:TZM') AS "lastmodifieddate", "NESTED_QUERY1582"."processedbyrevcommit" AS "processedbyrevcommit" FROM "TransactionAccountingLine" "NESTED_QUERY1582"_cdata_separator_0_cdata_separator_True]
2024-05-24T17:32:47.393+00:00 2 [1222|Q-Id]
[HTTP|Req: 163] POST https://tstdrvxxxxx.suitetalk.api.netsuite.com/services/rest/query/v1/suiteql?limit=1000&offset=0
2024-05-24T17:32:49.465+00:00 2 [1222|Q-Id]
[HTTP|Res: 163] HTTP/1.1 200 OK, 5155 Bytes Transferred
2024-05-24T17:32:49.466+00:00 2 [1222|Q-Id]
[HTTP|Res: 163] Request completed in 2073 ms.
2024-05-24T17:32:49.466+00:00 2 [1222|Q-Id]
[EXEC|Page ] Page successful: 1000 results (2075 ms)
Log File Checklist
A log file contains a wealth of valuable information. Here are some initial recommendations for checking if the solution is not immediately evident:
- Verify the logged connection string and compare log files (of previously made connections) to identify any changes in the connection string properties.
- Examine the GET and POST requests made.
- Look for any error messages in the HTTP responses within the log.
- Analyze the performance by checking the time taken for the query to succeed.
- Investigate the reasons for any potential timeouts.
Try CData Connect Cloud Today
To get governed access to hundreds of SaaS, Big Data, and NoSQL sources for live data consumption and analysis with your favorite tools, sign up for a free 30-day trial of CData Connect Cloud!
