Who’s been using my CAS tables?

3 Likes

What does the title remind you of? Maybe it reminds you of the Snow White fairy tale. Like the dwarfs, we want to know who has been doing what with "my" CAS tables.

This article will show you how to use the data collected by the Audit service to get the information on who has accessed the CAS tables and what kind of operation was performed.

The following topics are covered:

Read the audit entries related to CAS tables
Get a single View on the data returned
Example SAS code and audit entries

All examples run on SAS Viya Long Term Support 2023.10.

Read the audit entries related to CAS tables

We will make use of the sas-viya audit list command to retrieve the CAS related audit entries. My profile setting for the sas-viya command uses --output textby default. To be able to read these audit entries you have to be a SAS Administrator. Let's get started.

sas-viya audit list --application cas --after 2024-01-17T12:00Z --limit 10000 --sort-by
timeStamp

A few words on the options used:

Option	Description
`--application`	We are only interested in the audit entries for "cas".
`--after / --` `before`	We can specify a time frame using --after and/or --before. You can use a date like this 2024-01-17, or as in the example above specify minutes (and seconds), you do need the Z at the end. All timestamps are in UTC.
`--limit`	The default for limit is 50, so if you do not specify this option only 50 audit entries are returned.
`--sort-by`	By default the entries are sorted by the ID. So it is recommended to sort it by time stamp.

There are more options available, but most do not apply to what we want to do.

The result will look similar to this:

As we can see there is no information visible about CAS table access. However if we look at an individual audit entry using the sas-viya audit show-info command, we will see the details of the CAS table access, see example below:

So the audit entry has all the information about an operation on a CAS table like action (not the CAS action), state, caslib, table, scope, sessionUser etc.

You can get all the information by changing the output format to JSON. Example (the jq command is used to pretty print the JSON):

sas-viya --output json audit list --application cas --after 2024-01-17T14:46Z --limit 20000 --
sort-by timeStamp | jq

This screenshot shows the same audit entry as above.

Now it is only a matter of saving the result of the command to a file, so that it can then be read for instance by a SAS program. Example:

sas-viya --output json audit list --application cas --after 2024-01-17T14:45Z --limit 20000 --
sort-by timeStamp > audit.json

The "audit.json" file created by the command needs to be made available to the SAS environment where we later will run the SAS program to process the data.

I have used the --limit 20000in the commands above. I have found this to be the highest number that worked well for me, however on a busy system this might not return all audit entries. Using the

--after and --before options can help you to limit the time period, so less entries are returned.

Let's say for testing purposes we want to limit the audit entries for tables that begin with "CARS_...". We can use the "jq" command and apply some filtering. Here is an example:

sas-viya --output json audit list --application cas --after 2024-01-22T13:30Z --limit 10000 --
sort-by timeStamp | jq -c ' .items |= map( select( .properties.table| ascii_upcase | startswith("CARS_") ))' | jq

The first jq command does the filtering, the second jq does the pretty print

Get a single View on the data returned

We are going to use a SAS program to read the JSON file created earlier. The JSON libname engine allows us to read a JSON file and create a series of SAS data sets. The file used in the below libname statement is the one we saved with the command above.

/* 
 * read the json data 
 */ 
libname ajson json "/home/christine/audit.json" noalldata; 
proc copy in=ajson out=work; 
run;

Several SAS data sets will be copied to the WORK library. The ones we are interested in are ITEMS and ITEMS_PROPERTIES. The ITEMS data set contains all the basic information, the ITEMS_PROPERTIES data set has all the data we have seen before in the JSON under the key "properties".

Next we will join the two SAS data sets to have all the information together in one table. Here is an example to join those two SAS data sets:

/*
 * combine two tables
 */
proc sql;
create table work.cas_audit_entries as
   select
   t1.ordinal_items,
   t1.action,
   t1.application,
   t1.description,
   t1.id,
   t1.state,
   t1.timeStamp,
   input(t1.timeStamp, e8601dz32.) as timeStamp_sas format=e8601dz27.6,
   t1.type,
   t1.user,
   t2.caslib,
   t2.category,
   t2.controllerHost,
   t2.controllerPort,
   t2.scope,
   t2.sessionId,
   t2.sessionUser,
   t2.table,
   t2.createTimestamp,
   input(t2.createTimestamp, e8601dz32.) as createTimestamp_sas format=e8601dz27.6,
   t2.sourceCaslib,
   t2.sourceTable
   from
   work.items t1 left join work.items_properties t2
      on (t1.ordinal_items = t2.ordinal_items)
;
quit;

Since the timestamp information is a character value two additional variables are created, so the timestamps are proper SAS datetime values.

By now we have all information in one data set and we can start reporting on the data. You will notice that there are caslib names that look similar to this "_LIB_944E7BFE_7FB3FC0053F8". You can easily filter them out using a where statement like so: where not (caslib like '/_LIB/_%/_%' escape "/");

Example SAS code and audit entries created

Let's look at a few examples of SAS program code and the audit entry that is created from it.

Creating a CAS table using a DATA step

cas sugus sessopts=(caslib="casuser");
libname casuser cas caslib="casuser";

data casuser.cars_audit_ds;
set sashelp.cars;
run;

Resulting audit entry as JSON:

{
"action": "create",
"application": "cas",
"description": "Created table",
"httpContext": {},
"id": "3ff852dc-1a68-4b3f-ac43-d89056a3e8f9",
"links": [ ... ],
"properties": {
   "caslib": "CASUSER(christine)",
   "category": "change",
   "controllerHost": "controller.sas-cas-server-default.edu.svc.cluster.local",
   "controllerPort": "5570",
   "scope": "session",
   "sessionId": "028d460d-1c49-ea46-aaa3-8475b7afae0d",
   "sessionUser": "christine",
   "table": "CARS_AUDIT_DS"
 },
"state": "success",
"timeStamp": "2024-01-18T07:28:14.748771Z",
"type": "resource",
"user": "sas",
"version": 4
}

Loading a .sashdat file into memory and promote it to global scope

proc cas;
action table.loadtable /
caslib="workshop", path="employees_load.sashdat",
casout={caslib="workshop", name="employees_load", promote=true};
run;
quit;

For this program, two audit entries are created.

Entry for the load:

{
"action": "load",
"application": "cas",
"description": "Loaded table",
"httpContext": {},
"id": "077b0250-957a-42c5-a9f4-b5a669359c41",
"links": [ ...],
"properties": {
  "caslib": "Workshop",
  "category": "change",
  "controllerHost": "controller.sas-cas-server-default.edu.svc.cluster.local",
  "controllerPort": "5570",
  "scope": "session",
  "sessionId": "028d460d-1c49-ea46-aaa3-8475b7afae0d",
  "sessionUser": "christine",
  "table": "EMPLOYEES_LOAD" },
"state": "success",
"timeStamp": "2024-01-18T08:36:54.026319Z",
"type": "resource",
"user": "sas",
"version": 4
}

Entry for the promote:

{
"action": "promote",
"application": "cas",
"description": "Promoted a table to global scope",
"httpContext": {},
"id": "eddd2666-4488-4475-8646-7115445b2980",
"links": [ ... ],
"properties": {
  "caslib": "Workshop",
  "category": "change",
  "controllerHost": "controller.sas-cas-server-default.edu.svc.cluster.local",
  "controllerPort": "5570",
  "createTimestamp": "2024-01-18T08:36:54.024775+00:00",
  "scope": "global",
  "sessionId": "028d460d-1c49-ea46-aaa3-8475b7afae0d",
  "sessionUser": "christine",
  "sourceCaslib": "Workshop",
  "sourceTable": "employees_load.sashdat",
  "table": "EMPLOYEES_LOAD"
},
"state": "success",
"timeStamp": "2024-01-18T08:36:54.027316Z",
"type": "resource",
"user": "sas",
"version": 4
}

Dropping a CAS table (the one in memory)

proc cas;
action table.droptable / caslib="workshop", name="employees_load";
run;
quit;

Resulting audit entry as JSON:

{

"action": "drop",
"application": "cas",
"description": "Removed a table",
"httpContext": {},
"id": "bc82f224-2c62-4ec4-a665-f0c3c7eedd53",
"links": [ ... ],
"properties": {
   "caslib": "Workshop",
   "category": "change",
   "controllerHost": "controller.sas-cas-server-default.edu.svc.cluster.local",
   "controllerPort": "5570",
   "scope": "global",
   "sessionId": "4c2e302b-8a71-f94e-8e50-5bfb323fa078",
   "sessionUser": "christine",
   "table": "EMPLOYEES_LOAD"
},
"state": "success",
"timeStamp": "2024-01-18T10:07:04.631704Z",
"type": "resource",
"user": "sas",
"version": 4
}

Summary

All CAS table operations create an audit entry whether you use code or some visual application like SAS Visual Analytics or SAS Environment Manager. So, on a busy system, many caslibs/CAS tables/users, a lot of audit entries are created. In my experience the sas-viya audit list command can not handle 100 of thousands entries (--limit 20000 has worked on my system). If you want to have this information for a longer period, it might be better to archive them. See How to configure archiving of audit records in SAS Viya for an example on how to do it. Check the documentation on this as well: archive the audit entries. Also checkout Purging archived audit records on purging older archived audit entries.

Beside the operations on CAS tables, starting and stoping a CAS server will create audit entries as well. To see all start operations use this command: sas-viya audit list --application cas --action start

Find more articles from SAS Global Enablement and Learning here.