Derek Wilkinson is part of the Desktop Engineer Team at FlightAware. He provides support for internal IT across the company.
Most opportunities for automation are discovered during day-to-day activity and recognizing repetitive process. In addition to supporting all internal IT needs for FlightAware users, the Desktop team is always looking for ways to improve processes, including automating repetitive tasks to reduce human error and to save time. One of these tasks is onboarding and off-boarding users. This is a very important business process, but it can be very tedious and time consuming, which makes it a perfect candidate for automation. As I became more familiar with the processes, I was able to identify which steps would be eligible for automation and which would continue to require manual intervention.
Office365
I initially began with the Office 365 process of the user onboarding, which includes creating an account, applying a license, and adding the user to the correct mail groups. For this process, I used Microsoft PowerShell with the ExchangeOnline module. I have written plenty of PowerShell scripts, including scripts using the ExchangeOnline module, but those had mainly been smaller scripts with one or two commands, not a complete user onboarding. I began writing the script using a combination of my previous knowledge of the module commands and filling in the blanks with pseudocode. After I had the flow of the script, I began to refine the processes and replace any pseudocode with the correct commands. Thankfully, Microsoft has some great reference articles for the PowerShell commands used, which can be found here. I now had a script that would create and configure a new user in Office 365, but the script was still missing some of the desired functionality – namely, copying mailing lists and groups. Unfortunately, there isn’t a simple way to copy groups from one user to another. I did some research online and found other users who desired the same functions and found workarounds to get the desired actions. For example, there is not a command to find which Office 365 groups a user is a member of. However, you can query the members of an Office 365 group if you have the address of the group. In the script, I ran a command to get a list of all the Office 365 groups in our tenant:
I then used a "for each” loop to get the email addresses contained in the selected group, and then ran a command to check if the list of email addresses contained the email address of the user we are copying from with the following:
If the list contains the email address, it is added to a “groups to add” list. Once the loop is completed, the user is added to each of the groups in the groups to add list. With workarounds like this, I was able to complete the Office 365 onboarding automation, making it much quicker and easier to add and configure new users to our environment.
PSQL
But why stop there? I continued to look at the onboarding process for other steps we could automate. It was not yet important to determine how a task could be automated, but if it could or should be automated. Some other systems involved with new user setup were eligible for automation, so I began to research how to do these steps. All our users are added to a PSQL database table. Previously, they were added by connecting to the database and manually entering information provided by Human Resources. Using Python and some additional libraries, I was able to create a simple script that would prompt the user for the new employee’s information and then use their credentials to connect to the database and run the generated PSQL queries. This worked well, but it lacked error checking. What if another employee already existed with the same name? To prevent the existing user’s data from being overwritten, I added a step that would check for an existing user with the same information. A PSQL query is performed:
If a result was found, a user with the new user’s ID already exists in the table, and the script will terminate and output an error to the user.
What if the user needed different information entered than the standard input? What if the user had entered some incorrect information without realizing it? For these two instances, I added a function for the script to output the PSQL query and ask the user to verify if this was the query they wanted to run. If the user selected “no,” they are given the option to change the variables and create a new query or have the option to exit the script.
Mailman
The last step of the process was our mailing list, hosted in Mailman. Thankfully, we already had an existing webhook that could be used to add users to the mailing lists. Using the requests library, I was able to define the header and data to send to the webhook, making Mailman list management simple. To determine to which lists the user should be added, a list was defined based on each FlightAware department. While running the script, the Mailman lists, determined by the new employee's department, are displayed for the user to approve. If the user running the script wants to manually select Mailman lists, they could enter “manual” and use manual selection mode. This mode displays the available Mailman lists to choose from and asks the user to enter a list. Once the user has entered all their lists, they can enter “done” to continue, or “reset” if they would like to clear the selected list. Once the lists were determined, the selected lists and new employee’s email are passed to the Mailman webhook and the response is formatted and outputted to the user.
If a result was found, a user with the new user’s ID already exists in the table, and the script will terminate and output an error to the user.
Peer Review
At this point, the script was essentially finished. The processes were now automated with the script, and the project was ready for peer review. I sent the code and working copy to the other members of my team, who tested it in a non-production environment and worked to find bugs or steps that do not make sense. This is an essential step before moving to production, as the developer might not be able to find potential bugs while doing their own run through. Having a good peer review also helps with getting perspective of other team members, as they might make usability observations that the main developer can miss. Based on the received feedback, I made small changes to the script, corrected some bugs, and asked the team for a final peer review. After getting their approval, the project was ready for production.
To distribute the project, which required Python, PowerShell, and multiple libraries, I investigated Docker. Docker is a popular program that makes it easy to create containers for simplified distribution and is used widely in FlightAware. Although I was new to Docker, I was able to easily create a file, called a “Dockerfile,” that contained the details of the base image of the container and its dependencies. If you want to learn more about Dockerfiles, the documentation can be found here. On a Linux host with Docker installed, I was able to copy the Dockerfile and run the “docker build” command to create the container. This container was set to start the new user script automatically. To run the container, the user simply needed to SSH to the host server, run the docker run command (docker run -it fa/newuser) and then follow the prompts in the script.
Adding a GUI
Despite the script working fine at this point, I wanted to improve the user experience. The script was completely run through the terminal, with limited options and increased work for the user to change them. I began to research an easy way to add an interface to the main Python script and came across PyWebIO. After importing the library and making some small changes with the input and output lines of the script, I had a working web interface. This upgrade to the script really changed the whole project, leading me to think of more scripts and processes that could be changed from text-based scripts to scripts that can be run from a web portal.
I knew that the first requirement would be security. It was important to restrict access to the scripts to authorized users only. To accomplish this, I setup a SQLite database and used the bcrypt Python library to salt and hash passwords upon new user creation and decrypt them during login. I wrote a simple script in Python that generates the SQLite database, then asks for a username and password to create the initial admin user. Using PyWebIO and Python, I created a user portal where, using the web interface, a user could create other users, modify existing users, and run user history audits of actions performed from the portal. I then created another column in the SQLite database for user type to allow admin users and regular users. Admin users can run all the scripts, create and remove users, run audits, and change passwords for any user. Regular users have a limited number of scripts they can run and can only reset their own passwords. Allowing any user to have an account in the scripts web portal, not just the Desktop team, further expanded the possibilities of this project.
Polishing the Interface
Now that the user onboarding script had a web interface, I worked on improving the user experience. By default, output was displayed in plain black text on a white background. I went through the script and used PyWebIO’s output formatting functions to change the output where appropriate, from plain output to the informational output (beige background), success output (green background), warning output (light yellow background) or error output (red background). This might sound like a small change, but it makes a big difference in making the status of a function much clearer for a user. I also worked on having the interface scroll to the part of the browser window with the latest input, outputting the log file to a scrollable box, and the creation of a dropdown menu for user selection, instead of displaying a list of users and requiring the user to enter the username. Next, I updated all parts of the script that required a “yes or no" input. Previously, the user would have to type in yes or no (or, y or n). After making the updates, users had a yes or no button to select from. This interface improvement also cut down on the required code, as there was no longer a need to verify that the user was entering a valid form of yes or no.
From Onboarding to Off-boarding
Now that I was happy with the end user experience of the onboarding process, I worked to add our off-boarding scripts to the scripts portal. This process was relatively easy, as most of the functions were similar to the onboarding functions and just required changing certain commands from adding to removing the user. For example, instead of using “Add-UnifiedGroupLinks” to add a user to an Office 365 group with PowerShell, “Remove-UnifiedGroupLinks” is used. After adding the off-boarding functionality and making sure the interface was clean and functional, it was time for one of the most important tasks of all: documentation.
Documentation
To document the project, I started with two sections: one on how to use the scripts portal, and another section on how the script portal works. The first section contains documentation pages on each part of the scripts portal, including logging in, creating and managing users, and using the onboarding and off-boarding functions. I included screenshots and clear instructions on how to perform the desired functions. I believe that it is important to try to make the documentation as easy to follow as possible; otherwise, users might become frustrated and not even want to use software. After completing the documentation on how to use the script, I proceeded with the documentation on how the script works. This section was much more time consuming, as I went in depth and documented each function of each script. I created a page for each included script (eight Python scripts, two PowerShell scripts, two Docker related files, and a page explaining additional files used by the script). On each page, I created a section describing the purpose of the script, the libraries imported, any functions defined, variables or constants defined, and the functions performed by the script. When documenting a specific function within a script, I described the actions performed by the function, the parameters used, any variables returned, and variables used in the function. When describing this information, I provided the name of the item being described and why it was used. For example, if using a variable named “log,” I added the description “the opened log file.” My main goal with this part of the documentation was to make sure that the scripts would remain useful for years to come, even if they have a new maintainer.
Conclusion
To say that this script was a journey would be an understatement. What began as a process to automate Office 365 onboarding functions became a full-fledged web portal for running scripts that were previously shell-based or didn't exist at all. I believe that in this regard, this project reflects the spirit of FlightAware. What began as a way for Daniel Baker to share his location with friends and family while flying became a major force in aviation, being used by millions worldwide. With technology, things can always be improved; all it takes is the desire to make it happen.
