This is probably more than you’re asking for, but while it’s still rather fresh, and in case it’s helpful to others, I thought I’d share more about how I’ve worked to develop the overall process, as well as some aspects of my scripts. I’m definitely open to easier approaches to this. My primary goal it to work toward automating as much of this as possible, so as you’ll see I focused on scripting support for variations in the data I’ll get from each system.
As I began working on this I ran across several considerations in designing a process. Most of it has nothing to do with eramba, but affects how I prepare the seed files (or whatever they’re called now).
I started by defining who authorized users are for our company, and where I would obtain that list from. This is for my Exit reviews (I decided for now to just do Exits against a list of currently active authorized users). Turns out I need:
- HR’s Employee list
- HR’s contingent labor list (contractors, consultants, temp, outsourced, etc. Anyone not an employee, but recognized as an authorized user in some capacity.)
- Authorized “non-human” accounts (we often use the term “Service Account”, but it can also include things like Bots, core administrative or guest accounts, or any other authorized account on a system not tied to an identifiable individual. This list can vary from system to system, and there is overlap in naming (many systems have SA or Admin accounts, so I have to know what its called for each system).
Next, I need to know who is going to recertify a given system’s users and non-human accounts. Is it the individual managers of the users, is it a system or data owner, or some combination? Included in this is figuring out how to deal with company executives and board members, or other senior people. Can we establish a consistent policy that all Managing Directors and below must approve users, but levels above that may delegate? I have to program how will we manage that.
Finally, since each system has its own approach to UserIDs and account management, I need a way to reliably link a given system user ID to a known authorized user, and identify where they work and who their manager is. We’re using Active Directory to do this “normalization” to a known person (and a spreadsheet to track non-system accounts for a given system).
Collecting User List Reports
For each system I need to review I work on how I will collect the user list. I need a CSV or XLSX format report, and I’m able to handle some minimal formatting. I need a header row and a minimum set of fields: UserID, UserName (fn and ln combined or separated), Roles, only active accounts or a field which indicates active/enabled status, last login date, and email address of the user. In some cases I’ll take additional details which may help with a particular review, such as product (some systems have sub-products), a license type, profile type, or other relevant fields. I prefer that each Role for a user be represented by a separate row/record, and that the overall report be minimally processed, as I’m going to handle the processing later when I prepare it for eramba.
If the collection of this data can be configured as an automated report or query I work with the appropriate teams to set that up. In my case, our reporting team has established a set of reports based on sql queries I collect from system owners. In other cases, it can’t be automated due to some system limitation, so I work with the owner to define what is needed so they can send it to me on request. (Maybe I can get those added to our RPA tool down the road!)
Preparing To Bring It Under Management
You’ll find that no two systems use the same field names. I developed a set of mapping tables to map the names provided to a standard set I could process automatically in a script. For each new system I add to my process I first define the mapping tables that will be used. Source and Target file prefix names and locations, and any special filtering or processing requirements (start on excel file row 3, filter on descriptions that regex match “Information Technology”, filter on accounts flagged as not checking in for over 65 days, etc.). For example, in my default mapping group, to get a UserID I accept ‘UserID’, ‘User ID’, ‘Login ID’, ‘LoginID’, ‘Login Name’, or ‘LoginName’. If something other field name is used (such as an email address, or ‘Name’), I specify an additional mapping group so that the email address or Name field is read as the UserID.
As I start collecting user list files for each system I preparing my mapping table to support any variations and “bring it under support”.
Scripting It All Out
As for scripting, I’m using PowerShell 5 which gives me direct access to SQL queries (assuming I have an authorized account), AD user information queries, and good data management capabilities. There are many other scripting language options which may be better suited for other environments. My script is continually growing and adapting, but the high-level outline I’m using is:
- Look for any updates to my authorized users lists and update the Exit Seed File.
- Read the list of App Reports files to be processed, remove any dated/prior files (all my report file names include the date in yyyymmdd format) or files not already under support.
- For each of the supported App Reports I:
- Read the headers and normalize to my standard field set per the mapping table, removing fields I don’t need to process.
- Using the updated headers I read in the App Report
- Match each App UserID against Active Directory by one solid criteria (Employee ID, userID match, email address match, first name/last name match (assuming there is only one match)
- If no match can be made, check the spreadsheet for a known service account to match with.
- If all else fails it drops to the No-Match list.
- For each App UserID I collect the matched users AD ID, AD DisplayName, Department, and Manager. For un-matched IDs I assign some default values so its clear these weren’t matched to a known person or ID.
- I consolidate all the Roles from the User report for each user.
- I assemble all this information for a user in to an eramba Seed File record: UserID (from AD if matched), Roles (consolidated previously), Description (a combination of the App UserID, App fields like Last Login, email address, user name, etc., and any matched AD fields that will help the reviewer such as Profile, Department, etc.
- Once I’ve processed all the App UserIDs and assembled my eramba Seed File report I output that to a CSV file, taking care to back up/archive any previous versions. I generally output one Seed File for each Manager/Reviewer, a Seed File of all users, my No-Manager list, my Service-Accounts list, and if called for, Seed Files based on special criteria for a given application. The No-Manager and Service-Accounts lists help me identify accounts that may need more research or documentation.
I’m not a developer in my day job, so this has taken some time and a lot of google/youtube searches, reading, and trial and error (I’ve got some good resources to bounce ideas off of internally as well). Happy to share more script details if PowerShell is relevant for others.