cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Show  only  | Search instead for 
Did you mean: 

PowerShell script to export SharePoint Usage in CSV format used to Audit an Office 365 Tenant

Fabrice Romelard
Steel Contributor

‎Feb 27 2019 03:08 AM - edited ‎Feb 27 2019 04:59 AM

‎Feb 27 2019 03:08 AM - edited ‎Feb 27 2019 04:59 AM

PowerShell script to export SharePoint Usage in CSV format used to Audit an Office 365 Tenant

As I published the Exchange Audit script last time:

The question is similar for SharePoint part of Office 365 tenant.

So I wrote that script to export in CSV format a figure about SharePoint usage into an Office 365 Tenant.

It will loop by Site collection and, depending of the permission, subsites to extract size, volume, customization (for the old SP versions).

 

[boolean]$DebugGlobalMode = $True #$False
[string]$username = "Admin@yourtenant.onmicrosoft.com"

[string]$PwdTXTPath = "C:\SECUREDPWD\ExportedPWD-$($username).txt"
[string]$CSVFolderReport = "C:\SHAREPOINT\Reports\"
[string]$AdminTenantURL = "https://YourTenant-admin.sharepoint.com"

function Load-DLLandAssemblies
{
	[string]$defaultDLLPath = ""

	# Load assemblies to PowerShell session 

	$defaultDLLPath = "C:\Program Files\SharePoint Online Management Shell\Microsoft.Online.SharePoint.PowerShell\Microsoft.SharePoint.Client.dll"
	[System.Reflection.Assembly]::LoadFile($defaultDLLPath)

	$defaultDLLPath = "C:\Program Files\SharePoint Online Management Shell\Microsoft.Online.SharePoint.PowerShell\Microsoft.SharePoint.Client.Runtime.dll"
	[System.Reflection.Assembly]::LoadFile($defaultDLLPath)

	$defaultDLLPath = "C:\Program Files\SharePoint Online Management Shell\Microsoft.Online.SharePoint.PowerShell\Microsoft.Online.SharePoint.Client.Tenant.dll"
	[System.Reflection.Assembly]::LoadFile($defaultDLLPath)
}

function Get-SPOWebs(){
param(
	$Url = $(throw "Please provide a Site Collection Url"),
	$Credential = $(throw "Please provide a Credentials")
)

	$context = New-Object Microsoft.SharePoint.Client.ClientContext($Url)
	$context.Credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($Credential.UserName,$Credential.Password)
	$context.RequestTimeout = 1000000 # milliseconds
	$web = $context.Web
	$context.Load($web)
	$context.Load($web.Webs)
	$context.ExecuteQuery()
	foreach($myweb in $web.Webs)
	{
		Get-SPOWebs -Url $myweb.Url -Credential $Credential
		$myweb
	}
}

function Check-InfoPath-Usage($myspoWebSite, $myspcontext)
{
	[string]$InfotpathStatus = ""
	[boolean]$DebugMode = $false #$DebugGlobalMode
	$AllspwebLists = $myspoWebSite.lists
	$myspcontext.Load($AllspwebLists)
	$myspcontext.ExecuteQuery()
	Write-Host " ---------------------------------------------- "
	if($DebugMode) {Write-Host "         -->InfoPath:",$($myspoWebSite.Id), "-",$($myspoWebSite.Url), "-",$($myspoWebSite.Title) -ForegroundColor Yellow}
	foreach($myList in $AllspwebLists)
	{
		$myspcontext.Load($myList)
		$myspcontext.ExecuteQuery()
		$listTitle = $myList.Title
		$listType = $myList.BaseTemplate
		$listUrl = $myList.DefaultViewUrl
		try
		{
			if($DebugMode) {Write-Host "         -->Infopath: List Check", $listTitle, "(", $listType, ") at WebURL", $myspoWebSite.url -ForegroundColor Green}
			if($listType -eq 100 -or $listType -eq 101)
			{
				if($DebugMode) {Write-Host "            -->Infopath: Line 70 - listType:", $listType}
				$isSysList = $myList.IsSystemList
				$IswebCatalog =  $myList.IsCatalog
				$IsAppList =  $myList.IsApplicationList
				$listForms = $myList.Forms
				$myspcontext.Load($listForms)
				$myspcontext.ExecuteQuery()
				if($DebugMode) {Write-Host "            -->Infopath: Line 77 - isSysList:", $isSysList}
				if($DebugMode) {Write-Host "            -->Infopath: Line 78 - IsCatalog:", $IswebCatalog}
				if($DebugMode) {Write-Host "            -->Infopath: Line 79 - IsApplicationList:", $IsAppList}
				if($isSysList -or $IswebCatalog -or $IsAppList)
				{
					if($DebugMode) {Write-Host "            -->Infopath: System, Application or Catalog List Ignore", $listTitle, "at URL", $myspoWebSite.url -ForegroundColor Yellow}
				}
				else
				{
					if($listType -eq 101)
					{
						if($DebugMode) {Write-Host "            -->Infopath: Line 88 - listType:",  $listType}
						if($myList.AllowContentTypes)
						{
							if($DebugMode) {Write-Host "            -->Infopath: Line 89 - AllowContentTypes:", $myList.AllowContentTypes}
							$contentTyps = $myList.ContentTypes
							$myspcontext.Load($contentTyps)
							$myspcontext.ExecuteQuery()
							forEach($contType in $contentTyps)
							{
								if($DebugMode) {Write-Host "            -->Infopath: Line 97 - contType.Name:", $contType.Name}
								if($contType.Name -eq "Form")
								{
									Write-Host "            -->InfoPath: Found in Library", $listTitle, "at URL", $myspoWebSite.url -ForegroundColor Magenta
									$InfotpathStatus += "Infopath:"+ $myspoWebSite.url +";"
								}
							}
						}
						if($DebugMode) {Write-Host "            -->Infopath: Line 105 - listType:", $listType}
					}
					else
					{
						forEach($listFm in $listForms)
						{
							$listPath = $listFm.ServerRelativeUrl
							if($DebugMode) {Write-Host "            -->Infopath: Line 112 - listPath:", $listPath}
							if ($listPath -like '*displayifs.aspx')
							{
								Write-Host "            -->InfoPath: Found in List", $listTitle, "at URL", $myspoWebSite.url -ForegroundColor Magenta
								$InfotpathStatus += "Infopath:"+ $myspoWebSite.url +";"
							}
						}
					}
				}

			}
		}
		catch
		{
			Write-Host "         -->Infopath: Error Check for list:",  $listTitle -ForegroundColor Red
			Write-Host "             ErrorMessage:", $_.Exception -ForegroundColor Red
		}
    }

	return $InfotpathStatus
}

function Check-SPWorkflow($myspoWebSite, $myspcontext)
{
	[string]$WorkflowStatus = ""
	[boolean]$DebugMode = $false #$DebugGlobalMode
	$AllspwebLists = $myspoWebSite.lists
	$myspcontext.Load($AllspwebLists)
	$myspcontext.ExecuteQuery()
	if($DebugMode) {Write-Host "         --> WorkFlow: ",$($myspoWebSite.Id), "-",$($myspoWebSite.Url), "-",$($myspoWebSite.Title) -ForegroundColor Yellow}
	foreach($list in $AllspwebLists)
	{
		if($DebugMode) {Write-Host "         -->SPWorkflow: List Check", $list.Title, " at WebURL", $myspoWebSite.url -ForegroundColor Green}
		try
		{
			$myspcontext.Load($list.WorkflowAssociations)
			$myspcontext.ExecuteQuery()
			foreach($wfAssociation in $list.WorkflowAssociations)
			{
				if($DebugMode) {Write-Host "                 -->SPWorkflow: List ", $list.Title, "- Wokflow:", $wfAssociation.Name -ForegroundColor Magenta}
				$WorkflowStatus += "`"$($list.Title)`",`"$($wfAssociation.Name)`",`"$($wfAssociation.TaskListTitle)`","
				#$WorkflowStatus += "`"$($wfAssociation.HistoryListTitle)`",$($wfAssociation.Created),$($wfAssociation.Modified)"
			}  
		}
		catch
		{
			Write-Host "         -->WorkFlowCHeck: Error Check for list:",  $list.Title -ForegroundColor Red
			Write-Host "             ErrorMessage:", $_.Exception -ForegroundColor Red
		}
	}
	return $WorkflowStatus
}

cls
Write-Host " ---------------------------------------------- "
Load-DLLandAssemblies
Write-Host " ---------------------------------------------- "

$secureStringPwd = ConvertTo-SecureString -string (Get-Content $PwdTXTPath)
$adminCreds = New-Object System.Management.Automation.PSCredential $username, $secureStringPwd
#$adminCreds = get-credential

Connect-SPOService -Url $AdminTenantURL -credential $adminCreds -ErrorAction SilentlyContinue -ErrorVariable Err

$data = @()

#Retrieve all site collection infos
#$sitesInfo = Get-SPOSite -Limit 10 | Sort-Object -Property url | Select *
#$sitesInfo = Get-SPOSite -Template "STS#0" -Limit 10 | Sort-Object -Property url | Select *
$sitesInfo = Get-SPOSite -Limit ALL | Sort-Object -Property url | Select *

[int]$i = 1;
[string]$CheckInfoPathStatus = ""
[string]$CheckWorkFlowStatus = ""
$data = @()

Write-Host "--------------------------------------------------------------------------------------------"
#Retrieve and print all sites
foreach ($site in $sitesInfo)
{
	Write-Host "SiteColl Number:", $i, "- of:", $sitesInfo.Count;
    $i += 1;
	$RootSiteCreatedDate = get-date "1900-01-01"
	try
	{
		$Rootcontext = New-Object Microsoft.SharePoint.Client.ClientContext($site.Url)
		$Rootcontext.Credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($adminCreds.UserName,$adminCreds.Password)
		$Rootcontext.RequestTimeout = 1000000 # milliseconds
		$RootWeb = $Rootcontext.web
		$Rootcontext.Load($RootWeb)
		$Rootcontext.ExecuteQuery()
		$RootSiteCreatedDate = $RootWeb.Created
		$CheckInfoPathStatus = Check-InfoPath-Usage $RootWeb $Rootcontext
		$CheckWorkFlowStatus = Check-SPWorkflow $RootWeb $Rootcontext
	}
	catch
	{
		Write-host "  =====>>>> Impossible to get the RootSite " -ForegroundColor Red
		Write-host "  =====>>>> RootSite:", $site.Url -ForegroundColor Yellow
	}
		
	Write-Host "SPO Site collection:", $site.Url, "- Title:", $site.Title
	Write-Host "   => Creation Date:", $RootSiteCreatedDate, "- LastItemModifiedDate", $site.LastContentModifiedDate
	Write-Host "   => External Sharing:", $site.SharingCapability
	Write-Host "   => Site Template Used:", $site.Template
	Write-Host "   => Storage Quota:", $site.StorageQuota
	Write-Host "   => Storage used:", $site.StorageUsageCurrent
	Write-Host "   => Storage Warning Level:", $site.StorageQuotaWarningLevel
	Write-Host "   => Resource Quota:", $site.ResourceQuota, "- Resource used:", $site.ResourceUsageCurrent

	$SuborRootSite = "RootSite"
	$data += @(
		[pscustomobject]@{
		SiteCollectionURL = $site.Url
		SiteCollectionTitle = $site.Title
		SPType = $site.Template
		SubsiteURL = $site.Url
		SuborRootSite = $SuborRootSite
		WebTemplate = $site.Template
		WebCreationDate = $RootSiteCreatedDate
		LastItemModifiedDate = $site.LastContentModifiedDate
		ExternalSharingCapability = $site.SharingCapability
		StorageQuotaMB = $site.StorageQuota
		StorageUsageCurrentMB = $site.StorageUsageCurrent
		StorageQuotaWarningLevelMB = $site.StorageQuotaWarningLevel
		ResourceQuota = $site.ResourceQuota
		ResourceUsageCurrent = $site.ResourceUsageCurrent
		DevCustomCreated = ""
		DevCustomSPWorkflow = $CheckWorkFlowStatus
		DevSPFxCreated = ""
		DevMSFloworPowerAppsCreated = ""
		DevInforpathForm = $CheckInfoPathStatus
		}
	)
	try
	{
		$AllWebs = Get-SPOWebs -Url $site.Url -Credential $adminCreds
		if($DebugMode) {$AllWebs | %{ Write-Host "   >>", $_.Title, "-", $_.Url}}
		Write-Host "--------------------------------------------------------------------------------------------"

		foreach($mySPWeb in $AllWebs)
		{
			Write-Host "         >> Subsite:", $mySPWeb.Url -ForegroundColor magenta
			$CheckInfoPathStatus = Check-InfoPath-Usage $RootWeb $Rootcontext
			$CheckWorkFlowStatus = Check-SPWorkflow $RootWeb $Rootcontext
			$SuborRootSite = "SubSite"
			$data += @(
				[pscustomobject]@{
				SiteCollectionURL = $site.Url
				SiteCollectionTitle = $site.Title
				SPType = $site.Template
				SubsiteURL = $mySPWeb.Url
				SuborRootSite = $SuborRootSite
				WebTemplate = $mySPWeb.WebTemplate
				WebCreationDate = $mySPWeb.Created
				LastItemModifiedDate = $mySPWeb.LastItemModifiedDate
				ExternalSharingCapability = $site.SharingCapability
				StorageQuotaMB = $site.StorageQuota
				StorageUsageCurrentMB = $site.StorageUsageCurrent
				StorageQuotaWarningLevelMB = $site.StorageQuotaWarningLevel
				ResourceQuota = $site.ResourceQuota
				ResourceUsageCurrent = $site.ResourceUsageCurrent
				DevCustomCreated = ""
				DevCustomSPWorkflow = $CheckWorkFlowStatus
				DevSPFxCreated = ""
				DevMSFloworPowerAppsCreated = ""
				DevInforpathForm = $CheckInfoPathStatus
				}
			)
		}
	}
	catch
	{
		Write-host "  =====>>>> Impossible to get the Subsites " -ForegroundColor Red
		Write-host "  =====>>>> RootSite:", $site.Url -ForegroundColor Yellow
	}

} 

#Write-Host $data

$datestring = (get-date).ToString("yyyyMMdd-hhmm")
$CSVFileToExport = Join-Path -Path $CSVFolderReport  -ChildPath $("SharePoint_"+ $datestring + ".csv")
	
Write-host " -----------------------------------------" -ForegroundColor Green
Write-Host (" >>> writing to file {0}" -f $CSVFileToExport) -ForegroundColor Green
$data | Export-csv $CSVFileToExport -NoTypeInformation -enc utf8 
Write-host " -----------------------------------------" -ForegroundColor Green

You can adapt that script as you need, based on your own requirements

 

Fabrice Romelard

 

French version:

Source used:

 

7,612 Views
2 Likes
0 Replies
Reply
0 Replies