WARNING! NO LONGER GUARANTEED TO WORK IN 2019 VERSIONS, SOME KEY TABLES MISSING
FROM VERSION APRIL 2108 AND UP (LAST VERSION TESTED SEPTEMBER 2019 ) USE THE APRIL2018ANDUP.ZIP VERSION.
Sometimes large user directories are synced into Qlik Sense unnecessarily, either by intent or by design. This can lead to a lot of excess data in the database and unnecessary sync and caching operations that consume resources on the server.
Unfortunately the delete user operation is both time consuming and hard to perform in bulk meaning it can be quite impractical to quickly delete tens of thousands of users and most methods will leave the database quite large as it will take several weeks for the excess data to be purged.
Prerequisites for Windows Server 2008: If the server is running an old version of PowerShell, the script will fail to display the folder selection dialogs and will thus not run successfully. Please ask the customer to update their PowerShell version using the following link: https://www.microsoft.com/en-us/download/details.aspx?id=54616NOTE
: On most systems, execution of unsigned PowerShell Scripts is restricted through the PowerShell Execution Policy. In order to temporarily disable
this restriction, run the following commands in PowerShell with Elevated Permissions (run as Administrator):NOTE2
: You must run the entire process from the same PowerShell window, otherwise restoring the Execution Policy will not work.
$execPolicy = Get-ExecutionPolicy
Take a note of the resul, then run:
Set-ExecutionPolicy -ExecutionPolicy Unrestricted
You can now proceed with the steps in the Resolution
This article has attached a script that will purge all users that do not have any other references in Qlik Sense.
This means they have never had a document or license assigned to them and they have no association with any applications, tags or any other properties. This will not prevent the users from being synced again in a future operation, it merely clears them from the database when it is run.
The powershell script will:
- Request the Qlik Sense install location
- Stop all Qlik Sense Services on the node other than the Repository Database
- Request a location to store a backup of the database. NB The backup process may request a password
- Run the query in queries.sql against the database. NB This may also request a password
- Prepare the system to reset its snapshot
This script should be run on every node in the system, only after all nodes have been cleaned should you start any of the services, this needs to be done manually.
Once you have finished running the script, please remember to restore the default Execution Policy:
Set-ExecutionPolicy -ExecutionPolicy $execPolicy