I thought I'd post about how to automate extracting reports from Teams (could be any report within your M365 tenant though) and sending them to a SharePoint site for others to access or for historical access.
I get asked fairly often for stats or historical figures for general Teams things...how many messages were sent over a particular timeframe, how many minutes of audio or video were used. Things like that. The reports you can run from within Teams Admin Centre are fine for ad hoc queries but they only go back for a defined period so if the query is longer than the longest report (180 days) then you're reliant on having access to historical data.
So I thought I'd look at a regular automated process to get those reports at scheduled intervals via a script and then copy those to a SharePoint folder so they can be referenced later down the line if needed.
There are probably a few ways to do this but I opted for using a PowerShell script in an Azure Runbook that ran on a scheduled period to carry out the job.
First up was looking at how to access and extract the reports we want. Since we're doing this via an Azure Automation Account we need to create an App Registration with the right permissions to access the reports.
If you haven't already got an App Registration setup for this, access the Azure Portal and create a new one.
Give it a name, you can leave the other options on the first page as the default, and click Register.
You'll be taken to the App Registration and from here the next thing we need to do is add the permissions the app will need. As we can see from the Microsoft documentation for one of the Teams reports, we need to assign the Reports.Read.All permission and we'll want to assign it as an app permission since it'll be running as a background process, not as a logged in user.
This permission doesn't just relate to the Teams reports, it's permission to read all reports across the tenant so while this post is around extracting Teams reports, you could repurpose it for any reports you can run within your tenant really.
Anyway, back to the App Registration. From the overview page, click on API Permissions from the left hand menu then Add a Permission from the next window.
From the Request API Permissions window that opens, click on Microsoft Graph and then select Application Permissions (as mentioned above, we need to select this as the script will run as a background process).
In the Select Permissions box, enter Reports.Read.All and you should see the permission returned. Expand the Reports section, select the permission and then hit Add Permissions
You'll see the permission has been added to the App Registration and you now need to approve it. Click on Grant Admin Consent for Contoso (or whatever your tenant name is)
and when prompted click Yes to approve. You should now see that the permission has been added and is granted for the tenant.
You may notice the App Registration has also been given the User.Read.All permission when it was created, you can remove this if you want. It's not needed for what we're doing here and generally it's best practice to only grant the specific permissions you need to carry out the task. Permission sprawl can lead to Apps being granted more permissions than they need and you can end up with fairly permissive apps hanging around.
Now we've added the permissions, we need to add some of the authentication mechanisms we'll use to connect to Microsoft Graph for the reports and to connect to SharePoint to copy the files.
For the Graph connection, we'll use certificate based authentication as it's stronger than other methods available. If you have an internal PKI infrastructure you can get a certificate signed there to use. If not, you can create a self signed certificate which is what we'll use in this example. I won't go into the details of how to create that here, but it's fairly straightforward and this Microsoft article explains how to do it in a few easy steps, https://learn.microsoft.com/en-us/azure/active-directory/develop/howto-create-self-signed-certificate
Once you have you certificate, click on the Certificates & Secrets blade from the left hand menu, click Certificates and then Upload Certificate
Select the certificate you created and upload, you'll see it's been added. The Thumbprint value we'll need later on so it might be useful to copy this somewhere at this stage ready for later.
Finally in this section, we need to create a Client Secret. This will be for the connection to SharePoint to copy the files. Ideally we'd use the certificate for that connection as well but using the PNP.Online PowerShell module and certificates to connect has stumped me so I've only managed to get it to work with a Client Secret. I am sure it can be done with a certificate so feel free to leave a comment if you know how!
To create the secret, click on the Client Secrets tab next to Certificates and then New Client Secret. From the new window, give the secret a name and select an expiration period, the default of 6 months is probably best but choose one that fits your requirements.
Click Add and you'll see the Secret has been created. Now, an important step. The value of the Client Secret can only be viewed/copied on this page immediately after creation. Once you navigate away from the page you can't ever see it again.
So make sure you take a copy now and store it somewhere safe for future use if you need it.
Last thing to do before we leave the App Registraion page is to gather all the info we'll need for the PowerShell script later. We need the Client ID of the App Registration, the Tenant ID, the Certificate Thumbprint and the Client Secret. You should already have copied the certificate thumbprint somewhere along with the client secret. So to get the Client and Tenant ID, click on the Overview blade from the left hand menu and copy the Client ID and Tenant ID values.
Next step is to create our Automation Account which will contain our PowerShell Runbook. From the Azure Portal search for Automation Accounts and click on Create to generate a new one. Give it a name and select the Azure subscription, region and other options as appropriate for your requirements. You can leave them all as default if you like but you may have a requirement to use a particular Azure region or want to limit the access to Private networks for example.
We now need to add some details to the Automation Account such as the certificate we created earlier for authentication and we'll add some variables to the account so we can call them in our PowerShell script. This will allow us to not show the values for things like the certificate thumbprint, client secret and app registration ID in the PowerShell script itself for added security.
We'll add the certificate first, navigate to the Certificates blade in the left hand menu, click on Add a Certificate and upload the certificate we created earlier. Note that this time we are uploading the PFX file of the certificate not the CER file we added to the App Registration.
When you created the certificate earlier you would have been required to create a password for the PFX file, you'll need to enter that here as you upload it to the Automation Account.
Once uploaded you'll see the certificate displayed. Now click on Variables from the left hand menu and we need to add a variable for each of the following to use in our PowerShell script:
Client ID
Tenant ID
Certificate Thumbprint
Client
Select Add a Variable, give it a name and enter the value for each of the above. I'd suggest selecting Yes to Encrypted so the value isn't displayed in the Variables page for added security. Example below, repeat this step for all of the above so you have 4 variables.
Last thing to do before creating our Runbook is to add the PowerShell modules we'll need to the Automation Account for running the script. We need three to carry out the task, a module to authenticate to Microsoft Graph, a module to access the Reports and a module to run the SharePoint cmdlets to add the files to our site.
Head to the Modules blade in the left hand menu and click Browse Gallery from the top menu
In the search bar type Microsoft.Graph.Authentication and select it
Click Select again in bottom left corner of the new window and then selet 5.1 from the Runtime Version dropdown. Click Import and Azure will import the module into the account.
Repeat the above steps to add another module and add the Microsoft.Graph.Reports and PnP.PowerShell modules.
It'll take a few minutes for the modules to import, if you filter on Type "Custom" you'll see them importing
Once they've finished they'll show as Available. We can move on now and let that run, they'll have imported by the time we come to run our Runbook for the first time.
We can now add our PowerShell runbook to the Automation Account. Click on Runbooks from the left hand menu and then Create a Runbook from the top menu. Give the Runbook a name, select PowerShell as the type and 5.1 as the Runtime Version and then click Create.
You'll now be in the editing page for the Runbook and this is where we add our PowerShell script we want to run.
I've copied a working example below but you'll need to modify this based on your requirements and tenant details.
The key bits you'll need to change are the names of the Automation variables depending on what you've called them, so where below it says -Name ClientID for example, you'd change ClientID to whatever you named the variable for that. Same for the other three.
Then you'll need to amend the line below which says "https://xxx.sharepoint.com/sites/xxx/" and replace the xxx's with your details, first xxx will be your tenant name and second xxx will be the name of the site you're connecting to.
Lastly, on the final line where it says "-Folder "Shared Documents/General" you can change this to specify the folder within your site you want to add the files to. The above should work just fine as there should always be a General folder in a site but you can use whatever you want as long as the folder already exists.
The script below has comments added to for readability and so anyone viewing it can see what each step is doing. They aren't needed for the script to work but is good practice so others can understand what the script is doing and how it works, sharing is caring ;)
##Import the required variables to connect to Microsoft Graph. Imported as variables so the actual values are not included the script##
##Variable values are specificed and encrypted in the Automation Account##
$ClientID = Get-AutomationVariable -Name ClientID
$TenantID = Get-AutomationVariable -Name TenantID
$Cert = Get-AutomationVariable -Name Thumbprint
$Secret = Get-AutomationVariable -Name Secret
##Connect to Microsoft Graph using App Registraion ID (Client ID), Tenant ID and the Certificate thumbprint##
Connect-MgGraph -ClientID $ClientID -TenantId $TenantID -CertificateThumbprint $Cert
##Connect to the SharePoint site we want to add the files to, just needs the top level URL, not the actual folder we are adding to. We specify that later. Use the ClientID
##and Client Secret of the app registration ##
$Connection = Connect-PnPOnline -Url "https://xxx.sharepoint.com/sites/xxx/" -ClientID $ClientID -ClientSecret $Secret -ReturnConnection
##Download the report we want and store it in a file with date appended to the end so we know when it was created##
Invoke-MgGraphRequest -Uri "https://graph.microsoft.com/v1.0/reports/getTeamsUserActivityUserDetail(period='D30')" -OutputFilePath "TeamsUserDetailReport$((Get-Date).ToString('yyyy-MM-dd')).csv"
##Add the file to the specified folder within the SharePoint site, folder can be any folder within the site you're connected to##
Add-PnPFile -Path "TeamsUserDetailReport$((Get-Date).ToString('yyyy-MM-dd')).csv" -Folder "Shared Documents/General" -Connection $connection
Add the script to your runbook, and it should look like this.
Click on Save in the top left of the page to save the script.
We're almost ready to run and test the script but the final thing we need to do is make sure the App Registration has permissions to write files to our SharePoint site otherwise when we try and connect and add the files we'll get an error telling us we don't have permissions.
Open a new tab so we can quickly return to the Runbook later and navigate to the SharePoint site you want to add the reports to.
We now need to access a specific page within the site to add the App Registration and add the permissions we want. There are a couple of good articles I found which go through this in a bit more detail, I've copied them below for info. They both start off with how to create the App Registration which you can ignore as we've done that already. So it's just the part around adding the App Registration to the site and assigning the permissions. I've detailed this below so no need to go to the articles but they're useful for info.
To add the App Registration and permissions, append "/_layouts/15/AppInv.aspx" to your SharePoint site URL. So for example, in my example the site we're working on is https://xxx.sharepoint.com/sites/xxx (replace xxx with your tenant and site name info).
So we need to enter https://xxx.sharepoint.com/sites/xxx/_layouts15/AppInv.aspx into our browser and navigate there. If you've entered it right you should see a page like this:
In the App ID field, enter the Client ID of your App Registration and click Lookup, it should then fill in the name of your App Registration in the field below.
The App Domain is a mandatory field but can be whatever you like, it has no impact on whether this will work or not. So enter whatever you want here. The Redirect URL field isn't mandatory so you don't need to enter anything in there.
In the Permission Request XML box you need to enter the following, amending the 'Right="Write"' to the permissions you want to give the App Registration. I've specified "Write" in the example below as we only need the App to be able to add items to the site, not have full control of it.
<AppPermissionRequests AllowAppOnlyPolicy="true">
<AppPermissionRequest Scope="http://sharepoint/content/sitecollection" Right="Write"/>
<AppPermissionRequest Scope="http://sharepoint/content/sitecollection/web" Right="Write"/>
</AppPermissionRequests>Click on Create and you should be prompted to confirm you want to give the App Registraion the permissions you've specified.
Click "Trust It" and the permissions will be added. We can verify it's been added by navigating to https://xxx.sharepoint.com/sites/xxx/_layouts15/AppPrincipals.aspx (replace xxx as before) and you should see the App Registration listed.
Now...we can actually test our Runbook and see if it works!
Head back to the tab we kept open with the PowerShell script, if you've closed it go back to the Azure Portal, access the Automation Account, select Runbooks and click on the Runbook you created earlier.
We want to get back to this screen:
We're ready to test the script so click on Test Pane in the top left of the screen and from the new screen click Start. You should see the Runbook queuing and then running the script
If all goes well, the test pane will update with an output of the commands and details of the file that has been added to the site. If it encounters any errors these should be shown and you can work through them.
A succesful test should output something like this
and we can validate it's done its job by heading to the SharePoint site and seeing the file that has been added
Wonderful! Last thing to do is to publish our Runbook to confirm it's finalised. Go back to the tab with the Runbook, click on Edit PowerShell Runbook (or click the X in the top right) and then select Publish and confirm.
If you want to have this run on a regular basis you can add a schedule to the Runbook. To do that, click on the Runbook and from the new window click on Schedules from the left hand menu. Select Add a Schedule and follow the prompts to give it a name, set when you want the schedule to start, click Recurring and set the frequency you want.
Click Create once you're done and then OK from the menu it takes you back to.
You'll see it added and its status should be On
All done! A lot of the groundwork we did here in creating the App Registration, assigning its permissions and adding the modules to the Runbook are one time efforts. You can easily adjust the PowerShell script to fetch other reports from your tenant and have them added to the same SharePoint site. If you wanted to add them to a different site you'd need to go through the steps to add the App Registraion to the site and assign permissions.
Hope that was useful!
Mark
Comments