The following error or similar could be generated when loading data of type NCLOB or CLOB from an Oracle Data Source.

ERROR [HY000] [Qlik][OracleOCI] (3000) Oracle Caller Interface: ORA-00942:the table or view does not exist

The issue was found after upgrading from a previous Qlik Sense version (example QS February 2021 SR4; QS May 2021 SR2) to QS May 2022 SR1 and SR2

Environment

• Qlik Sense Enterprise May 2022

Resolution

- Defect QB-10789 was reported to R&D

- We noticed that only  fields of data type NCLOB or CLOB are affected

- A patch with the fix is planned to be delivered with the Qlik Sense August 2022 Patch 1 version

Information provided on this defect is given as is at the time of documenting. For up to date information, please review the most recent Release Notes, or contact support with the ID QB-10789 for reference.

Workaround:

A workaround could be a downgrade of Qlik ODBC connector package to the previous working version. Alternatively you can reach out to Qlik Support in order to get a temporary fixed ODBC connector package version 6.191 (a new official patch will be provided soon).

Cause

Product Defect ID: QB-10789

In this article, we will be walking through the basic troubleshooting steps required to look into Hadoop endpoint connectivity issues. 

These steps will focus on investigating two connections:

• HDFS connectivity
• HIVE connectivity

The Scenario

We have implemented two issues by blocking port 50070 and adding the wrong ODBC parameter for the Hive. The outlined troubleshooting steps will highlight how to identify these issues. 

In Replicate UI Go to Server Tab --> Logging --> Server Logging Levels and keep the COMMUNICATION and SERVER logging component to Trace during the troubleshooting.

As we begin, we can already see that the connection test fails when we click Test Connection. It fails with SYS-E-HTTPFAIL, Failed to connect to HDFS...

To view the Server logs go to Replicate UI --> Server Tab --> Logging --> Server Logging Levels --> View Logs --> Open the server log to see the below connectivity failed messages:

00011144: 2022-09-25T00:43:40:393877 [COMMUNICATION   ]T:  requested URL <http://10.51.2.44:50070/webhdfs/v1/?user.name=admin&op=GETFILESTATUS>, action is GET  (at_curl_http_client.c:504)

00011144: 2022-09-25T00:43:40:393877 [COMMUNICATION   ]T:  Clearing (if there is) curl old custom request  (at_curl_http_client.c:950)

00011144: 2022-09-25T00:43:40:393877 [COMMUNICATION   ]T:  calling user pre submit callback  (at_curl_http_client.c:1960)

00011144: 2022-09-25T00:43:40:393877 [COMMUNICATION   ]T:  user pre submit callback returned with status 0  (at_curl_http_client.c:1964)

00011144: 2022-09-25T00:43:40:393877 [COMMUNICATION   ]T:  preparing response structure; body will be written as dynamic string  (at_curl_http_client.c:1428)

00011144: 2022-09-25T00:43:40:395850 [COMMUNICATION   ]T:  AT_CURL->at_curl_easy_perform: curl_action_status == CURLE_OK failed  (at_curl.c:883)

00011144: 2022-09-25T00:43:40:395850 [COMMUNICATION   ]T:  Failed to curl_easy_perform: curl status = 7, Couldn't connect to server (Failed to connect to 10.51.2.44 port 50070: Bad access)  [1000143]  (at_curl.c:883)

The error Couldn't connect to server in the logs and Failed to connect in the UI indicates a connectivity issue and we will begin investigating there. 

1. Ping test.

This is a quick and common way to verify Hive and HDFS connectivity. It verifies if we have network connectivity at all. 

In a command line, we execute:

ping 10.51.2.44

Where 10.51.2.44 is the IP address of the destination server. 

Result:  PASS

2. Telnet Test

Depending on whether or not we are using Hive or HDFS, the ports used are different. It verifies if the port specified is being listened on.

In a command line, we execute:

telnet 10.51.2.44 50070

Where 10.51.2.44 is the IP address of the destination server. 

Result: FAIL

This indicates that port 50070 may not be available (blocked by a firewall, etc).

3. CURL Test

This test is only required for HDFS. We might need to use different authentication mechanisms with CURL based on the deployed authentication mechanisms (like Username\password, Kerberos with key tab, and Kerberos with AD Etc.,).

We must use the exact URL found in the Qlik Replicate log file.

In a command line, we execute:

curl -i -v "HTTP://10.51.2.44:50070/webhdfs/v1/user/admin/USER?user.name=admin&op?GETFILESTATUS"

Where 10.51.2.44 is the IP address of the destination server. 

Result: FAIL

Since both the CURL and Telnet tests failed, we look into port 50070 and identify that it has been blocked by a firewall. We enable the port and further tests show Telnet and CURL succeeding.

We can also verify the port using PowerShell and the following command:

Test-NetConnection -ComputerNamer 10.51.2.44 -Port 50070

This is an example of a successful CURL test:

However, when we test the connection in the UI, the connection still fails.

This time it displays a different error:

Cannot enable SSL for the connection when connecting to a server that has not enabled...

To view the Server logs go to Replicate UI --> Server Tab --> Logging --> Server Logging Levels --> View Logs --> Open the server log to see the below connectivity failed messages:

00028952: 2022-09-25T01:02:09:511445 [COMMUNICATION   ]T:  requested url <http://10.51.2.44:5 0070/webhdfs/v1/?user.name=admin&op=GETFILESTATUS>, action is GET  (at_curl_http_client.c:504)

00028952: 2022-09-25T01:02:09:511445 [COMMUNICATION   ]T:  Clearing (if there is) curl old custom request  (at_curl_http_client.c:950)

00028952: 2022-09-25T01:02:09:511445 [COMMUNICATION   ]T:  calling user pre submit callback  (at_curl_http_client.c:1960)

00028952: 2022-09-25T01:02:09:511445 [COMMUNICATION   ]T:  user pre submit callback returned with status 0  (at_curl_http_client.c:1964)

00028952: 2022-09-25T01:02:09:511445 [COMMUNICATION   ]T:  preparing response structure; body will be written as dynamic string  (at_curl_http_client.c:1428)

00028952: 2022-09-25T01:02:09:912486 [COMMUNICATION   ]T:  http response code is 200  (at_curl_http_client.c:1186)

00028952: 2022-09-25T01:02:10:127510 [SERVER          ]T:  going to connect using connection string <ssl=1;HiveServerType=2;Driver={Cloudera ODBC Driver for Apache Hive};HOST=10.51.2.44;PORT=10000;Schema=hive_lz;AuthMech=3;UID=hive;PWD={****};>  (hive_odbc_client.c:374)

00028952: 2022-09-25T01:02:10:546553 [INFRASTRUCTURE  ]T:  AR_ODBC_DRIVER_FUNC->ar_SQLDriverConnect returned -1  (ar_odbc_func.c:1117)

00028952: 2022-09-25T01:02:10:546553 [SERVER          ]T:  RetCode: SQL_ERROR  SqlState: HY000 NativeError: 1160 Message: [Cloudera][DriverSupport] (1160) Cannot enable SSL for the connection when connecting to a server that has not enabled SSL. If the server has SSL enabled, please check if it has been configured to use a SSL protocol version that is lower than what is allowed for the connection. The minimum SSL protocol version allowed for the connection is TLS 1.2. [1022502]  (ar_odbc_conn.c:584)

The logs confirm that the HDFS connection worked this time, but not the Hive connection failed.

We verify the Hive connection following the same method as before.

1. Ping test:

In a command line, we execute:

ping 10.51.2.44

Where 10.51.2.44 is the IP address of the destination server. 

 Result:  PASS

2. Port Test

This time we use PowerShell, but you can use telnet as well.

We will use port 10000.

In PowerShell, we execute:

Test-NetConnection -ComputerNamer 10.51.2.44 -Port 10000

Where 10.51.2.44 is the IP address of the destination server. 

Result: PASS

3.  ODBC Test

We will test the ODBC connection from the Qlik Replicate server:

Result: Pass

4. Review the Hive connection string

Locate the Hive connection string in the Qlik Replicate log and compare it with the DSN setup.

        ssl=1; HiveServerType=2;Driver={Cloudera ODBC Driver for Apache Hive};HOST=10.51.2.44;PORT=10000;Schema=swathi_lz;AuthMech=3;UID=hive;PWD={****};

The log indicates that we are using SSL.

But as we review the Hive DSN Setup, we can see that we have SSL unchecked:

Result: Everything matched other than ssl=1.

Let’s review and fix the endpoint settings in Qlik Replicate and perform the test connection by removing the internal parameter of ssl=1. 

But what if we receive different error messages?

Generally, driver issues can lead to blockers for Hive connections. If you locate a different error in the UI or the log files, research these errors using either Google or our knowledge base here in the Community, as fixes may already exist. 

Environment:

Qlik Replicate 
Hadoop Target

In some cases when Replicate can't read from the Staging Folder of a LogStream task for whatever reason (corrupted folder, lack of disk space etc), there could be difficulties to resume the LogStream task after the initial issue is solved.

You might see errors like the below when trying to resume from timestamp:

[UTILITIES ]E: Failed to write to audit file  <audit_folder directory>
[UTILITIES ]E: Timeout while waiting to get data from audit file [1002521] (at_audit_file.c:637)

[UTILITIES ]E: Error reading audit batch [1002509] (at_audit_file.c:679)

Environment

• Qlik Replicate 2021.11 (not exclusive)
• Linux RHEL / Windows 

Resolution

1. Stop all LogStream and Replication tasks
2. Kill the replicate sessions and process as the audit file is being locked by the process
3. Rename the audit folder of the problematic audit file (the folder is the one described under the endpoint settings: "Staging Folder" in the Replicate UI)
4. Resume LogStream task from timestamp (a few hours before the initial error), then resume replication tasks from the same timestamp 
5. If not solved, a reload of the task will be needed

Cause 

Usually, the Replicate process (repctl) is locking the audit file that was being written/read while the issue occurred.

While running Qlik NPrinting Tasks, many reports are generated. However, the setting for Days to Keep / Reports to Keep seems to be ignored.

Environment:

Qlik NPrinting all versions

The first thing to consider is that Qlik NPrinting generates one report for each Newsstand user. For this reason, there will be one file for each user, and not "just one", in the Qlik NPrinting Application Data folder. Those reports may be absolutely identical. 

In that sense, the "report to keep" setting does not apply here, and should be considered as "report to keep per user". There is currently an improvement at R&D. Check improvement on days to keep in Nprinting for details. In addition, see Publish task "Reports to Keep" does not work during first 24 hours.

As for the setting apparently being ignored, by default runs the check to delete old reports once every 24 hours. If no new report has been run in the meantime, the old reports will not be deleted, even if more than one day has passed.

Should there be a feeling that the periodic check is not run, the settings can be enforced:

1. Stop the Qlik NPrinting services Proper Order to Restart Qlik NPrinting Services
2. Navigate to and open  C:\Program Files\NPrintingServer\NPrinting\Scheduler\scheduler.config
3. Add the below lines before the </appSettings> closing tag
   

   <add key="cleanup-period" value="1800" />
   <add key="cleanup-period-files" value="43200" />​

4. Restart the services

The first setting is the recurrence in seconds (default: 30 minutes) for the task that will check for reports in the newsstand and unlink the older ones.
The second setting is the recurrence in seconds (default: 24 hours) for the task that will scan and remove the actual files, after checking that the conditions about days and reports to keep, plus the presence of newer reports that supersede them, are met. In this case, we would change the default value to a shorter time (43200 seconds, 12 hours) to make sure the task is run more frequently.

Qlik Sense Util comes bundled with Qlik Sense Enterprise on Windows. It is able to assist in the backing up process to help with the manual process.

Key elements that need to be backed up are: 

• the certificates used by Qlik Sense for internal communication
• the Repository Database used to store meta-data about the Qlik Sense site
• the applications used by the Qlik Sense site
• the Extensions, thumbnail images, etc used by Qlik Sense apps

Other options components can include:

• Logs of activity on the site
• Supplemental material used by the Qlik Sense site (e.g. QVDs, Excel files, etc)

By using the QlikSenseUtil tool, an administrator can back all of these up and configure the tool to be integrated in a silent, incremental backup policy.

How to get started with QlikSenseUtil

1. Locate the QlikSenseUtil tool
   
   
   • Default location: C:\Program Files\Qlik\Sense\Repository\Util\QlikSenseUtil\QlikSenseUtil.exe
   • Custom path example: Drive:\InstallationPath\Repository\Util\QlikSenseUtil\QlikSenseUtil.exe
     
     
2. In Notepad or any text editor of your choice, construct the desired command line syntax for the backup.
   
   
   • A sample command:
     

     C:\"Program Files"\Qlik\Sense\Repository\Util\QlikSenseUtil\QlikSenseUtil.exe -backup -databaseHostname="FQDNofTheDatabase" -databasePassword="SuperUserPassword" -path="Drive:\BackupLocation" -rootPath="\\ServiceCluster\Path"​

   • For a full listing of the flags possible, consult later in this article or the Readme.txt in the QlikSenseUtil directory.
     
     Note: Executing a backup will stop Qlik Sense services, so only execute this tool when downtime is acceptable
     Note: Executing as the Qlik Sense service account is recommended since the client certificate used by Qlik Sense is only accessible to the service account
     Note: For silent backups, do remember to use the -f flag
     Note: Logs for this tool will be written to %USERPROFILE%\AppData\Local\Temp\logs for the account executing the tool
     
     
3. Once the command is written out, launch a command prompt and paste in the command
4. Observe the files backed up to the path specified in the path flag
   
   

Video Walk-through:

Flags:

backup: specifies a backup operation (mandatory)
path: path to the folder where to store the backup (mandatory)
certificatePassword: password for protecting the certificates. If not specified then the certificates will not be backed up
databaseHostname: Hostname where the database is running (mandatory)
databasePassword: Superuser or postgres account password (mandatory, if password was configured)
databasePort: port number used by the database)
f: silent backup
h: help

To restore your environment, see Backup and restore Qlik Sense Enterprise on Windows.

Related Content:

• Qlik Sense Utility - Functions and Features 
• Backup and restore Qlik Sense Enterprise on Windows 

Three requirements need to be met in order to allow Anonymous users access to the Hub:

• The product must not be Qlik Sense Business or Qlik Sense Enterprise SaaS.
• The license must support anonymous access, see Which Qlik Sense Enterprise licenses support anonymous access?
• Proxy must be configured to allow Anonymous users
• A Stream dedicated to anonymous users must be created 

Useful information:

• Users who access Qlik Sense anonymously are identified as NONE\anonymousd9e61933-a7e5-4836-8540-5b137a600e69, where the GUID is a unique identifier.
• To create a license rule, granting login passes to anonymous users you can use two identifiers:
  • userDirectory=NONE 
  • user.IsAnonymous()

Configure the Proxy to allow Anonymous users

By default, anonymous access is disabled. The steps provided require a restart of the Proxy service after completion. 

1. Open the Qlik Sense Management Console.
2. Choose the Virtual Proxy to be used for anonymous access
3. Click Edit.
4. In the Properties menu (right side) choose Authentication
5. From the Anonymous access mode drop-down select Allow anonymous users
   
   
   
   

Set up a license rule 

Anonymous users will need to have a license rule available for them which gives them either an Analyzer Capacity License or a Login Access Token. The instructions in this article will focus on Analyzter Capacity Licenses. 

1. Open the Qlik Sense Management Console.
2. Navigate to License Management 
3. Click Analyzer capacity rules 
4. Click Create new 
5. Give the new rule a Name (mandatory) and Description (optional) 
6. Create one of two possible rules:
   1. In the Basic settings, select
      user: userDirectory =
      value: NONE
      
   2. In the Advanced settings (see Fig 2), provide the condition:
      user.IsAnonymous()
      
      Fig 2

       

7. Click Apply

Set up a Stream for anonymous access

You can use the default Everyone stream, which has already been set up for anonymous access, or create your own. 

We will create two rules. One to allow logged in (authenticated) users access and publishing permissions. One to allow anonymous user access, but no publishing permissons. Note that based on your requirements, this may need to modified. 

1. Go to the Qlik Sense Management Console
2. Go to Manage Content and Streams
3. Click Create New
4. Name your new Stream
5. Click Security Rules 
6. Click Create associated rule (see Fig 2) 
   This will be our "Everyone (anonymous) can read the stream." rule.
   
   Fig 3
7. Provide a Name (mandator) and Description (optional) 
8. In Basic only select Read 
9. In Advanced define
   Condition: user.IsAnonymous()
   Context: Only in hub
   
   See Fig 4. 
   
   Fig 4

    

10. Click Apply 
11. Click Create associated rule (see Fig 2)
    This will be our "Everyone (logged in) can access this stream and publish." rule.
12. Provide a Name (mandator) and Description (optional) 
13. In Basic only select Read and Publish.
14. In Advanced define
    Condition: !user.IsAnonymous()
    Context: Both in hub and QMC
15. Click Apply

Test!

We are now ready to test access. 

1. Go to the Qlik Sense Hub (hosted by the Virtual Proxy you have configured)
2. If you are logged in, log out.
   
   
   
   
3. Our new Anonymous stream is now avaiable.

Getting data out of Qlik Sense Enterprise SaaS, and distributing it to different users in formatted Excel, has been a manual task until now.

Thanks to the release of Qlik Application Automation, it is now possible to automate this workflow by leveraging the connectors for Office 365 - specifically Microsoft SharePoint and Microsoft Excel. 

Here I share two example QAA workspaces that you can use and modify to suit your requirements. 

Video:

Considerations

• QAA cannot currently attach files using the Mail block,  instead this approach distributes a SharePoint link.
• QAA has a limit of 100,000 rows when getting data out of a Qlik Sense straight table object.
• The On-Demand example uses an extension in QSE SaaS to send data to the Automation. An update to the Qlik Sense Button object is expected soon, which will provide a native way to pass selections to an Automation.

Example 1 - Scheduled Reports

1. Download the 'Scheduled Report.json' file attached to this document.
2. Create a new Automation in QSE SaaS, give it a name, and then upload the workspace you just downloaded by right clicking in the editor canvas, and selecting 'Upload workspace'.
3. Select the 'Create Binary File (Personal One Drive)' block, select 'Connection' in the block configurator to the right, and then create your connection to Microsoft SharePoint.
4. Select the 'Get Straight Table Data' block. Under 'Inputs' in the block configurator, lookup your the App Id, Sheet Id, and Object Id for the relevant QSE SaaS table you wish to output.
5. Select the 'Create Excel Table With Headers' block, select 'Connection' in the block configurator, and then create your connection to Microsoft Excel.
6. Select the 'Send Mail' block. Under 'Inputs' in the block configurator update the 'To' to reflect the addresses you wish to deliver to.
7. With the 'Send Mail' block still selected, select 'Connection' in the block configurator and add your Sender details.
8. To test, Save and then Run the Automation
9. If you receive any warnings or errors, navigate to the relevant blocks and ensure your Connection is selected in the block configurator. 
10. Select the 'Start' block. Under 'Inputs' in the block configurator, change Run Mode to Scheduled and define your required schedule. 

Example 2 - On-Demand Reports

Note - These instructions assume you have already created connections as required in Example 1.

1. Download the 'On-Demand Report v3.json' file attached to this document.
2. Download and install the 'qlik-blends' extension. See: 
   https://github.com/rileymd88/qlik-blends/files/6378232/qlik-blends.zip
3. Create a new Automation in QSE SaaS, give it a name, and then upload the workspace you just downloaded by right clicking in the editor canvas, and selecting 'Upload workspace'.
4. Ensure your Connections are selected in the block configurator for each of the following blocks, 'Create Binary File (Personal One Drive)', 'Create Excel Table With Headers', 'Add Rows To Excel Worksheet Table (Batch)', 'Create Sharing Link', and 'Send Mail'.  Save the Automation.
5. Select the 'Start' block and ensure Run Mode is set to Triggered. Make note of the of URL and Execution Token shown in the POST example. 
6. Open your chosen QSE SaaS application, and Edit the Sheet where you wish to add a Button to trigger an On-Demand report. 
7. Under 'Custom Objects' look for 'qlik-blends' from the Extensions menu and drag this into your Sheet.
8. Under the 'Blend' properties to the right, add-in your POST webhook URL and Token as noted in Step 5.
9. We will now add three measures to the 'qlik-blends' object. It is important you add them in the order described.  Add the first measure, using the following function in the expression editor: GetCurrentSelections()
10. Add the second measure, using the following function DocumentName()
11. The final measure will be the Object ID of the table you wish to use. To find the Object ID, select 'Done Editing'.  Then right click on the table, select 'Share', select 'Embed', then look for the Object ID under the preview. Copy this value, go back into Editing mode and paste this as your third measure value. 
12. With the 'qlik-blends' object selected, under Form select 'Add items'. For 'Item type' select Text. Under default value you can choose to add a default email address. For 'Label' and 'Reference'  type 'Email'. It is critical that Reference is updated to 'Email'.  Turn 'Required input' on. 
13. You can change the Appearance properties to suit your preferences, such as updating the Button label and message, enabling Dialog, and changing the Color under Theme.
14. Back in the Automation, under the Start Block. Set  'Run asynchronously' = yes to allow multiple requests to run at the same time (This will also increase the max run time from 1min to 60min)
15. Once happy, test the On-Demand report by entering an email and clicking the button.

This On-Demand Report Automation can be used across multiple apps and tables.  Simply copy the extension object between apps & sheets, and update the Object ID (Measure 3) for each instance. 

Environment

• Qlik Application Automation 
• Qlik Cloud 
• Microsoft Office 365

The information in this article is provided as-is and to be used at own discretion. Depending on tool(s) used, customization(s), and/or other factors ongoing support on the solution below may not be provided by Qlik Support.

Monitoring App reload fails with:

Error message: Error: QVX_UNEXPECTED_END_OF_DATA: HTTP protocol error 401 (Unauthorized): Requested resource requires authentication.

The error happens when the Qlik Sense services account does not match the account used in the reload task.

Resolution:

The data connections must be updated accordingly with the Qlik Service User ID and Password.

• Open the Qlik Sense Management Console
• Navigate to Data Connections 
• Locate monitor_apps_REST_ data connection
• Click Edit
• Enter the correct User ID and Password (as seen in Fig 1)
• Save the changes.

Fig 1

This article gives an overview of the Splunk Connector in Qlik Application Automation.
The Splunk connector has been created with the following use cases in mind:

• Being able to perform searches to a Splunk instance
• Inserting new events into Splunk using the HTTP Event Collector

Connecting the Splunk connector

The Splunk connector makes use of basic authentication. When connecting the connector, you will be prompted with the following popup screen:

Hostname: The hostname or IP address of the Splunk instance. Exclude the http(s):// part and the port number of the URL.
Username: The username you use to login to Splunk
Password: The password you use to login to Splunk
Use SSL: Select whether the management service of Splunk makes use of SSL. Default yes.
Management port: The port on which the management service of Splunk is running. Default is 8089. Providing no value will omit the port from the final URL.

Performing a search on Splunk

To perform searches using the Splunk connector it is possible to perform a synchronous search as well as an asynchronous search. The synchronous search has a timeout of 50 seconds, with the asynchronous search it is possible to have a longer window before the automation will timeout.

Synchronous Search

The synchronous search can be done using the Synchronous Search block. In the Search Query block you will have to provide a splunk search query.

Asynchronous Search

The asynchronous search block can be manually done using the block Asynchronous Search block. Then perform polling using the Get Search block using the returned Search ID. When finished, results can be obtained using the Get Results From Search block.

Alternatively it is possible to use the block Do Asynchronous Search and get Results. This block will perform the polling, only a search query has to be provided to obtain a list of results.

Inserting data into Splunk

The other use of the Splunk connector is to insert data into Splunk by making use of the HTTP Event Collector. An example usecase for this would be to send reload logs of a Qlik Sense app into Splunk.
To do this we will first have to create an HTTP Event Collector on the Splunk side.

To do this in Splunk go to Splunk in the web browser and go to Settings > Data > Data Inputs.
On this page you will see Http Event Collector. Press the Add new button.

Provide a name for the token and go to the next page. Select indexes to which this token can write. Click on Review and Submit. You will be given a token that you need to configure on the block.

In Qlik Application Automation make use of the Insert Data block. For the inputs, enter the HEC token value. Provide the port for the HTTP event collector. This can be obtained from the Splunk HTTP Event Collector page when clicking on the Global Settings button. You can also find whether SSL is enabled for the HTTP Event Collector, select this value for the Hec_Use_Ssl parameter. Next provide the index to which you're writing your events and provide a source string.
For data, you can send a JSON message. Event is a mandatory key to write in this JSON message.

The following example will perform a reload and send the reload log to Splunk:

In Splunk it is then possible to view the logs of the reload:

The information in this article is provided as-is and to be used at own discretion. Depending on tool(s) used, customization(s), and/or other factors ongoing support on the solution below may not be provided by Qlik Support.

In Qlik Saas, when adding an image <URL>  https://play-lh.googleusercontent.com/ahJtMe0vfOlAu1XJVQ6rcaGrQBgtrEZQefHy7SXB7jpijKhu1Kkox90XDuH8RmcBOXNn as a column to a table,  we expected this to fail because it should require  "img-src" directive to be checked in "Content Security Policy".

However, the logo with image <url> above is displayed correctly (see logo dispayed as Dim1 column in below screenshot ).

Adding a new column 'Alpha' with image <url>  https://encrypted-tbn0.gstatic.com/images?q=tbn:ANd9GcTaANtept27EFdcIQ62k2W2VCkpXSq0iDxT4w&usqp=CAU  does not display the expected image.

Environment

• Qlik Sense SaaS

Resolution

From a close look to console tab (of dev-tool), we noticed that some image <urls> are recognized by default (see above attached screenshot). They do not require the use of directive "img-src ".

Refused to load the image '<URL>' because it violates the following Content Security Policy directive: "img-src 'self' data: maps.qlikcloud.com ibasemaps-api.arcgis.com cdn.pendo.io app.pendo.io pendo-static-5763789454311424.storage.googleapis.com data.pendo.io *.gravatar.com *.wp.com *.googleusercontent.com cdn.qlik-stage.com cdn.qlikcloud.com upload.wikimedia.org".

A documentation request HLP-13324 was submitted to R&D .

Cause

Product Defect ID: HLP-13324

Snowflake supports using key pair authentication for enhanced authentication security as an alternative to basic authentication (i.e. username and password). This article covers end-to-end setup for Key Pair Authentication in Snowflake and Qlik Replicate.

This authentication method requires, at minimum, a 2048-bit RSA key pair. You can generate the Privacy Enhanced Mail (i.e. PEM) private-public key pair using OpenSSL.

Qlik Replicate will use the ODBC driver to connect snowflake and ODBC is one of the supported clients which will support key pair authentication.

Let's assume, you decided to use key pair authentication for the Snowflake user which is used in Qlik Replicate to connect to Snowflake. You have to follow the below process to convert user authentication from basic to key pair.

Step 1: Generate the Private Key

You can generate either an encrypted version of the private key or an unencrypted version of the private key.

To generate an unencrypted version use the following command in the command prompt:

$ openssl genrsa 2048|openssl pkcs8 -topk8 -inform PEM -out rsa_key.p8 -nocrypt

To generate an encrypted version (which omits -nocrypt) use:

$ openssl genrsa 2048|openssl pkcs8 -topk8 -v2 des3 -inform PEM -out rsa_key.p8

In our example case, we generate an encrypted version of a private key.

We:

1. Open a command prompt
2. Run $ openssl genrsa 2048|openssl pkcs8 -topk8 -v2 des3 -inform PEM -out rsa_key.p8
3. Enter our password
4. And store the password to use in Qlik Replicate.

This generates a private key in PEM format:

Step 2: Generate a Public Key

From the command line, we generate the public key by referencing the private key. The following command assumes the private key is encrypted and contained in the file named rsa_key.p8.

When it requests a passphrase, use the same password that we generated in step 1.

openssl rsa -in rsa_key.p8 -pubout -out rsa_key.pub

This command generates the public key in PEM format:

Step 3: Store the Private and Public Keys Securely

Copy the public and private key files to a local directory for storage and record the path to the files. Note that the private key is stored using the PKCS#8 (Public Key Cryptography Standards) format and is encrypted using the passphrase you specified in the previous step.

However, the file should still be protected from unauthorized access using the file permission mechanism provided by your operating system. It is your responsibility to secure the file when it is not being used.

Step 4: Assign the Public Key to a Snowflake User

Describe the user to see current information. We can see that there is no public key assigned to the HDW user. Therefore, the user needs to use basic authentication.

Execute an ALTER USER command to assign the public key to a Snowflake user.

Step 5: Verify the User’s Public Key Fingerprint

Execute a DESCRIBE USER command to verify the user’s public key.

Step 6: Configure Qlik Replicate to Use snowflake Key Pair Authentication

1. Enter the snowflake server name and the username that we configured key pair authentication.
   
   
   
   
2. We need to set the below parameters to set the authentication method as SNOWFLAKE_JWT, passing the private key and password from step 1.
   
   
   
   
3. Try to do the test connection\browse the database and you can see a missing password error. We can't ignore the password in the endpoint UI and at the same time, we shouldn't enter the password as we are using keypair authentication.
   
   
   
   
4. You can key in a dummy password, such as dummy, dummy123, etc., in the UI to eliminate the missing password error.
   
   Finally, browse the database and do the test connection.
   
   

Environment

Qlik Replicate 
Snowflake Target

When buying a QlikView Client Managed license with SaaS add-on together with a Qlik Sense Enterprise license for the cloud, changes applied to one of the environment are not reflected in the other ones.

So, it is not possible to understand, from one single window, how many Professional and Analyzer seats have been assigned in total. Customers need therefore to check both environments and count the assigned license manually.

Environment

• Qlik Cloud 
• QlikView, any

Resolution

As of September 2022, the QlikView with-SaaS-add-on and the Qlik Cloud licenses are tied only by contractual agreement.
There is no technical integration between the two.

It's the customer's contractual responsibility to make sure that the number of allocated professional and analyser licenses are not exceeded. As of now, there is no technical functionality, for the customer, at the moment, to cross-verify the two serial numbers or to block allocating more seats than what was agreed contractually.

Introduction:

For binary classification models, AutoML will generate a confusion matrix after running a model experiment. This represents the True Positives, True Negatives, False Positives (model predicted true but target value was false), and False Negatives (model predicted false but target value was true). Classification explanation here. 

Explanation:

The confusion matrix is calculated from the hold out set for data training.

During the model training phase, the tool automatically splits data between training and hold out sets on a 80/20 basis. 

The training data is split through a k fold iteration and trained on multiple samples.  The hold out dataset is held to the end of this process and then the model is run against this to generate the model metrics and confusion matrix.  You can think of the hold out as showing how the model performs against something it has never seen before.

Environment

• Qlik AutoML 

The information in this article is provided as-is and to be used at own discretion. Depending on tool(s) used, customization(s), and/or other factors ongoing support on the solution below may not be provided by Qlik Support.

This Support article covers functionality available in the new Qlik PostgreSQL Installer (QPI), which allows you to upgrade an existing Qlik Sense Repository Database (QRD) from PostgreSQL 9.6 to PostgreSQL 12.5, or install a brand new QRD based on PostgreSQL 12.5.

PostgreSQL 9.6 has reached End of Life Reference: PostgreSQL Versioning Policy 

>> Qlik PostgreSQL Installer - Download Link <<

Environment

• New Qlik Sense Repository Database (QRD) setups for, compatible with Qlik Sense Enterprise Client Managed since November 2019 - Reference: Qlik Sense's compatibility with PostgreSQL for the... - Qlik Community - 1716510
• Existing QRD setups running Qlik Sense embedded PostgreSQL 9.6 (Qlik Sense February 2022 and higher)

Supported Scenarios

• Upgrading an existing QRD from PostgreSQL 9.6 to 12.5. Upgrading earlier versions of QRD is not supported via this method. Instead, upgrade to a version of Qlik Sense that supports 9.6 to automatically migrate through this minor version - List of supported PostgreSQL and early Qlik Sense versions
• Installing a new QRD cluster on a new server, for a new environment or to migrate an existing Database to a separate host.
• Qlik Sense Enterprise on Windows February 2022 and above are supported versions for QPI. Upgrade to a supported version for QPI first and then use QPI to upgrade PostgreSQL.

Considerations

• This installer will not detect a standalone PostgreSQL database installed by any other means, other than through an official Qlik installer package
• The installer itself does not provide a rollback feature, this is described below
• The user who runs the installer has sufficient permissions to execute it and install services
• The user who runs the installer has sufficient permissions to create folders and files in the backup destination
• The backup destination has sufficient free disk space to dump the existing database 
• The backup destination is not a network path or virtual storage folder. It is recommended the backup is stored on the main drive
• There will be downtime during this operation, please plan accordingly

Installing a new Qlik Sense Repository Database using PostgreSQL 12.5

1. All services on rim nodes must be stopped
2. Run the Qlik PostgreSQL Installer 
3. Click on "Install"
4. Accept the Qlik Customer Agreement
5. Set your "Local database settings" and click "Next". You will use these details to connect other nodes to the same cluster
6. Set your "Database superuser password" and click "Next"
7. Review your settings and click "Install", then click "Finish"
8. Restore your existing QRD backup OR
9. Start installing Qlik Sense Enterprise Client Managed, connecting to an existing cluster 

A new service will now be present in your Services management console: 

If you are migrating your existing databases to a new host, please remember to reconfigure your nodes to connect to the correct host. How to configure Qlik Sense to use a dedicated Pos... - Qlik Community - 1791775

The tool will display information on the actions being performed. Once installation is finished, you can close the installer. 

The PostgreSQL package can be removed via the Control Panel in case you need to uninstall the database at one point. 

Upgrading an existing QRD to PostgreSQL 12.5 - Central Node

It is recommended that you first upgrade Qlik Sense to a version that supports QPI, starting with the February 2022 release. More information on Qlik Sense and PostgreSQL compatibility.

Installations other than in C:\Program Files\Qlik\Sense are not supported at the moment.

In this scenario, the new QRD will be using the same port as the old service, and rim nodes do not need any further reconfiguration. 

1. Stop all services on rim nodes
2. On your Central Node, stop all services but Qlik Sense Repository Database
3. Run the Qlik PostgreSQL Installer, an existing Database will be detected
4. Click on "Upgrade"
5. Accept the Qlik Customer Agreement
6. Provide your existing "Database superuser password" and click "Next"
7. Set your desired backup location and click "Next"
8. Click "Upgrade", then exit by clicking "Finish"
9. Start all other services on Central Node
10. Start all services on all other nodes

Final steps

At this point, it is important to validate that all services and nodes are operating as expected. If it seems like some data is missing from the QMC or elsewhere, you may revert back to the old database instance. To do this, stop the newly installed "postgresql-x64-12" service and set its startup type to Manual. Then start the original "Qlik Sense Repository Database" service, along with all other services. 

Once the upgrade and all validation is complete, the old Qlik Sense Repository Database service should be removed on the server. 

Perform the following steps to delete the Qlik Sense Repository Database Service

c:\cd ProgramData\Package Cache
c:\ProgramData\Package Cache\dir /s PostgreSQL.msi
The folder containing PostgreSQL.msi will be revealed.
Right click on the PostgreSQL.msi file and select uninstall from the menu.

This version is fully compatible with the official PostgreSQL installers from https://www.enterprisedb.com/downloads/postgres-postgresql-downloads 

If you need to upgrade to a higher release than Qlik provides you can install the same major PostgreSQL release 12.x or up to version 13.

Troubleshooting

If the installation crashes, server reboots unexpectedly during this process or there is a power outage, the new database may not be in a serviceable state. Installation / upgrade logs are available in your temporary files location, for example: 

C:\Users\Username\AppData\Local\Temp\2

A backup of the original database contents is available in your chosen location, or by default in:

C:\ProgramData\Qlik\Sense\Repository\PostgreSQL\backup\9.6

The information in this article is provided as-is and to be used at own discretion. Depending on tool(s) used, customization(s), and/or other factors ongoing support on the solution below may not be provided by Qlik Support. The video in this article was recorded in a earlier version of QPI, some screens might differ a little bit.

>> Qlik PostgreSQL Installer - Download Link <<

Related Content

Techspert Talks - Upgrading Qlik Sense Repository Service
Backup and Restore Qlik Sense Enterprise documentation
Migrating Like a Boss
Optimizing Performance for Qlik Sense Enterprise
Qlik Sense Enterprise on Windows: How To Upgrade Standalone PostgreSQL
How-to reset forgotten PostgreSQL password in Qlik Sense
How to configure Qlik Sense to use a dedicated PostgreSQL database
Troubleshooting Qlik Sense Upgrades

 Click here for video transcript

Qlik Download page in Community does not show the expected product and download page. 

The web page and browser console display errors messages referring to HTTP 401 Unauthorized access, which may look similar to below examples. 

• {"errors":[{"title":"State verification failed","detail":"State not valid, missing request forgery protection","code":"STATE-1","status":"401"}],"traceId":"{ID}"}
• VM624:1 GET https://download.us.qlikcloud.com/api/v1/users/me 401
• VM624:1 GET https://download.us.qlikcloud.com/api/v1/csrf-token?qlik-web-integration-id={ID} 401

Environment

• Qlik Download page; https://community.qlik.com/t5/Downloads/tkb-p/Downloads 

Resolution

Qlik Download page in Qlik Community requires 3rd party cookies as part of the current web integration. The accessing browser must allow must allow 3rd party cookies while accessing the Qlik Downloads page, in order for the page to render successfully. 

The browser does not have to completely allow 3rd party cookies, but can also just allow 3rd cookies for the *.qlik.com or community.qlik.com domain. 

The cookie settings are browser-specific, please consult browser help for more details. 

Cause

Qlik Download page in Qlik Community is composed by an embedded object that is hosted in Qlik Cloud. This means that cookies for the user session are associated with two different domains, community.qlik.com and qlikcloud.com.  The browser refer to the parent page (community.qlik.com) as a 1st party cookie, while the enmbedded content from a different domain (qlikcloud.com) is referred to as a 3rd party cookie. 

3rd party cookies may be blocked in a browser as a mechanisms to block user tracking and advertisment. Browser incogniro mode may also block 3rd party cookies by default as part of keeping the user more anonmyous. 

Related Content

• Google Chrome: https://support.google.com/chrome/answer/95647?hl=en&co=GENIE.Platform%3DDesktop 
• Firefox: https://support.mozilla.org/en-US/kb/third-party-cookies-firefox-tracking-protection 
• Microsoft Edge: https://support.microsoft.com/en-us/windows/microsoft-edge-browsing-data-and-privacy-bb8174ba-9d73-dcf2-9b4a-c582b4e640dd 
• Apple Safari: https://support.apple.com/en-au/guide/safari/sfri11471/mac 

In Qlik Sense, the thousands and decimal separator must be set as comma and point by default (as shown below),  while using the SAP extractor connector, to avoid a non-automatic conversion of values, when a field for instance is a CHAR (255) that contains floating point numbers.

SET ThousandSep=',';
SET DecimalSep='.';
SET MoneyThousandSep=',';
SET MoneyDecimalSep='.'; 

Environment:

Qlik Sense Enterprise on Windows 
Qlik SAP Connectors 

Internal Investigation ID(s):

QB-14332

This Techspert Talks session addresses the following:
- How Qlik Replicate Works
- Understanding Log Files
- Quickly Resolving Issues

00:00 - Intro

01:02 - What Qlik Replicate does

01:41 - How to create a task

05:02 - Troubleshooting Full Load error

06:06 - Analyzing the task logs

08:54 - Setting the correct schema in task settings

11:49 - Troubleshooting Change Data Capture (CDC)

13:38 - Solution in Mainframe

15:10 - Solution in Replicate

16:34 - Testing Data Capture Changes

18:03 - Q&A: How to do Replication without Primary Keys?

19:05 - Q&A: How to fix missing records?

19:58 - Q&A: What are best practices for Full Load and CDC?

21:03 - Q&A: Where is help for installing Replicate?

21:46 - Q&A: What about communication error?

22:49 - Q&A: Can Replicate have multiple tasks with same Source?

23:31 - Q&A: Where to find driver version info?

24:43 - Q&A: How to know the build number?

25:43 - Q&A: What is the N column in SQL error message?

26:38 - Q&A: Where is Kafka endpoint documentation?

27:28 - Q&A: How to find the table ID referenced in the logs?

28:16 - Q&A: What is restarting with Advanced Run Options?

28:50 - Q&A: What is Resume Processing?

29:12 - Q&A: Can you have multiple tasks pointing to the same target?

29:31 - Q&A: What log files are there?

30:21 - Q&A: Where to turn on Verbose logging?

31:19 - Q&A: How to get error-triggered verbose logging?

32:36 - Troubleshooting Recommendations

Resources:

Qlik Replicate User Guide (Help.Qlik.com)

Installing Qlik Replicate documentation

Troubleshooting CDC Missing Data

Troubleshooting Missing Data Durring Full Load

Adding and managing End Points - documentation

Release notes

Ideation

Q&A:

Q: Is it possible to convert to consumable data? 

A: Further research is needed. Can you open a case and give an example so that we may thoroughly answer your question? 

Q: Sometime log files will be missing? Have you ever had that kind of issue? Did customers ever report that?

A: Yes, there can be multiple reasons for missing database logs. Sometimes it is due to a situation where failovers occur and archive logs are not available on the other node, or in DB2 LUW they can become unavailable if not managed properly by the log management facility for archiving and de-archiving, in a timely fashion. Sometimes there have been IBM DB2 Patched needed to be applied. Definitely search the Community and if there are no solutions there then open a case and attach a Diagnostic Package. This article should be helpful: What information to provide when troubleshooting missing data?

Q: I don’t see the option to automatically enable data capture change from the Replicate side. Is that option only in db2 source endpoint? 

A: Yes, this is fore DB2 Z/OS and DB2 LUW endpoint. N. Not for the DB2 iSeries endpoint. It is not in other Sources like Oracle or SQL Server. 

Q: Hi, is there is an option to find out if Replicate was able to replicate a specific record based on specific key? Do we need to enable control tables for it? 

A: The Source_Capture set to Verbose may help as well as other Components may need Verbose Logging. It really depends on the Source type. You can try setting DATA_RECORD to verbose. Unfortunately, it does not allow you to select by Key, but you turn on the Store Changes (in Task Settings) to help expose the data records and their keys and find it in the target for a table called “your schema.tablename__CT” 

Q: I have a specific problem when I activate the CDC, it shows the error.

"Cannot initialize subtask. Failed while preparing stream component 'st_0_SqlServer2019-111'. Please add TRACE logging to the component that is showing that message.

RetCode: SQL_ERROR  SqlState: 42000 NativeError: 20028 Message: [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]The Distributor has not been installed correctly. Could not enable database for publishing. Line: 1 Column: -1 Failed (retcode -1) to execute statement: 'exec sp_replicationdboption @dbname = N'Northwind', @optname = N'publish', @value = N'true''

A: Yes this message is a SQL Server Message. I would first check with the SQL Server DBA on what the message means and search the Community for what other customers did to resolve the issue. Also, you can add TRACE logging to the component that is showing that message. Rerun the task and if it does not show enough information then open a case and attach the Diagnostic Package. 

Q: I had a "snapshot too big" issue on one of my task when using Oracle as a source. Is replicate doing a snapshot of the table to help it keep track of the changes happening on the table during the full load ? 

A: Yes, search the community for that error message. See if these article might be helpful: Loading a big table in Qlik Replicate or ORA-12899: value too large for column

Q: I am seeing now and then some records get added into apply_expection table, how to debug the reason - any specific log level to be increased from Info. 

A: YES, you can change the Target_apply to trace or verbose as needed BUT ONLY keep the extra logging on for a FEW MINUTES ONLY and then set it back to INFO when done. Then you can examine the contents of the attrep_apply_changes as it will have the INSERT/UPDATE/DELETE and the associated erro message. Depending on the error search the Community and ask your DBA, then if you cannot resolve it then open a case and attach the DIAGNOSTIC PACKAGE to the case. 

Q: Is the troubleshooting process similar in cases where Logstream is used for CDC? 

A: Yes, it is the exact same. Great question! View the logs search for the problem, add TRACE or VERBOSE logging only for a few minutes during the problem, then put the logging back to INFO. Search the community for the messages. If you cannot find the resolution and if it is a SQL error you can either Google it and discuss it with your DBA, or if needed, open the case and attach the Diagnostic Package to the case. 

Q: Which material should we go through in order to prepare for the qlik replicate certification? 

A: Review the User Guide extensively and peruse through the Community for How To’s. There are also some excellent courses on Learning.Qlik.com for ramping up on everything to do with Qlik Replicate

Q: When using DB2z as a source and Kafka as a target - we see that empty fields (not NULL fields - actual empty fields filled with blanks) at the source are not sent at all in the data message. Is there an option to force Replicate to send empty fields? We would like to be able to differentiate empty fields from NULL fields when reading the attunity messages in Kafka. 

A: Yes, if you have DB2 z/OS going to Kafka, you should select the row of the table and see the row in HEX ( HEX ON ) . Do you see 40’s which are BLANKS(SPACES) in the field? If so, they can come through to the target. We would need your Diag Pkg, and CREATE TABLE DDL to reproduce it in-house. A transformation may or may not be needed. Please create a new case.

Q: Tasks can be exported in JSON, and then you can compare versions exported on a regular basis. 

A: Yes, you can compare the versions of Replicate in the exported task. It will look like this:
"version": "2022.5.0.499"

Q: Barb, In your demo you mentioned you can re-run Full Load to grab the 8 records that you missed, if you do that does not duplicate records on your MS SQL Databse? 

A: The Duplicate records can be easily handled by going in the task settings, then Error Handling, then Apply Conflicts. Change the following. “Change to task policy” . Then Duplicate key when applying INSERT: should become “UPDATE the existing target record” and No record found for applying an UPDATE: should become “INSERT the missing target record” Save the settings and reload the table.

Click here for video transcript

If using ADFS as an IDP (Identity Provider) in order to conform to Qlik's security requirements, ADFS must be configured to use refresh tokens and not access tokens. If refresh tokens are not configured this configuration will not be supported.

To confirm if ADFS is configured correctly to allow refresh tokens, perform the steps set out below.

1. In a browser, make the following request:
   
   https://TENANT_URL_VALUE/oauth/authorize?client_id=CLIENT_ID_VALUE&redirect_uri=https://REDIRECT_URL_VALUE&code_challenge=i2q7Dklmv7xy4mznQOy1OPXVVfiQRCVFEKfHULYSCzQ&code_challenge_method=S256&scope=user_default%20offline_access&state=your_state
   
   This will generate a code in the response URL. The code needs to be copied and used in step 2.
   
   
2. Using Postman (or a similar tool):
   
   Run this POST request inserting the required values in the body of the request:
   
   Request:   https://TENANT_URL_VALUE/oauth/token
   
   The request body needs to be modified as follows:
   
   Insert your  CODE_VALUE  from step 1,
   Insert your applications CLIENT_ID_VALUE, CLIENT_SECRET_VALUE, REDIRECT_URL_VALUE.
   
   Request Body:
   
   

   {
   "code": CODE_VALUE,
   "code_verifier": "1234-5678-90-ABC_DEF_GJI~jkl.mno.pqrs~tuvwxyz",
   "grant_type": "authorization_code",
   "client_id": CLIENT_ID_VALUE,
   "client_secret": CLIENT_SECRET_VALUE,
   "redirect_uri": REDIRECT_URL_VALUE
   }​

   
   
   

Steps 1 and 2 need to be completed within a few seconds of each other. As the generated code from Step 1 expires after only some seconds. 

If the Request made in Step 2 is successful. ADFS is configured correctly and is using Refresh Tokens.

If the request fails you will need to change your ADFS configuration. 

Environment

Qlik Cloud 

The information in this article is provided as-is and to be used at own discretion. Depending on tool(s) used, customization(s), and/or other factors ongoing support on the solution below may not be provided by Qlik Support.

Related Content 

Using Active Directory Federation Services (ADFS) as an IDP for Qlik Cloud.
ADFS single sign-on settings

As of writing this article, it is not possible to restrict what users have access to export data in Qlik Cloud.

Environment:

Qlik Cloud 

Related Content:

Downloading app content 

When a data source service is down (for instance, an Oracle Server), Qlik Data Gateway Direct Access will not recognise the service as down and will try running the task until time out (three hours). The task cannot be stopped. 

The data load progress remains in Loading data... status.

When the timeout is reached, the reload task fails. This can be seen in the Reload History (located in the Management Console > App > Details):

The Reload summary shows failed to complete reload: exceeded maximum duration

Environment

Qlik Cloud 

Resolution:

This issue is caused by defect QB-11826. The defect is currently being worked on by Qlik's dev team and is expected to be fixed in the upcoming version of Qlik Data Gateway. This will require upgrading the installed software. 

Information provided on this defect is given as is at the time of documenting. For up-to-date information, please review the most recent Release Notes with the ID QB-11826 for reference.

Workaround

Make sure that data services are running before the reload starts.

Cause

Product Defect ID: QB-11826

Related Content 

Data gateways ‒ Qlik Cloud