Finding Private User Folders for Deleted SAS Platform Users

“How do I find all the private user folders for SAS users that have been deleted so I know which folders can be removed?”

I was asked this question recently and thought that I should be able to build up a single line XMLSELECT style SAS® metadata query to answer it. I could then paste this query in our free Metacoda Metadata Explorer Plug-in and export the results as a CSV file.

If you’re reading this post because that’s something you need to do, and you want the answer quickly, then here is the query:

{{{ Tree[@PublicType='Folder'][not(AssociatedIdentity/Person)][ParentTree/Tree[@Name='User Folders']/SoftwareComponents/SoftwareComponent[@PublicType='RootFolder']] }}}

The 3 curly brackets at the beginning and end are only needed if you are using the Metacoda Metadata Explorer Plug-in as it’s a signal that it is not a simple text search but an advanced XMLSELECT style query. If you are plugging this into some SAS PROC METADATA code then strip off the curly brackets.

If you want to know how that query works and see some other XMLSELECT samples for some of the intermediate results then read on …

Intermediate Working Steps

The SAS Open Metadata API XMLSELECT feature allows you to express some powerful and flexible metadata filters queries in a single line. They can be a little cryptic and I only know of a few examples of advanced usage. The XMLSELECT documentation is a good place to start. I have also posted several examples in a Metadata Explorer Plug-in blog post. I thought it might be useful to add to the collection of examples by posting the queries from the intermediate steps as I was working on building the query above.

These are all of the private user folders in this small test environment:

There are 14 of them. One of them is for the deleted user Larry Lomax and the other 13 are for users that still exist in metadata. In the examples below I’m working towards finding a query that gives me just the folder for Larry.

1) Find all (private user) folders associated with existing users:

{{{ Tree[@PublicType='Folder'][AssociatedIdentity/Person] }}}

This query finds all metadata objects with a model type of Tree that have a PublicType attribute value of ‘Folder’. It then further subsets that to those folders that are associated with Person (user) objects through the association named AssociatedIdentity. These types of folders are the private users folders. The results exclude private user folders for users that have been deleted (where there is no associated identity). It is essentially the opposite of what I wanted and returned 13 folders in my test environment.

2) Find all folders NOT associated with existing users:

{{{ Tree[@PublicType='Folder'][not(AssociatedIdentity/Person)] }}}

This is heading in the right direction but needs refinement. I want folders that are not associated with users (because those users have been deleted), but this includes every other folder in metadata including ones that are not under the ‘/User Folders’ top level folder. That’s way too many folders, 227 in this small test environment. I need to limit it to folders underneath ‘/User Folders’. Note that this folder was renamed in SAS 9.4 so if you are using SAS 9.2 or SAS 9.3 the top level folder is just ‘/Users’ and the query should be adjusted accordingly.

3) Find all folders NOT associated with existing users whose parent folder is named ‘User Folders’:

{{{ Tree[@PublicType='Folder'][not(AssociatedIdentity/Person)][ParentTree/Tree[@Name='User Folders'] }}}

I’m almost there and in most SAS environments this would be enough, however I have been devious and created another folder called ‘User Folders’ underneath the top level ‘Shared Data’ folder so it’s finding all the sub-folders under there too. I should refine the query to make sure it only looks under a TOP level folder named ‘User Folders’.

4) Find all folders NOT associated with existing users whose parent folder is a top level folder named ‘User Folders’:

{{{ Tree[@PublicType='Folder'][not(AssociatedIdentity/Person)][ParentTree/Tree[@Name='User Folders']/SoftwareComponents/SoftwareComponent[@PublicType='RootFolder'] }}}

In this last example I further constrain the parent folder test so it only returns those parent folders named ‘User Folders’ that are in turn associated with the root folder: associated through an associated named SoftwareComponents with a SoftwareComponent model type object that has a PublicType attribute value of ‘RootFolder’.

Now I have the result I want.

I hope you’ve found these examples useful. If you have any other interesting XMLSELECT examples that you’d like to share, please feel free to post them as comments below.

Update 05Feb2017: A reader asked me a question about an error they were getting with SAS 9.2 which reminded me that I had forgotten to point out that this query will only work with SAS 9.3 M0 and above. The reason for this is that support for the NOT logical operator in XMLSELECT metadata queries was added in SAS 9.3 M0 and so is not available in the earlier SAS 9.2 releases. If you try this query in SAS 9.2 you will get an error similar to this:

Improper syntax was detected at location '31' in the XMLSELECT option 'Tree[@PublicType='Folder'][not(AssociatedIdentity/Person)][ParentTree/Tree[@Name='User Folders']/SoftwareComponents/SoftwareComponent[@PublicType='RootFolder']]' used to select metadata.  The error is 'An unexpected XMLSELECT character '(' was found in the xpath syntax.'.

You will see that the ‘(‘ character at position 31 is the open bracket immediately after the NOT operator.

SAS 9.3 M0 included a few other useful SAS Metadata API enhancements that can be seen in the What’s New page in the SAS 9.3 Open Metadata Interface: Reference and Usage book