Nightly routine: installation guide

Script 3.1.1, public edition. For Qlik Sense Enterprise client-managed, on Windows.

The nightly routine is an optional PowerShell script that runs on the Qlik Sense server, overnight. It extracts the load script of every app and stores it in the same Git repository the extension uses. It is the safety net: whatever someone changed without saving through Versiona goes into Git all the same, in a commit called "Autosync", and the mascot invites them to explain the change.

Download the script (.ps1)

The script's comments and messages are in Portuguese.

Without the routine, Versiona works normally, and only what someone saves through the extension goes into Git. There is no routine for Qlik Cloud. The script is free and provided as is, with no warranty and no SLA, under the terms of use. Read the script before running it: it is a text file, and everything it does is written in it.

What you need

Administrator access to the Qlik Sense server (Windows Server, PowerShell 4.0 or newer).
A service account to run the scheduled task. It can be the Qlik service account.
Git for Windows 2.31 or newer, in C:\Program Files\Git.
The Qlik-Cli PowerShell module.
A private repository on GitLab, one per Qlik server, and permission to create an access token in it.
Optional: a mailbox with authenticated SMTP, for the daily summary.

The routine was tested with GitLab. It has not been tested with GitHub yet.

1. The script folder, with restricted access

The script and the credentials live in C:\versiona. Only the system, the administrators and the service account should reach that folder. In PowerShell, as administrator (replace SEUDOMINIO\svc_qlik with your service account):

New-Item -ItemType Directory -Force -Path C:\versiona
icacls C:\versiona /inheritance:r /grant:r "*S-1-5-18:(OI)(CI)F" "*S-1-5-32-544:(OI)(CI)F" "SEUDOMINIO\svc_qlik:(OI)(CI)M"

/inheritance:r removes the inherited permissions, which usually include "Users". The two codes are the system and the administrators group, and they work on Windows in any language. Copy the downloaded script into the folder.

2. The repository and the token on GitLab

Create the repository, private. The default name the routine looks for is scriptqlik_qliksense_<nome da máquina em minúsculas> (the machine name in lowercase), inside the group you specify. You can change the name in the script.
In the repository, open Settings, Access tokens and create a Project Access Token:
- name: for example versiona_qlik_dev (the name goes into the script);
- role: Developer;
- scopes: only read_repository and write_repository;
- expiration: up to one year. Write down the date: it also goes into the script.
Copy the token right away. GitLab shows it only once.
Under Settings, Repository, Protected branches, set "Allowed to push and merge" to Developers + Maintainers and leave force push off. With the default (Maintainers only), the push from the routine and the one from the extension are rejected.

A project token, not your personal token: it only sees that repository, it does not stop working when someone leaves the company, and it does not carry your permissions. The extension's commits still go out with each person's login.

3. Store the token securely (`.cred_gitlab_qlik`)

The token does not live in the script. It is encrypted by the Windows DPAPI, and the encrypted file can only be opened by the same account, on the same machine that generated it. That is why the command has to be run as the service account, on the Qlik server itself.

Open a PowerShell as the service account:

runas /user:SEUDOMINIO\svc_qlik powershell.exe

If the service account cannot open an interactive session, ask the administrator for the permission for this step only, or generate the file through a one-time scheduled task. What you cannot do is generate it with another account.

In the window that opens, check who you are and write the credential:

whoami
Read-Host -AsSecureString | ConvertFrom-SecureString | Out-File "C:\versiona\.cred_gitlab_qlik"

Read-Host waits for input: paste the token and press Enter. It does not appear on screen or go into the command history. To check that it was written, without showing the token:

(Get-Content "C:\versiona\.cred_gitlab_qlik" | ConvertTo-SecureString).Length

The number is the length of the token. If you get an error, the file was generated by another account or on another machine.

Do not copy the file from one server to another: generate it on each one.
Do not paste the token into the script, an email, a ticket or a chat.
After writing it, delete the token from wherever you noted it down.

4. Store the email password (`.cred_smtp`), if you will use it

The daily summary by email is optional, and the script ships with it turned off. To turn it on, the SMTP password is stored the same way, in the same service account window:

Read-Host -AsSecureString | ConvertFrom-SecureString | Out-File "C:\versiona\.cred_smtp"

Use a mailbox just for alerts, not a person's mailbox.
If the provider has an "app password" (Gmail does, with two-step verification turned on), use the app password, not the account password.
Sending uses TLS on port 587, with the certificate verified.

5. Adjust the script parameters

Open the script in an editor and adjust section 1, "Parâmetros do usuário" (user parameters). You do not need to touch the rest.

Parameter	What to put
`$vServidores`	One line per Qlik server: the machine name (what the `hostname` command shows, in uppercase), the environment, the token name and the date it expires (`aaaa-mm-dd`, that is, year-month-day). The expiration alert comes from that date.
`$vPastaDestino`	Where the extracted files and the local Git repository live. A folder on the server or a network share. The service account needs write access.
`$vGitBaseURL`	The GitLab address, without `https://`.
`$vGitNamespace`	The group where the repository is.
`$vGitRepoNome`	Only if the repository does not follow the default name.
`$vGitAutorNome`, `$vGitAutorEmail`	How the routine appears in the history.
`$vEnviarEmail` and the SMTP ones	`$true` and the addresses, after writing `.cred_smtp`.

The same file serves several servers: each one recognizes itself by the machine name. After saving, make sure the file is in UTF-8 with BOM, otherwise PowerShell 4.0 breaks the accented characters in the email:

$p = "C:\versiona\Versiona_RotinaNoturna_QlikSense.ps1"
$c = [IO.File]::ReadAllText($p, [Text.Encoding]::UTF8)
[IO.File]::WriteAllText($p, $c, (New-Object Text.UTF8Encoding($true)))

6. Run it the first time, by hand

In the service account window:

cd C:\versiona
powershell.exe -NoProfile -ExecutionPolicy Bypass -File .\Versiona_RotinaNoturna_QlikSense.ps1

The first thing the routine does is check access to the repository, before extracting anything. If the token is wrong, it stops there and says why. The first extraction takes a while: it is one app at a time. At the end, check on GitLab the stream folders, the __Trabalho folder (the personal work areas) and the _indice_apps.jsonl file.

7. Schedule it

In Task Scheduler, create a task that runs with the service account, set to "Run whether user is logged on or not":

Program	`powershell.exe`
Arguments	`-NoProfile -NonInteractive -ExecutionPolicy Bypass -WindowStyle Hidden -File "C:\versiona\Versiona_RotinaNoturna_QlikSense.ps1"`
Start in	`C:\versiona`
Frequency	Daily, overnight, outside the reload window.

The routine exits with code 1 when something fails, so that task monitoring can see it. If the task runs again on the same day, it does not extract again: it only syncs.

8. Tell the extension

If you do not have the extension yet, install Versiona. In it, under "Servers", edit the server and check "This environment runs the nightly versioning routine". With the routine, the server's folder in the repository is left blank: the routine writes to the root.

Renewing the token

Starting 30 days before the date you put in TokenExpira, the routine's email starts warning in the subject line. To renew: generate another token on GitLab, repeat step 3 on the server and update the name and the date in $vServidores.

If something goes wrong

What you see	What it usually is
"Falha ao ler credencial" (failed to read the credential)	The file was generated by another account or on another machine. Redo step 3 as the account that runs the task.
"Sem acesso ao repositório" (no access to the repository)	Token expired or revoked, without the Developer role, without the two scopes, or the repository does not exist under that name.
Push rejected, "protected branch"	The protected branch only accepts Maintainers. See step 2.
Certificate error in Git	The certificate authority is missing on the machine. Fix it on the machine; do not turn off verification.
"Certificado QlikClient não encontrado" (QlikClient certificate not found)	The routine has to run on a Qlik Sense node, with the client certificate installed.
Garbled accented characters in the email	The file lost its BOM. Run the command at the end of step 5.

What the routine does not do

It does not change any app: it only reads the load script.
It does not delete from Git the file of an app that was deleted or renamed.
It does not force push and does not rewrite history.
It does not send anything to Cubotimize: it talks only to your Qlik, your Git and your mail server.

Questions, suggestions or problems: [email protected]. Do not send tokens, passwords or load scripts by email.

Credits: Cubotimize | CuboPlus and Mario Soares.