Managing Google Apps with Powershell

You don't have to be too knowledgeable about computing to know Microsoft and Google are at each other's throats constantly in the IT world. Fortunately, that doesn't extend to every level, and so it is with some pleasure I have started to investigate the ability to use Powershell to provision and manage Google Apps for Education.

When I first started managing Google Apps domains for schools I went out looking for a useful management tool to do bulk account management (since Google doesn't provide for this in their admin console) and after a time discovered the Dito GAM tool (Google Apps Manager). Since then I have used it to perform some aspects of account management, which included bulk account suspension when the accounts we had created for students weren't immediately required. Whilst this tool has proved very versatile it lacks capability to be useful when called in scripts and also can't manage the Google Apps shared contacts (we need something to manage this because it is another capability lacking in the Google Apps console).

So at this point I am starting to look at Powershell extensions to manage Google Apps instead. Assuming they conform to Powershell conventions and interoperability they will be usable in Powershell scripts that I write and can therefore be easily integrated into the student add script I am working on. So far here are some tools I have found and will be evaluating:

• Google Apps Powershell (GaPoSh)
• GDataCmdLet
• GShell

Of these, GShell looks the most promising. GDataCmdLet is currently the only one of the three that supports contact management, but hasn't been updated since 2010.

Using Powershell with Active Directory [3]

So continuing on my Powershell with AD series, here is my student account script newly written in Powershell which is calling Get-ADUser to look up accounts in Active Directory.

function AddStudent($StudentData)
{
    write-host "Add " + $StudentData.LName + " " + $StudentData.FName
}

function UpdateStudent($StudentData)
{
    write-host "Update " + $StudentData.LName + " " + $StudentData.FName
}

function RemoveStudent($StudentData)
{
    write-host "Remove " + $StudentData.samAccountName
}

function CreateSubjects($StudentData)
{
}

For now, my function calls are just stubs that tell me they were called and what function they perform. The code for the dirty work still has to be written.

#First check CSV input against AD
#EnrolNum,Lname,Fname,Class,Pwd,StudentID,Logon
$Students = Import-CSV -Path "C:\Users\Patrick\NewStudents.txt"
foreach ($Student in $Students)
{
    # Look up the student in Active Directory to see if they are a current member
        $StudentID = $Student.StudentID
        $CurrentStudent = Get-ADUser -Filter 'EmployeeID -eq $StudentID'
        if ($CurrentStudent -ne $null)
        {
            UpdateStudent($Student)   
        }
        else
        {
            AddStudent($Student)
        }
}

This part of the script is getting the student accounts in from a CSV file. The column names are in the first line of the file and have to be exactly as shown. Then when I reference a row, I can use the same column name that was specified in the CSV file. We used the EmployeeID field in Active Directory to store the StudentID which in this case is the primary key from the database of the Student Management System. Even if the student's name changes, this number never changes. We assume there is only one result from the search, as there should be, and if the result is null (nothing found) we call our AddStudent function to add the account; otherwise we call UpdateStudent to update any changes to the student's data, such as their username.

#Next check AD against CSV
$ADStudents = Get-ADUser -Filter * -SearchBase "ou=HCS-Students,dc=hcs,dc=local" -SearchScope Subtree -Properties employeeID
foreach ($ADStudent in $ADStudents)
{
    $found = $false
    foreach ($Student in $Students)
    {
        if ($Student.StudentID -eq $ADStudent.EmployeeID)
        {
            $found = $true
            break
        }
    }
    if ($found -eq $false)
    {
        RemoveStudent($ADStudent)
    }
}

The main function of this block of code is to do a reverse lookup from the existing accounts in Active Directory to see if these accounts are found in the CSV file. If not, then RemoveStudent is called to disable their account and archive their home drive. Note that in this case we have set the filter to * (all accounts) and instead have used SearchBase and SearchScope to specify where to find the accounts in AD. We also have to use the Properties parameter to get EmployeeID back from AD because it isn't in the set of default properties returned.


In theory Where-Object should make it possible to do the reverse lookup but I wasn't able to make this work so I have the double Foreach loops with the breakout capability in the inner loop. It's still fast enough.

#Now create the subject links for the students
$AllStudents = Get-ADUser -Filter 'enabled -eq $true' -SearchBase "ou=HCS-Students,dc=hcs,dc=local" -SearchScope Subtree
foreach ($Student in $AllStudents)
{
    CreateSubjects($Student)
}

The last piece of code is to do with setting up subject folders in the student's home drive and then creating junctions to them for the teacher to find those folders when they are marking work. This time in addition to SearchBase and SearchScope we have used a filter to find accounts that are enabled. The code needs to be improved so that it also updates subjects such as when a new subject is added. Command line parameters are also needed to allow it to handle only a partial input file such as would be received to add only one or two accounts at a time, instead of the full start of year file.

Well that is enough for now and next time I will post the extra code for the full working version of the script.

Filtering Output with PowerShell

So the next thing to look at in PowerShell is how to filter output from a cmdlet. A good example is Get-Mailbox, a cmdlet that gets you information on the Exchange mailbox settings of a user. This cmdlet has dozens of properties and this gets even worse if you want to see the mailbox settings of all your users at once. Clearly you need some way of just displaying the properties you want to see and excluding the ones you don't need. This can be done using the Select-Object cmdlet and is very simple. We simply pipe the output of Get-Mailbox into Select-Object and specify which properties we want to see.

get-mailbox | select-object

and then specify the names of the properties we want to see out of get-mailbox

So here is an example that produces a CSV file by piping the output from Select-Object to Export-CSV. Note that Select-Object has an alias, select, which I can use to abbreviate it.

 get-mailbox |select name,primarysmtpaddress,forwardingaddress,forwardingsmtpaddress,delivertomailboxandforward | export-csv -notypeinformation -path c:\users\p.dunford\bbb.csv

This gives me a nice CSV list of all the users in these specific mailbox properties only.

Powershell has a lot of useful stuff to do with filtering either properties or the objects that are being returned where you are getting more than one object in a cmdlet. As we see above I used get-mailbox without specifying a particular instance. This returns all the mailboxes on the Exchange server. I could also have the option of using a query to return a set of particular mailboxes. The cmdlet to do this is Where-Object, alias where.

For example, if I want to return only those maiboxes where ForwardingAddress is set to something, I can do this using the command string below:

get-mailbox | where {$_.ForwardingAddress -ne $null}

Note in this case I'm using $_ to refer to the current object instance.

That result could then be piped into select and then to export-csv to get the list in a file as above.

PowerShell's capabilities in filtering objects and properties are far more powerful than the old Windows command interpreter or various VBScript kludges.