Google Apps for CAP Webinar

[JeffDG]

Everyone,

Several people have asked me "How have you set up Google Apps for your Wing?"

Well, instead of doing this a bunch of times, I'm going to set up a conference call/screen sharing session to walk through our process in some detail, including:

• Account Provisioning (setting up and disabling accounts) based on CAPWATCH data
• E-mail lists
• Event Registration
• Calendars
• Unit Websites
• "Branded" CAP E-mail
• Member Stats reports from CAPWATCH

Initial thought is to do this June 21 at about 1300 EDT, maybe repeat it more than once for those who can't make that time.

If you have other areas you'd like to see covered, I'll try and accommodate those (if I know them!)

[arajca]

Any chance of getting this recorded? May be easier and less time consuming than repeating and folks can review it afterward if they have questions.

[JeffDG]

Any chance of getting this recorded? May be easier and less time consuming than repeating and folks can review it afterward if they have questions.

I think I should be able to...should be able to do it with Google Hangouts On Air actually.

[Tim Day]

That's an outstanding idea, Jeff.

If the provisioning process runs on Google Apps scripts you may want to separate that one out. I think lots of folks can take advantage of the other apps without really knowing anything about scripting, but some folks may have a harder time with scripting.

[JeffDG]

In the spirit of "Eating Our Own Cooking", I've gone into Google Apps and created a discussion group for Google Apps for CAP Units:

https://groups.google.com/a/tncap.us/forum/#!forum/google-apps-working-group

Please feel free to join up!

[NIN]

Odd

[JeffDG]

Odd

Hmmm...I thought I set it for "anyone can ask to join"

[NIN]

BTW, I want to watch this as an IT guy (in my day job, dealing with Google Apps a bunch) but also because we just got setup with Google apps at the unit...

[JeffDG]

BTW, I want to watch this as an IT guy (in my day job, dealing with Google Apps a bunch) but also because we just got setup with Google apps at the unit...

Hell, I'm pretty sure I'm doing it wrong...any input will be welcome!

[a2capt]

"Please sign in with an authorized account to view this content."


It appears to be restricted to within the tncap.us domain. You may need to manually add users from outside, or open up something further than you have now.

[JeffDG]

Should be fixed now...missed one checkbox to open it up.

[a2capt]

Yup. S'all good now.

[JeffDG]

OK...

6/21 is a no-go...SER Exercise happening that I'd like to try and participate in if I can.

6/28 is out too.

7/5 is on the 4th of July long weekend, not going there...

7/12 looks like the next good day!

Sorry, guys...juggling a bunch of balls in the air right now.

[a2capt]

Just stabbing in the dark .. but .. any weekday/evening? .. anything wrong with the other 50% of the weekend?

Of course, it depends on the host's availability and preference..

Never hurts to ask..

[JeffDG]

Just stabbing in the dark .. but .. any weekday/evening? .. anything wrong with the other 50% of the weekend?

Of course, it depends on the host's availability and preference..

Never hurts to ask..

I'd love to do weekday, but honestly, after work and stuff, I find I end up being just drained, then finding a reasonable time with timezones (at least for TNWG stuff we just have EDT and CDT)...

[UWONGO2]

Another vote for Google Apps for Education. Although we're really only using it for email and document storage right now, it's easy, it's free, and we're excited about the possibilities of what the system can do for us.

[aviator9417]

Did I miss the party?

[JeffDG]

Did I miss the party?

No...got sidetracked and haven't circled back to holding this...let me find a date that works!

[jeders]

*bump*

Any updates on this? If scheduling is an issue, have you thought about just recording a basic instructional video and posting it to YouTube? Not quite as nice as being able to interact, but it gets the job done.

On a side note, I got the go ahead from my squadron commander to set our squadron up for Google Apps, and I'm wondering what most people used as the Mission Statement; a generic CAP statement or your own.

[Eclipse]

Where do you need a mission statement? 

Is that just for the website?

[JeffDG]

Where do you need a mission statement? 

Is that just for the website?

No, it's for the Google Apps for Nonprofit application...you have to provide your EIN and an organizational mission statement.

I just used the generic CAP one.

[Eclipse]

Wow - it's been a while, I'm sure I provided that 10 years ago when we did my Groups.
I'm waiting on some EINs for my kids BSA Troop for their setup.

They...ahem...appear to have not have a charter or proper tax status for a number of years.
The troop moved charter hosts and the attention to detail of the leadership was somewhat...lacking...

It came to light when I went to register their trailer after being driven with expired plates for a couple years
(seriously).

Remember er that people next time you're whining about the Wing FM needing too much paperwork.

[JeffDG]

Well, I seem to be having trouble scheduling...too [darn] many duty assignments in CAP and the outside world!

That said, I'll start putting some notes here.  This one will be overall design, I'll get to code a bit later!

My prerequisites are simple:  the Wing's CAPWATCH database needs to be available in a MySQL database and a user needs to be able to access it.  You can use Google CloudSQL (not free) for this, or you can have your own unit MySQL server (which is what we do).  Another member downloads the CAPWATCH zip file and loads it into MySQL, so I won't do anything about that, just presume it as a "given".

All of the scripts that I speak about run in Google App Script and run on timed "triggers", so they're entirely hands-off once they're configured.

Basically what I have are three primary "provisioning" scripts that I use. 

The first is for "Members", and it creates an account for every member of the Wing (except for Patron and -000) in the form of "<capid>@<domain>".  It also scans for expired or transferred out members and automatically disables those accounts.  That means that we can do things on our website and simply require this login to be used to access and not have to worry about a member who is expired having access to CAP data, again, hands-off.  Members who are "ghosted" (transferred to -000) are disabled, similarly Patrons are disabled.  Finally, the script ignores any account that does not begin with a number, so if we need to grant access to someone outside (for example our Wing Admin) who doesn't show up in CAPWATCH, we create them with a "real" name, and the script doesn't touch them.  Wing IT guy once created an account for a neighbouring wing guy to transfer some files, and said "Hey, everyone else is <capid>@<domain> I'll just do the same, and wondered why the next day the account was disabled...  Finally, any disabled account is reactivated if the member becomes active again, so anything they had, or settings they had, will come right back automatically.

The second script does "security" groups, and contains only those account created by the above.  It goes by unit and duty position, and basically does any duty position it finds.  So, if you're the PD Officer for unit 025 (not a real unit in our wing), you would become a member of the group DUTYUNIT-PD-025@<domain>.  We could then have a folder for PD officers across the wing that would have all these DUTYUNIT-PD-XXX groups having rights to it, so whenever your commander appointed you as a PDO, you would automatically gain access.  I abbreviate the duty positions based on the Correspondence regulation, so the ES officer is DUTYUNIT-DOS-XXX while the ES Training officer is -DOST-XXX.  Again, all automagic and hands off.

The third is my "Blast" groups.  Unlike the above, they have regular (outside) e-mail addresses as members.  That means they reach people who don't log into their Wing e-mail accounts.  This one is actually pretty slick now.  First, there is a Google Spreadsheet that controls the process.  Each "blast" list has SQL code that defines it (basically pulls the member, unit and e-mail out of capwatch based on qualification).  That SQL is stored in a Google Docs document, and the document ID is put into the master sheet, so if it needs to be tweaked later, it's really easy to edit.  The results are automatically dumped into another spreadsheet, so you can share the results easily.  For example, the Wing DC can look at a list of the Comm Officers across the wing that's auto-updated.  The format of the blast lists is "blast-<listname>-<unit>@<domain>", so for example, I can send an e-mail to the Wing ITOs by sending to blast-ito-001@<domain>.  Additionally there are "rollups" for each group, so if I want to hit all Group 1 ITOs, it's "blast-ito-group1@<domain>" (will send to the Group 1 ITO + all subordinate unit ITOs), as well as a blast-ito-wing@<domain> that goes to everyone in the wing.  Many of these lists also get "friendly" aliases as well.  For ITOs, sending to ITO@<domain> goes to the blast-ito-001 list, while TNxxxITO@<domain> goes to blast-ito-xxx list.

The blast lists can be based on just about anything in CAPWATCH.  So we have blast lists for Duty Positions (like ITOs, Commanders, Deputy Commanders, etc.), as well as ES Qualifications (ICs, PIOs, MPs, GTLs).  Each unit also has a mailing list of all members.  Currently we have over 40 different sets of blast lists, and growing every time we identify a new need.

The scheduling piece runs the update scripts hourly, however the first thing they do is check to see if the "DownLoadDate" table has a date after the last execution, so whenever the CAPWATCH tables are refreshed, the update scripts kick off within an hour of that.

[Tim Day]

Jeff,

How do you initially create the Google Groups?

[JeffDG]

Jeff,

How do you initially create the Google Groups?

All scripted.

Basically, I query CAPWATCH for contact e-mails based on whatever criteria I am using, the use the AdminDirectory.Group functions to create the groups, and the AdminDirectory.Members to populate the group memberships with the e-mails from CAPWATCH.

[Tim Day]

Do you just use the default group settings then? Duty position groups are pretty close to the default settings, but it'd be nice to disable public posting to the members-only or security groups. I'm doing this now through a PHP script that employs the Groups Settings API, but would like to find a way to do it within GAS (mostly for continuity purposes).

[JeffDG]

Do you just use the default group settings then? Duty position groups are pretty close to the default settings, but it'd be nice to disable public posting to the members-only or security groups. I'm doing this now through a PHP script that employs the Groups Settings API, but would like to find a way to do it within GAS (mostly for continuity purposes).

If you'd asked me that question several weeks ago, I could give you an answer.

The DomainGroup API had a way to set posting preferences, but that API has now become deprecated, and the GroupSettings API is not one that is natively available to GAS...I've been trying to get an OAuth Service Account to work in GAS to do GroupSettings, but have not had any success with it so far.

[JeffDG]

Account Creation Script

Code Select Expand

//Parameters necessary for accessing SQL
//Actual values omitted

var sqlsvr = "";
var user = "";
var pw = "";
var dbname = "";


function UserProvision() {
 
  // This script will proceed in multiple steps
  // Step 1 will retrieve a list of users from CAPWATCH
  // Step 2 will retrieve a list of users from GApps
  // Step 3 will add/suspend users as appropriate
 
  var capidlist = Array(100000);
  var capwatch = {};
  var nextcapid = 0;

//See if script needs to run, ie. has the DownLoadDate changed since last run 
  var conn = Jdbc.getConnection("jdbc:mysql://"+sqlsvr+"/", user, pw);
  var stmt = conn.createStatement();
  stmt.setMaxRows(100000);
  var start = new Date();
  var sql=" SELECT * FROM "+dbname+".DownLoadDate";
 
   
  var rs = stmt.executeQuery(sql);
  var dldate;
  while (rs.next())
  {
    dldate=rs.getString("DownLoadDate");
  }

  var lastrun = PropertiesService.getScriptProperties().getProperty("lastrun");
 
//The "return" here basically skips the rest if the DownloadDate is the same as the last run

  if (dldate==lastrun) return;
 
  //Execute query to get member data from CAPWATCH
  var sql=" \
select \
mem.CAPID \
,mem.NameLast \
,mem.NameFirst \
,mem.Type \
,mem.MbrStatus \
,mem.Unit \
,mc.contact \
from \
" + dbname + ".Member mem \
left join "+dbname+".MbrContact mc on mem.capid = mc.capid and mc.type='EMAIL' and mc.priority='PRIMARY' \
where  \
mem.type <> 'PATRON' \
and \
MbrStatus <> 'EXPIRED' \
and \
Unit <> '0' \
";
 
  var rs = stmt.executeQuery(sql);
 
  while (rs.next())
  {
    var capid = rs.getString("CAPID");
    var lname = rs.getString("NameLast");
    var fname = rs.getString("NameFirst");
    var mbrtype = rs.getString("Type");
    var mbrStatus = rs.getString("MbrStatus");
    var email = rs.getString("contact");
    var unit = rs.getString("Unit");
   
    capidlist[nextcapid] = capid;
   
    capwatch[capid] = { "LName":lname,"FName":fname,"mbrtype":mbrtype,"mbrStatus":mbrStatus,"unit":unit,"Email": email,"Add":1,"Remove":0,"Unsuspend":1};
   
    nextcapid++;
   
   
  }
 
  // OK...the userlist from CAPWATCH is fully populated now.  Let's get the list from GApps
 
  var remcount =0; var suspcount=0;
  var pageToken, page;
  do {
    page = AdminDirectory.Users.list({
      domain: 'tncap.us',
      maxResults: 250,
      pageToken: pageToken
    });
    var users = page.users;
    if (users) {
      for (var i = 0; i < users.length; i++) {
        var username = users[i].primaryEmail;

//All managed accounts start with a number, so ignore any accounts not beginning with a number       
        if (username.match("^[0-9]"))
        {
          var thiscapid = username.substring(0,6);
         
          if (thiscapid in capwatch)
          {
           
            capwatch[thiscapid].Add = 0;
            capwatch[thiscapid].Remove = 0;
            var suspended = users[i].suspended;
            if (suspended)
            {
//Account exists but is disabled, flag for re-enable
             capwatch[thiscapid].Unsuspend = 1;
            }
            else
            {
              capwatch[thiscapid].Unsuspend = 0;
            }
          }
          else
          {
            if (suspended)
            {
              //User already suspended
              suspcount++;
            }
            else
            {
              capidlist[nextcapid] = thiscapid;
              capwatch[thiscapid] = {"Remove":1};
              nextcapid++;
              remcount++;
            }
           
           
           
           
          }
         
        }
       

       
      }
    } else {
      Logger.log('No users found.');
    }
    pageToken = page.nextPageToken;
  } while (pageToken);
 
  // OK, so now I have a list of CAPIDs that are either Add, Remove, Unsuspend or do nothing!

 
  for (var i=0; i<nextcapid;i++)
  {
   
    var curcapid = capidlist[i];
   
    var add = capwatch[curcapid].Add;
    var rem = capwatch[curcapid].Remove;
    var unsusp = capwatch[curcapid].Unsuspend;
   
    var log = "";
    if (add > 0)
    {
      log+="Adding user, CAPID:  " + curcapid +" Lname: "+capwatch[curcapid].LName + "\n";
      addUser(curcapid,capwatch[curcapid]);
    }
    if (rem > 0)
    {
      log+="Removing user, CAPID:  " + curcapid+"\n";
      deleteUser(curcapid,capwatch[curcapid]);
    }
    if (unsusp > 0)
    {
      unsuspendUser(curcapid,capwatch[curcapid]);
      log+="Unsuspending user, CAPID:  " + curcapid +" Lname: "+capwatch[curcapid].LName+"\n";
    }
   
  }
 
  Logger.log(log);
  //Once everything has been processed, update the last run date
  //If something fails in the script before this point, it will run again next time as this hasn't been updated
  PropertiesService.getScriptProperties().setProperty("lastrun", dldate);
 
}

function deleteUser (capid,user)
{
  var a=user;
  Logger.log(capid+"@tncap.us");
  var userobj = AdminDirectory.Users.get(capid+"@tncap.us");
  //Don't actually delete the account, just disable it.
  userobj.suspended=true;
  var suspend = AdminDirectory.Users.update(userobj,capid+"@tncap.us");
 
}
function addUser (capid,user)
{
  var a=user;
  var pw=generatepass(8);
  var newuser = {
    primaryEmail: capid+"@tncap.us",
    name: {
    givenName: user.FName,
    familyName: user.LName
  },
      changePasswordAtNextLogin: true,
        // Generate a random password string.
        password: pw
};

newuser = AdminDirectory.Users.insert(newuser);
var userEmail = capid+"@tncap.us";
//Attempt to create a "FirstName.LastName" alias, but silently fail if one already exists
var alias = {
  alias: user.FName+"."+user.LName+"@tncap.us" };
try
{
  alias = AdminDirectory.Users.Aliases.insert(alias, userEmail);
}

catch (e)
{}
//Send an e-mail to the new user with the information they need
SendNewAccount(capid,pw,user.EMail,user.LName,user.FName);


var b="";

}
function unsuspendUser (capid,user)
{
  var a=user;
  var userobj = AdminDirectory.Users.get(capid+"@tncap.us");
  userobj.suspended=false;
  var unsuspend = AdminDirectory.Users.update(userobj,capid+"@tncap.us");
  var b="";
 
}


function generatepass(length) {
 
  var charset = "abcdefghijklnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789",
      retVal = "";
  for (var i = 0, n = charset.length; i < length; ++i) {
    retVal += charset.charAt(Math.floor(Math.random() * n));
  }
  return retVal;
}


function SendNewAccount(capid,pw,emailaddr,ln,fn)
{
  var tncaplogoblob = UrlFetchApp.fetch("https://www.google.com/a/cpanel/tncap.us/images/logo.gif?service=jotspot").getBlob().setName("tncaplogo");
  var tnwgpatchblob = UrlFetchApp.fetch("http://www.tncap.us/home/TNWG%20Patch.jpg?attredirects=0").getBlob().setName("tnwgpatch");
 
  try
  {
    MailApp.sendEmail(
      emailaddr,
      "New Account on TNCAP.US",
      "",
      { bcc:  "it@tncap.us",
       htmlBody:
      '<!DOCTYPE html><html><body><h1><img src="cid:tncaplogo" width=100></img>Tennessee Wing, Civil Air Patrol</h1>'+fn+
' '+ln+'<p>Welcome!<p>You have been provisioned an account on [url=http://WWW.TNCAP.US]WWW.TNCAP.US[/url], the official '+
'information sharing portal of Tennesee Wing.  The account details are below:<p>Access:  <a href="http://www.tncap.us">'+
'[url=http://www.tncap.us]www.tncap.us[/url]</a><br>Username:  <b>'+capid+'@tncap.us</b><br>Password:  <b>'+pw+'</b><br>'+
'<p>Please visit the <a href="http://www.tncap.us/login-training">IT Training Page</a> for details about using your new account.<p>If you '+
'have issues, please contact <a href="mailto:it@tncap.us">Tennessee Wing IT</a></body></html>',
       
       inlineImages:
       { tncaplogo: tncaplogoblob
       //tnwgpatch:tnwgpatchblob
      }
      }
     
    );
  }
  catch (err)
  {
    Logger.log("Error:  " + err);
  }
  finally
  {
  }
 
 
 
}


[A.Member]

Thanks for the info Jeff.   :clap: 

This is exciting and seems to be exactly what we'd like to do for our Wing.  I'll have to come back and look at what you're doing more closely to see how we can leverage.

Essentially, we have two sites:
* External/Public - mnwg.cap.gov which just had a silent go live Fri. night (just tweaking/optimizing before broader advertisement)
* Intranet - this is Google Apps with a Sites page (the Public site also links to this); this is currently being built out

We need to overcome the challenge of account provisioning but hopefully after reviewing your info closer we can understand enough of what you did to leverage it.  Ideally, the goal is for every member of the Wing to have a @mnwg.cap.gov email address/Google account.  Groups will be used for communication/announcements.  Only those with a @mnwg.cap.gov account will be allowed access to the intranet. 

The challenge is in managing accounts, particularly since an automated feed from CAPWATCH is no longer offered by National.  However, if we did a monthly manual download, perhaps that frequency would be "good enough" to meet our needs.  Seems the real impact would be that new members may have to wait as long as a month before their account would be established.  There would also be up a month lag when disabling.  Is that a huge deal?  Probably not.

Once we get stuff squared away we can decommission our current site.

[JeffDG]

Thanks for the info Jeff.   :clap: 

This is exciting and seems to be exactly what we'd like to do for our Wing.  I'll have to come back and look at what you're doing more closely to see how we can leverage.

Essentially, we have two sites:
* External/Public - mnwg.cap.gov which just had a silent go live Fri. night (just tweaking/optimizing before broader advertisement)
* Intranet - this is Google Apps with a Sites page (the Public site also links to this); this is currently being built out

We need to overcome the challenge of account provisioning but hopefully after reviewing your info closer we can understand enough of what you did to leverage it.  Ideally, the goal is for every member of the Wing to have a @mnwg.cap.gov email address/Google account.  Groups will be used for communication/announcements.  Only those with a @mnwg.cap.gov account will be allowed access to the intranet. 

The challenge is in managing accounts, particularly since an automated feed from CAPWATCH is no longer offered by National.  However, if we did a monthly manual download, perhaps that frequency would be "good enough" to meet our needs.  Seems the real impact would be that new members may have to wait as long as a month before their account would be established.  There would also be up a month lag when disabling.  Is that a huge deal?  Probably not.

Once we get stuff squared away we can decommission our current site.

You can actually combine both on one public page.

You can have your "public" page, with members-only pages that you need to be authenticated to see.

[Tim Day]

Jeff,

Here's a script I adapted from one I found online that was an example of OAuth 2.0 use. I adapted it to use the Groups Settings API to modify a single setting in a group. It's hard-coded and not ready for operational use but it does demonstrate how to change a groups settings via the API. The PHP toolkit was actually simpler to use.

Code Select Expand


// --------------------- YOUR CONFIGURATION  HERE----------------------

/* get CLIENT_ID_ and CLIENT_SECRET_ as listed in the google developers console for your project */
var CLIENT_ID_ = PropertiesService.getScriptProperties().getProperty("CLIENT_ID");
var CLIENT_SECRET_= PropertiesService.getScriptProperties().getProperty("CLIENT_SECRET");

function id() {
Logger.log(CLIENT_ID_);
}

/* the required scope of the request being made in getData(); if this
* value is changed between invocations, it will invalidate any
* cached tokens the next time they are requested */

//Groups Settings API
var SCOPE = '[url]https://www.googleapis.com/auth/apps.groups.settings';[/url]

// Group Settings API for identified Group
var API_URI_ ='[url]https://www.googleapis.com/groups/v1/groups';[/url] // /info@vawg.cap.gov';

// Group Settings API Patch Group
// PATCH method isn't support by Google Apps Scripts' UrlFetchApp, so we have to use PUT and
// add a method override in the headers
var CALL_METHOD_ = 'PUT';

// For development use a hard-coded group
var targetGroup = 'aa_ad-test@vawg.cap.gov';

// First redirect URI for production version (when web app is published)
var REQ_REDIRECT_URI_ = (function (){
  var tmp = ScriptApp.getService().getUrl();
  // this is kind of ham-fisted, but we swap out the trailing /dev or /exec here
  var uri = tmp.substring(0,tmp.lastIndexOf('/')) + '/usercallback';
  return uri;
  //return tmp;
})();

/* this is listed as a separate variable just as a reminder that ScriptApp.getService.getUrl() 
does not return consistent urls within this app depending on whether you are hitting
a dev url or a versioned one;  versioned urls are consistent across invocations, dev ones
are not, so when the second redirect fails, get that url and paste it here

tl;dr if you are in dev mode, expect to have to hard code TOKEN_REDIRECT_URI_ AND add it to the Google Developer's Console */

// Production Setup
var TOKEN_REDIRECT_URI_='[url]https://script.google.com/a/macros/vawg.cap.gov/s/AKfycbx0aAJQ8_O2Hh0PsjWFDSGo8x8HwcUoB7AVNLKaU_HSBSyK2lA/usercallback';[/url]

// more: Original source claimed:
// QUOTE
//TOKEN_REDIRECT_URI_ is the second redirect and in domain (e.g., Google Apps for Education) the redirect URI is different.
// ENDQUOTE
// However, I have not found that to be true.
// In dev, the URIs are the same. Both URIs must be added to console. Just add the second URI on it's own line.

// Developmental Setup (both redirect URIs are the same):
//var TOKEN_REDIRECT_URI_ = '[url]https://script.google.com/a/macros/vawg.cap.gov/s/AKfycbyLzkkTj4BiRYA9VKnOial2DuFLa1POSOTAJiPv0FA/usercallback';[/url]
//var REQ_REDIRECT_URI_ = TOKEN_REDIRECT_URI_;


// -------------------- YOUR GOOGLE API CALL HERE ---------------------
function getData(){
 
  // This is the URI specified by the API
  var getDataURL = API_URI_ + '/' + targetGroup;
 
  // payload may look like an object but it's a string
  var payload = JSON.stringify({
    whoCanPostMessage: 'ALL_IN_DOMAIN_CAN_POST'
    });
 
  // headers is an object
  var headers = {
    "Authorization" : getAuthorizationHeader_(), //required oAuth value
    "Accept" : "application/json",
    "X-HTTP-Method-Override":'PATCH'
    };
 
  //this is the complete request body 
  var requestProps = {
    "contentType" : "application/json",
    "muteHttpExceptions":true,
    "method" : CALL_METHOD_,
    "headers" : headers,
    "payload" : payload
  };
   
  var dataResponse = UrlFetchApp.fetch(getDataURL, requestProps).getContentText();
  return dataResponse;
   
}

// -------------------- THE AUTHORIZATION CODE HERE -------------------

// set token prefix to your own here
var TOKEN_PREFIX_ = "tdgmIIv2_wflow";
var TOKEN_CACHE_KEY_ = TOKEN_PREFIX_ + '_auth_token';
var TOKEN_SCOPE_KEY_ = TOKEN_PREFIX_ + '_scope';

function doGet(e) { 
  /* uncomment this and visit the dev page to figure out what to set REQ_REDIRECT_URI_ to */
  //return ContentService.createTextOutput(REQ_REDIRECT_URI_);
 
  try { 
    if(typeof e.parameter.reset  !== 'undefined'){
      clearMyAuthToken();
      return getLoginPage();
    }
   
    var authHeader = getAuthorizationHeader_();
    if(authHeader){
      var HTMLToOutput = '<h4>'+ authHeader+'</h4>';
      HTMLToOutput = HTMLToOutput + '<br/>' + getData(); //added this
      return HtmlService.createHtmlOutput(HTMLToOutput);
    }
    else {//we are starting from scratch or resetting
      return getLoginPage();
    }
  } catch (ex){
    Logger.log("now catching an error");
    return ContentService.createTextOutput("Errors: " + ex + "\n" + ex.stack);
  }
}

function getLoginPage(){
  Logger.log("creating login page");
  var loginPage = HtmlService.createTemplateFromFile("login");
  Logger.log("in login page");
  loginPage.authUrl = getURLForAuthorization();
  //return ContentService.createTextOutput(loginPage.authUrl);
  return loginPage.evaluate();
}

/** run this to figure out which redirect_uri to put in google developer's console;
don't use this if you are trying to get a dev uri, it will only work for the currently
published (non-dev) version */
function printRedirectURI(){
  Logger.log(REQ_REDIRECT_URI_);
}

/**
* Remove's the calling user's AuthToken from the cache.  Useful if you need to
* yank a token during development or if you need to switch the outgoing account
* you have linked to.
*/
function clearMyAuthToken(){
  CacheService.getPrivateCache().remove(TOKEN_CACHE_KEY_);
  CacheService.getPrivateCache().remove(TOKEN_SCOPE_KEY_);
}

/**
* Called by the auth services; serves the redirect_uri responses.
*/
function usercallback_( request ){

  /* if the state token isn't valid, the StateToken redirect url will reject before it gets here */ 
  try {
    var code = request.parameter.code;
    if(code){
      var parameters = {
        method : 'post',
        contentType:'application/x-www-form-urlencoded',
        muteHttpExceptions:false,
        followRedirects:false,
        payload : 'client_id='+CLIENT_ID_+'&client_secret='+CLIENT_SECRET_+'&grant_type=authorization_code&code=' +code+ '&redirect_uri='+ TOKEN_REDIRECT_URI_
      };
     
      var httpResponse = UrlFetchApp.fetch('[url]https://accounts.google.com/o/oauth2/token',parameters[/url]);
      var tokenResponse = JSON.parse(httpResponse.getContentText());
     
      CacheService.getPrivateCache().put(TOKEN_SCOPE_KEY_, SCOPE);
      CacheService.getPrivateCache().put(TOKEN_CACHE_KEY_, tokenResponse.access_token, 3600-120);
    }
     
    return doGet(request);
   
  }catch(e){
    Logger.log(e + "\n" + e.stack);
    var simulatedRequest = UrlFetchApp.getRequest('[url]https://accounts.google.com/o/oauth2/token',parameters[/url]);
    Logger.log("Failed request looked something like this:\n" + JSON.stringify(simulatedRequest));
    return ContentService.createTextOutput().append("ERRORS RETRIEVING AUTH TOKEN\n").append(Logger.getLog());
  }
}

function getURLForAuthorization(){
  var stateToken = ScriptApp.newStateToken().withMethod('usercallback_').withTimeout(180).createToken();
  var authUrl = Utilities.formatString('[url]https://accounts.google.com/o/oauth2/auth?response_type=code&client_id=%s&redirect_uri=%s&scope=%s&state=%s&approval_prompt=force',CLIENT_ID_,[/url] REQ_REDIRECT_URI_, encodeURIComponent(SCOPE),stateToken); // removed encode URL
  return authUrl; 
}

/**
* Fetches the cached authorization token or null if non exist.  If the
* SCOPE has changed, clears out the token value as a side-effect.
*/

function getAuthorizationHeader_(){
  /* Invalidate the cached token if the scope has changed. */
  var cachedScope = CacheService.getPrivateCache().get(TOKEN_SCOPE_KEY_);
  Logger.log(cachedScope);
  if(cachedScope !== SCOPE){ // changed from !== to !=
    Logger.log("Scope Changed");
    CacheService.getPrivateCache().remove(TOKEN_SCOPE_KEY_);
    CacheService.getPrivateCache().remove(TOKEN_CACHE_KEY_);
    return null;
  } else {
    var token = CacheService.getPrivateCache().get(TOKEN_CACHE_KEY_);
    return token ? 'Bearer ' + token : null;
  }
}


The HTML for the login page is here:

Code Select Expand


<link rel="stylesheet" href="https://ssl.gstatic.com/docs/script/css/add-ons.css">
<div class='sidebar'>
<h3 id='header'>Authorization Required</h3>
<div id="info">If you are seeing this page, it is because you don't currently have an access token.
</div>

<div>
<button onclick="closeMe();">Nope</button>
<a href="asdf" class="action button" href="<?= authUrl ?>">Login</a>
</div>

<script>
function closeMe(){
  if(google && google.script && google.script.host){
      google.script.host.close();
  } else if(window && window.close){
      window.close();
  }
 
  document.getElementById('header').innerHTML = "<span class='error'>Oh SNAP!</span>";
  document.getElementById('info').innerHTML = "<span class='error'>I tried to close this window but failed.  It wasn't because I didn't try, though.</span>";
}
</script>
</div>


[JeffDG]

OK,

Another major issue I've had to deal with is "Password Reset" requests from people who forget their password on TNCAP.US.

I've asked NHQ to open up an authentication portal where we can authenticate users, but their first response was to throw the "It violates our security" flag.  When I made clear that I actually know something about which I speak (ie. "How does someone authenticating to your site for access to mine violate your security again?", I got the "Uhhh, let me look into that"  A year later, I prodded and got "too busy, go away" response.

So...PW resets need to be done.

This is how we do it:
First, a form that is available to anyone (no authentication needed), is attached.  Users punch in their CAPID and select whether they want to use their Primary or Secondary E-mail or Cell Phone (SMS) to receive a new randomized password.

The form then runs a script on submission of a form that resets the password and delivers the password as requested:

Code Select Expand

/* Twilio Creds */
var account_sid = ''
var auth_token = ''


var client = new twiliogas.RestClient(account_sid, auth_token);
var mytwilionum = "";
var sqlserver = ""
var sqluser = "";
var sqlpw = "";
var mysqldb = "";


function allResponses () {
  var url=ScriptApp.getService().getUrl();
 
  var form = FormApp.openById('<<formid>>');
  var formResponses = form.getResponses();
  for (var i = 0; i < formResponses.length; i++) {
    var formResponse = formResponses[i];
    ProcessResponse (formResponses[i]);
  }
 
 
}


function responseTest (e) {
  var response = e.response;
 
  ProcessResponse (response);
 
}

function ProcessResponse (r) {
 
  var items = r.getItemResponses();
 
  var capid = "";
  var deliveryroutes;
 
  for (var i=0;i<items.length;i++)
  {
    var curitem = items[i];
   
    var title = curitem.getItem().getTitle();
    var text = curitem.getResponse();
   
    if (title=="CAPID")
    {
      capid = text;
    }
   
    if (title=="Deliver New Password By")
    {
      deliveryroutes=text;
    }
  }
 
 
  var sql = "select * from "+mysqldb+".MbrContact where capid = '" + capid + "'";
 
  var conn = Jdbc.getConnection("jdbc:mysql://"+sqlserver+":3306/", sqluser, sqlpw);
  var stmt = conn.createStatement();
  stmt.setMaxRows(50000);
  var start = new Date();
  var rs = stmt.executeQuery(sql);
 
  //Read the data into a temporary array
  var rowcount = 0;
 
  var rowset = Array(50000)
 
  while (rs.next())
  {
    rowset[rowcount] = Array(4);
   
    for (var i=0;i<4;i++)
    {
      rowset[rowcount][i] = rs.getString(i+1);
    }
   
   
   
    rowcount++;
   
  }
 
  var newpw = generatepass(8);
  var signature = Utilities.computeDigest(Utilities.DigestAlgorithm.MD5, newpw);
  var signatureStr = '';
    for (i = 0; i < signature.length; i++) {
      var byte = signature[i];
      if (byte < 0)
        byte += 256;
      var byteStr = byte.toString(16);
      // Ensure we have 2 chars in our byte, pad with 0
      if (byteStr.length == 1) byteStr = '0'+byteStr;
      signatureStr += byteStr;
    }   
  Logger.log(signatureStr);
  var newpwmd5= signatureStr;

 
  if (rowcount > 0)
  {
    var validuser = 0;
    var user;
    try {
      user = AdminDirectory.Users.get(capid+"@tncap.us");
      user.hashFunction = "MD5";
      user.password = newpwmd5;
      user.changePasswordAtNextLogin=true;
      AdminDirectory.Users.update(user, capid+"@tncap.us");
      validuser++
    }
    catch (e)
    {
      var a=JSON.stringify(e);
      var b=5;
    }
   
    if (validuser > 0)
    {
      for (var j=0;j<rowcount;j++)
      {
        var currow = rowset[j];
        var ctype = currow[1];
        var cpri = currow[2];
        var cid = currow[3];
        var ccapid = currow[0];
       
        if (ctype == "EMAIL")
        {
          if (cpri == "PRIMARY")
          {
            var idx = deliveryroutes.indexOf("Primary EMail");
            if (idx > -1)
            {
              SendMail (cid,ccapid,newpw);
            }
          }
          if (cpri == "SECONDARY")
          {
            var idx = deliveryroutes.indexOf("Secondary EMail");
            if (idx > -1)
            {
              SendMail (cid,ccapid,newpw);
            }
           
          }
        }
        if (ctype == "CELL PHONE")
        {
          if (cpri == "PRIMARY")
          {
            var idx = deliveryroutes.indexOf("Primary Cell Text");
            if (idx > -1)
            {
              SendText (cid,ccapid,newpw);
            }
           
          }
          if (cpri == "SECONDARY")
          {
            var idx = deliveryroutes.indexOf("Secondary Cell Text");
            if (idx > -1)
            {
              SendText (cid,ccapid,newpw);
            }
           
          }
        }
       
       
       
       
        var a=1;
       
       
       
       
      }
     
     
     
    }
   
   
   
   
  }
 
  var c=1;
 
 
 
 
}

function SendText(number,capid,newpw)
{

  var client = new twiliogas.RestClient(account_sid, auth_token);
  var mytwilionum = "";
 
  //  return;
 
  var message = "New Password Set for account: "+capid+"@tncap.us.  The new password is \""+newpw+"\".  If you have any questions, e-mail ito@tncap.us";
    Logger.log("Sending SMS to "+number+" with message: "+message);
  var chunks = chunkString(message,160);
 
  for (var i=0;i<chunks.length;i++)
  {
   
    client.sendSms({
      to:"+1"+number, // Any number Twilio can deliver to
      from: mytwilionum, // A number you bought from Twilio and can use for outbound communication
      body: chunks[i] // body of the SMS message
    });
  }
 
}


function chunkString(s, len)
{
  var curr = len, prev = 0;
 
  output = [];
 
  while(s[curr]) {
    if(s[curr++] == ' ') {
      output.push(s.substring(prev,curr));
      prev = curr;
      curr += len;
    }
    else
    {
      var currReverse = curr;
      do {
        if(s.substring(currReverse - 1, currReverse) == ' ')
        {
          output.push(s.substring(prev,currReverse));
          prev = currReverse;
          curr = currReverse + len;
          break;
        }
        currReverse--;
      } while(currReverse > prev)
        }
        }
    output.push(s.substr(prev));
    return output;
  }
 
  function SendMail (email,capid,newpw)
  {
    MailApp.sendEmail(email, "TNCAP.US Password Reset Request for "+capid, "A request has been made to reset the password for account "+capid+"@tncap.us.  The new password is:  "+newpw+"\n\nYou will be required to change this passowrd at the next login.\n\nIf you have questions, please contact ito@tncap.us");
  }
 
  function generatepass(length) {
   
    var charset = "abcdefghijknopqrstuvwxyzABCDEFGHJKLMNPQRSTUVWXYZ23456789",
        retVal = "";
    for (var i = 0, n = charset.length; i < length; ++i) {
      retVal += charset.charAt(Math.floor(Math.random() * n));
    }
    return retVal;
  };


[A.Member]

Thanks for the info Jeff.   :clap: 

This is exciting and seems to be exactly what we'd like to do for our Wing.  I'll have to come back and look at what you're doing more closely to see how we can leverage.

Essentially, we have two sites:
* External/Public - mnwg.cap.gov which just had a silent go live Fri. night (just tweaking/optimizing before broader advertisement)
* Intranet - this is Google Apps with a Sites page (the Public site also links to this); this is currently being built out

We need to overcome the challenge of account provisioning but hopefully after reviewing your info closer we can understand enough of what you did to leverage it.  Ideally, the goal is for every member of the Wing to have a @mnwg.cap.gov email address/Google account.  Groups will be used for communication/announcements.  Only those with a @mnwg.cap.gov account will be allowed access to the intranet. 

The challenge is in managing accounts, particularly since an automated feed from CAPWATCH is no longer offered by National.  However, if we did a monthly manual download, perhaps that frequency would be "good enough" to meet our needs.  Seems the real impact would be that new members may have to wait as long as a month before their account would be established.  There would also be up a month lag when disabling.  Is that a huge deal?  Probably not.

Once we get stuff squared away we can decommission our current site.

You can actually combine both on one public page.

You can have your "public" page, with members-only pages that you need to be authenticated to see.

Yes, we could and considered doing so.  However, having these on two separate domains (.gov and .org) offered one key advantage:  When it comes to member maintained content, keeping this in the .org domain is less restrictive.  That is to say, we don't have to worry about whether the content owner correctly added alerts to external links to content, etc. notifying visitors that they're leaving the domain (.gov requires this).  Thus, it helps ensure we are able to continue meeting requirements for a .gov domain use.

Back to account provisioning, how do you handle communication of the password to members upon initial account set up?  Passwords are automatically generated when the account is set up but users will have no way to know what it is for their initial log in.

[JeffDG]

Thanks for the info Jeff.   :clap: 

This is exciting and seems to be exactly what we'd like to do for our Wing.  I'll have to come back and look at what you're doing more closely to see how we can leverage.

Essentially, we have two sites:
* External/Public - mnwg.cap.gov which just had a silent go live Fri. night (just tweaking/optimizing before broader advertisement)
* Intranet - this is Google Apps with a Sites page (the Public site also links to this); this is currently being built out

We need to overcome the challenge of account provisioning but hopefully after reviewing your info closer we can understand enough of what you did to leverage it.  Ideally, the goal is for every member of the Wing to have a @mnwg.cap.gov email address/Google account.  Groups will be used for communication/announcements.  Only those with a @mnwg.cap.gov account will be allowed access to the intranet. 

The challenge is in managing accounts, particularly since an automated feed from CAPWATCH is no longer offered by National.  However, if we did a monthly manual download, perhaps that frequency would be "good enough" to meet our needs.  Seems the real impact would be that new members may have to wait as long as a month before their account would be established.  There would also be up a month lag when disabling.  Is that a huge deal?  Probably not.

Once we get stuff squared away we can decommission our current site.

You can actually combine both on one public page.

You can have your "public" page, with members-only pages that you need to be authenticated to see.

Yes, we could and considered doing so.  However, having these on two separate domains (.gov and .org) offered one key advantage:  When it comes to member maintained content, keeping this in the .org domain is less restrictive.  That is to say, we don't have to worry about whether the content owner correctly added alerts to external links to content, etc. notifying visitors that they're leaving the domain (.gov requires this).  Thus, it helps ensure we are able to continue meeting requirements for a .gov domain use.

Back to account provisioning, how do you handle communication of the password to members upon initial account set up?  Passwords are automatically generated when the account is set up but users will have no way to know what it is for their initial log in.

It's mailed to their primary email from eServices

[A.Member]

Back to account provisioning, how do you handle communication of the password to members upon initial account set up?  Passwords are automatically generated when the account is set up but users will have no way to know what it is for their initial log in.

It's mailed to their primary email from eServices

OK.  So, I'm assuming you do something like this:
1.  export/download all users from Google Apps
2.  manually generate and assign passwords to each user record
3.  link the downloaded record and manually generated password back to the users email address you obtained from the member record on CAPWATCH via Excel or something similar
4.  upload the user records and password back to Google Apps
5.  run a script or something similar that sends the account name and password to the email address listed on the CAPWATCH record

Am I close?

[JeffDG]

Back to account provisioning, how do you handle communication of the password to members upon initial account set up?  Passwords are automatically generated when the account is set up but users will have no way to know what it is for their initial log in.

It's mailed to their primary email from eServices

OK.  So, I'm assuming you do something like this:
1.  export/download all users from Google Apps
2.  manually generate and assign passwords to each user record
3.  link the downloaded record and manually generated password back to the users email address you obtained from the member record on CAPWATCH via Excel or something similar
4.  upload the user records and password back to Google Apps
5.  run a script or something similar that sends the account name and password to the email address listed on the CAPWATCH record

Am I close?

Not really.

1. Scan CAPWATCH for new user records
2.  Generate random password for user
3.  Create account for user
4.  Query CAPWATCH for user's primary e-mail
5.  Send email to user with account and password

Password is not stored anywhere.

[A.Member]

Back to account provisioning, how do you handle communication of the password to members upon initial account set up?  Passwords are automatically generated when the account is set up but users will have no way to know what it is for their initial log in.

It's mailed to their primary email from eServices

OK.  So, I'm assuming you do something like this:
1.  export/download all users from Google Apps
2.  manually generate and assign passwords to each user record
3.  link the downloaded record and manually generated password back to the users email address you obtained from the member record on CAPWATCH via Excel or something similar
4.  upload the user records and password back to Google Apps
5.  run a script or something similar that sends the account name and password to the email address listed on the CAPWATCH record

Am I close?

Not really.

1. Scan CAPWATCH for new user records
2.  Generate random password for user
3.  Create account for user
4.  Query CAPWATCH for user's primary e-mail
5.  Send email to user with account and password

Password is not stored anywhere.

Makes sense, I think, but how do you manage that process for an initial implementation when you have hundreds (thousand+) users that need accounts set up and passwords generated and sent.  You're not doing this manually are you?

[JeffDG]

Makes sense, I think, but how do you manage that process for an initial implementation when you have hundreds (thousand+) users that need accounts set up and passwords generated and sent.  You're not doing this manually are you?

Same way.

Script looks at Google Apps and says I see users A,B and C

Then looks at the CAPWATCH DB and sees valid users of A,D,E and concludes that it needs to add D and E and disable B and C.  Doesn't matter if it wants to add 2 users or 200, it just adds each individual account and informs the user of his/her new password and account info.

What you may run into on initial implementation is that Google App Script has a maximum runtime of 6 minutes.  Because of how it works, if it times out creating a lot of accounts, it will pick up where it left off automatically.

[A.Member]

Finally had a chance to look at your account creation script closer and think I understand your approach but want to confirm:

You're setting up the accounts with CAP ID but then creating an alias so the members have an email address like firstname.lastname@tncap.us, correct?  If this is correct, do the member login to their account with the alias or do they need to login with the capid@tncap.us?  I'm still very much learning Google Apps (so pardon me if this seems basic) but my understanding is a user must login with credentials used to create the account (ie, in this case the members CAP ID). 

[JeffDG]

Finally had a chance to look at your account creation script closer and think I understand your approach but want to confirm:

You're setting up the accounts with CAP ID but then creating an alias so the members have an email address like firstname.lastname@tncap.us, correct?  If this is correct, do the member login to their account with the alias or do they need to login with the capid@tncap.us?  I'm still very much learning Google Apps (so pardon me if this seems basic) but my understanding is a user must login with credentials used to create the account (ie, in this case the members CAP ID).

Correct.  Account names are in the form of <capid>@tncap.us  Users log in with that as their account name.

This provided two main advantages:
1.  Uniqueness.  I don't need to check for uniqueness in names.  No matter how many John Smiths I have, each and every one has a unique CAPID, saving me a lot of code checking for uniqueness, and coding rules to fix duplicates.

2.  Delineation of script-managed accounts:  Never put all your eggs in one basket.  A bad CAPWATCH file could have your script disable every single account in your system (including yours as the admin).  For that reason, we have a small number of manually administered accounts.  The rule is, if the account name starts with a number, then the script will manage it.  Otherwise, it will leave it alone.  So, while my 477407@tncap.us account is managed by the script, I have a back-door account that will let me in if things go wrong.  I need to redo it since we rewrote some of our provisioning, but there was a site where the password for that account could be reset by anyone who is assigned as a Wing ITO, Commander or Vice Commander for emergency purposes, so if I get disgruntled, my access can be taken away!

Another reason for a manually-administered account is someone outside your scope needs access.  For example, if I want to let someone from SER into our system, I can create an account for them based on their name.  So long as I don't start it with a number, it will not be auto-disabled, and you can closely monitor access by that account to make sure people aren't abusing the account.

Finally, it's a good idea to have an emergency account, and record that in your Internet Operations application in eServices.

[A.Member]

Finally had a chance to look at your account creation script closer and think I understand your approach but want to confirm:

You're setting up the accounts with CAP ID but then creating an alias so the members have an email address like firstname.lastname@tncap.us, correct?  If this is correct, do the member login to their account with the alias or do they need to login with the capid@tncap.us?  I'm still very much learning Google Apps (so pardon me if this seems basic) but my understanding is a user must login with credentials used to create the account (ie, in this case the members CAP ID).

Correct.  Account names are in the form of <capid>@tncap.us  Users log in with that as their account name.

This provided two main advantages:
1.  Uniqueness.  I don't need to check for uniqueness in names.  No matter how many John Smiths I have, each and every one has a unique CAPID, saving me a lot of code checking for uniqueness, and coding rules to fix duplicates.

2.  Delineation of script-managed accounts:  Never put all your eggs in one basket.  A bad CAPWATCH file could have your script disable every single account in your system (including yours as the admin).  For that reason, we have a small number of manually administered accounts.  The rule is, if the account name starts with a number, then the script will manage it.  Otherwise, it will leave it alone.  So, while my 477407@tncap.us account is managed by the script, I have a back-door account that will let me in if things go wrong.  I need to redo it since we rewrote some of our provisioning, but there was a site where the password for that account could be reset by anyone who is assigned as a Wing ITO, Commander or Vice Commander for emergency purposes, so if I get disgruntled, my access can be taken away!

Another reason for a manually-administered account is someone outside your scope needs access.  For example, if I want to let someone from SER into our system, I can create an account for them based on their name.  So long as I don't start it with a number, it will not be auto-disabled, and you can closely monitor access by that account to make sure people aren't abusing the account.

Finally, it's a good idea to have an emergency account, and record that in your Internet Operations application in eServices.

I like it.  We're tracking in the same direction.

On the topic of CAPWATCH, can you shed a little light on the impact of a bad CAPWATCH update.  In your script it looks like you check for the last CAPWATCH download date.  If a new download is not received, the last download date should be the same and no action is taken (ie the script is not run).  However, what happens if CAPWATCH is updated but they have an issue populating a table, resulting in say blank columns in the Member table (I don't have experience loading CAPWATCH daily so I don't know how real that problem could be)?  I don't see anything obvious that looks for such a scenario.  So, based on what I think I understand, that will result in the Google account being disabled, correct?  Is the fix simply to wait for a good CAPWATCH file (possibly the next day) when the script reruns again and switches the account status from disable to active?  It doesn't recreate brand new accounts, correct?

[JeffDG]

Finally had a chance to look at your account creation script closer and think I understand your approach but want to confirm:

You're setting up the accounts with CAP ID but then creating an alias so the members have an email address like firstname.lastname@tncap.us, correct?  If this is correct, do the member login to their account with the alias or do they need to login with the capid@tncap.us?  I'm still very much learning Google Apps (so pardon me if this seems basic) but my understanding is a user must login with credentials used to create the account (ie, in this case the members CAP ID).

Correct.  Account names are in the form of <capid>@tncap.us  Users log in with that as their account name.

This provided two main advantages:
1.  Uniqueness.  I don't need to check for uniqueness in names.  No matter how many John Smiths I have, each and every one has a unique CAPID, saving me a lot of code checking for uniqueness, and coding rules to fix duplicates.

2.  Delineation of script-managed accounts:  Never put all your eggs in one basket.  A bad CAPWATCH file could have your script disable every single account in your system (including yours as the admin).  For that reason, we have a small number of manually administered accounts.  The rule is, if the account name starts with a number, then the script will manage it.  Otherwise, it will leave it alone.  So, while my 477407@tncap.us account is managed by the script, I have a back-door account that will let me in if things go wrong.  I need to redo it since we rewrote some of our provisioning, but there was a site where the password for that account could be reset by anyone who is assigned as a Wing ITO, Commander or Vice Commander for emergency purposes, so if I get disgruntled, my access can be taken away!

Another reason for a manually-administered account is someone outside your scope needs access.  For example, if I want to let someone from SER into our system, I can create an account for them based on their name.  So long as I don't start it with a number, it will not be auto-disabled, and you can closely monitor access by that account to make sure people aren't abusing the account.

Finally, it's a good idea to have an emergency account, and record that in your Internet Operations application in eServices.

I like it.  We're tracking in the same direction.

On the topic of CAPWATCH, can you shed a little light on the impact of a bad CAPWATCH update.  In your script it looks like you check for the last CAPWATCH download date.  If a new download is not received, the last download date should be the same and no action is taken (ie the script is not run).  However, what happens if CAPWATCH is updated but they have an issue populating a table, resulting in say blank columns in the Member table (I don't have experience loading CAPWATCH daily so I don't know how real that problem could be).  I don't see anything obvious that looks for such a scenario.  So, based on what I think I understand, that will result in the Google account being disabled, correct?  Is the fix simply to wait for a good CAPWATCH file (possibly the next day) when the script reruns again and switches the account status from disable to active?  It doesn't recreate brand new accounts, correct?

What I worry about is a hiccup at NHQ results in a CAPWATCH that has a DownLoadDate that's new, but the Member table is empty.

That would result in every account we have that's script-managed being "disabled" en masse.  If we didn't have other accounts, we could not get in to fix it easily.

Hasn't happened yet, but I do contingency plans for as many failure modes as I can conceive of.

[A.Member]

Finally had a chance to look at your account creation script closer and think I understand your approach but want to confirm:

You're setting up the accounts with CAP ID but then creating an alias so the members have an email address like firstname.lastname@tncap.us, correct?  If this is correct, do the member login to their account with the alias or do they need to login with the capid@tncap.us?  I'm still very much learning Google Apps (so pardon me if this seems basic) but my understanding is a user must login with credentials used to create the account (ie, in this case the members CAP ID).

Correct.  Account names are in the form of <capid>@tncap.us  Users log in with that as their account name.

This provided two main advantages:
1.  Uniqueness.  I don't need to check for uniqueness in names.  No matter how many John Smiths I have, each and every one has a unique CAPID, saving me a lot of code checking for uniqueness, and coding rules to fix duplicates.

2.  Delineation of script-managed accounts:  Never put all your eggs in one basket.  A bad CAPWATCH file could have your script disable every single account in your system (including yours as the admin).  For that reason, we have a small number of manually administered accounts.  The rule is, if the account name starts with a number, then the script will manage it.  Otherwise, it will leave it alone.  So, while my 477407@tncap.us account is managed by the script, I have a back-door account that will let me in if things go wrong.  I need to redo it since we rewrote some of our provisioning, but there was a site where the password for that account could be reset by anyone who is assigned as a Wing ITO, Commander or Vice Commander for emergency purposes, so if I get disgruntled, my access can be taken away!

Another reason for a manually-administered account is someone outside your scope needs access.  For example, if I want to let someone from SER into our system, I can create an account for them based on their name.  So long as I don't start it with a number, it will not be auto-disabled, and you can closely monitor access by that account to make sure people aren't abusing the account.

Finally, it's a good idea to have an emergency account, and record that in your Internet Operations application in eServices.

I like it.  We're tracking in the same direction.

On the topic of CAPWATCH, can you shed a little light on the impact of a bad CAPWATCH update.  In your script it looks like you check for the last CAPWATCH download date.  If a new download is not received, the last download date should be the same and no action is taken (ie the script is not run).  However, what happens if CAPWATCH is updated but they have an issue populating a table, resulting in say blank columns in the Member table (I don't have experience loading CAPWATCH daily so I don't know how real that problem could be).  I don't see anything obvious that looks for such a scenario.  So, based on what I think I understand, that will result in the Google account being disabled, correct?  Is the fix simply to wait for a good CAPWATCH file (possibly the next day) when the script reruns again and switches the account status from disable to active?  It doesn't recreate brand new accounts, correct?

What I worry about is a hiccup at NHQ results in a CAPWATCH that has a DownLoadDate that's new, but the Member table is empty.

That would result in every account we have that's script-managed being "disabled" en masse.  If we didn't have other accounts, we could not get in to fix it easily.

Hasn't happened yet, but I do contingency plans for as many failure modes as I can conceive of.

Yes, that's exactly what I was referring to but perhaps didn't articulate well. 

Help me understand this better...what happens in a scenario where an active member let's their membership lapse/non-renews (CAPID goes from active to expired in CAPWATCH Member table) but then, say, 3 weeks later they update their renewal (have gone from expired back to active in CAPWATCH under the same CAP ID)?  In this scenario, what takes place with the Google Account?  How is this handled?

[JeffDG]

Finally had a chance to look at your account creation script closer and think I understand your approach but want to confirm:

You're setting up the accounts with CAP ID but then creating an alias so the members have an email address like firstname.lastname@tncap.us, correct?  If this is correct, do the member login to their account with the alias or do they need to login with the capid@tncap.us?  I'm still very much learning Google Apps (so pardon me if this seems basic) but my understanding is a user must login with credentials used to create the account (ie, in this case the members CAP ID).

Correct.  Account names are in the form of <capid>@tncap.us  Users log in with that as their account name.

This provided two main advantages:
1.  Uniqueness.  I don't need to check for uniqueness in names.  No matter how many John Smiths I have, each and every one has a unique CAPID, saving me a lot of code checking for uniqueness, and coding rules to fix duplicates.

2.  Delineation of script-managed accounts:  Never put all your eggs in one basket.  A bad CAPWATCH file could have your script disable every single account in your system (including yours as the admin).  For that reason, we have a small number of manually administered accounts.  The rule is, if the account name starts with a number, then the script will manage it.  Otherwise, it will leave it alone.  So, while my 477407@tncap.us account is managed by the script, I have a back-door account that will let me in if things go wrong.  I need to redo it since we rewrote some of our provisioning, but there was a site where the password for that account could be reset by anyone who is assigned as a Wing ITO, Commander or Vice Commander for emergency purposes, so if I get disgruntled, my access can be taken away!

Another reason for a manually-administered account is someone outside your scope needs access.  For example, if I want to let someone from SER into our system, I can create an account for them based on their name.  So long as I don't start it with a number, it will not be auto-disabled, and you can closely monitor access by that account to make sure people aren't abusing the account.

Finally, it's a good idea to have an emergency account, and record that in your Internet Operations application in eServices.

I like it.  We're tracking in the same direction.

On the topic of CAPWATCH, can you shed a little light on the impact of a bad CAPWATCH update.  In your script it looks like you check for the last CAPWATCH download date.  If a new download is not received, the last download date should be the same and no action is taken (ie the script is not run).  However, what happens if CAPWATCH is updated but they have an issue populating a table, resulting in say blank columns in the Member table (I don't have experience loading CAPWATCH daily so I don't know how real that problem could be).  I don't see anything obvious that looks for such a scenario.  So, based on what I think I understand, that will result in the Google account being disabled, correct?  Is the fix simply to wait for a good CAPWATCH file (possibly the next day) when the script reruns again and switches the account status from disable to active?  It doesn't recreate brand new accounts, correct?

What I worry about is a hiccup at NHQ results in a CAPWATCH that has a DownLoadDate that's new, but the Member table is empty.

That would result in every account we have that's script-managed being "disabled" en masse.  If we didn't have other accounts, we could not get in to fix it easily.

Hasn't happened yet, but I do contingency plans for as many failure modes as I can conceive of.

Yes, that's exactly what I was referring to but perhaps didn't articulate well. 

Help me understand this better...what happens in a scenario where an active member let's their membership lapse/non-renews (CAPID goes from active to expired in CAPWATCH Member table) but then, say, 3 weeks later they update their renewal (have gone from expired back to active in CAPWATCH under the same CAP ID)?  In this scenario, what takes place with the Google Account?  How is this handled?

OK...let's say john smith, capid 123456 expires DEC 31 and renews Jan 15

On Jan 1, the account will be suspended, and John will no longer have access to any member resources.  Then on the 16th, it will be unsuspended, and all prior privs will automatically be restored

[A.Member]

What I worry about is a hiccup at NHQ results in a CAPWATCH that has a DownLoadDate that's new, but the Member table is empty.

That would result in every account we have that's script-managed being "disabled" en masse.  If we didn't have other accounts, we could not get in to fix it easily.

Hasn't happened yet, but I do contingency plans for as many failure modes as I can conceive of.

Yes, that's exactly what I was referring to but perhaps didn't articulate well. 

Help me understand this better...what happens in a scenario where an active member let's their membership lapse/non-renews (CAPID goes from active to expired in CAPWATCH Member table) but then, say, 3 weeks later they update their renewal (have gone from expired back to active in CAPWATCH under the same CAP ID)?  In this scenario, what takes place with the Google Account?  How is this handled?

OK...let's say john smith, capid 123456 expires DEC 31 and renews Jan 15

On Jan 1, the account will be suspended, and John will no longer have access to any member resources.  Then on the 16th, it will be unsuspended, and all prior privs will automatically be restored

Makes sense and is what I understood when looking at the script. 

So, it seems a worst case scenario might be where National runs into an issue and doesn't populate Member table correctly, resulting in all numerical based accounts being moved to suspended.  However, you'll still have access to your alpha-based accounts and when National corrects the issue (say the following day), the script is rerun and should return each of those accounts back to active.  Thus, resolving the issue.

Also, in your script, it indicates that you disable Google accounts but don't actually delete them.  Is there a scenario where you actually perform maintenance (whether via script or manually) and actually delete the user account; ex. after 2 years in suspended status (because national would reissue a new CAP ID)?  We have a 2,000 account limit on Google Apps so after awhile it seems we'd need to clean some of those "old" disabled accounts.  Have you run into this scenario?