Nprinting Rest Get user groups

[JeromeS]

Dear qlik developers,

I am trying to load a table containing all nprinting users and the groups they belong to using a script in qlik sense and a rest connection.

I have managed to load a table containing all the users basic information but I can't figure out how to select the groups attached to each user

Any help or tip would be greatly appreciated

Here is an exemple of the json structure that i cannot address:

 

Here is the script to load users details (using Qlik Nprinting API)

RestUserMasterTable:

SQL SELECT

"__KEY_data",

(SELECT

"id",
"email",
"created",
"lastUpdate", 
"enabled",
"userName",
"domainAccount",
"timezone",
"locale",
"folder",
"subFolder",
"__FK_items"

FROM "items" FK "__FK_items")

FROM JSON (wrap off) "data" PK "__KEY_data"

WITH CONNECTION( URL "https://<nprintingurl>/api/v1/users", HTTPHEADER "cookie" "$(vCookie)" );

users_items:

LOAD 
[id] AS users_id,
[email] AS users_email,
[created] AS users_created,
[lastUpdate] AS users_lastUpdate, 
[enabled] AS users_enabled,
[userName] AS users_userName,
[domainAccount] AS users_domainAccount,
[timezone] AS users_timezone,
[locale] AS users_locale,
[folder] AS users_folder,
[subFolder] AS users_subFolder

RESIDENT RestUserMasterTable;

DROP TABLE RestUserMasterTable;

Here is my draft script to load users groups .

The script executes itself without error but no data is loaded for field "usergroups_id"

For i=0 to NoOfRows('users_items')-2

let vUserid= peek('users_id',$(i),'users_items');

let vUrl = 'https://<nprinting url>/api/v1/users/'&  '$(vUserid)'& '/groups' ;

RestUsersGroupsMasterTable:

SQL SELECT 

"__KEY_data",

(SELECT 

"groupids"

FROM "items" FK "__FK_items")

FROM JSON (wrap off) "data" PK "__KEY_data"

WITH CONNECTION( URL "$(vUrl)", HTTPHEADER "cookie" "$(vCookie)" );

Usergroups_items:

LOAD 

'$(vUserid)' as usergroups_user,
[groupids] as usergroups_id

Resident RestUsersGroupsMasterTable;


NEXT i

Jérôme

[ryo_okabe · Accepted solution]

Hi,

Why don't you try to use following sample code.

It works fine in my environment.

Following is get user data.

// Get User with "usera" email
RestUserMasterTable:
SQL SELECT
"__KEY_data",
(SELECT
"id",
"email",
"__FK_items"
FROM "items" FK "__FK_items")
FROM JSON (wrap off) "data" PK "__KEY_data"
WITH CONNECTION( URL "https://<NPServer IP>:4993/api/v1/users", HTTPHEADER "cookie" "$(vCookie)" );

[users_items]:
LOAD [id] AS [users_userId],
[email] AS [users_userEmail]
RESIDENT RestUserMasterTable
WHERE NOT IsNull([__FK_items]);
//Extracts the userId of the desired NP User
let vUserId = Peek('users_userId',0,'users_items');
DROP TABLE RestUserMasterTable;

Following is get group data and get user groups.

RestGroupMasterTable:
SQL SELECT
"__KEY_data",
(SELECT 
"name",
"id",
"__FK_items",
"Created"
FROM "items" FK "__FK_items")
FROM JSON (wrap off) "data" PK "__KEY_data"
WITH CONNECTION( URL "https://<NPServer IP>:4993/api/v1/Groups", HTTPHEADER "cookie" "$(vCookie)" );


[group]:
LOAD [id] AS [groups_groupId],
    [name] AS [groups_groupName]
RESIDENT RestGroupMasterTable
WHERE NOT IsNull([__FK_items]);
let vUserId = Peek('users_userId',0,'users_items');
DROP TABLE RestGroupMasterTable;


For i = 0 to NoOfRows('group')-2

let vGroupid= peek('groups_groupId',$(i),'group');

let vUrl = 'https://<NPServer IP>:4993/api/v1/Groups/'& '$(vGroupid)'& '/Users' ;

RestConnectorMasterTable:
Load @Value as [users_userId],
     '$(vGroupid)' as [groups_groupId];
SQL SELECT 
	"__KEY_data",
	(SELECT 
		"@Value",
		"__FK_items"
	FROM "items" FK "__FK_items" ArrayValueAlias "@Value")
FROM JSON (wrap off) "data" PK "__KEY_data"
WITH CONNECTION( URL "$(vUrl)", HTTPHEADER "cookie" "$(vCookie)" );

next

[JeromeS]

Thanks Ryookabe, it works fine 🙂

[poojask123]

Hi Ryo_okabe,

 I am trying to modify the group name of a particular user using the NP API's. I am using the put user/<id>/groups command to do so.

Data retrieved for Get user/<id>/groups is in the form of an array and m not sure how manipulate this.

But my code is failing in preparing the body request for API. Can you please help ?