query SAS Metadata to find list of active users accessing SAS Studio/SAS EG

[shirishkamath]

Hi All,

Does anyone know how to query SAS Metadata to find list of active users accessing SAS Studio/SAS EG from certain date till now? Basically, we need to do a cleanup of users from SMC and hence we need to understand the usage statistics of all users.

Thanks in advance.

[PaulHomes]

The usage information you require is not in SAS metadata. You can find a list of potential users in metadata, but for actual platform usage information you will need to look in log files. To save you time I would strongly suggest you start by looking at the SAS Environment Manager Service Architecture Data Marts. The Service Architecture Framework Data Mart tables contains information extracted from log files (as well as other sources). You may find the information you need in those tables (or at least a subset as a starting point).

For more information start by reading the Understanding SAS Environment Manager Service Architecture section in the SAS Environment Manager 2.5 Users Guide. In that book you will find information on how to enable the Service Architecture Framework (if not already enabled at your site), the reports available in the Report Centre, as well as information on the structure of the data mart tables.

[Kurt_Bremser]

I can give you example code for retrieving usage data from the spawner log, if you want to go down that path.

[ozg1969]

Good morning Kurt,

I am in need of reading spawner log to determine who utilizes SAS Studo, and SAS EG. Would you mind sharing sample code you have ?

Thank you very much indeed.

Eren O.

[Kurt_Bremser · Accepted solution]

See here:

filename in "&infile1" recfm=v lrecl=512;

%macro read_spawnlog;
data spawnlog;
infile in truncover;
format
  datum ddmmyyp10.
  zeit  time8.
  user  $8.
  port  $4.
  address $15.
  success $1.
;
input
zeile $512.
;
if index(zeile,"New client connection") > 0;
%if "&sysver" = "9.4"
%then %do;
datum = input(substr(zeile,1,10),yymmdd10.);
zeit = input(substr(zeile,12,8),time8.);
%end;
%else %if "&sysver" = "9.2"
%then %do;
datum = input(substr(zeile,1,10),yymmdd10.);
zeit = input(substr(zeile,12,8),time8.);
%end;
%else %if "&sysver" = "9.1"
%then %do;
datum = input(substr(zeile,1,8),yymmdd8.);
zeit = input(substr(zeile,10,8),time8.);
%end;
%else %do;
abort abend 7;
%end;
pos = index(zeile,"server port");
port = substr(zeile,pos+12,4);
pos = index(zeile,"for user");
if pos ne 0
then do;
  user = substr(zeile,pos+9,5);
  pos = indexc(user,".");
  if pos ne 0 then user = substr(user,1,pos-1);
end;
else do;
  pos = index(zeile,"for SAS token user");
  user = substr(zeile,pos+19);
  pos = indexc(user,"@");
  if pos ne 0 then user = substr(user,1,pos-1);
end;
pos = index(zeile,"port are");
%if "&sysver" = "9.4"
%then %do;
address = substr(zeile,pos+17,15);
pos = indexc(address,"]");
if pos > 0 then address = substr(address,1,pos-1);
%end;
%else %if "&sysver" = "9.2"
%then %do;
address = substr(zeile,pos+17,15);
pos = indexc(address,"]");
if pos > 0 then address = substr(address,1,pos-1);
%end;
%else %do;
address = substr(zeile,pos+9,15);
pos = indexc(address,":");
if pos > 0 then address = substr(address,1,pos-1);
%end;
if index(zeile,"rejected") > 0 then success = "N"; else success = "J";
drop zeile pos;
run;
%mend;

The macro definition is only there to deal with different logfile layouts because of SAS versions.

[ozg1969]

I appreciate that. Thank you very much indeed.

[sasprofile]

Hi Kurtbremser

Am looking for a way to find the list of active users accessing or using PC SAS/SAS Studio/SAS EG in our environment, The code which you posted back in Nov 2018, I tried to run it from SAS Studio and SAS EG (connects locally), but It didn't not give any output nor any error.does this code works or do I need to make any changes to this code in order to make it work.

your help here would be really appreciated.

[Kurt_Bremser]

When code (especially one from an accepted solution) does not work for you, it is ALWAYS a VERY GOOD IDEA to post the log.

[sasprofile]

Below is the log, I dont see error or any info that would be helpful

1 The SAS System 13:26 Sunday, June 2, 2019

1 ;*';*";*/;quit;run;
2 OPTIONS PAGENO=MIN;
3 %LET _CLIENTTASKLABEL='Program';
4 %LET _CLIENTPROCESSFLOWNAME='Process Flow';
5 %LET _CLIENTPROJECTPATH='';
6 %LET _CLIENTPROJECTPATHHOST='';
7 %LET _CLIENTPROJECTNAME='';
8 %LET _SASPROGRAMFILE='';
9 %LET _SASPROGRAMFILEHOST='';
10
11 ODS _ALL_ CLOSE;
12 OPTIONS DEV=PNG;
13 GOPTIONS XPIXELS=0 YPIXELS=0;
14 FILENAME EGSR TEMP;
15 ODS tagsets.sasreport13(ID=EGSR) FILE=EGSR
16 STYLE=HtmlBlue
17 STYLESHEET=(URL="file:///F:/app/SAS94/SASEnterpriseGuide/7.1/Styles/HtmlBlue.css")
18 NOGTITLE
19 NOGFOOTNOTE
20 GPATH=&sasworklocation
21 ENCODING=UTF8
22 options(rolap="on")
23 ;
NOTE: Writing TAGSETS.SASREPORT13(EGSR) Body file: EGSR
24
25 GOPTIONS ACCESSIBLE;
26 filename in "&infile1" recfm=v lrecl=512;
WARNING: Apparent symbolic reference INFILE1 not resolved.
27
28 %macro read_spawnlog;
29 data spawnlog;
30 infile in truncover;
31 format
32 datum ddmmyyp10.
33 zeit time8.
34 user $8.
35 port $4.
36 address $15.
37 success $1.
38 ;
39 input
40 zeile $512.
41 ;
42 if index(zeile,"New client connection") > 0;
43 %if "&sysver" = "9.4"
44 %then %do;
45 datum = input(substr(zeile,1,10),yymmdd10.);
46 zeit = input(substr(zeile,12,8),time8.);
47 %end;
48 %else %if "&sysver" = "9.2"
49 %then %do;
50 datum = input(substr(zeile,1,10),yymmdd10.);
51 zeit = input(substr(zeile,12,8),time8.);
52 %end;
53 %else %if "&sysver" = "9.1"
54 %then %do;
55 datum = input(substr(zeile,1,8),yymmdd8.);
56 zeit = input(substr(zeile,10,8),time8.);
2 The SAS System 13:26 Sunday, June 2, 2019

57 %end;
58 %else %do;
59 abort abend 7;
60 %end;
61 pos = index(zeile,"server port");
62 port = substr(zeile,pos+12,4);
63 pos = index(zeile,"for user");
64 if pos ne 0
65 then do;
66 user = substr(zeile,pos+9,5);
67 pos = indexc(user,".");
68 if pos ne 0 then user = substr(user,1,pos-1);
69 end;
70 else do;
71 pos = index(zeile,"for SAS token user");
72 user = substr(zeile,pos+19);
73 pos = indexc(user,"@");
74 if pos ne 0 then user = substr(user,1,pos-1);
75 end;
76 pos = index(zeile,"port are");
77 %if "&sysver" = "9.4"
78 %then %do;
79 address = substr(zeile,pos+17,15);
80 pos = indexc(address,"]");
81 if pos > 0 then address = substr(address,1,pos-1);
82 %end;
83 %else %if "&sysver" = "9.2"
84 %then %do;
85 address = substr(zeile,pos+17,15);
86 pos = indexc(address,"]");
87 if pos > 0 then address = substr(address,1,pos-1);
88 %end;
89 %else %do;
90 address = substr(zeile,pos+9,15);
91 pos = indexc(address,":");
92 if pos > 0 then address = substr(address,1,pos-1);
93 %end;
94 if index(zeile,"rejected") > 0 then success = "N"; else success = "J";
95 drop zeile pos;
96 run;
97 %mend;
98
99 GOPTIONS NOACCESSIBLE;
100 %LET _CLIENTTASKLABEL=;
101 %LET _CLIENTPROCESSFLOWNAME=;
102 %LET _CLIENTPROJECTPATH=;
103 %LET _CLIENTPROJECTPATHHOST=;
104 %LET _CLIENTPROJECTNAME=;
105 %LET _SASPROGRAMFILE=;
106 %LET _SASPROGRAMFILEHOST=;
107
108 ;*';*";*/;quit;run;
109 ODS _ALL_ CLOSE;
110
111
112 QUIT; RUN;
113

[MargaretC]

On line 26, the FILENAME statement is trying to assign a physical path name for a file to the symbolic reference IN. as you can see, there is a WARNING that the &infile1 did not resolve to a file. This is bad.
On line 30, the DATA step has an INFILE statement that is trying to resolve IN to the physical file. And since this does not resolve to a file, the DATA has no data to read in, and thereby the step fails.
Does this make sense?
Margaret

[sasprofile]

Could you please be more clear about the physical path, which data physical path file you are saying to declare.

fyi ours is just basic sas foundation environment with sas eg connects locally to foundation. we dont have any sas bi platform.

so i want to know which are all the user actively using base sas and sas eg.

btw we have only one 9.4 version.

%let infile1= \c:\xxxx\xxxx/xxxxx;   ( which phyical path i need to declare here )

[Kurt_Bremser]

---

@sasprofile wrote:

Could you please be more clear about the physical path, which data physical path file you are saying to declare.

fyi ours is just basic sas foundation environment with sas eg connects locally to foundation. we dont have any sas bi platform.

so i want to know which are all the user actively using base sas and sas eg.

btw we have only one 9.4 version.

%let infile1= \c:\xxxx\xxxx/xxxxx;   ( which phyical path i need to declare here )

---

You need the physical path of your Metadata Server log. This location depends on your SAS installation, so ask your SAS administrator.

From your other post I take it that you do not have a metadata server at all, so this code won't be of any help to you anyway.

For better help, provide thorough information about how your SAS system is set up and how you use it.

[Kurt_Bremser]

infile1 is a macro variable, which you should set to the whole physical pathname of your log file.

After fixing that, it is essential that you not only define the macro (that's what my code does), but actually call it.

Note that the macro definition around the code is only necessary to deal with different SAS versions; if you only need code for one, you can remove the other branches and the whole macro logic.

[boemskats]

If for whatever reason the answers already provided here don't give you what you need, here is an approach that might help you gather this data moving forward. It won't give you an answer as to who has logged in in the last 3 months, but will tell you who has logged in in the next 3 :). Also, as you're talking about SASStudio and EG only, this approach will cover all bases.

Assuming you're on Linux, add the following to your WorkspaceServer_usermods.sh:

echo "$(date --iso-8601),$METAUSER" >> /somepath/useraudit.txt

(note, make sure that your somepath is writable by all users)

This will over time give you a logfile that looks like this:

2018-03-03,sasdemo
2018-03-04,nik
2018-03-04,nik
2018-03-04,drjim
2018-03-04,drjim
2018-03-04,drjim
2018-03-05,allan
2018-03-05,drjim

You can see you have the date of the logon and the username. Should be relatively simple to read into SAS.

For something a bit more useful in terms of managing metadata identities, once you've read the above in you can reconcile the logins with those login's user objects using something like this.

data stuff;
  format userID identityID userName userDisplayName $200.;
  * read in your parsed log dataset ;
  set my_logfile_with_user_field_as_userID;
  * get identity of the user that logged on ;
  rc=metadata_getnasn(cats("omsobj:Login?@UserID contains '",userID,"'"),
                      "AssociatedIdentity", 1, identityID);
  * get identity name & displayname properties;
  rc=metadata_getattr(identityID, "Name", userName);
  rc=metadata_getattr(identityID, "DisplayName", userDisplayName);
run;

You'll need to run the above code with admin privileges in Metadata as it'll need to be able to read everyone's Login objects. The resulting table will tell you who logged in and when.

Nik

[nhvdwalt]

Currently we're doing this....

1.) Get a list of named users - This is really just a list of user ids extracted from /etc/passwd. User accounts follow a naming standard, so they are easy to spot.

2.) In the Workspace Server and Batch Server usermods.sh file, write a piece of code that logs $USER to a .txt file. Similar to what @boemskats is doing. Then at some point, just do a unique sort of these users ids. This will give you a unique list of users that have actually used the platform in x period. The delta between the named users and the active users is a good starting point to start some cleanups.