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

[kovuruy]

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. 

[gwootton]

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;

[kovuruy]

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. 

[gwootton]

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;

 

 

[kovuruy]

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

[kovuruy]

@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. 

[kovuruy]

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

[gwootton]

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;

[kovuruy]

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;

 

[gwootton]

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;

[kovuruy]

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

[gwootton]

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.

[kovuruy]

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

[gwootton]

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).

[kovuruy]

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