Graph and PowerShell BlogWhen I started my current role, just over 6 years ago, one of the first things I changed was the user creation process. The IT Department was using template accounts that were copied and then edited to be used as a new account. This method had several issues, so I designed a web interface where all users would get created via one web page.
There were many advantages to this:
- New users were created in the correct location.
- Typos were eliminated.
- Other mail objects like Shared Mailboxes, Contacts and Groups, all got created with the same settings.
- A change to a department could be made in the script rather than several template accounts.
Despite this, it took a lot of work to keep up with OU and Group changes. Rather than putting the emphasis on me to keep up with administration changes, I decided the best solution was to create a spreadsheet in Teams and have managers update that, then have the script pull this data regularly and use in the creation script.
I've never looked at pulling information from Teams via PowerShell up to now, other than knowing it should be possible in theory. There were several steps to this process:
1. Assign Permissions to the Sharepoint Site
Each Teams Channel will have its own SharePoint site, granting permissions to the files has to be done at the SharePoint level. We don't want to give our SharePoint app control over all SharePoint Sites, just the Sites we implicitly grant access to:
1. Connect to PnP PowerShell (as a SharePoint Admin): Connect-PnPOnline -Interactive -Url https://[mydomain].sharepoint.com/
2. Grant-PnPAzureADAppSitePermission -AppId "[myAzureAppID]" -DisplayName "AppRead" -Permissions Read -Site https://[mydomain].sharepoint.com/sites/HelpDesk-MailboxCreationSpreadsheet
3. In your Azure App add the permission 'Sites.Selected'. This will also need admin approval.
2. Grant-PnPAzureADAppSitePermission -AppId "[myAzureAppID]" -DisplayName "AppRead" -Permissions Read -Site https://[mydomain].sharepoint.com/sites/HelpDesk-MailboxCreationSpreadsheet
3. In your Azure App add the permission 'Sites.Selected'. This will also need admin approval.
↑ Your new Azure App permission will need to be approved by a Global Admin.
2. Find the Site and Files we want to Access
Now that we have the permission to view our Excel file we need to go and actually find it. Due to a bug, you will need the SiteID of the SharePoint Site, the name alone will not work in the Invoke-MgGraph command. After this, we need the DriveID and finally we list all children to get the FileID.
1. Open the SharePoint Site from Teams channel, note the URL.
2. Based on this URL go to the following page: https://[mydomain].sharepoint.com/sites/HelpDesk-MailboxCreationSpreadsheet/_api/site/Id , note down the SiteID from the XML.
3. Using the SiteID we connect to MgGraph and list the files: Invoke-MgGraphRequest -Method GET https://graph.microsoft.com/v1.0/[siteID]/drive/root/children
2. Based on this URL go to the following page: https://[mydomain].sharepoint.com/sites/HelpDesk-MailboxCreationSpreadsheet/_api/site/Id , note down the SiteID from the XML.
3. Using the SiteID we connect to MgGraph and list the files: Invoke-MgGraphRequest -Method GET https://graph.microsoft.com/v1.0/[siteID]/drive/root/children
↑ Open the SharePoint Site from the Teams channel.
3. Access and Parse the Excel file
After the last step, we have the DriveId and ItemId that we can use to access the file.
Connect-MgGraph -CertificateThumbPrint "xxx" -AppID "xxx" -TenantId "xxx" -NoWelcome
$driveid = "xxx"
$driveitemid = "xxx"
$excelfile = "c:\scripts\Exchange\ex-createUserNew.xlsx"
Get-MgDriveItemContent -driveid $driveid -driveitemid $driveitemid -OutFile $excelfile
$rows = import-excel $excelfile
foreach ($row in $rows)
{
$r_ou = $row.ou
$r_group = $row.groups
...
$driveid = "xxx"
$driveitemid = "xxx"
$excelfile = "c:\scripts\Exchange\ex-createUserNew.xlsx"
Get-MgDriveItemContent -driveid $driveid -driveitemid $driveitemid -OutFile $excelfile
$rows = import-excel $excelfile
foreach ($row in $rows)
{
$r_ou = $row.ou
$r_group = $row.groups
...
Using Get-MgDriveContent we can return the Excel document as a file, then use the ImportExcel PowerShell Module to import the data and fill out our user creation script. The rows get imported into a [psCustomObject] for flexibility.
4. Build User Creation Script from Excel File
My user creation process is made up of two parts, a PHP front-end that saves the entries to a text file, and a PowerShell script that reads this text file and creates the user mailbox. The first step to validate is on the PHP page where users can select what OU the user should be created in. These OUs get changed regularly so the best way to keep updated is to use the Excel file to update a text field that will be included in the PHP page.
To keep managers updated when an OU is changed I also added an email alert when an OU from the Excel file is not found in Active Directory (AD).
I set up another alert if a group listed in Excel does not match anything in AD. Outside of that, there are not really any other fields that would be changed and go unnoticed.