Qlik Sense extensions: How to audit 3rd party extension usage, match the number of required user licenses, and keep costs under control

If you rely on 3rd party extensions to enhance the capabilities of your Qlik Sense dashboards, then this article will provide valuable insights on how to keep your user licenses under control. Let's dive into my personal experience with this issue.

As we were migrating a group division to Qlik Cloud, one of our main concerns was determining the number of user licenses we needed to purchase.

Importing an extension and making it available to everyone is a breeze, but the challenge lies in tracking where these extensions are actually being used. Each individual has the freedom to drag and drop extensions and customize their own sheets, making it difficult to keep track of license usage.

To address this, I explored different options for auditing the usage of 3rd party extensions and determining the number of users requiring a license. Here are the options I discovered:

1. Best case scenario: The 3rd party extension vendor keeps track of user licenses whenever the extensions are loaded by a user. If this is your situation, you can skip what follows.
2. Open each and every application and manually check which extensions are used on each sheet. This information can then be combined with user sessions per application. However, this option can be extremely time-consuming and impractical if you have hundreds of applications.
3. Utilize the Telemetry dashboard and locate the app details .csv files it produces. By combining these files with user sessions per application, you can gain insights into license usage. However, this option is still not ideal as it requires loading every application in-memory. The Telemetry extraction process shortly gets the servers to run out of memory, skipping application details. In my experience, with thousands of dashboards, we were only able to fetch about 5% of the application details, rendering the statistics unreliable as we missed out on 95% of the data.

For the longest time, it seemed like we were stuck with no viable solution. But then, a glimmer of hope appeared on the horizon. I decided to put my brainstorming skills to the test and see if I could find a way to make it work for us. I even considered leveraging ChatGPT to find a solution, but that turned out to be a waste of time. I'm sure many of you can relate to such moments of frustration.

So, I took matters into my own hands and delved into the QRS and Engine API documentations. While the QRS API provided access to the repository database and returned useful application metadata, it didn't provide information about individual visualization objects within each sheet. Likewise, the Qlik CLI for Windows, whether it was the original version from Adam Haydon or the latest much faster compiled version from Marc Collins , didn't offer the solution I needed (for sure, I did not manage to find how to get there).

The Engine API is a powerful tool that empowers you to interact with Qlik Sense applications in various ways. With the Engine API, you have the ability to create new sheets, update the content of your app, and even list the objects present on each sheet. It's undeniably cool and offers a wide range of possibilities.

However, it's important to note that the Engine API operates over a websocket using JSON. While this technology is perfectly suitable for many developers, it may not be everyone's preferred approach. Personally, I found working with websockets and JSON to be a bit outside of my comfort zone. I wasn't quite sure how to achieve a quick win without embarking on a C# or Node.js project, which could be time-consuming and complex.

But then, I recalled something someone had mentioned in a chat a few years ago. The Qlik CLI is not only a great tool with Qlik Cloud, but it can also work with Qlik Sense on Windows! Excitedly, I rushed to the documentation and discovered exactly what I was looking for: a simple command to extract the objects on each sheet. You can find the details here: Qlik CLI app-object

In just 30 minutes, I was able to achieve my goal. Let me share with you how I did it. Fortunately, I already had Qlik CLI set up on my laptop, so I proceeded with the following steps:

1. Set up a JWT virtual proxy on my Qlik Sense client managed site. You can find a helpful guide on how to do this here: Qlik Sense: How to setup JWT authentication
2. Create a new Qlik CLI context using the command:

qlik context create qs-dev --server https://qs-dev.mycompany.net/jwt --comment "Qlik Sense Enterprise on Windows" --api-key <REPLACE WITH YOUR API KEY>        

3. Finally, I extracted the objects from an app using the command:

qlik app object ls --app d8717f0b-a5f3-46bd-b14f-0d714cb217d3 --no-data        

As you can see below, we receive a well formatted table output:

I was on the verge of completion; the only remaining task was to iterate through a list of application IDs and save the output in a CSV file.

This is where ChatGPT proved to be an invaluable companion. With its assistance, I was able to develop a script that accomplished the desired functionality.

The list of application ids are stored in a file named app_values.csv. For each row, the PowerShell script will extract the application objects and store them into result_$timestamp.csv where $timestamp will be unique with each execution of the script.

Allow me to share the script with you:

$appValuesFilePath = "C:\Temp\app_values.csv"
$timestamp = Get-Date -Format "yyyyMMdd_HHmmss"
$csvFilePath = "C:\Temp\result_$timestamp.csv"
New-Item -path $csvFilePath -type "file" -Force
$errorLogFilePath = "C:\Temp\error_log_$timestamp.csv"

$errorLog = @()

$appValues = Import-Csv -Path $appValuesFilePath | Select-Object -ExpandProperty AppValue

foreach ($appValue in $appValues) {
    Write-Output "Processing app value: $appValue"
    
    try {
        #$result = qlik app object ls --app $appValue --no-data --json | ConvertFrom-Json
		
		$metadata = New-Object System.Collections.ArrayList;
		$tempMetadata = qlik app object ls --app $appValue --no-data --json | ConvertFrom-Json;
		foreach ($object in $tempMetadata) {
			if (!@('LoadModel', 'sheet', 'appprops').contains($object.qType)) {
				$objectMetadata = @{};
				$objectMetadata | Add-Member -Name appId -Value $appValue -MemberType NoteProperty;
				$objectMetadata | Add-Member -Name isMasterObject -Value 0 -MemberType NoteProperty;
				if ($object.qType -eq "masterobject") {
					$objectType = (qlik app object properties --app $appValue $object.qId --no-data --json | ConvertFrom-Json -AsHashTable).visualization;
					$objectMetadata['qType'] = $objectType;
					$objectMetadata['isMasterObject'] = 1;
				}
				else {
					$objectMetadata['qType'] = $object.qType;
				}
				$objectMetadata['objectId'] = $object.qId;
				$objectMetadata['objectTitle'] = $object.title;
				$metadata.add($objectMetadata) | Out-Null;
			}
		}
		
    } catch {
        $errorMessage = "Error executing qlik app object ls command: $_"
        Write-Output $errorMessage
        $errorLog += [PSCustomObject]@{
            AppValue = $appValue
            ErrorMessage = $errorMessage
        }
        continue
    }

    if ($metadata -eq $null) {
        $errorMessage = "Error processing app value '$appValue': No output received"
        Write-Output $errorMessage
        $errorLog += [PSCustomObject]@{
            AppValue = $appValue
            ErrorMessage = $errorMessage
        }
    } elseif ($metadata -is [String] -and $metadata.StartsWith("could not open app with ID")) {
        $errorMessage = "Error processing app value '$appValue': $metadata"
        Write-Output $errorMessage
        $errorLog += [PSCustomObject]@{
            AppValue = $appValue
            ErrorMessage = $errorMessage
        }
    } else {
        #$result | Select-Object @{Name="App"; Expression={$appValue}}, qType | Export-Csv -Path $csvFilePath -NoTypeInformation -Append
		$metadata | ConvertTo-Csv | Out-File -Append $csvFilePath;
    }
}

$errorLog | Export-Csv -Path $errorLogFilePath -NoTypeInformation

if (-not (Test-Path $errorLogFilePath)) {
    Write-Output "No errors were encountered."
}        

There is also an error log to record each application for which the details could not be returned, typically because of section access.

could not open app with ID 'cfb8160d-5838-4d4a-9325-fcf29104feaa': : Access denied (5 GENERIC ACCESS DENIED)        

To bypass the section access issue, you can probably use the same Qlik CLI client to export the applications without data, import the same applications, then since they contain no data they also contain no section access, meaning you can fetch the objects, then delete these temporary applications.

By using the Qlik CLI to generate a CSV file, I was able to obtain a comprehensive list of both Qlik native extensions and third-party extensions for each application. By following the steps I've described, you can effectively monitor and manage the usage of third-party extensions, enabling you to accurately determine the necessary user licenses you need or for your migration to Qlik Cloud.

Please note this solution actually also works with Qlik Cloud, you only need to change the context in Qlik CLI.

By the way, in case your servers still run out of memory while running this piece of code, you can run this script in multiple batches, i.e. you only need to update the list of remaining application IDs for which you want to extract the details.

I encourage you to explore the available APIs, CLI tools, and documentation to find the right solution for your specific requirements. It's important to remember that sometimes the answer to your challenges may be found in unexpected places. So, don't lose hope and keep searching for innovative solutions that can address your unique needs.