Ever since Office 365 Groups were launched with support for connectors, the problem of reporting just what data sources a given Group or Team is connecting to has been lurking in the minds of IT admins and security folks. Microsoft hasn’t made it easy on us to obtain and report on such data, but thanks to the Graph API /installedApps and /tabs endpoints we can create programmatic solutions for this task. So here’s a sample script I prepared as a “proof of concept”.
As I mentioned above, the script will run some queries against the Microsoft Graph, and in order to do so we need a valid token. Tokens in turn are obtained for a specific application, so as a prerequisite for running the script you will need to input the details of an application registered in your Azure AD tenant that has the necessary permissions. In this case, I’ve opted out to use the application permissions model, as we want to include all Teams and potentially run the script as a background task. Permission-wise, the Group.Read.All scope should be sufficient.
Once you have entered the application ID, client secret and tenant ID, you should be able to obtain the needed token. Of course you can just replace this part of the script with your preferred method to get a token. Just store it in the $token variable and the script should run fine.
The first query the script will execute against the Graph is to fetch a list of all Team-enabled Groups in the tenant. For that, we will use the /beta endpoint and the “resourceProvisioningOptions/Any(x:x eq ‘Team’)” filter. And when I say all, I don’t actually mean all, as the script doesn’t handle pagination and stuff (remember, it’s a proof of concept). Next, we will go over the list of Teams, and for each we will query the /installedApps endpoint. This will return any and all applications added to a given Team, and the result might surprise you – there are over 20 such integrations for each Team, even a newly created one!
Some details will be gathered for each application and presented in the output. Next, the script will iterate over each channel in the Team, and enumerate the Tabs configured, if any. For each Tab, an information about the corresponding application will be returned. And yes, this will include Private channels as well. As the last step, the output will be exported to two CSV files, one for applications and another one for tabs. You can easily filter those out for a specific Team, channel or app as needed. Sample below:
If you plan to use this script in a production environment, make sure to add some error handling, add support for pagination, handle token expiration, throttling and so on. None of this is currently covered, so don’t expect miracles. Lastly, here’s the download link: https://github.com/michevnew/PowerShell/blob/master/Report_Teams_Apps.ps1
UPDATE: Since quite few folks have asked about the method used to store the client secret, I guess I should add some details. The idea is to avoid storing the client secret in plain text – just like any other password/credential it’s a very, very bad idea to put this directly in your script file. I know most my “proof of concept” script actually do that, but those serve as quick examples, not something you should be using in production!
Anyway, as far as securing credentials goes, there are multiple different methods you can use, including leveraging Azure KeyVault and similar. The method I use in this sample script is very similar to the way PowerShell handles credentials when you invoke Get-Credential. Let’s take a look at an example:
$creds = Get-Credential vasil $creds UserName Password -------- -------- vasil System.Security.SecureString
Now, if you try to take a peek at the password, by invoking $creds.password, you will get the same System.Security.SecureString output. You can actually use the legacy $creds.GetNetworkCredential().password method to get it’s value, but that’s not the point here. Instead, let’s see what we can do with the SecureString object. For starters, we can “convert it” by using ConvertFrom-SecureString:
$creds.Password | ConvertFrom-SecureString 01000000d08c9ddf0115d1118c7a00c04fc297eb010000002f25de35cf93c34787b597c17e884fed0000000002000000000010660000000100002000000029e31bb9ca5d426e3ef95f1405c3be553281583ffab04a97abe32a3c7d5ed2b8000000000e800000000200002000000056864ba562e9db3ff906aaed8e6c3f17911d0855d7dd5703612ad51915da8fb320000000f6d87b761c3bf0b8ed1ea9abc864ba8135f9fbb9b2f0cb5cd2853e469441e224400000009890648fedde74a1983173aeceb39b1cf4d9bfef2628fd1dadc5c9805d49738258ae5b3ccc138a9053237f17df6d06c8ece7777a0af5c68495c3effbc0cab16f
This represents the DPAPI-encrypted value, which only the current user can decrypt. Thus you can safely store this value in a text file and rest assured that if the file is copied to another device, it will not expose the password. So this is effectively what we do in the script above, take the value of the client secret for our Azure AD app, pass it to the Get-Credentials cmdlet, then store it in the ReportingAPIsecret.txt file in encrypted form. Here’s the code stitched together:
$creds = Get-Credential xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx $creds.Password | ConvertFrom-SecureString | Set-Content ReportingApiSecret.txt
Hi Vasil
can you update the script to provide more results like i have 100K users in my prod
Hi Vasail
This is really nice one, which am expeecting since long, howver am getting the below error, but i got the output file
nvoke-WebRequest : The remote server returned an error: (403) Forbidden.
At line:75 char:21
+ … mChannels = Invoke-WebRequest -Headers $AuthHeader1 -Uri “https://gra …
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : InvalidOperation: (System.Net.HttpWebRequest:HttpWebRequest) [Invoke-WebRequest], WebException
+ FullyQualifiedErrorId : WebCmdletWebResponseException,Microsoft.PowerShell.Commands.InvokeWebRequestCommand
ConvertFrom-Json : Cannot bind argument to parameter ‘InputObject’ because it is null.
At line:76 char:46
+ $TeamChannels = ($TeamChannels.Content | ConvertFrom-Json).value
+ ~~~~~~~~~~~~~~~~
+ CategoryInfo : InvalidData: (:) [ConvertFrom-Json], ParameterBindingValidationException
+ FullyQualifiedErrorId : ParameterArgumentValidationErrorNullNotAllowed,Microsoft.PowerShell.Commands.ConvertFromJsonCommand
Check your token/permissions.
at the line number 75 below, am getting error, do we need to give permisison in AAD for channel level? . after ran all the errors, its giveing output fill with correct information. Can i ignire the error
$TeamChannels = Invoke-WebRequest -Headers $AuthHeader1 -Uri “https://graph.microsoft.com/beta/Teams/$($Team.id)/channels” -ErrorAction Stop
Error Details
Invoke-WebRequest : The remote server returned an error: (403) Forbidden.
At C:\itsme\PS1\Teams App Report with API\POC_WiTH_API.ps1:75 char:21
+ … mChannels = Invoke-WebRequest -Headers $AuthHeader1 -Uri “https://gra …
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : InvalidOperation: (System.Net.HttpWebRequest:HttpWebRequest) [Invoke-WebRequest], WebException
+ FullyQualifiedErrorId : WebCmdletWebResponseException,Microsoft.PowerShell.Commands.InvokeWebRequestCommand
ConvertFrom-Json : Cannot bind argument to parameter ‘InputObject’ because it is null.
At C:\itsme\PS1\Teams App Report with API\POC_WiTH_API.ps1:76 char:46
+ $TeamChannels = ($TeamChannels.Content | ConvertFrom-Json).value
+ ~~~~~~~~~~~~~~~~
+ CategoryInfo : InvalidData: (:) [ConvertFrom-Json], ParameterBindingValidationException
+ FullyQualifiedErrorId : ParameterArgumentValidationErrorNullNotAllowed,Microsoft.PowerShell.Commands.ConvertFromJsonCommand
Oh, it might be because of Shared channels and such… I need to update it to handle all the new stuff. As long as you are getting the output you expect, you can ignore the errors 🙂
Hi Vasil,
Thanks for the incredible article and script. Is there any way to identify if the queried Apps are Allowed or Blocked in MS Teams?
Thanks,
Hi, in what format ReportingAPIsecret.txt file should be ?
The example I’m using is storing the client secret into the file as SecureString, but you can use any other method you prefer.
Pingback: How to Fetch Large Amounts of Teams Data Using Graph API calls - Office 365 for IT Pros
Thanks for sharing this, has anyone tried to export more than “&$top=999”
Ok cool now it’s working fine thanks so much for your help 😉
Hi Vasil ,
i execute the script works fine thanks really very helpful but my results is only 100 i have 421 groups i know you update the script to handle more then 100 can you provide me the link exactly thanks again
The link above points to the updated script.
Exactly i download it but it still give me only 100 Group thanks for help
Erm, it seems to be missing a simple “&$top=999” at the end of the query string.
Hi Vasil , can you updated directly in the powershell ? thanks again for help 🙂
Script works great, thank you so much. One Question: How can I get to report on more than 100 Teams?
You will have to add pagination, see the previous comments above.
Thank Vasil, I tried to add that code to the script, but it seems to be failing.
At P:\ListAllApps.ps1:37 char:13
+ if($result.’@odata.nextLink’)
+ ~~~~~~~~~~~~~~~~~~~~
Unexpected token ‘€™@odata.nextLink’’ in expression or statement.
At P:\ListAllApps.ps1:37 char:13
+ if($result.’@odata.nextLink’)
+ ~~~~~~~~~~~~~~~~~~~~
Missing closing ‘)’ after expression in ‘if’ statement.
At P:\ListAllApps.ps1:26 char:13
+ while ($url){
+ ~
Missing closing ‘}’ in statement block or type definition.
At P:\ListAllApps.ps1:37 char:33
+ if($result.’@odata.nextLink’)
+ ~
Unexpected token ‘)’ in expression or statement.
At P:\ListAllApps.ps1:39 char:17
+ $url = $result.’@odata.nextLink’
+ ~~~~~~~~~~~~~~~~~~~~
Unexpected token ‘€™@odata.nextLink’’ in expression or statement.
At P:\ListAllApps.ps1:45 char:1
+ }
+ ~
Unexpected token ‘}’ in expression or statement.
+ CategoryInfo : ParserError: (:) [], ParseException
+ FullyQualifiedErrorId : UnexpectedToken
Do you know where I would add it within your script?
I’ve updated the script to handle larger set of Teams.
Thanks Vasil.
Very nice script. Vasil. Thanks for sharing! FYI: I needed to add -UseBasicParsing for tne web-requests.
To be able to handle pagination and retrieve all teams you could add a check for the odata.nextLink where the teams are retrieved:
$url = “https://graph.microsoft.com/beta/groups?`$filter=resourceProvisioningOptions/Any(x:x eq ‘Team’)”
$Teams = @()
while ($url){
Write-Output “Fetching teams..”
$webrequest = Invoke-WebRequest -Headers $AuthHeader1 -Uri $url -UseBasicParsing -ErrorAction Stop
$result = $webrequest.Content | ConvertFrom-Json
if($result.value)
{
$Teams += $result.value
}
if($result.’@odata.nextLink’)
{
$url = $result.’@odata.nextLink’
}
else
{
$url = $null
}
}
Thanks Sondre. As with most of my other stuff, it’s a basic proof of concept script, not something designed to handle every scenario 🙂
This script is fantastic, thank you so much!
Hello,
we’ve been trying this for the longest time.
I get this error when I use your script-
Unable to obtain access token, aborting…
This could be several reasons, I am also new to Graph. I got my application ID from-
https://graph.microsoft.com/v1.0/appCatalogs/teamsApps?$filter=distributionMethod eq ‘organization’
Let me know in case I missed something.
Thanks for help
In order to obtain a token, you need an Azure AD application, not Teams one. Read here: https://docs.microsoft.com/en-us/graph/auth-v2-service