topic Re: SAS VIYA - Fetching List of Users/Groups/Creation Date in Administration and Deployment

SAS VIYA - Fetching List of Users/Groups/Creation Date

kovuruy — Fri, 05 Mar 2021 07:46:20 GMT

Hi All - Request you all to help on how to extract User Profile information from SAS VIYA into an excel

As I checked, I can view each user details and do not see any option to download to excel for all the users listed in SAS Viya.

Details: Current version--> SAS Environment manager 8.1.

Requirement: List all users and groups and if possible creation date

Thanks in advance.

Re: SAS VIYA - Fetching List of Users/Groups/Creation Date

gwootton — Fri, 05 Mar 2021 15:16:10 GMT

Exporting user information to Excel is not a function of Environment Manager.

You can use the identities service REST API to pull this information.

/identities/users would return the users

/identities/users/<user_id> would return information about that user, including creation date.

/identities/users/<user_id>/memberships would return the groups that user is a member of.

Once you have the desired information in your REST API client, you could export to csv or excel depending on the client.

This program could be run in SAS Studio to pull this information, it combines all the group IDs into a single comma separated value (i.e. group1,group2,group3). You end up with a dataset work.userinfo you could then export.

/* --- Begin Edit --- */
%let baseurl=http://viya.demo.sas.com;
 
/* Set a per response limit. */
%let limit=100;

/* Init some files for the PROC HTTP output. */
filename headout temp;
filename resp temp;
filename init temp;

/* Define a table to store the user IDs we find. */
data work.users;
    stop;
    length id $ 255;
run;

%macro getusers;
 
/* Use our new token to search the users. */
proc http url="&baseurl/identities/users?start=0%nrstr(&limit)=&limit" oauth_bearer=sas_services out=init headerout=headout HEADEROUT_OVERWRITE;
    headers "Accept"="application/json";
run;
 
/* Read in the response. */
libname init;
libname init json fileref=init;
 
/* Put it into the dataset. */
proc sql;
    insert into work.users select id from init.items;
quit;
 
/* Read the links to see if a "next" link exists. If so, set it to a macro variable "next" */
data _null_;
    set init.links;
    if rel="next" then call execute('%let next=%nrstr('||href||')');
run;
 
 /* This loop will keep following the "next" link and appending records to the rules table until it has pulled all the rules defined. */
 
%do %while (%length(&next)>0);
 
/* Clear any existing users fileref and create one for this iteration. */
filename users;
filename users temp;
 
/* Call the "next" URL and output to the fileref.  */
proc http url="&baseurl&next" oauth_bearer=sas_services out=users headerout=headout HEADEROUT_OVERWRITE;
    headers "Accept"="application/json";
run;

/* clear any existing users libref. */
libname users;
/* Read in the file ref. */
libname users json fileref=users;
 
%let next=;
/* If that output contains a next link, set it to the next variable, otherwise empty the next variable to end the do/while loop. */
data _null_;
    set users.links;
    if rel="next" then call execute('%let next=%nrstr('||href||')');
run;
 
/* Put the user IDs from this iteration into our data set. */
proc sql;
    insert into work.users select id from users.items;
quit;
 
%end;
%mend;
 
/* Build the table of user IDs.*/

%getusers;

/* Create an empty table with the values we want. */
data userinfo;
	stop;
	length name id title email state providerId phone created $ 255 groups $2048;
run;

/* Write a macro that can be run for a given user ID to write the user's information into the table. */
%macro getuserinfo(id=);
filename userinfo;
filename userinfo temp;
filename grpinfo;
filename grpinfo temp;

proc http url="&baseurl/identities/users/&id" oauth_bearer=sas_services out=userinfo headerout=headout HEADEROUT_OVERWRITE;
    headers "Accept"="application/json";
run;

libname userinfo;
libname userinfo json fileref=userinfo;

proc http url="&baseurl/identities/users/&id/memberships" oauth_bearer=sas_services out=grpinfo headerout=headout HEADEROUT_OVERWRITE;
    headers "Accept"="application/json";
run;

libname grpinfo;
libname grpinfo json fileref=grpinfo;

data groups;
	length groups $ 2048;
	keep groups;
	do until (last.id);
	set grpinfo.items end=eof;
	groups=catx(',',groups,id);
	if eof then output;
	end;
run;

proc sql noprint;
	insert into work.userinfo select r.name,r.id,r.title,e.value as email,r.state,r.providerId,p.value as phone,r.creationTimeStamp as created, g.groups from userinfo.root as r,userinfo.emailaddresses as e,userinfo.phonenumbers as p,work.groups as g where e.ordinal_emailAddresses=1 and p.type="work";
quit;

%mend;

/* Run that macro for each user ID. (obs=10 limits this to 10 responses for demo purposes.) */
data _null_ ;
	set users (obs=10);
	str=catt('%getuserinfo(id=',id,');');
	call execute(str);
run;

Re: SAS VIYA - Fetching List of Users/Groups/Creation Date

kovuruy — Tue, 16 Mar 2021 07:10:17 GMT

Thank you @gwootton : You are a legend and as I recollect, I have been using the program that you have shared to extract users information SAS 9.4

I have tried the above program in SAS Viya and these are my observations:

1. Program ran successfully but Users.Info dataset only listed 10 users and corresponding groups

2. Users dataset listed close to 2800 users present in our environment.

Please request you to inform me if I am missing any steps or Do I need change anything if there is a limit on no of users that can list in the program

And also, There is no option available in SAS Studio to download to our local computer.

Thank you.

Re: SAS VIYA - Fetching List of Users/Groups/Creation Date

gwootton — Tue, 16 Mar 2021 13:24:11 GMT

I added a 10 user limit for demo purposes, you can modify it by removing "(obs=10)" to pull all of them. In SAS Studio's output data pane there is an Export option. If file system access is available this could be exported to the file system of the server, or you could save it to the Content Server and download it to your desktop either in SAS Studio or from SASDrive.

/* Run that macro for each user ID. (obs=10 limits this to 10 responses for demo purposes.) */
data _null_ ;
	set users (obs=10);
	str=catt('%getuserinfo(id=',id,');');
	call execute(str);
run;

Re: SAS VIYA - Fetching List of Users/Groups/Creation Date

kovuruy — Tue, 16 Mar 2021 16:47:40 GMT

Thank you very much. I will try the above options and keep you posted.

Re: SAS VIYA - Fetching List of Users/Groups/Creation Date

kovuruy — Wed, 17 Mar 2021 17:43:39 GMT

@gwootton : Program works with the option obs=10 and tables are generated

Further to update, I have removed the parameter obs=10 and have encountered below error messages.

1. Log file exceeds 1000000 characters and gets below warning.

2. Program errors out with the below messages and ends in infinite loop.

3. And also there is no option displaying for us to download the dataset.

(Note: The dataset generated with the parameter obs=10)

Kindly request you to suggest if I am missing anything here and looking forward for your help on this.

Thank you.

Re: SAS VIYA - Fetching List of Users/Groups/Creation Date

kovuruy — Wed, 17 Mar 2021 17:55:28 GMT

Hi @gwootton : Download file sorted. However, Program errors out and goes into infinite loop when I remove obs=10 option

Please request your help here.

Thank you

Re: SAS VIYA - Fetching List of Users/Groups/Creation Date

gwootton — Wed, 17 Mar 2021 19:46:10 GMT

There are two loops in this program. The first builds the USERS table by looping through the identities/users "next" links until they no longer are present. This occurs regardless of whether obs is set to 10 or not so I don't think that loop is the problem.

The other loop is for each observation in the USERS table, so if you had 2800 users it would loop 2800 times rather than infinitely, I could certainly see this code producing over 1 million characters though.

The error messages provided:

ERROR: File USERINFO.EMAILADDRESSES.DATA does not exist
ERROR: Column title could not be found in the table/view identified...
ERROR: Unresolved reference to table/correlation name r

Seem to be related to a problem pulling a valid response from the identities/users/<user_id> endpoint, though the log would have more detail on the PROC HTTP responses. That the error specifically mentions USERINFO.EMAILADDRESSES is not present could also mean some users do not have email addresses defined and my code is not correcting for that. You could remove the references to email and phone data sets to see if that has an impact. I.e. replace this:

proc sql noprint;
insert into work.userinfo select r.name,r.id,r.title,e.value as email,r.state,r.providerId,p.value as phone,r.creationTimeStamp as created, g.groups from userinfo.root as r,userinfo.emailaddresses as e,userinfo.phonenumbers as p,work.groups as g where e.ordinal_emailAddresses=1 and p.type="work";
quit;

With

proc sql noprint;
insert into work.userinfo select r.name,r.id,r.title,r.state,r.providerId,r.creationTimeStamp as created, g.groups from userinfo.root as r,work.groups as g ;
quit;

Given 2800 users would result in 5,880 calls to the identities service, it could be overwhelming the services or LDAP with requests. It might be worth trying to add a sleep statement in there to slow it down. Maybe add this under each of the proc http calls in the getuserinfo macro:

data _null_;
rc=sleep(1,1);
run;

This would however result in the program pausing for 5600 seconds total, so 1.5 hours of waiting time. If it works you could try tuning down the units to improve performance, say pausing for 1/10th of a second:

data _null_;
rc=sleep(1,.1);
run;

Re: SAS VIYA - Fetching List of Users/Groups/Creation Date

kovuruy — Thu, 18 Mar 2021 11:31:40 GMT

Hi @gwootton : Thank you. There is no luck yet 🙂 and have encountered different error message this time along with existing error message that we had where log file exceeding maximum size.

Please see the below updated program that I tried to execute.

Error Message:

Updated program as per your recommendations and request help here if I missed any

/* --- Begin Edit --- */
%let baseurl=http://sample.sas.com;
 
/* Set a per response limit. */
%let limit=100;

/* Init some files for the PROC HTTP output. */
filename headout temp;
filename resp temp;
filename init temp;

/* Define a table to store the user IDs we find. */
data work.users;
    stop;
    length id $ 255;
run;

%macro getusers;
 
/* Use our new token to search the users. */
proc http url="&baseurl/identities/users?start=0%nrstr(&limit)=&limit" oauth_bearer=sas_services out=init headerout=headout HEADEROUT_OVERWRITE;
    headers "Accept"="application/json";
run;

data _null_;
rc=sleep(1,1);
run;
 
/* Read in the response. */
libname init;
libname init json fileref=init;
 
/* Put it into the dataset. */
proc sql;
    insert into work.users select id from init.items;
quit;
 
/* Read the links to see if a "next" link exists. If so, set it to a macro variable "next" */
data _null_;
    set init.links;
    if rel="next" then call execute('%let next=%nrstr('||href||')');
run;
 
 /* This loop will keep following the "next" link and appending records to the rules table until it has pulled all the rules defined. */
 
%do %while (%length(&next)>0);
 
/* Clear any existing users fileref and create one for this iteration. */
filename users;
filename users temp;
 
/* Call the "next" URL and output to the fileref.  */
proc http url="&baseurl&next" oauth_bearer=sas_services out=users headerout=headout HEADEROUT_OVERWRITE;
    headers "Accept"="application/json";
run;

data _null_;
rc=sleep(1,1);
run;

/* clear any existing users libref. */
libname users;
/* Read in the file ref. */
libname users json fileref=users;
 
%let next=;
/* If that output contains a next link, set it to the next variable, otherwise empty the next variable to end the do/while loop. */
data _null_;
    set users.links;
    if rel="next" then call execute('%let next=%nrstr('||href||')');
run;
 
/* Put the user IDs from this iteration into our data set. */
proc sql;
    insert into work.users select id from users.items;
quit;
 
%end;
%mend;
 
/* Build the table of user IDs.*/

%getusers;

/* Create an empty table with the values we want. */
data userinfo;
	stop;
	length name id title email state providerId created $ 255 groups $2048;
run;

/* Write a macro that can be run for a given user ID to write the user's information into the table. */
%macro getuserinfo(id=);
filename userinfo;
filename userinfo temp;
filename grpinfo;
filename grpinfo temp;

proc http url="&baseurl/identities/users/&id" oauth_bearer=sas_services out=userinfo headerout=headout HEADEROUT_OVERWRITE;
    headers "Accept"="application/json";
run;

data _null_;
rc=sleep(1,1);
run;

libname userinfo;
libname userinfo json fileref=userinfo;

proc http url="&baseurl/identities/users/&id/memberships" oauth_bearer=sas_services out=grpinfo headerout=headout HEADEROUT_OVERWRITE;
    headers "Accept"="application/json";
run;

data _null_;
rc=sleep(1,1);
run;

libname grpinfo;
libname grpinfo json fileref=grpinfo;

data groups;
	length groups $ 2048;
	keep groups;
	do until (last.id);
	set grpinfo.items end=eof;
	groups=catx(',',groups,id);
	if eof then output;
	end;
run;

proc sql noprint;
insert into work.userinfo select r.name,r.id,r.title,r.state,r.providerId,r.creationTimeStamp as created, g.groups from userinfo.root as r,work.groups as g ;
quit;

%mend;

/* Run that macro for each user ID. (obs=10 limits this to 10 responses for demo purposes.) */
data _null_ ;
	set users;
	str=catt('%getuserinfo(id=',id,');');
	call execute(str);
run;

Re: SAS VIYA - Fetching List of Users/Groups/Creation Date

gwootton — Thu, 18 Mar 2021 12:55:22 GMT

Sorry, you would also need to modify the data step that creates work.userinfo to omit those values. i.e.

/* Create an empty table with the values we want. */
data userinfo;
stop;
length name id title email state providerId phone created $ 255 groups $2048;
run;

Would now be

/* Create an empty table with the values we want. */
data userinfo;
stop;
length name id title state providerId created $ 255 groups $2048;
run;

Re: SAS VIYA - Fetching List of Users/Groups/Creation Date

kovuruy — Thu, 18 Mar 2021 15:13:09 GMT

Hi @gwootton : Update - I still get the error message on the log file exceeding million characters.

And also, Program errors out with the below message. Please request your help here if I am missed any.

Updated the program with creationtimestamp as there was an error with attribute: created - No luck yet

I believe, I am missing many things here.

/* --- Begin Edit --- */
%let baseurl=http://sample.sas.com;
 
/* Set a per response limit. */
%let limit=100;

/* Init some files for the PROC HTTP output. */
filename headout temp;
filename resp temp;
filename init temp;

/* Define a table to store the user IDs we find. */
data work.users;
    stop;
    length id $ 255;
run;

%macro getusers;
 
/* Use our new token to search the users. */
proc http url="&baseurl/identities/users?start=0%nrstr(&limit)=&limit" oauth_bearer=sas_services out=init headerout=headout HEADEROUT_OVERWRITE;
    headers "Accept"="application/json";
run;

data _null_;
rc=sleep(1,1);
run;
 
/* Read in the response. */
libname init;
libname init json fileref=init;
 
/* Put it into the dataset. */
proc sql;
    insert into work.users select id from init.items;
quit;
 
/* Read the links to see if a "next" link exists. If so, set it to a macro variable "next" */
data _null_;
    set init.links;
    if rel="next" then call execute('%let next=%nrstr('||href||')');
run;
 
 /* This loop will keep following the "next" link and appending records to the rules table until it has pulled all the rules defined. */
 
%do %while (%length(&next)>0);
 
/* Clear any existing users fileref and create one for this iteration. */
filename users;
filename users temp;
 
/* Call the "next" URL and output to the fileref.  */
proc http url="&baseurl&next" oauth_bearer=sas_services out=users headerout=headout HEADEROUT_OVERWRITE;
    headers "Accept"="application/json";
run;

data _null_;
rc=sleep(1,1);
run;

/* clear any existing users libref. */
libname users;
/* Read in the file ref. */
libname users json fileref=users;
 
%let next=;
/* If that output contains a next link, set it to the next variable, otherwise empty the next variable to end the do/while loop. */
data _null_;
    set users.links;
    if rel="next" then call execute('%let next=%nrstr('||href||')');
run;
 
/* Put the user IDs from this iteration into our data set. */
proc sql;
    insert into work.users select id from users.items;
quit;
 
%end;
%mend;
 
/* Build the table of user IDs.*/

%getusers;

/* Create an empty table with the values we want. */

data userinfo;
stop;
length name id title state providerId creationTimeStamp $ 255 groups $2048;
run;

/* Write a macro that can be run for a given user ID to write the user's information into the table. */
%macro getuserinfo(id=);
filename userinfo;
filename userinfo temp;
filename grpinfo;
filename grpinfo temp;

proc http url="&baseurl/identities/users/&id" oauth_bearer=sas_services out=userinfo headerout=headout HEADEROUT_OVERWRITE;
    headers "Accept"="application/json";
run;

data _null_;
rc=sleep(1,1);
run;

libname userinfo;
libname userinfo json fileref=userinfo;

proc http url="&baseurl/identities/users/&id/memberships" oauth_bearer=sas_services out=grpinfo headerout=headout HEADEROUT_OVERWRITE;
    headers "Accept"="application/json";
run;

data _null_;
rc=sleep(1,1);
run;

libname grpinfo;
libname grpinfo json fileref=grpinfo;

data groups;
	length groups $ 2048;
	keep groups;
	do until (last.id);
	set grpinfo.items end=eof;
	groups=catx(',',groups,id);
	if eof then output;
	end;
run;

proc sql noprint;
insert into work.userinfo select r.name,r.id,r.title,r.state,r.providerId,r.creationTimeStamp as created, g.groups from userinfo.root as r,work.groups as g ;
quit;

%mend;

/* Run that macro for each user ID. (obs=10 limits this to 10 responses for demo purposes.) */
data _null_ ;
	set users;
	str=catt('%getuserinfo(id=',id,');');
	call execute(str);
run;

After above modified version, I am getting below error message

Please request your help when you get a chance to look into this.

Thank you

Re: SAS VIYA - Fetching List of Users/Groups/Creation Date

gwootton — Thu, 18 Mar 2021 15:21:22 GMT

Looks like it also doesn't like the "title" column, so you might want to try removing that too from the procsql and data steps.

Re: SAS VIYA - Fetching List of Users/Groups/Creation Date

kovuruy — Thu, 18 Mar 2021 16:55:33 GMT

Hi @gwootton : Further to update, Program execution does not go ax expected as I have encountered earlier message where log file exceeds 1000000 characters.

I have tried other to keep obs=5000 and this executed for sometime and session was killed in SAS Studio automatically.

Now, I am trying with obs=3000 and will keep you posted on results.

Thank you

Re: SAS VIYA - Fetching List of Users/Groups/Creation Date

gwootton — Thu, 18 Mar 2021 17:13:16 GMT

With the addition of the sleep delays, session timeout could be another issue you are encountering. You might want to pull those out and see if you still encounter problems with the problem columns removed, or limit your returns to below what would produce an hour long execution (1800 entries).

Re: SAS VIYA - Fetching List of Users/Groups/Creation Date

kovuruy — Thu, 18 Mar 2021 17:30:44 GMT

Hi @gwootton Could you please help if we have any setting to increase session time-out in SAS Studio 5.2

And also, with restriction on no of records to fetch, do you think we do not get complete usage from SAS Studio

Re: SAS VIYA - Fetching List of Users/Groups/Creation Date

gwootton — Thu, 18 Mar 2021 17:46:57 GMT

Set Time-out Interval for SAS Viya Web Applications
https://go.documentation.sas.com/?cdcId=calcdc&cdcVersion=3.5&docsetId=calconfig&docsetTarget=n03000sasconfiguration0admin.htm&locale=en#n03020sasconfiguration0admin

I'm not sure I understand your second question. While I can't confirm, I suspect the errors you are encountering are because this code assumes there will always be a title, email, phone number present in the details, which may not be the case in your environment. I hit the million line message when I run the program in my environment with only 247 users, but the data set is still created.

Re: SAS VIYA - Fetching List of Users/Groups/Creation Date

kovuruy — Fri, 19 Mar 2021 16:47:46 GMT

Thank you @gwootton : I will try the above options and keep you posted with updates.

Re: SAS VIYA - Fetching List of Users/Groups/Creation Date

AllanBowe — Mon, 12 Sep 2022 19:19:39 GMT

To get a list of Viya users: https://core.sasjs.io/mv__getusers_8sas.html

Viya groups: https://core.sasjs.io/mv__getgroups_8sas.html

Group Members: https://core.sasjs.io/mv__getgroupmembers_8sas.html

Member Groups: https://core.sasjs.io/mv__getusergroups_8sas.html

Re: SAS VIYA - Fetching List of Users/Groups/Creation Date

sergie89 — Mon, 20 Mar 2023 13:50:21 GMT

@gwoottonThank you for your post. Is it possible to create an overview for custom groups with your SAS code ?

Re: SAS VIYA - Fetching List of Users/Groups/Creation Date

gwootton — Mon, 20 Mar 2023 17:32:25 GMT

The code in this thread does not make a distinction between custom groups and those retrieved from LDAP, but you could probably modify it to only add groups that have a providerId of "local" in the groups data step.