Extracting Active Directory accounts into SAS using Powershell

byAllan|Jun 22, 2016|sas powershell ad-users windows domain accounts

Having a list of domain users and associated properties can be useful for a number of reasons:

  • Identifying joiners / leavers
  • Getting a list of employees
  • Converting usernames into email addresses
  • Alerting administrators when accounts are locked out
Below is an approach for obtaining those users, producing a SAS dataset that could serve in a DI job for staging a database table.
/_ set up command via pipe fileref /
options noquotelenmax;
filename process pipe
  “powershell ""Get-ADUser -Filter {enabled -eq $True} -properties Title,LockedOut,Created,Department,EmailAddress
  |Select-Object SamAccountName,Name,GivenName,Surname,UserPrincipalName,DistinguishedName,Title,LockedOut,created,Department,EmailAddress
  |Export-CSV  ‘%sysfunc(pathname(work))adusers.csv’ "" ”;

/* execute command and capture any output from the shell /
data null;
  infile process;
  input;
  list;
run;

/ load CSV into SAS dataset */
data ADUSERS;
  infile “%sysfunc(pathname(work))adusers.csv” dsd
    firstobs=3 lrecl=10000;
  input SamAccountName:$100. name:$100. GivenName:$50.
    SurName:$50. UserPrincipalName:$200.
    DistinguishedName:$500. Title:$100. LockedOut:$5.
    created:$25. Department:$50. EmailAddress:$100.;
  if index(DistinguishedName,‘OU=Employees’) then EMPLOYEEFLG=1;
  else EMPLOYEEFLG=0;
run;

This will extract all ACTIVE accounts (enabled eq $True) with some selected additional properties (more here).  Some conditional logic is applied to distinguish employees from service accounts.

Prerequisites:
  • X Command enabled (see here for instructions, is similar process for workspace servers)
  • Powershell installed
Enjoy!