Capacity licences are a great way of granting many, infrequent, users access to your Qlik apps. Monitoring usage of available capacity is critical though and is not immediately straightforward in Qlik Cloud (SaaS). This tutorial, and sample app, show how you can monitor usage and alert when usage is exceeding what is expected.
What is Capacity Licencing?
Purchased in packs of 1,000 minutes per calendar month, a capacity licence is a subscription which can be consumed by any user for access to any app in six minute time slices. This flexibility makes the capacity licence an excellent choice for getting large numbers of infrequent users using Qlik apps.
Once you have these licences though, it is crucial that you monitor things to ensure that you are not burning through your capacity too quickly. Just looking at how many minutes have been consumed against the total is not enough, you need to be aware of how far through the month you are. Looking at current usage against a run-rate of available minutes allows this. If usage is exceeding the minutes available then someone needs to be notified, so licences can be reallocated or an additional capacity licence pack be added.
This post describes how to set up this monitoring and alerting on Qlik Cloud (SaaS). A similar approach can be taken for client managed also.
Connecting To The Qlik API
Information about your Qlik Cloud tenant is exposed through a number of API Endpoints. In order to read from these endpoints API Keys need to be enabled under Management Console Settings, a nominated user must have Developer rights in the Users section of the Management Console, and that user needs to generate an API key in the API Keys section of their Profile Settings. This key is then used in the load script of the app.
This is quite a convoluted process, but it is well described in the documentation for the App Analyzer for Qlik SaaS. The analyzer app itself is one you may want to considering in your SaaS tenant.
Once you have an API key, paste it into a safe place as you will need it later.
To implement a similar solution on Qlik Sense Enterprise for Windows (Client Managed) you can use the provided REST connection, monitor_apps_REST_license_overview.
Rather than setting up a connection to each API that is needed in your SaaS environment, it makes more sense to create just one and then modify it using WITH CONNECTION. To enable this, set up a new REST connection, pointing to a placeholder (e.g. https://jsonplaceholder.typicode.com/posts) with the type set to GET and everything else as defaults. Give this connection the name GenericGET.
Sending Alerts From The Qlik Load Script
It is possible to use Qlik Alerting in Qlik Cloud, but for this purpose I would suggest simply sending directly from the load script. The SMTP connector in Qlik Cloud allows for this.
Create a new SMTP connection, populating it with the details of the mail server you wish to send via. You may want to set up an email account, such as a free GMail account, specifically for this purpose. Give this connection the default name of SMTP.
This will allow you to send custom emails from your Qlik load script. In order to do the same in Qlik Sense Enterprise for Windows see this previous blog post on Sending Data from the Qlik Load Script.
Building The Capacity Usage App
As with any Qlik app, most of the magic happens in the load script. Here we talk through the code you need to build an alerts app.
First of all some environmental variables need to be set, specifying where your tenant can be found and what email settings you wish to use:
// Name or alias of your Qlik Sense Cloud instance
let vInstance = 'instance.region.qlikcloud.com';
// Email configuration fields
let vTo = 'recipient@domain.com';
let vFrom = 'sender@domain.com';
let vFromName = 'Sense Capacity Alert';
// The API key to use - generated under your Profile settings
let vAPIKey = 'ABC123456789';
Note that you will need to paste the API key that you obtained above into this code.
Now some options to configure. Firstly at what percentage of the time-of-month available minutes do you wish to alert, 1 is anything over 100% of usage – you can increase or decrease this as you wish. You can also specify how frequently you want to be reminded you are over threshold (if usage keeps rising) or ask to be reminded every time the app reloads:
// Decimal at which run-rate percent to send alert - 1 = 100%
let vAlertAt = 1;
// Days in which to repeat send of alert - can be a decimal fraction of a day
let vRepeatIn = 0.25;
// Binary flag indicating whether you wish to ignore increase and repeat thresholds
let vSendAll = 0;
Note that the repeat value of 0.25 is equivalent to every six hours. You can adjust as you see fit.
Now we need to connect to the REST connection. This will check your placeholder is present, but do nothing else at this point:
// Connect to the REST connector
LIB CONNECT TO 'GenericGET';
Note that if you have created your REST connection in a managed space, you will want to prefix the name with the space name, e.g. ‘MySpace:GenericGET’;.
Most of the heavy lifting of working out where we are in the month is done in this next statement. It connects to the API, gives the credentials defined above, calculates the run rate and formats the output:
// Retrieve data and calculate run-rates and percentages
CapacityUsed:
LOAD
*,
num([Used Minutes] / [Available Run Rate], '#,##0.0%') as [Percent Used Run Rate]
;
LOAD
name as [Licence Type],
usageClass as [Usage Type],
units as [Total Minutes],
unitsUsed as [Used Minutes],
num(unitsUsed / units, '#,##0.0%') as [Percent Used],
// Work out a run rate by working out how far through the month we are in days,
// divided by the total number of days in the month
num(units *
((now() - MonthStart(today())) /
Day(MonthEnd(today()))), '#,##0.0') as [Available Run Rate],
overage
WHERE name = 'analyzer_time'
;
SQL SELECT
(SELECT
"name",
"usageClass",
"units",
"unitsUsed",
"overage"
FROM "allotments" FK "__FK_allotments")
FROM JSON (wrap on) "root" PK "__KEY_root"
WITH CONNECTION (
URL "https://$(vInstance)/api/v1/licenses/overview",
HTTPHEADER "Authorization" "Bearer $(vAPIKey)"
)
;
In order to be able to check values and send them in the load script we need to peek them into variables:
// Retrieve values from the data set
let vRunRate = num(peek('Percent Used Run Rate', -1, 'CapacityUsed'), '#,##0.0%');
let vTotal = num(peek('Total Minutes', -1, 'CapacityUsed'), '#,##0');
let vUsed = num(peek('Used Minutes', -1, 'CapacityUsed'), '#,##0');
let vPercent = num(peek('Percent Used', -1, 'CapacityUsed'), '#,##0.0%');
let vAvailable = num(peek('Available Run Rate', -1, 'CapacityUsed'), '#,##0.0');
Then to give information back to the user running the script we can use these variables in a set of trace statements.
// Notify of current status to load stream
TRACE ;
TRACE $(vUsed) Minutes of $(vTotal) consumed.;
TRACE $(vPercent) of Total and $(vRunRate) of Run Rate minutes.;
Capacity subscriptions reset at the end of each calendar month. We are only interested in being notified if the previous percentage usage has been exceeded, but we need to reset that if we are in a new month. That is done here:
// Reset last run rate if we are in a new month
if Month(alt(vLastRun, 0)) < > month(today()) then
let vLastRunRate = 0;
end if
We then need to decide whether the current run-rate exceeds the value at which we want to alert, and whether other criteria such as used percentage increased and interval time elapsed. This is all in one big if statement:
// Check if we have an alert situation and whether repeat and increase criteria met
if vRunRate > vAlertAt and (vSendAll < > 0
or (vRunRate > alt(vLastRunRate, 0) and now() > alt(vLastAlert + vRepeatIn, 0))) then
Now we have to set a couple of variables, to record what level we are alerting at, and when we are doing it. We are also writing to the load script progress window:
TRACE Sending alert to $(vTo);
// Set variables to log last time an alert sent
let vLastRunRate = vRunRate;
let vLastAlert = now();
Sending emails from the Qlik SaaS load script requires a connection to the SMTP connection to be made:
// Connect to SMTP connection
LIB CONNECT TO 'SMTP';
As with the REST connection, the connection name should be prefixed with the space name and a colon if the connection is in a managed space.
Then the sending of an email is simply a LOAD call, with the recipient, sender, subject and body of the email all included in the SELECT statement:
// Send email and retrieve result of send
Email:
LOAD
status as [Email Status],
result as [Email Result]
;
SELECT
status,
result
FROM SendEmail
WITH PROPERTIES (
to='$(vTo)',
subject='Capacity Usage Exceeding Expected For Time Of Month ($(vRunRate))',
message='
The capacity licence usage on $(vInstance) is currently $(vUsed) minutes out of $(vTotal).
This is $(vPercent) of the total capacity, and $(vRunRate) of the minutes pro-rata for the time of month.',
html='true',
fromName='$(vFromName)',
fromEmail='$(vFrom)',
cc='',
bcc=''
);
Note that you can place HTML into the body of the message here (due to the html=’true’ flag). The code above will be missing the line break and bold tags, you can simply add them back in as required.
The response from the email server is kept in the Email table. We are not looking into that here, but you can check the values in these fields in the Analyze part of your app.
We then need to end if the IF statement we started above, and clear down the variables we have used and no longer have a need of:
end if
// Clean up after ourselves
let vInstance = ;
let vTo = ;
let vFrom = ;
let vFromName = ;
let vAPIKey = ;
let vAlertAt = ;
let vRepeatIn = ;
let vSendAll = ;
So, all being well that should be that. Your tenant’s API will be polled, capacity usage will be loaded (note other licences are in that endpoint also, just excluded by a WHERE), run rates will be calculated and emails will be sent.
Seeing the data is in a Sense app though, it would be a shame not to have a chart or two…
Building a Front End for the Application
This application will only have one row of data in it and a handful of fields that can be used for measures. Master measures can be made for these, as follows:
Total Capacity: Sum([Total Minutes])
Used Minutes: Sum([Used Minutes])
Capacity Run Rate: Sum([Available Run Rate])
Percent of Total Used: Sum([Used Minutes]) / Sum([Total Minutes])
Percent of Run Rate Used: Sum([Used Minutes]) / Sum([Available Run Rate])
You might then chose to place these onto a sheet a bit like this:
If after all of this you would rather just download an app that already has all of the script and front end in it, then you are in luck. I have uploaded the completed app to Qlik Community and you can find it here:
https://community.qlik.com/t5/Qlik-Sense-Documents/Qlik-Sense-App-Monitor-Capacity-Licences-on-Qlik-Cloud-and/ta-p/1805633
I would be very interested to hear how you get on with this application. Please feel free to contact me via the comments if you wish to get in touch.
Expanding The Solution
If you want to take this solution further, and write the capacity usage as at each reload to an online source, you may want to read my next blog post also:
Thank you for the great work. It looks like the scripts are missing some ‘=’ operators:
// Reset last run rate if we are in a new month
if Month(alt(vLastRun, 0)) month(today()) then
let vLastRunRate = 0;
end if
Thanks Jacques, there were two instances of less than / greater than, which was being mistaken for HTML tags. It’s due to a slight flaw in the plugin I am using for showing code that this doesn’t work. I have added a space between the two characters, which then means that they show – but the space needs to be removed to make the code work. Downloading the completed app and using that gets away from that issue.