Audit Log Trimming removed from SharePoint Online
We got a surprise today: "Given this audit experience is powered by the Unified Audit pipeline, we no longer support Trimming as a feature. You will be able to access files that have been trimmed so far in the document library that you have set for this purpose." We create a Site collection for each project - and keep audit logs for the entire lifecycle of the project - which could be years. We had just started implementing an online version of our project sites in the last couple of months, but that may stop now that Microsoft has removed the ability to trim out audit logs to a report that can be saved. Does anyone else keep audit logs on their sites for longer than 90 days? If so, how are you extracting and keeping the data?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: PowerShell script to export Exchange Usage in CSV format used to Audit an Office 365 Tenant 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: http://blogs.developpeur.org/fabrice69/archive/2019/02/27/office-365-script-powershell-pour-auditer-l-usage-de-sharepoint-online-de-votre-tenant.aspx Source used: http://sharepoint.stackexchange.com/questions/86842/extract-all-list-names-from-sharepoint-site-to-csv https://collab365.community/forum/topics/find-all-sharepoint-online-sites-with-an-infopath-form-and-determine-if-still-being-used/ https://sharepoint.stackexchange.com/questions/240149/get-the-list-of-all-available-infopath-forms-in-a-sharepoint-site-collection-s https://gallery.technet.microsoft.com/office/SharePoint-Online-Get-all-40d76baa
