If you are a SAS Administrator who is tasked with managing SAS users including adding and removing SAS users in the SAS Management Console, assigning (or re-assigning) them to Groups and Roles, maintaining their General information and user Accounts, then you know that when number of users grow, at some point things can get messy.
SAS Users Definition in the Metadata
Suppose, you have multiple SAS users defined in the SAS Management Console as in the following example:
1 - General tab:
2 - Groups and Roles tab:
3 - Accounts tab:
After adding a couple dozen (or hundred) users to SAS Metadata using the User Manager plug-in of the SAS Management Console you will start dreaming of some “push-a-button” report that will show a listing of all your SAS users with all the relevant information (name, title, description, e-mail, groups, user id, etc.) in a plain view.
Luckily, SAS provides you with a little-known, but well-documented macro that makes such SAS users reporting a snap.
%MDUEXTR Macro
This macro is documented in the SAS Intelligence Platform: Security Administration Guide and it is one of the several User Import Macros that you get with SAS Business Intelligence or SAS Visual Analytics installation. When invoked, it extracts SAS user identity information from the SAS metadata and creates several canonical tables:
SAS Users Report Implementation
Having the above tables at your fingertips, you can easily combine them in any way you need to generate a report you want. Here is the code example:
/* Connect to the SAS metadata server */ options metaserver = "a123.us.company.com" metaport = 8561 metauser = "sasadm@saspw" metapass = "{SAS002}1D57933958C580064BD3DCA81A33DFB2" metarepository = Foundation metaprotocol = bridge ; /* Extract user information from the SAS metadata */ %mduextr(libref=work); /* Combine data from multiple tables */ data work.metadata_users (drop=keyid); merge work.person (keep=keyid name DisplayName title description in=user) work.logins (keep=keyid UserID) work.groupmempersons_info (keep=memid name rename=(name=groupname memid=keyid)) work.email (keep=keyid emailAddr) ; by keyid; if user; run; proc sort data=work.metadata_users; by name groupname; run; /* Blank out duplicate information */ data work.metadata_users_ready; set work.metadata_users; by name; array a [*] name DisplayName title description emailAddr UserID; if not first.name then do i=1 to dim(a); a[i] = ''; end; run; /* Specify output file location */ filename fout 'C:\PROJECTS\_BLOG_SAS\sas-admin-keeping-track-of-sas-users\user-roster.html'; /* Generate report on SAS metadata users and their groups */ ods html file=fout; title "SAS Metadata Registered Users (as of %sysfunc(putn(%sysfunc(datetime()),datetime19.)))"; proc print data=work.metadata_users_ready noobs label; var name DisplayName title description emailAddr UserID groupname; label name = 'User Name' DisplayName = 'Display Name' title = 'Job Title' description = 'Description' emailAddr = 'Email Address' UserID = 'User ID' groupname = 'Member of Group' ; run; ods html close; |
SAS Metadata Users Report Output
Here is a fragment of the SAS Users report output in HTML format produced by the above code (you may click on the image below to see the report in a web browser):
Note
In the above implementation, we assumed for simplicity that each user has a single user id. If that is not the case, you would need to modify the code section denoted as /* Blank out duplicate information */.
37 Comments
Hello Team,
I need help to find out the inactive users in SAS, want to remove them for SAS and reduce the users who are not using SAS.
Also want to know how many jobs scheduled on schedule manager in SAS SMC,
Help me with the answers.
You can explore SAS users activity by analyzing SAS metadata logs (found in Lev1\SASMeta\MetadataServer\Logs). If you schedule SAS job via SAS Management Console, you can find and count them in SAS Management Console. For more specific information please contact SAS Technical Support.
Hi, is there a way for me to see who created a user or group?
Thanks.
Hi Ronny, yes, there is a way to see who created a user or a group. You can find user activity in SAS metadata log (e.g. location SAS/Config/Lev1/SASMeta/MetadataServer/Logs/SASMeta_MetadataServer_2021-09-28_MACHINEID_XXXX.log). For example, I just created a new Group called AAA, then looked in the metadata log and found the following records:
2021-09-28T09:24:55,584 INFO [02142337] 68313:USERID@DOMAIN - Audit Public Object Type=User group Name=AAA ObjId=A50W7ZWR.A500001L has been added.
2021-09-28T09:24:55,584 INFO [02142337] 68313:USERID@DOMAIN - Added IdentityType=IdentityGroup Name=AAA, ObjId=A50W7ZWR.A500001L.
USERID@DOMAIN indicates who created group AAA.
Hope this helps.
Can we access sas useid creation date. I need to create a report showing users added in FY2019
Hi Michael,
Thank you for your great question. Yes, we can get creation dates of all users and then you can report on them as you wish. Take a look at SAS Global Forum paper Exploring the Metadata Family Tree by Elena Muriel, look at page 6. It shows various attributes and their values that you can get for the given Person object using METADATA_GETNATR() function.
You can get a list of all users using %mduextr macro (Person data table). Below is a code sample that I put together to loop through all the users and filter out only the attribute of our interest MetadataCreated (there is also attribute MetadataUpdated for your reporting needs):
%mduextr(libref=work); data WORK.USERS (keep=NAME CREATEDATE); set WORK.PERSON; length attr metavalue $256; format CREATEDATE yymmdd10.; call missing(attr, metavalue); do i=1 by 1 until(rc < 0); rc = metadata_getnatr("omsobj:Person?@Name='"||strip(NAME)||"'",i,attr,metavalue); if rc>0 and attr='MetadataCreated' then do; CREATEDATE = datepart(input(metavalue, datetime.)); output; end; end; run;From the output data table WORK.USERS you can report on the users and their corresponding CREATEDATE.
Hope this helps.
Leonid thank you so much for that code. Apart from the created date and updated date for a user ID, is it possible to find out who created that user ID? Asking since we have multiple admins and it would be great to figure out who granted access to an individual.
Hi Bob, thank you for your comment and the question. The tool described in this blog post is limited to extracting information on users' registration in the metadata. It is possible to track users' activities (including administrators' activities) by inspecting/parsing SAS metadata log (see in Lev1/SASMeta/MetadataServer/Logs). You may also find useful Metadata user activities discussion on SAS Support Community.
Thank you for your prompt reply Leonid! I'll check it out.
You are very welcome, Bob!
Hi All,
I need to get result which SAS users using which SAS application in my environment?
Thank you,
Ganga
Hi Ganga,
The information you are asking about is not stored in the SAS metadata. For that you will need to look into SAS Web Applications logs (if you are talking about Web Applications) found at:
/SAS-configuration-directory/Lev1/Web/WebAppServer/SASServer1_1/logs/server.log
/SAS-configuration-directory/Lev1/Web/WebAppServer/SASServer1_1/logs/gemfire.log
For more info, please refer to Usage Note 55426: Locating the log files for the SAS® 9.4 middle tier.
I have a problem because I need to restrict access to users in SAS 9.4 Guide through an instruction or code since I have a process that identifies the space used and if it exceeds the maximum quota it would disable access, automatically. I thank you for your answer, hoping you can help me.
Hi Mauricio,
For that kind of help, I suggest contacting SAS Technical Support.
Hi Leonid, nice blog. I'm trying to extract user identities from SAS VIYA server using metadata functions. I'm not sure if its supported in SAS VIYA?? In SAS 9.4 i have used open metadata interface for metadata management. What would you recommend to extract user identities in SAS Viya platform or in general what technique to use for metadata management in SAS VIYA? thanks , much appreciated
Hi Salman,
SAS Viya does not have a metadata server -- not in the way that you know it from SAS 9. However, it does have a robust admin tool in a command-line interface. This blog post should cover it.
El código es excelente, en mi caso me esta dando el siguiente error. Soy nuevo en esta area.
RROR: IOM call failed because of a data conversion error.
ERROR: Fallos al transcodificar los datos de la codificación U_UTF8_CE a U_LATIN1_CE porque contenían caracteres que no permiten en
la codificación de la sesión SAS. Revise las opciones del Sistema SAS encoding= y locale= para garantizar que pueden acomodarse a
los datos que desea procesar. Una parte de esta cadena de origen, en representación hexadecimal es:
NOTE: 7f74f267a130: 3c 47 65 74 4d 65 74 61 64 61 74 61 4f 62 6a 65 |A00|
ERROR: Some code points did not transcode.
This error can occur if the data you access contains characters aren't part of the character set in the SAS session encoding. The best way to fix this is to use SAS with ENCODING=utf8. This is a SAS startup option that an admin needs to set.
Hi,
Can I please get the last login details of the user in SAS from metadata? From the other blogs, I use the Metadata server logs. But unfortunately it contains only the login details of few super users but not the details of all the users.
Help on this request is appreciated. Thanks!
SAS users login details are not stored in the SAS metadata, therefore you cannot get them from there. If you experience a problem getting the login details from metadata server logs, I suggest contacting SAS Technical Support. Follow their steps 1, 2, and 3.
Thanks very much for this wonderful code.
I have been looking for this long time ago. I am implementing solution that route work for each user in certain group.
As a workaround I created table in database and inserted all users in it.
But now I used your code to use meta data instead.
Thanks again. You made my day.
Great to hear from you, Ashrat, that this solution was useful for you. Thank you for your complimentary feedback.
Is there a way i can find out and generate a metrics report on how many users use sas everyday from the mainframe side
Mahek, I am sure there is. However, without knowing your specific system configuration and used SAS products it is impossible to answer your question. I suggest you contact SAS technical support. Be ready to provide you SAS Site Number and Operating System.
Thanks Leonid.
Also had a question i know this is not the right discussion but was curious if you or anyone can help me with some document on maintenance upgrade for sas9.4
Mahek, again I have to refer you to SAS Technical Support. This is free service that comes with your SAS software license. Best regards.
Love the script, we have three different metadata systems on different hosts and need to concatenated them to provide for linux user accounts. This got me all the details I needed!
Thank you, Clinton, for your kind feedback. I am glad to hear it is useful for you and your company.
I want to rename group. I tried to update name by keeping same keyid in idgrps but it is updating displayname instead..any idea how to fix this.
I don't believe you can rename a group or a user name in SAS Management Console, only display name, see Renaming a user, group or role in SAS Management Console, the Name field is grayed out as it is an identifier of the Group object in SAS metadata (you can't create a new group or user with an existing name). You would need to create a new Group and delete the old one instead of renaming.
Very helpful macro code. If I'm not mistaken, this is a similar report that is generated from within SAS Environment Manager (Report Center) ? Assuming you have all the pre-requisites (APM enabled, log sharing..) configured for the Environment Manager 2.5..
Hello -
This program is very useful as there are hundreds of users in my organization. Can we add when a user last logged in?
In short I need to know when a user last connected to SAS server using SAS EG or PC-SAS.
Appreciate your quick response.
Thank you, Praveen, for your comment.
This blog post is written around %MDUEXTR macro that is limited to information on users' registration in the metadata. Tracking users' connection can be done by parsing SAS metadata log (see in Lev1/SASMeta/MetadataServer/Logs). You may also find useful the following discussion Metadata user activities.
I am a sas administrator and I need help with permission access. I am trying to delete a user name where the write metadata for the user is marked deny instead of granted. Is there a way to reverse the permission access for a user name? Currently I do not have access to change the permission or delete the user id.
KG, I can't provide you assistance via this blog. Please contact SAS Technical Support.
Hello Leonid,
interesting example to readout sas metadata with some simple steps.
It`s quite a good way to document the user / group structure.
kind regards
Marius
Thank you, Marius.