PowerShell script to audit and export Channel content details of your Office 365 Stream

Steel Contributor

Following the previous script developed to audit Office 365 Video Portal:

I worked to retrieve something equivalent with Office 365 Stream.

 

There is not yet public API available into Office 365 Stream, MS communicated that API delivery Q2 or Q3 2019.

But O365 Stream portal (https://web.microsoftstream.com) is using internal API used into Stream internal pages like the following examples:

 

List of O365 Groups:

List of videos for one O365 Group:

List of Channels

Those API, giving results in JSON format, can be used only if you connect O365 Stream first to start your web session, and because that solution is only to wait official Public API, I decided to stay in that Web Browser usage mode.

 

The process to respect step by step (steps are placed into the PowerShell script):

  1. Open O365 Stream Portal into your Web Browser (with your admin credentials)
    - https://web.microsoftstream.com/?NoSignUpCheck=1
  2. With the same Web browser (other tab or other windows), open the following links:
    1. FROM 0 to 100: https://euno-1.api.microsoftstream.com/api/channels?$top=100&$orderby=metrics%2Fvideos%20desc&$expan...
    2. FROM 100 to 200: https://euno-1.api.microsoftstream.com/api/channels?$top=100&$orderby=metrics%2Fvideos%20desc&$expan...
    3. ... : Stop when you have no value into the page
  3. Save each of that JSON Results into the work folder (C:\PSScript\ChannelsJSON\)
    1. channels100.json
    2. channels200.json
  4. PowerShell script will generate an Aggregated CSV file containing O365 Stream Channel details

 

Before execute the script, you have to set the parameters with the correct value (depending on total channels you have into your O365 Stream and where you place the PowerShell script):

  • [int]$Loopnumber = 2    # << 2 indicates between 100 and 200 channels
  • [string]$PowerShellScriptFolder = "C:\PSScript"

 

[string]$PowerShellScriptFolder = "C:\PSScript"
[string]$streamJSONfolder = Join-Path -Path $PowerShellScriptFolder -ChildPath "ChannelsJSON"
Remove-Item -path $streamJSONfolder\* -include *.json -Force -Recurse

[string]$StreamPortal = "https://web.microsoftstream.com/?NoSignUpCheck=1"
[string]$StreamPortalChannelRoot = "https://web.microsoftstream.com/channel/"
[string]$StreamAPIChannels100 = "https://euno-1.api.microsoftstream.com/api/channels?NoSignUpCheck=1&`$top=100&`$orderby=metrics%2Fvideos%20desc&`$expand=creator,group&api-version=1.3-private&`$skip="

[int]$Loopnumber = 2

Write-host " -----------------------------------------" -ForegroundColor Green
Write-Host "  =====>>>> PortalURL:", $StreamPortal
Start-Process -FilePath 'iexplore.exe' -ArgumentList $StreamPortal
Write-Host "      Enter your credentials to load O365 Stream portal" -ForegroundColor Magenta
Read-Host -Prompt "Press Enter to continue ...."

for($i=0;$i -lt $Loopnumber; $i++)
{
	Write-host " -----------------------------------------" -ForegroundColor Green
	$StreamAPIChannels100 = $StreamAPIChannels100 + $($i*100)
	Write-Host "  =====>>>> First 100 channels (from", $($i*100), "to", $(($i+1)*100), "):", $StreamAPIChannels100
	Start-Process -FilePath 'iexplore.exe' -ArgumentList $StreamAPIChannels100
	Write-Host "      Save the 100 channels (from", $($i*100), "to", $(($i+1)*100), ") into the folder $streamJSONfolder respecting the name channels100.json" -ForegroundColor Magenta
	Read-Host -Prompt "Press Enter to continue ...."
}

Write-host " -----------------------------------------" -ForegroundColor Green
$ChannelJSONFiles = Get-ChildItem -Path $streamJSONfolder -Recurse -Include *.json
[int]$channelscounter = 0
$ChanneljsonAggregateddata=@()
$data=@()

foreach($channelsjson in $ChannelJSONFiles)
{
	Write-host " -----------------------------------------" -ForegroundColor Green
	Write-Host "     =====>>>> JSON File:", $channelsjson, "- Path:", $channelsjson.FullName -ForegroundColor Yellow
	$Channeljsondata = Get-Content -Raw -Path $channelsjson.FullName | ConvertFrom-Json
	$ChanneljsonAggregateddata += $Channeljsondata
	Write-host " -----------------------------------------" -ForegroundColor Green
	#Write-Host "     =====>>>> Channel JSON Raw data:", $Channeljsondata -ForegroundColor green
	#Read-Host -Prompt "Press Enter to continue ...."
}

foreach($myChannel in $ChanneljsonAggregateddata.value)
{
	if($myChannel.metrics.videos -gt -1)
	{
		$channelscounter += 1
		$datum = New-Object -TypeName PSObject
		Write-host "        -----------------------------------------" -ForegroundColor Green
		Write-Host "        =====>>>> Channel (N°", $channelscounter ,") ID:", $myChannel.id, "- isDefault Channel:", $myChannel.isDefault -ForegroundColor green
		Write-Host "        ---- Channel Name:", $myChannel.name,"- Channel Portal URL:", $($StreamPortalChannelRoot + $myChannel.id)
		Write-Host "        ---- Channel CreationDate:", $myChannel.created,"- Channel ModificationDate:", $myChannel.modified
		Write-Host "        =====>>>> Channel Metrics Followers:", $myChannel.metrics.follows, "- Video Total:", $myChannel.metrics.videos -ForegroundColor Magenta
		Write-Host "        =====>>>> O365 Channel Creator Name: ", $myChannel.creator.name , " - Email:", $myChannel.creator.mail -ForegroundColor Magenta

		Write-Host " O365 GROUP Name:", $myChannel.group.name, "- ID:", $myChannel.group.id -ForegroundColor Yellow
		Write-Host "        =====>>>> O365 Group ID: ", $myChannel.group.id , " - Group Email:", $myChannel.group.aadGroup.mail -ForegroundColor Magenta
		Write-Host "        =====>>>> O365 Group Metrics Channel total:", $myChannel.group.metrics.channels, "- Video Total:", $myChannel.group.metrics.videos -ForegroundColor Magenta

		$datum | Add-Member -MemberType NoteProperty -Name ChannelID -Value $myChannel.id
		$datum | Add-Member -MemberType NoteProperty -Name ChannelName -Value $myChannel.name
		$datum | Add-Member -MemberType NoteProperty -Name ChannelURL -Value $($StreamPortalChannelRoot + $myChannel.id)
		$datum | Add-Member -MemberType NoteProperty -Name ChannelDefault -Value $myChannel.isDefault
		$datum | Add-Member -MemberType NoteProperty -Name ChannelFollowers -Value $myChannel.metrics.follows
		$datum | Add-Member -MemberType NoteProperty -Name ChannelVideos -Value $myChannel.metrics.videos
		$datum | Add-Member -MemberType NoteProperty -Name ChannelCreatorName -Value $myChannel.creator.name
		$datum | Add-Member -MemberType NoteProperty -Name ChannelCreatorEmail -Value $myChannel.creator.mail
		$datum | Add-Member -MemberType NoteProperty -Name ChannelCreationDate -Value $myChannel.created
		$datum | Add-Member -MemberType NoteProperty -Name ChannelModificationDate -Value $myChannel.modified

		$datum | Add-Member -MemberType NoteProperty -Name O365GroupId -Value $myChannel.group.id
		$datum | Add-Member -MemberType NoteProperty -Name O365GroupName -Value $myChannel.group.name
		$datum | Add-Member -MemberType NoteProperty -Name O365GroupEmail -Value $myChannel.group.aadGroup.mail
		$datum | Add-Member -MemberType NoteProperty -Name O365GroupTotalChannels -Value $myChannel.group.metrics.channels
		$datum | Add-Member -MemberType NoteProperty -Name O365GroupTotalVideos -Value $myChannel.group.metrics.videos

		$data += $datum
	}
}

$datestring = (get-date).ToString("yyyyMMdd-hhmm")
$fileName = ($PowerShellScriptFolder + "\O365StreamDetails_" + $datestring + ".csv")
	
Write-host " -----------------------------------------" -ForegroundColor Green
Write-Host (" >>> writing to file {0}" -f $fileName) -ForegroundColor Green
$data | Export-csv $fileName -NoTypeInformation
Write-host " -----------------------------------------" -ForegroundColor Green

You can use that solution as you want and modify it depending of your case and request.

 

Fabrice Romelard

 

French version:

 

Update March 22 2019:

Stream root URL used for Internal API can be different based on tenant region. I collected some URLs as much as I can (if you have anyone new, please to add it in comment to update that list)

 

22 Replies

@Fabrice Romelard  I tried to follow up these steps, but it returns 401 when I run the API query. For example, I signed in Stream with global admin and then access https://euno-1.api.microsoftstream.com/api/channels?$top=100&$orderby=metrics%2Fvideos%20desc&$expan..., it return 401. May I know if I missed something? 

@EbenLiu1390 Error 401 is related to permission question.

Are you sure to be Stream Admin ?

 

I will retry it from my tenant next week to confirm if it's still possible to use that solution.

Is anyone else has the same error loading similar link ?

 

Fab

@Fabrice Romelard Thanks for your reply. I can run it now with 

https://aase-1.api.microsoftstream.com. So I think the internal API endpoint is different for Stream tenant in a different region. 

@EbenLiu1390 

 

Thanks a lot for your update, in that case, if we want to use that solution, we only have to use the internal API Root URL per tenant region.

 

By the way, Microsoft communicated their current activity on that public API.

 

I will try to find the list of Tenant Root URLs for Stream, if you have something related feel free to post it in comment

 

Fab

For now, I found only global URL associated to Office 365:
 - https://docs.microsoft.com/fr-fr/office365/enterprise/urls-and-ip-address-ranges

 

No specification per tenant region.

 

Fab

@Fabrice Romelard admin can use F12 to capture the user report request (Stream > Admin Settings > Manage User Data to run a report) to get their own internal API endpoint. 

@EbenLiu1390 thanks for your message, this is what I used to get the URL and format to respect.

Fab

@Fabrice Romelard Is there a way to export all Videos from the stream api url

Hi,

 

Also here 401 while clicking on the 2nd link, I am both stream admin and MS365 global admin

 

 

@Filippo_Morosini Would it be possible for you to update this blog post for 2020 accuracy?  I just want to make sure I'm referencing the most current information.

I was mainly referencing this statement in the post "There is not yet public API available into Office 365 Stream, MS communicated that API delivery Q2 or Q3 2019." I think it's now CA Q4 2020 for public APIs

Hi,

@Mel_Zak I'm not the author of this post, I think your question was for @Fabrice_R :)

@Fabrice Romelard, US West Region is:  https://uswe-1.api.microsoftstream.com/api/

 

Wish I could get info on whether or not the video is shared and who it's shared with.  Including the Entire Company or not.  Thanks for your good work!  Also, there was no Make Directory commands setup for the folder structure.  I had to manually create these: stream-analysis\VideosJSON.

 

I just processed 27 pages worth.

@Fabrice RomelardThanks for your post. Unfortunately, I'm getting a blank excel file. No errors, file is getting generated, but no entries in excel, can someone help me on this.

How the JSON contents are saved? I couldn't find the code for saving the JSON contents here.

Well, i found this one belonging to US south region - 

https://usso-1.api.microsoftstream.com/

attached is the screenshot.

 

@Fabrice Romelard ,take a peek at this one https://bit.ly/PnPScriptSamplesMSStream,

It's wrapped up in a nice way,  asking for creds and getting the tokens needed to export a csv with a report. Ill be adding more stuff soon . Hope it helps.

@Fabrice Romelard 

 

I've taken the liberty of rewriting this so most of the work is done programmatically, you just need to update a cookies file that looks like this:

Name,Domain,Value
UserSession_Api,.api.microsoftstream.com,[Add Cookie Value]
Signature_Api,.api.microsoftstream.com,[Add Cookie Value]
Signature,aase-1.api.microsoftstream.com,[Add Cookie Value]
Authorization_Api,.api.microsoftstream.com,[Add Cookie Value]
Authorization,aase-1.api.microsoftstream.com,[Add Cookie Value]

 

Then you can run this script:

## Before you start go to this URL and login https://web.microsoftstream.com/?NoSignUpCheck=1
## Then go to this URL https://aase-1.api.microsoftstream.com/api/videos?NoSignUpCheck=1&$top=100&$orderby=publishedDate%20desc&$expand=creator,events&$filter=published%20and%20(state%20eq%20%27Completed%27%20or%20contentSource%20eq%20%27livestream%27)&adminmode=true&api-version=1.4-private&$skip=0
## Open the developer tools and go to Application, copy the Cookie values into cookies.csv
$cookiesCSVFilePath = ".\cookies.csv"
$outputCSVFilePath = ".\output.csv"
[string]$StreamPortalVideoViewRoot = "https://web.microsoftstream.com/video/"

$WebSession = New-Object Microsoft.PowerShell.Commands.WebRequestSession
$cookies = Import-CSV $cookiesCSVFilePath

foreach ($cookie in $cookies) {
    $newCookie = New-Object System.Net.Cookie
    $newCookie.Name = $cookie.Name
    $newCookie.Value = $cookie.Value
    $newCookie.Domain = $cookie.Domain
    $WebSession.Cookies.Add($newCookie)
}

$headerParams = @{
    "ContentType" = "application/json"
}

$i = 0
$continue = $true
while ($continue -eq $true) {
    $i++
    $skip = ($i - 1) * 100
    Write-Output "Getting videos $skip - $($skip + 100)"
    $queryOptions = @{
        Method     = "GET"
        URI        = "https://aase-1.api.microsoftstream.com/api/videos?NoSignUpCheck=1&`$top=100&`$orderby=publishedDate%20desc&`$expand=creator,events&`$filter=published%20and%20(state%20eq%20%27Completed%27%20or%20contentSource%20eq%20%27livestream%27)&adminmode=true&api-version=1.4-private&`$skip=$skip"
        Headers    = $headerParams
        WebSession = $WebSession
    } 

    try {
        $videoResult = Invoke-RestMethod @queryOptions -ErrorAction Stop
    }
    catch {
        Write-Output $_.exception.message
    }

    if ($videoResult.value.length -eq 0) {
        $continue = $false
    }
    else {
        foreach ($video in $videoResult.value) {
            $videoInfo = [PSCustomObject]@{
                VideoID               = $video.id
                VideoName             = $video.name
                VideoURL              = $StreamPortalVideoViewRoot + $video.id
                VideoCreatorName      = $video.creator.name
                VideoCreatorEmail     = $video.creator.mail
                VideoCreationDate     = $video.created
                VideoModificationDate = $video.modified
                VideoLikes            = $video.metrics.likes
                VideoViews            = $video.metrics.views
                VideoComments         = $video.metrics.comments
                Videodescription      = $video.description
                VideoDuration         = $video.media.duration
                VideoHeight           = $video.media.height
                VideoWidth            = $video.media.width
                VideoIsAudioOnly      = $video.media.isAudioOnly
                VideoContentType      = $video.contentType
            }
        
            [array]$videoList += $videoInfo
        
        }
    }
}

$videoList | Export-CSV -NoTypeInformation $outputCSVFilePath