How to: Use Qlik Application Automation to manage users

[AfeefaTk]

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
• Sheet Configuration
• Automation Configuration
• Running the Automation
• Considerations
• Related 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

1. 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.
   
   
   
   
2. 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.
   
   
3. Add the Get Current Tenant Id block from Qlik Cloud Services Connector.
   
   

4. 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. 
     
     
     
     
5. 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.
   
   
   
   
6. 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.
   
   
   
   
   
7. If the conditions are true,  add the below variables by initially setting all these variables to empty:
   
   
   1. 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.
      
      
      
   2. AssignedRoles: Variable of type object to store the role ID as key-value pair.
   3. AssignedRolesList: List variable to store the AssignedRoles object variable as an item.
      
      
8. Add a Loop block to loop through the Roles list variable where the roles of the user are stored from the Excel file.
   
   
   
9. 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.
   
   
   
10. 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.
    
    
    
    
11. 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.
    
    
    
    
12. Add the Raw API Request block from the Qlik Cloud Services connector to create the user. Configure the block as shown below.
    
    
    
    
13. Add the Condition block to check if the user has been created. If the condition is true increment the UsersCreatedCounter variable by 1.
    
    
    
    
    
    
14. Add the Condition block to check if we need to allocate a license to the user created in the above step.
    
    
    
    
15. 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.
    
    
    
    
16. 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.
    
    
    
    
17. If the conditions are true, add another Condition block to check if the user has a named license.
    
    
    
    
    1. 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.
       
       
       
       
       
18. Add the Raw API Request block to delete the user. Configure the input parameters as shown below.
    
    
    
    
19. Add the UsersDeletedCounter variable and increment it by 1.
    
    
20. 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

• Qlik Cloud  
• Qlik Application Automation

[random_user_3869]

Hello @AfeefaTk  !

Thank you for this well detailed explanation, well done !

Regarding the Prerequisites I have the following questions :

Instead using Office 365 account is it possible to use Google Sheet.

And my other question is :

Is it possible to manage licences with group of users instead of single users ?

Thank you

[AfeefaTk]

Hi @random_user_3869 

Thanks for your feedback.

As for your first question, yes you can replace the Excel file with Google sheet file.

Please find the automation attached to this article (QAA_users_management_google_sheet.json)

I will look into your second query and get back to you.

Thanks

[AfeefaTk]

Hi @random_user_3869 

Yes, it's possible to allocate license to multiple users.

Please take a look at the API doc:

https://qlik.dev/apis/rest/licenses/#%23%2Fentries%2Fv1%2Flicenses%2Fassignments%2Factions%2Fadd-pos...

You can pass the below json in the post body of the Raw API Request block :

{"add":[{"type":"professional","subject":"qlik\\kalle"},{"type":"analyzer","subject":"qlik\\nalle"}]}

Hope this helps!!

Thanks

[random_user_3869]

Hello @AfeefaTk 

I want to thank you a lot for your answer.

About what you said about the following"Yes, it's possible to allocate license to multiple users."

I want to clarify something.

If we create a group of user in active directory.

Then can we allocate licences to this group also ?

Thank you

[DaveChannon]

@random_user_3869 entitlements can only be assigned to users, we do not have and do not plan to have a mechanism for assigning them to a group.

If you wish to automatically assign an entitlement to a user based on their group when they first log in (and their user is created), you could use an automation triggered on new user creation to check the groups brought in with the user, and assign an entitlement if they have a group?

[random_user_3869]

Hello thank you a lot for this reply.

It really helps

[random_user_3869]

Hello @AfeefaTk  @DaveChannon ,

Thank you for your insight.

I was wondering if it is possible to do the following in the same automation:

- assign a user or group to a managed space and assign one of the following role in space (Can view, Can edit, Can edit data in apps, or Can consume data)

[DaveChannon]

Hi @random_user_3869 

Ues, you can use blocks from either the QCS connector: 

or from the QPO connector:

The list of role names for each space can be found on the API spec at https://qlik.dev/apis/rest/spaces/#%23%2Fdefinitions%2FRoleType and are currently:

Supported roles by space type:

• Shared: codeveloper, consumer, dataconsumer, facilitator, producer
• Managed: consumer, contributor, dataconsumer, facilitator, publisher, basicconsumer
• Data: consumer, dataconsumer, facilitator, operator, producer, publisher

[random_user_3869]

Hello,

I'm setting up this automation next week but i have a question.

Basically it can create an account if if the user hasn't logged in ?

Thank you

[Sonja_Bauernfeind]

Hello @random_user_3869 

Users can be created, yes. Please note the following section in the article:

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.

All the best,
Sonja