Get Check out Documents from all sites in a site collection Using Power shell in SharePoint Online

In this is post we will see how to get the  details of  check out Documents items  using CSOM power shell  using from  all the sub sites in a site collection in SharePoint online.

This post is the 5th post   in series of the  Power shell Scripts for Getting the data from site collection in SharePoint Online.

Get all Sub site details in Site Collection in SharePoint online using Power shell .

Get All Lists details in a site collection Using Power shell in SharePoint Online

Get All Lists items in a site collection Using Power shell in SharePoint Online

Get All Document details from all sites in a site collection Using Power shell in SharePoint Online

Note:  please check the  above link before this post before to proceed further , since this is  continuation of the first post and we are using the excel generated in the above post as an input for the script below.

1) Copy the below script to notepad and save the file with Get all Checked out Document Info.ps1

#Add - PSSnapin Microsoft.SharePoint.PowerShell  

$host.Runspace.ThreadOptions = "ReuseThread" 

#Definition of the function that allows to create a new view in a SharePoint Online list 

function Get-Users
{
    param ($sCSOMPath,$sSiteID,$sSiteName,$sSiteUrl,$ListID,$ListTitle,$ListViewUrl,$BaseTemplate,$sUserName,$sPassword,$sWeburl,$FileName)
        #Adding the Client OM Assemblies
        $sCSOMRuntimePath=$sCSOMPath +  "\Microsoft.SharePoint.Client.Runtime.dll"
        $sCSOMPathdll=$sCSOMPath +  "\Microsoft.SharePoint.Client.dll"
        Add-Type -Path $sCSOMPathdll
        Add-Type -Path $sCSOMRuntimePath        

        #SPO Client Object Model Context
        $spoCtx = New-Object Microsoft.SharePoint.Client.ClientContext($sSiteUrl)
        $spoCredentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($sUserName, $sPassword)
		#$spoCredentials = New-Object System.Net.NetworkCredential($sUserName, $sPassword)
        $spoCtx.Credentials = $spoCredentials       

		$web = $spoCtx.Web
		$UserList = $web.SiteUserInfoList
		#$camluser="<View><Query><Where><Eq><FieldRef Name='ID' /><Value Type='Text'>" + $AssignedTo + "</Value></Eq></Where></Query><RowLimit>5000</RowLimit></View>";
		$camluser="<View><ViewFields><FieldRef Name='ID' /><FieldRef Name='Name' /><FieldRef Name='EMail' /></ViewFields><RowLimit>5000</RowLimit></View>"
		$cuserquery = New-Object Microsoft.SharePoint.Client.CamlQuery
		$cuserquery.ViewXml=$camluser
		$listusers = $UserList.GetItems($cuserquery)

		$spoCtx.Load($listusers)
		$spoCtx.ExecuteQuery()
        #$spoCtx.Dispose() 

		return $listusers

} 

function Get-ListItemInfo-ByList
{
    param ($sCSOMPath,$sSiteID,$sSiteName,$sSiteUrl,$ListID,$ListTitle,$ListViewUrl,$BaseTemplate,$sUserName,$sPassword,$sWeburl,$FileName,$UsersList)
    try
    {
        #Adding the Client OM Assemblies
        $sCSOMRuntimePath=$sCSOMPath +  "\Microsoft.SharePoint.Client.Runtime.dll"
        $sCSOMPathdll=$sCSOMPath +  "\Microsoft.SharePoint.Client.dll"
        Add-Type -Path $sCSOMPathdll
        Add-Type -Path $sCSOMRuntimePath        

        #SPO Client Object Model Context
        $spoCtx = New-Object Microsoft.SharePoint.Client.ClientContext($sSiteUrl)
        $spoCredentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($sUserName, $sPassword)
		#$spoCredentials = New-Object System.Net.NetworkCredential($sUserName, $sPassword)
        $spoCtx.Credentials = $spoCredentials       

		$web = $spoCtx.Web
		$List = $web.Lists.GetByTitle($ListTitle);

		$caml="<View Scope='RecursiveAll'><Query><Where><And><Eq><FieldRef Name='FSObjType' /><Value Type='Lookup'>0</Value></Eq><Geq><FieldRef Name='CheckoutUser' LookupId='TRUE' /><Value Type='int'>0</Value></Geq></And></Where></Query></View>"
		$cquery = New-Object Microsoft.SharePoint.Client.CamlQuery
		$cquery.ViewXml=$caml
		$listItems = $List.GetItems($cquery)

		$spoCtx.Load($List)
		$spoCtx.Load($listItems)
		$spoCtx.ExecuteQuery() 

		$AllListItems = @()
		Write-Host "Item Count - $($listItems.Count)"
		foreach ($listitem in $listItems)
		{
			$Title =""
			$FileType =""
			$FileRef =""
			$FileSize ="0 KB"
			$CreatedBy =  "System Account"
			$ModifiedBy = "System Account"
			$IsCheckedoutToLocal = "No"
			$CheckoutUser =""
			$CheckoutUserEmail =""
			If([string]::IsNullOrEmpty($listitem.FieldValues["FileLeafRef"]))
			{
				$Title = "No Title"
			}
			else
			{
				$Title =$listitem.FieldValues["FileLeafRef"]
			}

			If([string]::IsNullOrEmpty($listitem.FieldValues["File_x0020_Type"]))
			{
				$FileType = "None"
			}
			else
			{
				$FileType =$listitem.FieldValues["File_x0020_Type"]
			}

			If([string]::IsNullOrEmpty($listitem.FieldValues["FileRef"]))
			{
				$FileRef = "None"
			}
			else
			{
				$FileRef =($sWeburl + $listitem.FieldValues["FileRef"])
			}

			If([string]::IsNullOrEmpty($listitem.FieldValues["File_x0020_Size"]))
			{
				$FileSize = "0 KB"
			}
			else
			{
				$value = [math]::Round(($listitem.FieldValues["File_x0020_Size"]/1024),2)
				$decimal = "{0:N2}" -f $value
				$FileSize = ($decimal + " KB")
				#Write-Host " Field Size2 = $($FileSize)"
			}

			#If([string]::IsNullOrEmpty($listitem.FieldValues["CheckoutUser"]))
			#{
			#	$IsCheckedoutToLocal = "No"
			#}
			#else
			#{
				$value = $listitem.FieldValues["IsCheckedoutToLocal"]
				if($value -ne "0")
				{
					$IsCheckedoutToLocal ="Yes"
				}
				else
				{
					$IsCheckedoutToLocal ="No"
				}

				If([string]::IsNullOrEmpty($listitem.FieldValues["CheckoutUser"]))
				{
					$CheckoutUser = "No"
				}
				else
				{
					$FieldCheckoutUser = [Microsoft.SharePoint.Client.FieldUserValue]$listitem.FieldValues["CheckoutUser"]
					foreach($user in $UsersList)
					{
						if($user.FieldValues["ID"] -eq $FieldCheckoutUser.LookupId)
						{
							$CheckoutUser = $user.FieldValues["Name"]
							$CheckoutUserEmail= $user.FieldValues["EMail"]
							break
							#Write-Host "$($AssignedToLoginName)  = $($AssignedToEmail)"
						}
						else
						{
							$CheckoutUser =$FieldCheckoutUser.LookupValue
						}
					}
				}
			#}

			#if($IsCheckedoutToLocal -eq "Yes")
			#{
			$mmddyyyy  =($listitem.FieldValues["Last_x0020_Modified"]).ToString().Substring(0,10).split("-")
				$mmddyyyy1  =($listitem.FieldValues["Created_x0020_Date"]).ToString().Substring(0,10).split("-")

				$modmmddyyyy  = $mmddyyyy[1] +"/"+ $mmddyyyy[2] +"/"+ $mmddyyyy[0]
				$cremmddyyyy  = $mmddyyyy1[1] +"/"+ $mmddyyyy1[2] +"/"+ $mmddyyyy1[0]

				$AllListItems += New-Object -TypeName PSObject -Property @{
									SiteID = $sSiteID
									SiteName = $sSiteName
									SiteUrl =$sSiteUrl
									ListID = $ListID
									ListTitle = $ListTitle
									ListViewUrl =$ListViewUrl
									BaseTemplate= $BaseTemplate
									DocumentID=$listitem.ID
									FileName=$Title
									FileType= $FileType
									FileSize= $FileSize
									FileRef= $FileRef
									IsCheckedoutToLocal =$IsCheckedoutToLocal
									CheckoutUser =$CheckoutUser
									CheckoutUserEmail =$CheckoutUserEmail
									CreatedBy = $listitem.FieldValues["Created_x0020_By"]
									ModifiedBy = $listitem.FieldValues["Modified_x0020_By"]
									CreatedDate = $cremmddyyyy
                                    ModifiedDate = $modmmddyyyy

									} | Select  SiteID,SiteName,SiteUrl,ListID,ListTitle,ListViewUrl,BaseTemplate,DocumentID,FileName,FileType,FileSize,FileRef,IsCheckedoutToLocal,CheckoutUser,CheckoutUserEmail,CreatedBy,ModifiedBy,CreatedDate,ModifiedDate
			#}
		}
		$AllListItems| Export-CSV ($sCSOMPath+"\"+$FileName+".csv") -NoTypeInformation -Append #-Encoding UTF8

        $spoCtx.Dispose()
    }
    catch [System.Exception]
    {
	    $FailedSite = @()
		$FailedSite += New-Object -TypeName PSObject -Property @{
								SiteID = $sSiteID
								SiteName = $sSiteName
								SiteUrl =$sSiteUrl
                                ListID = $ListID
								ListTitle = $ListTitle
                                ListViewUrl =$ListViewUrl
								BaseTemplate= $BaseTemplate
								BaseType=$BaseType
								ItemCount=$ItemCount
								Exception=$_.Exception.Message
            					} | Select  SiteID,SiteName,SiteUrl,ListID,ListTitle,ListViewUrl,BaseTemplate,BaseType,ItemCount,Exception

		$FailedSite| Export-CSV ($sCSOMPath+"\"+$FileName+"_Failed.csv") -NoTypeInformation -Append #-Encoding UTF8
        Write-Host -ForegroundColor Red $_.Exception.ToString()
		#Read-Host -Prompt "Operation failed..! Press any key to run the next item"
    }
} 

 function Get-ListItemInfo
{
    param ($sCSOMPath,$sSiteUrl,$sUserName,$sPassword,$sWeburl,$FileName,$ImportFile)
    try
    {
       	Write-Host "Getting checked out Document info from All Document Libraries in a Site Collection"
		Write-Host "----------------------------------------------------------------------------------"
        #$ListBaseTemplate =@("110","111","112","113","114","116","121","122","123")
		$DocumentList =@("DocumentLibrary")

		$ListInfo = import-csv ($sCSOMPath+"\"+$ImportFile+".csv") | Where {$DocumentList -contains $_.BaseType  -And $_.ItemCount -ne 0}#  -And $_.ListViewUrl -notcontains $DefaultLists }

		 $UsersList = Get-Users -sCSOMPath $dir -sSiteUrl $sSiteUrl -sUserName $sUserName -sPassword $sPassword -sWeburl $sWeburl -FileName $FileName -ImportFile $ImportFileName	

		write-host "List Info Cont -$($ListInfo.Count)"
		 for ($i = 0; $i -lt $ListInfo.Count; $i++)
		 #for ($i = 161; $i -lt 162; $i++)
		 {
			$List=$ListInfo[$i]
			Write-Host "$($i). List Title : $($List.ListTitle) :: Site: $($List.SiteUrl)"

			Get-ListItemInfo-ByList -sCSOMPath $sCSOMPath -sSiteID $List.SiteID -sSiteName $List.SiteName -sSiteUrl $List.SiteUrl -ListID $List.ListID -ListTitle $List.ListTitle -ListViewUrl $List.ListViewUrl -BaseTemplate $List.BaseTemplate -sUserName $sUserName -sPassword $sPassword -sWeburl $sWeburl -FileName $FileName -UsersList $UsersList
		 }

		Read-Host -Prompt "file created Successfull..! in the following path $sCSOMPath, Press any key to close this window"

    }
    catch [System.Exception]
    {
        Write-Host -ForegroundColor Red $_.Exception.ToString()
		Read-Host -Prompt "Operation failed..! Press any key to close this and re run the script -main"
    }
} 

$FileName = "Source_Document_CheckOut_Collection"        #Read-Host -Prompt "Enter File Name to write (eg: XXXXX)"
$ImportFileName= "Source_ListCollection"        #Read-Host -Prompt "Enter existing File Name to import file extension is csv (eg: YYYYY)"
$sWeburl = "https://tarun.sharepoint.com/"        #Read-Host -Prompt "Enter Root Site URL (eg: http://Server:port/)"
$sSiteUrl = "https://tarun.sharepoint.com/sites/sharepointmates/"    #Read-Host -Prompt "Enter Site Collection URL (eg: http://Server:port/Sites/Dev)"
$sUserName = "admin@tarun.onmicrosoft.com"            #Read-Host -Prompt "Enter User Name"
$sPassword = ConvertTo-SecureString "XXX" -AsPlainText -Force             #Read-Host -Prompt "Enter your password" -AsSecureString 

$scriptpath = $MyInvocation.MyCommand.Path
$dir = Split-Path $scriptpath

Get-ListItemInfo -sCSOMPath $dir -sSiteUrl $sSiteUrl -sUserName $sUserName -sPassword $sPassword -sWeburl $sWeburl -FileName $FileName -ImportFile $ImportFileName
  • Please update the  $sWeburl,$sUserName,$sPassword  and $sSiteUrl variables with your SharePoint online  environment.
  • In ImportFileName= “Source_ListCollection”,   Source_ListCollectionis the name of the  excel which contains the details of the  all list  details that was generated in the second post .  If you want to give the your custom file  as an input to the above script please create the excel with the  name Source_ListCollection and the excel sheet name Source_ListCollection  with structure as below

ListItemsexcelResultScreen

Or else you can use the second post to generate the excel

2) Now Right click on the Get all Document Info from all Sites.ps1  Power shell script file and select Run with Power Shell

3) After Successful execution of the scripts we will get the below message

checkoutDocumentslsit

4) Now go to the folder, we will find the excel file With name Source_Document_CheckOut_Collection   and open it.

checkoutDocumentsresult

For On premises we need to slightly change  $spoCredentials  and $sUserName    variables in the above script.

$spoCredentials = New-Object System.Net.NetworkCredential($sUserName, $sPassword)

We need to give the user name along with domain name as below

$sUserName = "2013dev\spadmin"

please change the  on premises site  collection url , web url  and password as well.

In the Next post we will how to get the  Get All List Level  2013  Workflows from all the sub sites in a site collection in SharePoint Online

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s