Brief/Challenge
As the Museum moves to a mobile-first environment brought on by the Pandemic, managing the Virtual Private Network (VPN) became my responsibility. I handled all the account creation, troubleshooting, password resets, connection issues and everything else associated with the VPN accounts on our Dell laptops.
However, the process for managing the VPN account became a little more challenging due to the competing workloads. Some requests would come through our Spiceworks ticketing system, emails, chats in Microsoft Teams, face to face, and even phone calls.
And of course, if an account was modified, no record existed of when and what was performed to the account. I quickly moved into action to streamline this process, not just for me but also for the current and future members of the IT department.
Solution
Step One – Assessment
Returning to an old standby, I created another Microsoft Form, this time with only 3 simple questions with the understanding that only those in the IT department will be filling out this form so it can be short and sweet. Currently, there are NO plans to share the VPN passwords with end users so it helps to have a central place for requests AND a central place to confirm previous passwords and/or VPN account status.
- What would you like to do?
- CREATE NEW VPN Account
- CHANGE a password for VPN account
- DELETE a VPN account
- What is the FIRST NAME on the VPN account? (before the period in the email address)
- What is the LAST NAME on the VPN account? (after the period in the email address)
The reason the form specifically asks for the first and last name is because some names have to be modified in our systems due to length, hyphens, and/or entirely different preferred names.
This ensures that the VPN account details will match up evenly with the spelling of their company email address. This helps limit confusion and makes troubleshooting easier in the future if needed.
An additional benefit is that it consolidates all of the previous methods of requesting VPN account management into a single funnel.
Instead of simply replying to chat messages with the status of a VPN account (which ends up being lost in the shuffle), I can now point to one location for requests and share the result files with the IT department so anyone can see the status of an end user’s VPN account.
And in this format, it is easy to see what is going on and the date it occurred on (which I manually fill in).
Step Two – Deploy and Track
The next step is to refer to check the requests on the backend, create an account and password as needed, and then inform the requestor directly of the changes (through email). Even though in Step One, I stated that a requestor can check the backend to see the status of all VPN accounts since this system was setup, it is a fallback plan.
I don’t think any process should rely on someone manually checking a spreadsheet to see if their request was processed. So let’s walk though the next steps by looking at a a request on the backend.
The account we are working on today is Killua Zoldyck (the real ones know). I am NOT creating a company VPN account for Killua but highlighting the process works for a real VPN account request.
There are some things to note on the backend. I can send the date and time the request came in along with the name and email address of the requestor. That is important for the next step. Notice that the Password | Account Status and Completed Date are blank. I fill those in after I create the account.
Previously, I was creating the VPN passwords by hand using a system where I decided to take whatever was on my mind and then turn it into a password by counting the letters and adding the number after the word and then slapping on a exclamation point.
For example: Zero4Chill5! would be a VPN password
I ended up automating this password creation process by using Python and made a Windows installer to generate offline passwords on demand (Python Password Generator – Philadelphia Museum of Art).
For Killua’s account, let’s use the hypothetical password of Zero4Chill5! and plug it into the spreadsheet back end.
Now how to get that account information over to the requestor (me in this case) in the least steps as possible?
Enter Microsoft Flow (under Microsoft Automate) where I created an automation which emails that account status and password to the requestor with the click of one button directly from the spreadsheet on Excel Online. By simply highlighting the Password | Account Status cell, then clicking DATA on the Excel menu bar and clicking FLOW, my VPN Email option will appears.
The next screen confirms what the flow will be accessing (Excel Online and Office 365 Outlook in this case)
And here is a screenshot of the email I received from the Flow I started and finished all through Excel Online (with the template I created ahead of time):
Outcome
Currently, the Museum has over 250+ employees eligible for laptops and therefore VPN accounts. By streamlining the process, it saves collective hours between me and the rest of the IT Department, especially when it comes time to setup or replace hardware for new or current employees.
And this process can be modified easily for any other sort of requests which are quick in nature but can easily fall between the cracks. We have proper ticket systems to request New Hires for example but becomes too cumbersome for a simple request such as this one.
By removing the additional friction, it ensures that the IT Department (myself included) uses the form to keep our records straight. And even if someone requests a VPN account using the previous methods (a Teams chat request for example), I enter their request into this form so the request is recorded.
*NOTE: While I could have automated the entire process using Python and/or Microsoft Power Automate, I decided against since that meant the VPN admin credentials would have to be baked into the code and that’s no bueno.