Glossary:
Team/s = Microsoft Teams
team/s = curriculum area or group of staff

In the session 21/22 the decision was taken to create a Microsoft Team for every taught course. An example would be DACC (HND Accounting). Although courses can have many cohorts (A, B groups etc.), it was felt that having a central point of contact for all students on a particular course would be useful. This is especially true early in the academic year when new students may need guidance to find their fellow students and tutors. This is further highlighted by the increased number of online only classes during the pandemic.

We already know who enrolled students are from the SITS sce table, so it is quite straightforward to create Teams as the enrollments are added.

The automated MS Team creation takes place on a Linux server. This seems counter-intuitive however I find managing Powershell and it’s associated modules easier on that platform. I will produce a separate blog about how to set up Powershell with the Teams module on Linux / Mac in the future.

I will provide the complete script here, sanitised of course, so it can be adapted for other purposes.

Secure CREDENTIALS

When testing MS Teams automations I would often use the Connect-MicrosoftTeams command following the instructions to authenticate via browser. For the production script it is necessary to use encrypted credentials automatically. There are many valuable guides to doing this such as the one provided by PDQ.com. When you have successfully created an encrypted credential file and AES key your script can connect autonomously in this fashion:

$AdminAccount = "youradmin@yourdomain.com"
$AdminPass = Get-Content "/yourfolder/yourencryptedcreds.txt" | ConvertTo-SecureString -Key (Get-Content "/yourfolder/AES.key")
$credential = New-Object System.Management.Automation.PsCredential($AdminAccount, $AdminPass)
Connect-MicrosoftTeams -Credential $credential

DATABASE CONNECTION

It is very easy to utilise MSSQL databases in Powershell. To keep your script tidy you should include your read only database user credentials in a separate file and check for the existence of the file, exiting on failure (you don’t want to be connected to Teams any more if your DB connection was not successful).

# Load the database credentials from info file
$ScriptDirectory = Split-Path -Path $MyInvocation.MyCommand.Definition -Parent

try {
    . ("$ScriptDirectory\dbcreds.ps1")
    } catch
{
    Write-Host "Error loading database include file"
    Exit
}

The dbcreds.ps1 file looks like this:

	$SQL_hostname 				= "dbserver"
	$SQL_database 				= "dbname"
	$SQL_uid_read				= "dbuser"
	$SQL_pwd_read 				= "dbpasswd"

GET COURSE LIST

It will be essential to create a SQL query containing course names, course codes and student email addresses, sorted by course (the reason for this will become clear later). I will not reproduce this exactly here for obvious reasons. This query can be adapted to your circumstances.

select studentcode, CONCAT(studentcode, '@yourdomain.ac.uk') as studentemail, coursecode, CONCAT(coursecode, ' - ', coursename, ' (2021/22)') as teamtitle
GROUP BY coursecode,studentcode
ORDER BY coursecode

Group your results by course code, and student code. be sure to order the query by course code. You should be careful when naming Teams that you are able to identify them again later to remove previous sessions. With that in mind I have created Teams using the following convention:

coursecode – coursename (session)

This produces a pleasing, and easily identified Team name.

DACC – HND Accounting @ SCQF Level 7 (2021/22)

Every student from whatever cohort of DACC should find that Team when they log in for the first time.

NEXT TIME

In the next post I will work through the query results creating Teams and adding team users