PowerShell script to return the sites not used in last 6 months

Hi All,

I've worked on a PowerShell script to read the site Urls from .CSV file and return the sites which are being used in last 6 months. I've faced many challenges as I had go through the sub-sites and list. For each list, I had to iterate through each item's Modified Date to check whether the item is modified or not in last 6 months.
It was very difficult for the lists who item count exceeds list view threshold i.e. 5000 items. For those lists, I had to run in batches to overcome this error.

Please see the script below.


Add-Type -Path "C:\Program Files\Common Files\microsoft shared\Web Server Extensions\15\ISAPI\Microsoft.SharePoint.Client.dll"  
Add-Type -Path "C:\Program Files\Common Files\microsoft shared\Web Server Extensions\15\ISAPI\Microsoft.SharePoint.Client.Runtime.dll"  

Add-PSSnapin Microsoft.SharePoint.PowerShell -ErrorAction SilentlyContinue

#$siteURL = "https://uat-intranet.cbreglobalinvestors.com"  
$userId = "US\KBasu"  
$pwd = Read-Host -Prompt "Enter password" -AsSecureString

$siteUrls =@()

##Global variable used
$global:count = 0

$siteTitle = New-Object System.Collections.Generic.List[System.Object]
$siteLinks = New-Object System.Collections.Generic.List[System.Object]
$listTitle = New-Object System.Collections.Generic.List[System.Object]
$listItemTitle = New-Object System.Collections.Generic.List[System.Object]
$listItemModified = New-Object System.Collections.Generic.List[System.Object]


try{ 
    

     Function GetWebs($siteUrl){
        $context = New-Object Microsoft.SharePoint.Client.ClientContext($siteURL);
        $context.Credentials = New-Object System.Net.NetworkCredential($userId, $pwd);

        $web = $context.Web
        $context.Load($web)
       
        
        #Get Immediate sub-webs in site collection
        $webs = $web.Webs
        $context.Load($webs)
        $context.ExecuteQuery()

        Write-Host "Processing web : "$web.Url -ForegroundColor Magenta
        
        $lists = $web.Lists
        $context.Load($lists)
        $context.executeQuery() 

        Foreach($list in $web.Lists){
            Write-Host "Processing list : " $list.Title -ForegroundColor Yellow
            $context.Load($list)
            $context.ExecuteQuery()
            Write-Host "List items count :" $list.ItemCount
            
            Write-Host "---------------------------------------------------------------------------------------------------"
            
            ##Process List Items in Batches to Fix SharePoint Online List View Threshold Issue in PowerShell     
            $BatchSize = 500
            $Query = New-Object Microsoft.SharePoint.Client.CamlQuery
            $Query.ViewXml = "<View Scope='RecursiveAll'><Query><OrderBy><FieldRef Name='ID' Ascending='TRUE'/></OrderBy></Query><RowLimit Paged='TRUE'>$BatchSize</RowLimit></View>"
            
            ##$ListItems = $list.GetItems([Microsoft.SharePoint.Client.CamlQuery]::CreateAllItemsQuery())
            $ListItems = $list.GetItems($Query)
            $context.Load($ListItems)
            $context.ExecuteQuery();

            
            
            Foreach($listItem in $ListItems){
                

            
                if($listItem["Modified"] -gt $dateCheck){   
                    $global:count = $global:count + 1
                                  
                    $siteTitle.Add($context.Web.Title)
                    $siteLinks.Add($context.Web.Url)
                    $listTitle.Add($list.Title)
                    $listItemTitle.Add($listItem["Title"])
                    $listItemModified.Add($listItem["Modified"])
                    
                    Continue
                }
                else{
                    Break
                }
            }


        }
    

        Foreach($subweb in $webs){
            
            GetWebs($subweb.Url)
            Write-Host "########################################################################################"
        }
        
    }


     
     $dateCheck = [DateTime]::Now.AddMonths(-6)
    
    
    $csv = Import-Csv "D:\Kunal\Global Investors\Automation\Sites.csv"
    $csv | ForEach-Object {
        $siteUrls += $_."SiteUrl"
        
    }
    
    foreach($siteUrl in $siteUrls){
        Write-Host "Processing URL: " $siteUrl -ForegroundColor DarkMagenta
        GetWebs($siteUrl)
    }
    Write-Host "Count: "$global:count
    

    $results = @()
    for($i=0; $i -le $count; $i++){
        $ExportItem = New-Object PSObject
        $ExportItem | Add-Member -MemberType NoteProperty -name "Site" -value $siteTitle[$i]
        $ExportItem | Add-Member -MemberType NoteProperty -Name "Site Url" -value $siteLinks[$i]
        $ExportItem | Add-Member -MemberType NoteProperty -name "List Name" -value $listTitle[$i]
        $ExportItem | Add-Member -MemberType NoteProperty -name "ListItem Title" -value $listItemTitle[$i]
        $ExportItem | Add-Member -MemberType NoteProperty -name "ListItem Modified" -value $listItemModified[$i]
        $results += $ExportItem
    }
    $results | Export-Csv "D:\Kunal\Global Investors\Automation\Reports.csv" -NoTypeInformation
     
}  
catch{  
    write-host "$($_.Exception.Message)" -foregroundcolor red  
}