Performing a Sharepoint CAML query in powershell

I do alot of audits on Sharepoint. The following is one way of getting a list of Not-Declared-As-Records on sharepoint.

change the directory to match your situation as well as the Sharepoint list where your records reside.


If ((Get-PSSnapIn -Name Microsoft.SharePoint.PowerShell -ErrorAction SilentlyContinue) -eq $null )
{ Add-PSSnapIn -Name Microsoft.SharePoint.PowerShell }

#not used in this example
function Write-Success {
process { Write-Host $_ -ForegroundColor Green }
}

#not used in this example
function YearMonth($date) {
return "$($date.Year)/$($date.Month)";
}

function GetNonRecordCount([ref]$listNamesRef, [ref]$SPWebRef) {
$listNames = $listNamesRef.Value;
$SPWeb = $SPWebRef.Value;

$hitlist = @();
foreach ($listName in $listNames) {
$target = @{};
$spqQuery = New-Object Microsoft.SharePoint.SPQuery;
$spqQuery.Query = "
<Where>
<IsNull>
<FieldRef Name='_vti_ItemDeclaredRecord' />
</IsNull>
</Where>
";
$spqQuery.ViewFields = "<FieldRef Name='FileLeafRef' /><FieldRef Name='Title' />";
$spqQuery.ViewFieldsOnly = $true;
$items = $SPWeb.Lists[$listName].GetItems($spqQuery);

$target.Name = $listName;
$target.Count = $items.Count;

$hitlist += $target;
}
return ,$hitlist;
}

clear;
cd C:\FolderWhereYourSharepointScriptsWithConfigJSON\
$config = (Get-Content config.json) -join "`n" | ConvertFrom-Json
$SPWeb = Get-SPWeb $config.SPSite;

$listNames = @(
'Sharepoint List 1'
, 'Sharepoint List 2'
, 'Sharepoint List 3'
);

$hitlist = GetNonRecordCount ([ref]$listNames) ([ref]$SPWeb);

$hitlist | ConvertTo-Json -Depth 100;