SharePoint Online: CAML Query with IsNull and IsNotNull Examples in PowerShell

Requirement: Get all items from a list where a particular filed value is Null.

SharePoint Online: CAML Query IsNull Example
Here is the example of how IsNull operator can be used in CAML:

#Parameters
$SiteURL = "https://crescent.sharepoint.com/sites/marketing/"
$ListName= "Documents"
$FieldName = "Title"
$ReportOutput = "C:\Temp\ListInventory.csv"
 
#Connect to SharePoint Online site
Connect-PnPOnline $SiteURL -UseWebLogin
 
#Array to store results
$Results = @()
 
#Define CAML Query to get items where the field value is null
$CAMLQuery = "<View><Query><Where><IsNull><FieldRef Name='$FieldName' /></IsNull></Where></Query></View>"
#Get all Items filtered by CAML query
$ListItems = Get-PnPListItem -List $ListName -Query $CAMLQuery -PageSize 1000
Write-host "Total Number of Items Found:"$ListItems.Count

$ItemCounter = 0 
#Iterate through each item
Foreach ($Item in $ListItems)
{
    #get the Field Values
    $Results += New-Object PSObject -Property ([ordered]@{
        Name              = $Item.FieldValues.FileLeafRef
        Type              = $Item.FileSystemObjectType
        FileType          = $Item.FieldValues.File_x0020_Type
        RelativeURL       = $Item.FieldValues.FileRef
        CreatedBy         = $Item["Author"].Email
    })
    $ItemCounter++
    Write-Progress -PercentComplete ($ItemCounter / ($List.ItemCount) * 100) -Activity "Processing Items $ItemCounter of $($List.ItemCount)" -Status "Getting Metadata from Item '$($Item['FileLeafRef'])"          
}

#Export the results to CSV
$Results | Export-Csv -Path $ReportOutput -NoTypeInformation
Write-host "Document Library Inventory Exported to CSV Successfully!"

SharePoint Online: CAML query with IsNotNull Operator in PowerShell
Similarly, You can use "<IsNotNull>" Operator to get list items where a particular field value is not null. This time, let us use CSOM PowerShell script.

#Set parameter values
$SiteURL="https://crescent.sharepoint.com/sites/marketing"
$ListName="Config"
$FieldName = "description" #Internal Name
  
#Get Credentials to connect
$Cred= Get-Credential
   
#Setup the context
$Ctx = New-Object Microsoft.SharePoint.Client.ClientContext($SiteURL)
$Ctx.Credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($Cred.Username, $Cred.Password)
  
#Get the List
$List = $Ctx.Web.lists.GetByTitle($ListName)
 
#Define the CAML Query
$Query = New-Object Microsoft.SharePoint.Client.CamlQuery
$Query.ViewXml = "@
<View>
    <Query>
        <Where>
            <IsNotNull><FieldRef Name='$FieldName' /></IsNotNull>
        </Where>
    </Query>
</View>"
 
#Get All List Items matching the query
$ListItems = $List.GetItems($Query)
$Ctx.Load($ListItems)
$Ctx.ExecuteQuery()
 
Write-host "Total Number of Items:"$ListItems.count
 
#Loop through each List Item
ForEach($Item in $ListItems)
{ 
    #Do Something
    Write-host $Item.id
    Write-host $Item["Title"]
}