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
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
4) Now go to the folder, we will find the excel file With name Source_Document_CheckOut_Collection and open it.
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
SharepointMates 