Home

PowerShell script to audit and export all content details of your Office Video Portal

Fabrice Romelard
MVP

As you probably know now, Office 365 Video Portal is an old product of Office 365, and the new solution to use for any Video need is now Office 365 Stream.

 

The question is clear for any tenant admin, or for any assessment request how to have a clear view of the current Video Portal usage in a tenant.

 

I wrote that PowerShell script to export in only one CSV the following details (you can import and manipulate later with Excel):

  • ChannelName
  • ChannelURL
  • ChannelStorageinMB
  • FileTotal
  • FileName
  • FileType
  • FileSizeMB
  • FileAbsoluteURL

You can easily use it or adapt it as you need to.

 

function Invoke-RestSPO
{
	Param(
	[Parameter(Mandatory=$True)]
	[String]$AdminPortalUrl,

	[Parameter(Mandatory=$True)]
	[String]$SPOUrl,

	[Parameter(Mandatory=$True)]
	[String]$UserName,

	[Parameter(Mandatory=$True)]
	[String]$Password
	)

	[string]$VideoListName = "Videos"
	
	Add-Type -Path ([System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SharePoint.Client").location)
	Add-Type -Path ([System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SharePoint.Client.runtime").location)

	$SecurePassword = ConvertTo-SecureString -string (Get-Content $Password)
	$creds = New-Object System.Management.Automation.PSCredential -ArgumentList $username, $SecurePassword
	#$creds = Get-Credential

	$SPOQueryUrl= $SPOUrl + "/_api/search/query?querytext=%27contentclass:sts_site WebTemplate:POINTPUBLISHINGTOPIC%27&SelectProperties=%27Sitename%27&rowlimit=5000"
	
    Write-Host "Performing the SPO Search Query to get all the Office 365 Video Channels..." -ForegroundColor Green
	$request = [System.Net.WebRequest]::Create($SPOQueryUrl)
	$request.Credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($creds.UserName,$creds.Password)
	$request.Headers.Add("X-FORMS_BASED_AUTH_ACCEPTED", "f")
	$request.Accept = "application/json;odata=verbose"
	#$request.Method=$Method
	$request.Method=[Microsoft.PowerShell.Commands.WebRequestMethod]::Get
	$response = $request.GetResponse()
	$requestStream = $response.GetResponseStream()
	$readStream = New-Object System.IO.StreamReader $requestStream
	$data=$readStream.ReadToEnd()
	
	$results = $data | ConvertFrom-Json
	$N4result=$results.d.query.PrimaryQueryResult.RelevantResults.Table.Rows.results.Cells.results
	$Channels = @()
		    foreach($r in $N4result){
			    If($r.Key -eq "SiteName")
			    {
			    $Channels +=$r.Value
			    }           
			}

    Write-Host "Collecting the Office 365 Video Channel Storage Details..." -ForegroundColor Green
	Connect-SPOService -Url $AdminPortalUrl -Credential $creds

	$data=@()
    Write-Host 
    Write-Host "Office 365 Video Channel Storage Details:" -ForegroundColor Green
    Write-Host "-----------------------------------------" -ForegroundColor Green
    Write-Host 
	foreach($chname in $Channels)
	{
		Write-Host "  ---------------------------------------------------------------------------  "
		Write-Host "Channel URL                  :",  $chname
		$site = Get-SPOSite -Identity $chname -Detailed
		
		$ctx=New-Object Microsoft.SharePoint.Client.ClientContext($site.URL)
		#$creds = New-Object System.Management.Automation.PSCredential -ArgumentList ($Username, $AdminPassword)
		$ctx.Credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($creds.UserName,$creds.Password)
		$ctx.RequestTimeout = 1000000 # milliseconds
		$spoweb = $ctx.Web
		$ctx.Load($spoweb)
		$ctx.ExecuteQuery()
		Write-Host
		#write-host "Channel Name                 :", $spoweb.Title
		#Write-Host "Site collection Url          :", $ctx.Url #-BackgroundColor White -ForegroundColor DarkGreen
		
		Write-Host "Channel Name                 :", $site.Title -foregroundcolor Green
		Write-Host "Channel URL                  :", $site.URL -foregroundcolor Yellow
		Write-Host "ChannelStorageinMB           :", $site.StorageUsageCurrent -foregroundcolor Yellow
		write-host "Total Files                  :", $ListItems.Count -foregroundcolor Yellow

		$MyVideoslist = $spoweb.Lists.GetByTitle($VideoListName)
		$ListItems = $MyVideoslist.GetItems([Microsoft.SharePoint.Client.CamlQuery]::CreateAllItemsQuery())
		$ctx.Load($ListItems)
		$ctx.ExecuteQuery()

		foreach($MyListItem in $ListItems)
		{
			$datum = New-Object -TypeName PSObject
			Write-Host "   > file:", $MyListItem["Title"], "- FileType:",  $MyListItem["File_x0020_Type"], "- Size (MB):", ([math]::round(($MyListItem["File_x0020_Size"]/(1024*1024)), 2)), "- FileURL:", $($MyListItem["FileDirRef"] +"/"+ $MyListItem["FileLeafRef"])
			
			$datum | Add-Member -MemberType NoteProperty -Name ChannelName -Value $site.Title
			$datum | Add-Member -MemberType NoteProperty -Name ChannelURL -Value $site.URL
			$datum | Add-Member -MemberType NoteProperty -Name ChannelStorageinMB -Value $site.StorageUsageCurrent
			$datum | Add-Member -MemberType NoteProperty -Name FileTotal -Value $ListItems.Count
			$datum | Add-Member -MemberType NoteProperty -Name FileName -Value $MyListItem["Title"]
			$datum | Add-Member -MemberType NoteProperty -Name FileType -Value $MyListItem["File_x0020_Type"]
			$datum | Add-Member -MemberType NoteProperty -Name FileSizeMB -Value ([math]::round(($MyListItem["File_x0020_Size"]/(1024*1024)), 2))
			$datum | Add-Member -MemberType NoteProperty -Name FileAbsoluteURL -Value  $($MyListItem["FileDirRef"] +"/"+ $MyListItem["FileLeafRef"])
			$data += $datum
		}
		
		#Write-Host "StorageQuotainGB             :", ($site.StorageQuota/1024) -foregroundcolor Green
		#Write-Host "StorageQuotaWarningLevelinGB :", ($site.StorageQuotaWarningLevel/1024) -foregroundcolor Green
		Write-Host ""
		Write-Host "  ---------------------------------------------------------------------------  "
    }

	$datestring = (get-date).ToString("yyyyMMdd-hhmm")
	$fileName = ("C:\VideoPortal\VideoPortalDetails_" + $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
		
}


[string]$username = "admin@yourtenant.onmicrosoft.com"
[string]$PwdTXTPath = "C:\SECUREDPWD\ExportedPWD-$($username).txt"

# Get needed information from end user
[string]$SPOAdminPortal = "https://yourtenant-admin.sharepoint.com"
[string]$SPOSiteURL = "https://yourtenant.sharepoint.com"

Invoke-RestSPO -AdminPortalUrl $SPOAdminPortal -SPOUrl $SPOSiteURL -UserName $username -Password $PwdTXTPath

 

The CSV can be used in Excel and a Pivot Table will give you that kind of usage:

 

 

Capture-ListOfFile.JPGFileType for a Channel

 

That will give you an idea about the task force to organize for your migration project or Integration project.

 

Fabrice Romelard

 

French version:

 

Some articles used to build it:

2 Replies
Highlighted

Dear all,

I updated the script adding export option to list the dates (creation and modification) and also get the viewcount.

 

Function Get-RestContents ($url, $cred) {
    $r = [System.Net.WebRequest]::Create($url);
    $r.Credentials = $cred
    $r.Accept = "application/json;odata=verbose"
    $r.Headers.Add("X-FORMS_BASED_AUTH_ACCEPTED", "f")

    $res = $r.GetResponse()
    $stream = New-Object System.IO.StreamReader $res.GetResponseStream()
    $results = $stream.ReadToEnd()
    $stream.Dispose()
    return ($results | ConvertFrom-Json).d
}

function Invoke-RestSPO
{
	Param(
	[Parameter(Mandatory=$True)] [String]$AdminPortalUrl, [Parameter(Mandatory=$True)] [String]$SPOUrl, [Parameter(Mandatory=$True)] [String]$UserName,	[Parameter(Mandatory=$True)] [String]$Password,	[Parameter(Mandatory=$True)] [String]$TenantName
	)

	[string]$VideoListName = "Videos"
	[string]$videoPortalUrl = "https://$($TenantName).sharepoint.com/portals/hub/_api/VideoService/"
	
	Add-Type -Path ([System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SharePoint.Client").location)
	Add-Type -Path ([System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SharePoint.Client.runtime").location)

	$SecurePassword = ConvertTo-SecureString -string (Get-Content $Password)
	$creds = New-Object System.Management.Automation.PSCredential -ArgumentList $username, $SecurePassword
	#$creds = Get-Credential
	$StandardUsercreds = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($creds.UserName, $creds.Password);

	$SPOQueryUrl= $SPOUrl + "/_api/search/query?querytext=%27contentclass:sts_site WebTemplate:POINTPUBLISHINGTOPIC%27&SelectProperties=%27Sitename%27&rowlimit=5000"
	
    Write-Host "Performing the SPO Search Query to get all the Office 365 Video Channels..." -ForegroundColor Green
	Write-Host "APISearch:",  $SPOQueryUrl -ForegroundColor Green

	$request = [System.Net.WebRequest]::Create($SPOQueryUrl)
	$request.Credentials = $StandardUsercreds
	$request.Headers.Add("X-FORMS_BASED_AUTH_ACCEPTED", "f")
	$request.Accept = "application/json;odata=verbose"
	#$request.Method=$Method
	$request.Method=[Microsoft.PowerShell.Commands.WebRequestMethod]::Get
	$response = $request.GetResponse()
	$requestStream = $response.GetResponseStream()
	$readStream = New-Object System.IO.StreamReader $requestStream
	$data=$readStream.ReadToEnd()
	
	$results = $data | ConvertFrom-Json
	$N4result=$results.d.query.PrimaryQueryResult.RelevantResults.Table.Rows.results.Cells.results
	$Channels = @()
		    foreach($r in $N4result){
			    If($r.Key -eq "SiteName")
			    {
			    $Channels +=$r.Value
			    }           
			}

    Write-Host "Collecting the Office 365 Video Channel Storage Details..." -ForegroundColor Green
	Connect-SPOService -Url $AdminPortalUrl -Credential $creds

	$data=@()
    Write-Host 
    Write-Host "Office 365 Video Channel Storage Details:" -ForegroundColor Green
    Write-Host "-----------------------------------------" -ForegroundColor Green
    Write-Host 
	foreach($chname in $Channels)
	{
		Write-Host "  ---------------------------------------------------------------------------  "
		Write-Host "Channel URL                  :",  $chname
		$site = Get-SPOSite -Identity $chname -Detailed
		
		$ctx=New-Object Microsoft.SharePoint.Client.ClientContext($site.URL)
		$ctx.Credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($creds.UserName,$creds.Password)
		$ctx.RequestTimeout = 1000000 # milliseconds
		$sposite = $ctx.Site
		$spoweb = $ctx.Web
		$ctx.Load($spoweb)
		$ctx.Load($sposite)
		$ctx.ExecuteQuery()
		Write-Host
		#write-host "Channel Name                 :", $spoweb.Title
		#Write-Host "Site collection Url          :", $ctx.Url #-BackgroundColor White -ForegroundColor DarkGreen
		
		Write-Host "SPOSite & Channel ID         :", $sposite.id -foregroundcolor Green
#		Write-Host "SPOWeb ID                    :", $spoweb.id -foregroundcolor Green
		Write-Host "Channel Name                 :", $site.Title -foregroundcolor Green
		Write-Host "Channel URL                  :", $site.URL -foregroundcolor Yellow
		Write-Host "ChannelStorageinMB           :", $site.StorageUsageCurrent -foregroundcolor Yellow
		write-host "Total Files                  :", $ListItems.Count -foregroundcolor Yellow

		$MyVideoslist = $spoweb.Lists.GetByTitle($VideoListName)
		$ListItems = $MyVideoslist.GetItems([Microsoft.SharePoint.Client.CamlQuery]::CreateAllItemsQuery())
		$ctx.Load($ListItems)
		$ctx.ExecuteQuery()

		foreach($MyListItem in $ListItems)
		{
			[string]$ChannelId = $sposite.id
			[string]$videoId = $MyListItem["UniqueId"]
			$datum = New-Object -TypeName PSObject
			Write-Host "   > file:", $MyListItem["Title"], "- FileType:",  $MyListItem["File_x0020_Type"], "- Size (MB):", ([math]::round(($MyListItem["File_x0020_Size"]/(1024*1024)), 2))
			Write-Host "   > FileURL:", $($MyListItem["FileDirRef"] +"/"+ $MyListItem["FileLeafRef"])
			Write-Host "   > fileID:", $MyListItem["UniqueId"], "- Creation Date:", $MyListItem["Created"] , "- Modification Date:", $MyListItem["Modified"]
			Write-Host "   > FileCreator:", $MyListItem["Created_x0020_By"], "- FileModifier:", $MyListItem["Modified_x0020_By"]

			#Get the ViewCount need to call Video RESTAPI similar to:
			#https://yourtenant.sharepoint.com/portals/hub/_api/VideoService/Channels(guid'63216c90-3f31-4cf0-8abe-c01855a2a794')/search/query(guid'3bc6342d-5e77-42cf-bc25-63117efc5c6d')?$Select=ViewCount
			try
			{
				$viewCounturl = $videoPortalUrl +"Channels(guid'"+ $ChannelId +"')/search/query(guid'"+ $videoId +"')?`$Select=ViewCount"
				$viewCountSource = Get-RestContents $viewCounturl $StandardUsercreds
				$FileviewCount = $viewCountSource.results | Select-Object ViewCount
				Write-Host "  =====>>>> URL:", $viewCounturl -ForegroundColor Yellow
				Write-Host "  =====>>>> Results:", $viewCountSource.results -ForegroundColor Yellow
				Write-Host "  =====>>>> FileviewCount:", $FileviewCount.ViewCount -ForegroundColor Yellow
			}
			catch
			{
				Write-Host "  =====>>>> Video API URL not working:", $viewCounturl -ForegroundColor Red
				Write-Host "            ERROR MESSAGE:", $_.Exception.Message -ForegroundColor Red
				$FileviewCount = -1
			}
			Write-Host "  >>> FileviewCount:", $FileviewCount -foregroundcolor Yellow
			$datum | Add-Member -MemberType NoteProperty -Name ChannelID -Value $sposite.id
			$datum | Add-Member -MemberType NoteProperty -Name ChannelName -Value $site.Title
			$datum | Add-Member -MemberType NoteProperty -Name ChannelURL -Value $site.URL
			$datum | Add-Member -MemberType NoteProperty -Name ChannelStorageinMB -Value $site.StorageUsageCurrent
			$datum | Add-Member -MemberType NoteProperty -Name FileTotal -Value $ListItems.Count
			$datum | Add-Member -MemberType NoteProperty -Name FileID -Value $MyListItem["UniqueId"]
			$datum | Add-Member -MemberType NoteProperty -Name FileName -Value $MyListItem["Title"]
			$datum | Add-Member -MemberType NoteProperty -Name FileviewCount -Value $FileviewCount.ViewCount
			$datum | Add-Member -MemberType NoteProperty -Name FileCreator -Value $MyListItem["Created_x0020_By"]
			$datum | Add-Member -MemberType NoteProperty -Name FileModifier -Value $MyListItem["Modified_x0020_By"]
			$datum | Add-Member -MemberType NoteProperty -Name CreationDate -Value $MyListItem["Created"]
			$datum | Add-Member -MemberType NoteProperty -Name ModificationDate -Value $MyListItem["Modified"]
			$datum | Add-Member -MemberType NoteProperty -Name FileType -Value $MyListItem["File_x0020_Type"]
			$datum | Add-Member -MemberType NoteProperty -Name FileSizeMB -Value ([math]::round(($MyListItem["File_x0020_Size"]/(1024*1024)), 2))
			$datum | Add-Member -MemberType NoteProperty -Name FileAbsoluteURL -Value  $($MyListItem["FileDirRef"] +"/"+ $MyListItem["FileLeafRef"])
			$data += $datum
		}
		#Write-Host "StorageQuotainGB             :", ($site.StorageQuota/1024) -foregroundcolor Green
		#Write-Host "StorageQuotaWarningLevelinGB :", ($site.StorageQuotaWarningLevel/1024) -foregroundcolor Green
		Write-Host ""
		Write-Host "  ---------------------------------------------------------------------------  "
    }

	$datestring = (get-date).ToString("yyyyMMdd-hhmm")
	$fileName = ("C:\VideoPortal\VideoPortalDetails_" + $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
}

[string]$username = "Admin@yourtenant.onmicrosoft.com"
[string]$PwdTXTPath = "C:\SECUREDPWD\ExportedPWD-$($username).txt"

# Get needed information from end user
[string]$MyTenantName = "yourtenant"
[string]$SPOAdminPortal =  "https://"+ $MyTenantName +"-admin.sharepoint.com"
[string]$SPOSiteURL = "https://"+ $MyTenantName +".sharepoint.com"

Invoke-RestSPO -AdminPortalUrl $SPOAdminPortal -SPOUrl $SPOSiteURL -UserName $username -Password $PwdTXTPath -TenantName $MyTenantName

You can use that new version as you want.

Fabrice Romelard

 

Source used: