BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
marccarrion
Calcite | Level 5

Hi, first of all, I'm not a SAS VA administrator. I'm a software developer with background in different databases and languages. We have been using SAS VA for a while, but the person that set it up is not longer with us. I've been doing the administration through the SAS Management Console so far with no problem. Now I've been asked to produce a report with all users and groups they belong to. So, I started digging around to see if that was possible. I thought I could do it login directly the underlying database (mysql?) or through the application creating a new report that would pull from users and groups instead of the data we are using for other reports. The problem is that I can't find where users and groups are stored. I checked several answers in this forums and they did not made sense to me (remember, I'm new to this tool). Can I get some help on where should I look?

 

Thanks,

 

Marc

1 ACCEPTED SOLUTION

Accepted Solutions
alexal
SAS Employee

@marccarrion,

 

This SAS program produces a list of users and groups in the metadata with the date they were added. Just run this program in SAS Studio or EG:

 

data users_grps; 
  
/* The LENGTH statement defines variables for function arguments and 
assigns the maximum length of each variable.  */   
  
  length uri name dispname group groupuri $256 
id MDUpdate $20; 
  
/* The CALL MISSING routine initializes output variables to missing values.*/ 
  
  n=1; 
      call missing(uri, name, dispname, group, groupuri, id, MDUpdate); 
  
     
  /* The METADATA_GETNOBJ function specifies to get the Person objects 
in the repository. The n argument specifies to get the first Person object that is 
returned. The uri argument will return the actual uri of the Person object that 
is returned. The program prints an informational message if no Person objects 
are found. */ 
  
      nobj=metadata_getnobj("omsobj:Person?@Id contains '.'",n,uri); 
  if nobj=0 then put 'No Persons available.'; 
  
/* The DO statement specifies a group of statements to be executed as a unit 
for the Person object that is returned by METADATA_GETNOBJ. The METADATA_GETATTR 
function gets the values of the object's Name and DisplayName attributes. */ 
  
  else do while (nobj > 0); 
     rc=metadata_getattr(uri, "Name", Name); 
     rc=metadata_getattr(uri, "DisplayName", DispName); 
  
  
/* The METADATA_GETNASN function gets objects associated via the IdentityGroups 
association. The a argument specifies to return the first associated object for 
that association type. The URI of the associated object is returned in the 
groupuri variable.  */ 
  
   a=1; 
     grpassn=metadata_getnasn(uri,"IdentityGroups",a,groupuri); 
         
        /* If a person does not belong to any groups, set their group 
          variable to 'No groups' and output their name. */ 
  
     if grpassn in (-3,-4) then do; 
            group="No groups"; 
        output; 
     end; 
  
        /* If the person belongs to many groups, loop through the list 
          and retrieve the Name and MetadataUpdated attributes of each group, 
            outputting each on a separate record. */ 
  
     else do while (grpassn > 0); 
        rc2=metadata_getattr(groupuri, "Name", group); 
        rc=metadata_getattr(groupuri, "MetadataUpdated", MDUpdate); 
        a+1; 
        output; 
        grpassn=metadata_getnasn(uri,"IdentityGroups",a,groupuri); 
     end; 
        
       /* Retrieve the next person's information */ 
  
     n+1; 
     nobj=metadata_getnobj("omsobj:Person?@Id contains '.'",n,uri); 
  end; 
  
/* The KEEP statement specifies the variables to include in the output data set. */ 
  
  keep name dispname MDUpdate group; 
run; 
  
   /* Display the list of users and their groups */ 
proc report data=users_grps nowd headline headskip; 
  columns name dispname group MDUpdate; 
  define name / order 'User Name' format=$30.; 
  define dispname / order 'Display Name' format=$30.; 
  define group / order 'Group' format=$30.; 
  define MDUpdate / display 'Updated' format=$20.; 
  break after name / skip; 
run; 

View solution in original post

8 REPLIES 8
alexal
SAS Employee

@marccarrion,

 

This SAS program produces a list of users and groups in the metadata with the date they were added. Just run this program in SAS Studio or EG:

 

data users_grps; 
  
/* The LENGTH statement defines variables for function arguments and 
assigns the maximum length of each variable.  */   
  
  length uri name dispname group groupuri $256 
id MDUpdate $20; 
  
/* The CALL MISSING routine initializes output variables to missing values.*/ 
  
  n=1; 
      call missing(uri, name, dispname, group, groupuri, id, MDUpdate); 
  
     
  /* The METADATA_GETNOBJ function specifies to get the Person objects 
in the repository. The n argument specifies to get the first Person object that is 
returned. The uri argument will return the actual uri of the Person object that 
is returned. The program prints an informational message if no Person objects 
are found. */ 
  
      nobj=metadata_getnobj("omsobj:Person?@Id contains '.'",n,uri); 
  if nobj=0 then put 'No Persons available.'; 
  
/* The DO statement specifies a group of statements to be executed as a unit 
for the Person object that is returned by METADATA_GETNOBJ. The METADATA_GETATTR 
function gets the values of the object's Name and DisplayName attributes. */ 
  
  else do while (nobj > 0); 
     rc=metadata_getattr(uri, "Name", Name); 
     rc=metadata_getattr(uri, "DisplayName", DispName); 
  
  
/* The METADATA_GETNASN function gets objects associated via the IdentityGroups 
association. The a argument specifies to return the first associated object for 
that association type. The URI of the associated object is returned in the 
groupuri variable.  */ 
  
   a=1; 
     grpassn=metadata_getnasn(uri,"IdentityGroups",a,groupuri); 
         
        /* If a person does not belong to any groups, set their group 
          variable to 'No groups' and output their name. */ 
  
     if grpassn in (-3,-4) then do; 
            group="No groups"; 
        output; 
     end; 
  
        /* If the person belongs to many groups, loop through the list 
          and retrieve the Name and MetadataUpdated attributes of each group, 
            outputting each on a separate record. */ 
  
     else do while (grpassn > 0); 
        rc2=metadata_getattr(groupuri, "Name", group); 
        rc=metadata_getattr(groupuri, "MetadataUpdated", MDUpdate); 
        a+1; 
        output; 
        grpassn=metadata_getnasn(uri,"IdentityGroups",a,groupuri); 
     end; 
        
       /* Retrieve the next person's information */ 
  
     n+1; 
     nobj=metadata_getnobj("omsobj:Person?@Id contains '.'",n,uri); 
  end; 
  
/* The KEEP statement specifies the variables to include in the output data set. */ 
  
  keep name dispname MDUpdate group; 
run; 
  
   /* Display the list of users and their groups */ 
proc report data=users_grps nowd headline headskip; 
  columns name dispname group MDUpdate; 
  define name / order 'User Name' format=$30.; 
  define dispname / order 'Display Name' format=$30.; 
  define group / order 'Group' format=$30.; 
  define MDUpdate / display 'Updated' format=$20.; 
  break after name / skip; 
run; 
marccarrion
Calcite | Level 5

Thanks Alexal, this was my first time using EG, and it worked great, just as you said. I'm going to check with my users to see what else they want on the report, but this gives me a great starting point. Thanks!!

 

Marc

sasprofile
Quartz | Level 8

Does this code works for single tier windows environment with just sas foundation and sas eg connects locally with no sas bi platform.

I ran the code but with 0 records. actually am looking for a code which can give list of all active users using sas (base sas/sas eg ) in our environment.

 

 

below is the log

 

 

1 The SAS System 13:27 Monday, June 3, 2019

1 ;*';*";*/;quit;run;
2 OPTIONS PAGENO=MIN;
3 %LET _CLIENTTASKLABEL='Program';
4 %LET _CLIENTPROCESSFLOWNAME='Process Flow';
5 %LET _CLIENTPROJECTPATH='';
6 %LET _CLIENTPROJECTNAME='';
7 %LET _SASPROGRAMFILE=;
8
9 ODS _ALL_ CLOSE;
10 OPTIONS DEV=PNG;
11 GOPTIONS XPIXELS=0 YPIXELS=0;
12 FILENAME EGSR TEMP;
13 ODS tagsets.sasreport13(ID=EGSR) FILE=EGSR
14 STYLE=HtmlBlue
15 STYLESHEET=(URL="file:///C:/Program%20Files/SASHome/SASEnterpriseGuide/7.1/Styles/HtmlBlue.css")
16 NOGTITLE
17 NOGFOOTNOTE
18 GPATH=&sasworklocation
19 ENCODING=UTF8
20 options(rolap="on")
21 ;
NOTE: Writing TAGSETS.SASREPORT13(EGSR) Body file: EGSR
22
23 GOPTIONS ACCESSIBLE;
24 data users_grps;
25
26 /* The LENGTH statement defines variables for function arguments and
27 assigns the maximum length of each variable. */
28
29 length uri name dispname group groupuri $256
30 id MDUpdate $20;
31
32 /* The CALL MISSING routine initializes output variables to missing values.*/
33
34 n=1;
35 call missing(uri, name, dispname, group, groupuri, id, MDUpdate);
36
37
38 /* The METADATA_GETNOBJ function specifies to get the Person objects
39 in the repository. The n argument specifies to get the first Person object that is
40 returned. The uri argument will return the actual uri of the Person object that
41 is returned. The program prints an informational message if no Person objects
42 are found. */
43
44 nobj=metadata_getnobj("omsobj:Person?@Id contains '.'",n,uri);
45 if nobj=0 then put 'No Persons available.';
46
47 /* The DO statement specifies a group of statements to be executed as a unit
48 for the Person object that is returned by METADATA_GETNOBJ. The METADATA_GETATTR
49 function gets the values of the object's Name and DisplayName attributes. */
50
51 else do while (nobj > 0);
52 rc=metadata_getattr(uri, "Name", Name);
53 rc=metadata_getattr(uri, "DisplayName", DispName);
54
55
56 /* The METADATA_GETNASN function gets objects associated via the IdentityGroups
57 association. The a argument specifies to return the first associated object for
2 The SAS System 13:27 Monday, June 3, 2019

58 that association type. The URI of the associated object is returned in the
59 groupuri variable. */
60
61 a=1;
62 grpassn=metadata_getnasn(uri,"IdentityGroups",a,groupuri);
63
64 /* If a person does not belong to any groups, set their group
65 variable to 'No groups' and output their name. */
66
67 if grpassn in (-3,-4) then do;
68 group="No groups";
69 output;
70 end;
71
72 /* If the person belongs to many groups, loop through the list
73 and retrieve the Name and MetadataUpdated attributes of each group,
74 outputting each on a separate record. */
75
76 else do while (grpassn > 0);
77 rc2=metadata_getattr(groupuri, "Name", group);
78 rc=metadata_getattr(groupuri, "MetadataUpdated", MDUpdate);
79 a+1;
80 output;
81 grpassn=metadata_getnasn(uri,"IdentityGroups",a,groupuri);
82 end;
83
84 /* Retrieve the next person's information */
85
86 n+1;
87 nobj=metadata_getnobj("omsobj:Person?@Id contains '.'",n,uri);
88 end;
89
90 /* The KEEP statement specifies the variables to include in the output data set. */
91
92 keep name dispname MDUpdate group;
93 run;

NOTE: The data set WORK.USERS_GRPS has 0 observations and 4 variables.
NOTE: DATA statement used (Total process time):
real time 0.15 seconds
cpu time 0.04 seconds

94
95 /* Display the list of users and their groups */
96 proc report data=users_grps nowd headline headskip;
97 columns name dispname group MDUpdate;
98 define name / order 'User Name' format=$30.;
99 define dispname / order 'Display Name' format=$30.;
100 define group / order 'Group' format=$30.;
101 define MDUpdate / display 'Updated' format=$20.;
102 break after name / skip;
103 run;

NOTE: No observations in data set WORK.USERS_GRPS.
NOTE: No observations in input data set.
NOTE: PROCEDURE REPORT used (Total process time):
real time 0.12 seconds
3 The SAS System 13:27 Monday, June 3, 2019

cpu time 0.04 seconds

104
105 GOPTIONS NOACCESSIBLE;
106 %LET _CLIENTTASKLABEL=;
107 %LET _CLIENTPROCESSFLOWNAME=;
108 %LET _CLIENTPROJECTPATH=;
109 %LET _CLIENTPROJECTNAME=;
110 %LET _SASPROGRAMFILE=;
111
112 ;*';*";*/;quit;run;
113 ODS _ALL_ CLOSE;
114
115
116 QUIT; RUN;
117

Kurt_Bremser
Super User

PLEASE (like "I'm begging on my knees, please!") use the {i} button for posting logs, so we don't get smileys and other funnies, and keep all text formatting as is. PLEASE.

 

Without a metadata server (which is included in BI Server offerings), there won't be much to query. But I am surprised that you don't get any error messages from the metadata functions.

 

If you only have local SAS installations and use the Local server from EG, there would also be no use trying to get all the users working with SAS from a single machine, as most of the users work on other machines anyway.

 

If such a need arises, I would seriously advise to switch to a BI Server based environment. You might even find that a centralized SAS setup is economically better than what you currently have (one single, more expensive license vs. lots of cheaper licenses, reduced maintenance costs, ...)

StevenW_Holmes
Calcite | Level 5

Awesome !! Thanks so much---

Mohitha
Calcite | Level 5
Hi Alexal. Can you please suggest me on how to get User Id in another column by using the above code?
AndrewHowell
Moderator

If you have Extended Monitoring turned on, the user/group report is one of a myriad of automated reports available through the Stored Process Web Server.

suga badge.PNGThe SAS Users Group for Administrators (SUGA) is open to all SAS administrators and architects who install, update, manage or maintain a SAS deployment. 

Join SUGA 

CLI in SAS Viya

Learn how to install the SAS Viya CLI and a few commands you may find useful in this video by SAS’ Darrell Barton.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 8 replies
  • 18242 views
  • 5 likes
  • 7 in conversation