2

I have written a script to get the items of a specific list leveraging the GetListItems method, but I am now being asked to join multiple lists, so I believe I must use the GetItems method. The code is essentially the same, but I am not able to get the fields back that I need. I have removed the joins and preferredfields nodes to simplify the code below. I would welcome your input:


cls
#Region Configure for Remote Execution
$pass = ConvertTo-SecureString "******" -AsPlainText -Force
$creds = New-Object System.Management.Automation.PSCredential -ArgumentList "domain\user", $pass
Enter-PSSession -ComputerName SPSERVER-Authentication CredSSP -Credential:$creds
Add-PSSnapin Microsoft.SharePoint.PowerShell
#EndRegion

$list = $web.GetList("http://webapp/site/Lists/ServerCatalog")
$query = new-object Microsoft.SharePoint.SPQuery

$query.query = [xml]@"
<Query>
<Where>
     <And> 
          <And>
              <Neq><FieldRef Name='DeploymentStatus' /><Value Type='MultiChoice'>Build</Value></Neq>
              <Neq><FieldRef Name='DeploymentStatus' /><Value Type='MultiChoice'>Inactive</Value></Neq>
          </And>
          <Or>
              <Or>
                   <Or>
                        <Eq><FieldRef Name='Team' /><Value Type='Text'>AD</Value></Eq>
                        <Eq><FieldRef Name='App_x0020_Server' /><Value Type='bit'>1</Value></Eq>
                   </Or>
                   <Or>
                        <Eq><FieldRef Name='Web_x0020_Server' /><Value Type='bit'>1</Value></Eq>
                        <Eq><FieldRef Name='SQL_x0020_DB' /><Value Type='bit'>1</Value></Eq>
                   </Or>
              </Or>
              <Eq><FieldRef Name='Desktop' /><Value Type='bit'>1</Value></Eq>
          </Or>
     </And>
</Where>
</Query>
"@

$query.ViewFields = [xml]@"
<ViewFields>
     <FieldRef Name="ows_Title" />
    <FieldRef Name="MgmtIP" />
     <FieldRef Name="OU" />
     <FieldRef Name="CNAMES" />
     <FieldRef Name="Processes" />
     <FieldRef Name="URLs" />
     <FieldRef Name="IgnoreURLs" />
     <FieldRef Name="SQL_x0020_Accounts" />
     <FieldRef Name="SQLStatements" />
     <FieldRef Name="App_x0020_Server" />
     <FieldRef Name="SQL_x0020_DB" />
     <FieldRef Name="Web_x0020_Server" />
     <FieldRef Name="Desktop" />
     <FieldRef Name="DeploymentStatus" />    
</ViewFields>
"@
$query.IncludeMandatoryColumns = $false
$query.DatesInUtc = $true
$items = $list.GetItems($query)

1

If you need to query multiple lists with the same CAML query you probably want to do the SPSiteDataQuery route: http://msdn.microsoft.com/en-us/library/microsoft.sharepoint.spsitedataquery.aspx

You use the Lists properties of the class to specify which lists (...by template, base type, or explicit) are included in the search: http://msdn.microsoft.com/en-us/library/microsoft.sharepoint.spsitedataquery.lists.aspx

  • I will evaluate this alternative method, but there is nothing wrong with leveraging GetItems() against multiple lists. – websch01ar Aug 8 '11 at 11:21
  • If you want to execute the method against each list individually and then manually join the results then you're right there is nothing wrong with leveraging GetItems...I have offered up an alternative that will not require that extra work. – Rob D'Oria Aug 8 '11 at 13:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.