This article provides an overview of how to manage users using Qlik Application Automation. This approach can be useful when migrating from QlikView, or Qlik Sense Client Managed, to Qlik Sense Cloud when security concerns prevent the usage of Qlik-CLI and PowerShell scripting.
You will find an automation attached to this article that works with the Microsoft Excel connector. More information on importing automation can be found here.
Content
Prerequisites
- Access to a Qlik Cloud tenant
- User assigned the role of Tenant Admin
- Office 365 account (for Microsoft Excel)
Sheet Configuration
In this example, we use a Microsoft Excel file as a source file to manage users. A sheet name, for example, Users, must be added and this must also be provided as input when running the automation. The sheet must also contain these headers: userId, Name, Subject, Email, Roles, Licence, and Flag.
Example of sheet configuration:
If users are to be created the Flag column must be set to create. If users are to be deleted, there's no need to include roles, but Flag must be set to delete.
Automation Configuration
- Add an Input block that takes the name of the worksheet from which data should be read as input. You also need to specify the first and last cell to read data from, as well as if users are to be created or deleted.
- Add two variables of the type number. One to keep track of created users and one to keep track of deleted users. These variables will be used to update the run title when the automation has been completed. Only one of them will be used when running the automation since only one of the modes can be used (Create or Delete). Let's call the variables UsersCreatedCounter and UsersDeletedCounter.Intially set these variables to 0.
- Add the Get Current Tenant Id block from Qlik Cloud Services Connector.
-
Add the List Rows With Headers block from the Microsoft Excel connector to read the values that have been configured in the Excel sheet.
- Use the "do lookup" function to find the Drive ID and Item ID of the file you will be using as a source.
- Configure the other fields to get the values from the input fields.
- Inside of the List Rows With Headers loop add the Search Users block from the Qlik Cloud Services connector. Configure Fields and Filter input parameters as shown below.
- Add a Condition block to check if the user exists, if we're in Create mode, and if the Flag column in the Excel file is set to create.
- If the conditions are true, add the below variables by initially setting all these variables to empty:
- Roles: List variable to store a list of roles that needs to be assigned to the user. Configure the variable by clicking on Add operation button and selecting the 'Merge other list into Roles' option. In the List field, use explode formula to convert comma-separated roles from the Role column in the Excel file into a list.
- AssignedRoles: Variable of type object to store the role ID as key-value pair.
- AssignedRolesList: List variable to store the AssignedRoles object variable as an item.
- Add a Loop block to loop through the Roles list variable where the roles of the user are stored from the Excel file.
- Add the List Roles block from the Qlik Cloud Services connector to retrieve the IDs of the roles. Configure the Filter input parameter as shown below.
- Add AssignedRoles object variable to store role ID. Configure the variable by clicking on Add operation button and selecting the 'Set key/values of the AssignedRoles' option. Click on Add key/value button and use the ID as the key and the ID from the output of the List Roles block as the value.
- Add the AssignedRolesList variable to store the AssignedRoles object variable as an item to this list variable. To do this click on Add operation button and select the 'Add item to AssignedRolesList' option. In the Item field map the AssignedRoles object variable.
- Add the Raw API Request block from the Qlik Cloud Services connector to create the user. Configure the block as shown below.
- Add the Condition block to check if the user has been created. If the condition is true increment the UsersCreatedCounter variable by 1.
- Add the Condition block to check if we need to allocate a license to the user created in the above step.
- If the conditions are true, add the Raw API Request block to allocate a license to the user. Configure the input parameters as shown below.
- If the first condition in the List Rows With Headers loop is false, then use another Condition block to find out if we are in Delete mode, if the flag column in the Excel file is set to delete, and if the user exists.
- If the conditions are true, add another Condition block to check if the user has a named license.
- If the condition outcome evaluates to true, add the Raw API Request block to deallocate the user's license. Configure the input parameters as shown below.
- Add the Raw API Request block to delete the user. Configure the input parameters as shown below.
- Add the UsersDeletedCounter variable and increment it by 1.
- The last part of this automation is to check the mode and update the automation's run title to display how many users have been created or deleted.
Running the Automation
When running the automation you must provide input to the automation, this includes the name of the worksheet to read data from. You also need to specify the first and last cell to read data from, as well as if users are to be created or deleted. Example :
Input |
Value |
Worksheet Name |
Users |
Excel Start Cell |
A1 |
Excel End Cell |
G5 |
Mode |
Create |
Considerations
- A multitenant scenario can be supported by utilizing the Qlik Platform Operations connector instead of the Qlik Cloud Services connector.
- Google Sheets could be used instead of Microsoft Excel.
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
How to manage space membership (users)
Environment