Automation Tools

There are a number of tools that work together for this.

  1. Google Form
  2. Notification Script
  3. UMass Helpdesk
  4. Google API Service Account
  5. Running the scrpit
  6. [Mailing list](#mailing list)
  7. Setpass

Overall Flow

There are many interconnected steps in the process of adding a new user. The flow of information looks like this:

  1. user fills out the Google Form, response is saved in the Google Sheet
  2. Form sends a notification to appropriate set of people for approval
  3. Someone marks the request approved (or moves it to Spam)
  4. The notification script checks the spreadsheet periodically, and when it finds a new approved request, it sends an email to the UMass helpdesk to generate a ticket
  5. The helpdesk operators respond to the ticket. If they cannot resolve it for some reason, they assign the ticket to MOC Staff.
  6. The scripts do their thing, and send appropriate emails to the user.

Google Form

Users fill out either the signup form or the [quota change form]. These are Google Forms owned by the mocwebsiteacct Gmail account.

The forms don’t do much data validation other than requiring certain fields. Responses are stored in a Google Sheet:

  • Access Request Spreadsheet
  • [Quota Request Spreasdheet]

Responses are stored on a tab called Form Responses 1. There is also a tab where processed responses are stored permanently. In the Access Request spreadsheet this is called Current Users, in the Quota spreadsheet it is Processed Requests. Changing the name of the spreadsheet or any tabs will break interaction with both the form and the automation scripts. If for some reason you do need to rename things, be sure to make the appropriate updates to the form settings,, and this documentation.

The forms have an add-on called Email Notifications for Forms. To change these settings, make sure you are logged in as mocwebsiteacct. Then click the add-ons icon to the top right (looks like a puzzle piece) and choose this add-on from the list. Click Manage Form Settings. Choose the notification you want to modify from the dropdown.

Notification script

This script is called and lives in the moc-openstack-tools repo.

It runs as an hourly cronjob in the helpdesk VM. Each time it runs, it checks through the entries on both the access request spreadsheet and the quota request spreadsheet. If it detects that an entry has been marked as Approved, but the Helpdesk Notified column is blank, then it will generate an email to the helpdesk which automatically creates a ticket. Then it will enter a timestamp in the Helpdesk Notified column.

It also detects if a request has reached a specified age without being approved, and generates an email reminder. It records the time of the last reminder in the Reminder Sent column of the spreadsheet, and then sends additional reminders at a specified interval (updating this column each time). The length of time that passes before the first request and the interval of the subsequent requests is configured in settings.ini.

UMass Helpdesk

The UMass Helpdesk will see the Access Request and Change Quota Request tickets generated by the script. They will log into the helpdesk VM:

$ ssh helpdesk@

and use the moc interface to run the script. This is a bash function layered in between the helpdesk user and the actual scripts - the intention is to prevent all the helpdesk users from having access to the settings.ini file which contains passwords. More information about this is at Deploying a Helpdesk VM.

Google API Service Account

The Google API project project-mocwebsite is owned by the mocwebsiteacct Gmail account and can be managed by logging into the Google Developer’s Console.

This API project has a Service Account named mocwebsite. To manage it, click the menu near the top left next to ‘Google APIs’, and choose ‘IAM & Admin’. Then in the left sidebar, choose ‘Service Accounts’.

This page shows the service account ID: To grant the service account access to a resource (such as a spreadsheet):

  1. open the resource and click Share
  • choose Advanced
  • enter the resource ID in the ‘Invite People’ box
  • from the dropdown, choose Edit permissions
  • uncheck Notify people (because the ID isn’t really an email address, so the notification bounces)

This page also manages keys associated with the account. You can create new keys or delete old ones from this page. If you delete a key you will have to provide a new key file for any scripts/applications that were using the deleted key, such as the addusers script.

The key used by our “production” helpdesk VMs in Kaizen and Engage1 is in a file named project-mocwebsite-5b5ccc23e55c.json which is checked into the helpdesk subdirectory of the moc repo. Never commit this file to the public moc-openstack-tools repo.

If you lose all copies of a key you can’t re-download it, you must create a new key on this page and and put the new key file in place of the old one every place it is used.

Running the script

#####(Helpdesk Method) Log into the addusers VM and run the script. Make sure to use the latest code:

$ ssh helpdesk@
Last login: Fri Jul  7 02:07:39 2017
Welcome to the MOC helpdesk interface.  Type `moc help` for instructions.
$ moc grant-access --user <username>
-- or --
$ moc update-quotas --project <project_name>
-- or --
$ moc reset-password <username> <pin>

#####(Manual method)

$ ssh <your_username>@
$ sudo su - moc-tools
$ cd production          # on engage1, the directory is called engage1
$ python --user <username>
-- or --
$ python --project <project_name>
-- or --
$ python <username> <pin> and both will accept the argument --all instead, which will process all approved requests. This can be useful for situations like adding a long list of students from a class. Technically the helpdesk user could do this too, but it’s not documented in their documentation. The helpdesk operators prefer doing one at a time even if they have several requests, because they are usually working on individual tickets.

#####Check script output for errors The script performs the following tasks:

  1. Read and parse new user data from the spreadsheet
  2. Create new OpenStack projects and users based on the data
  3. Add all new users, passwords, and PINs to the Setpass service database
  4. Subscribe all new users to the mailing list
  5. Copy all successfully processed rows to the Current Users or Processed Requests tab
  6. Delete all successfully processed rows from the Form Responses 1 tab
  7. Print any errors and warnings about rows it was unable to process

Any rows the script cannot process (including users that already exist) will not be copied to Current Users or deleted from Form Responses 1. So if there is an error, you can take steps to address it and then run the script again if needed.

The copying/delete steps are batched and happen after user creation is finished for all users. So if the script ever creates a few users but then throws some exception and exits before the spreadsheet is updated, you will need to move those users to Current Users manually.

Mailing list

See Deploying a Helpdesk VM for more information on the mailing list.


Setpass is a tool originally created by Kristi which allows users to set their own passwords securely.

It receives the user’s randomized password, PIN, and user ID from the script and stores them in a database along with a token. The token is used in a URL given to the user. The user goes to the URL and enters the same PIN that they used when signing up, along with the password they wish to use. They have 3 attempts to get the PIN right.

In Kaizen this service is running on port 5001 at, so the URLs the user should navigate to are constructed like this:<token>.

Config files for setpass are found in the helpdesk directory of the moc repo.