Self-Directed Reality

Actions are greater than Thoughts

project management

Automated VPN Account Management – Philadelphia Museum of Art

by

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.

  1. What would you like to do?
    1. CREATE NEW VPN  Account
    2. CHANGE a password for VPN account
    3. DELETE a VPN account
  2. What is the FIRST NAME on the VPN account? (before the period in the email address)
  3. 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.

Jamf – Philadelphia Museum of Art

by

As the subject matter expert on JAMF, I was tasked with figuring out how the system worked, what Apple hardware the Museum had, and how to best document that knowledge for future reference.

I started this complex project with a simple concept: “How does this software work in perspective as a new user?”

I automated the setup of iPads, tracked down and remove old devices, and created an easy to maintain Asset Management system while documenting everything to make it easier to teach to someone else.

RESOURCES: Markdown Markup Language, HTML, Skitch, Snagit, Microsoft Power Canvas Apps, Microsoft Excel, technical writing, video editing, Adobe Creative Suite

Brief/Challenge

This multi-faceted process covered 3 aspects (Assessment, Deploy & Document, and Tracking & Management) of the SaaS Jamf as a Mobile Device Management (MDM) of Apple hardware in the Museum.

This covered the assessment of the Apple hardware and software at the Museum, management the deployment of Apple hardware and software, and of course the tracking (documentation) of the hardware at the Museum.

Jamf is a massively integrated MDM for Apple hardware and software in an enterprise environment. As of January 2021, Jamf is managing 20 million Apple devices around the world (source).

Needless to say, it is a big challenge to prepare for the rollout of this software in our own environment. I was tasked with the rollout of JAMF in our environment. To get started, I viewed this project as 3 separate parts (Assessment, Deploy & Document, and Tracking & Management) with the major focus point being well documented and streamlined processes.

Solution

Step One – Assessment

The first step of the process was to determine the exact state of relevant Apple hardware that was accessible at the Philadelphia Museum of the Art. In the flowchart below, I laid out my thought process for determining what hardware we have and the following steps.

Determining which products were worth keeping and which devices would be e-wasted took a considerable amount of time. Prior to my involvement, no one every took an inventory of all the iPads, iPhones, iMacs, MacBooks and other devices on site at the Museum.

According to my records, we had more than 250+ Apple devices floating around in various stages of usability in addition to the fact that the exact location of most of these devices were unknown.

Step Two – Deploy & Document

The second step of the process was to make sure Jamf worked for our needs and document the process for future reference.

In the images below, there are 2 iPad home screens, one setup normally and one setup through Jamf:

  • the iPad home screen on the left is the home screen of a brand new iPad from Apple that was setup normally (i.e. you buy an iPad and go through the normal setup).
  • the iPad home screen on the right is the home screen of a brand new iPad from Apple that I enrolled into Jamf prior. The actual setup (including my custom branded wallpaper I designed and the apps and folder placement) was automatically performed by Jamf (i.e. you turn this iPad on and it connects to the server and pull the configuration files).

In addition to the time savings for each iPad and the associated setup, iPads can be easily repurposed for different departments or needs since Jamf handles the rollout of applications as well.

Step Three – Tracking & Management

Using the Microsoft Power Apps platform, I used Canvas apps to create an asset management system for Active Assets and e-waste Assets with data being supplied from a Microsoft Excel spreadsheet that I painstakingly assembled.

Active Assets Excel Sheet Sample (sensistive info is blurred out)
eWaste Assets Excel Sheet Sample (sensistive info is blurred out)

The Excel spreadsheets (and by extension, the canvas apps) track a few important details of each hardware record.

The Apple eWaste Assets tracks the following information:

  • Discontinued Year: The year Apple stopped manufacturing the device
  • JAMF Status: The device should be listed as REMOVED from Jamf for ewaste
  • Model Name: The moniker provided by Apple (i.e. MacBook, iPhone)
  • Model Number: The model number provided by Apple and begins with an A (i.e. A1286, A1486)
  • Release Year: The year Apple released the device
  • Serial Number: The device’s serial number printer on the back/bottom of the device.
  • Year E-Wasted: The year that the device was ewasted at the Museum.

The Apple Active Assets tracks a bit more information:

  • Serial Number: The device’s serial number printer on the back/bottom of the device.
  • Model Name: The moniker provided by Apple (i.e. MacBook, iPhone)
  • Assigned User: The user that the device is tied to (i.e. Paris Hunter)
  • Assigned Department: The department that device is tied to (i.e. IT)
  • Location: The location of the device/user (i.e. Building A > IT Dept > Apple Office)
  • AppleCare+ Until End Date: The date AppleCare ends. Expired is listed otherwise
  • Current OS: The OS version (and release year) installed at the time (i.e Big Sur (2020))
  • Maximum Supported OS: The maximum OS version (and release year)(i.e. Monterey (2021) and beyond)
  • Screen Size: The size of the screen if relevant. A Mac Pro has N/A for a screen size
  • Model Number: The model number provided by Apple and begins with an A (i.e. A1286, A1486)
  • Release Year: The year Apple released the device
  • JAMF Status: Is the device enrolled or not enrolled in Jamf? (i.e. Enrolled)
  • Additional Notes: Notes relevant to this hardware (i.e. Ticket #12345 for cracked screen)

Also the counts on the home screens for both apps update in real-time either from records added in the applications OR manually added to the underlying Excel spreadsheet.

This solution allows for easy management in the future (since a variety of devices can access the Asset apps) and easy to manage since Excel is easily understood at a base level for most users. The app allows the data in the Excel spreadsheet to be searchable even using partial matches. Also, this information allows management to make an easy decision as to what hardware should be updated.

43 second demo of the Apple Active Assets app on iPhone hosted through Microsoft Teams

Outcome

Assessment of Inventory and Hardware

Our records originally reported 250+ Apple devices when I started this project. After the project, I was able to reduce the active devices down to 116, with JAMF licensing costs required for only 90 of those devices.

Regarding the JAMF licensing costs, I have saved at least $2600 per year in additional licenses costs that were no longer needed regarding active devices.

In addition, I was able to ewaste 103 devices that were no longer being used at the Museum, saving on potential license costs, employee time and mangement of the outdated hardware.

In addition to all of the above, almost every Apple device in the Museum is now accounted for and easily tracked to a user and/or department making hardware management easier in the Windows dominated environment.

Deployment & Documentation of JAMF

I created a 55+ page technical guide with images written in Markdown showing exactly how to use Jamf in the scope of the Philadelphia Museum of Art. I went through extensive detail showing the exact settings and common solutions to Jamf issues (such as updating annual certificates to keep the system working).

This document was shared with all of the IT department so that future management can be

Tracking & Management of Hardware

The main focus of the App method was to get ahead of the digital clutter where critical data lives in a bunch of files spread across various locations in multiple stages of completion (i.e. Apple_Assets_2021_2022_v3_Work_In_Progress.xls). I am sure we all had to deal with some level of this in our life and careers.

The added purpose of creating the apps were to have a central place to manage and update information regarding Apple hardware at the Museum. As it currently stands, the apps are easily accessible through Microsoft Teams through any device (mobile or otherwise) as long as the user has proper credentials.

Another benefit of the apps (as a whole) is to provide future technicians an easy way of recording and updating devices on the go using in-house resources (i.e. an old iMac was tossed due to a cracked screen or Paris gets a new 14″ MacBook Pro which should be reflected in the Active Asset records).

I have laid the foundation for work that can easily be expanded for the future of the Philadelphia Museum of Art.