Second Life of a Hungarian SharePoint Geek

February 27, 2018

Paging through the Entities returned by the ProjectData OData Interface of Project Server using PowerShell

Filed under: OData, PowerShell, Project Server, REST — Tags: , , , — Peter Holpar @ 21:13

About a year ago I wrote about how you can use PowerShell and Project Server REST interfaces (ProjectServer and ProjectData) to generate reports. However, at the end of last year we had a problem with the script that query the projects via the ProjectData interface:

  1. $url = 'http://YourProjectServer/PWA/_api/ProjectData/%5Ben-US%5D/Projects?$select=ProjectId,ProjectName,ProjectCreatedDate'
  2. $reportPath = "C:\Data\ProjServerReports\"
  3.  
  4. $request = [System.Net.WebRequest]::Create($url)
  5. $request.UseDefaultCredentials = $true
  6. $request.Accept = "application/json;odata=verbose"
  7.  
  8. $response = $request.GetResponse()
  9. $reader = New-Object System.IO.StreamReader $response.GetResponseStream()
  10. $data = $reader.ReadToEnd()
  11.  
  12. $result = ConvertFrom-Json -InputObject $data
  13. $result.d.results | select ProjectId, ProjectName, ProjectCreatedDate | Export-Csv -Path ($reportPath + 'ProjectsFromProjectDataNoBatching.csv') -Delimiter ";" -Encoding UTF8 -NoTypeInformation

Important! You should use single quotes around the URL in this code snippet and in the other ones below to avoid PowerShell to remove the string $select in the REST query. More about that here.

A user reported an error that some of the projects were not included in the results. We found that the count of projects included in the report via ProjectServer was greater than the count of the projects we queried via  ProjectData. It is the script version that use the ProjectServer interface:

  1. $url = 'http://YourProjectServer/PWA/_api/ProjectServer/Projects?$select=Id,Name,CreatedDate'
  2. $reportPath = "C:\Data\ProjServerReports\"
  3.  
  4. $request = [System.Net.WebRequest]::Create($url)
  5. $request.UseDefaultCredentials = $true
  6. $request.Accept = "application/json;odata=verbose"
  7.  
  8. $response = $request.GetResponse()
  9. $reader = New-Object System.IO.StreamReader $response.GetResponseStream()
  10. $data = $reader.ReadToEnd()
  11.  
  12. $result = ConvertFrom-Json -InputObject $data
  13. $result.d.results | select Id, Name, CreatedDate | Export-Csv -Path ($reportPath + 'ProjectsFromProjectServer.csv') -Delimiter ";" -Encoding UTF8 -NoTypeInformation

First I thought it is an issue with some permissions, but it was suspicious, that all of the missing projects were at the end of the project list (when ordered alphabetically), and the number of included project was exactly 100 in the ProjectData-based report.

Then I found this statement in the ProjectData – Project OData service reference:

“There are limits to the number of entities that can be returned in one query of the ProjectData service.”

What a surprise, the default limit for projects in the on-premise version we have is exactly 100!

You can use the Set-SPProjectOdataConfiguration PowerShell cmdlet for on-premise instances of Project Server to override the default query page size limits for any specified entity set.

Set-SPProjectOdataConfiguration -EntitySetName Projects -PageSizeOverride 200

However, it is a global change on the server, that affects all queries of the specific entity type, that can adversely the server performance, and as such, is not recommended. Instead of this, the suggested solution to query all instance of a given entity type is to implement paging by sending multiple queries using the $top and $skip operator.

Instead of a fix URL we define only a formatting pattern with placeholders for the $skip and $top parameters for the subsequent queries. More about this format is available here.

By applying the $inlinecount=allpages in the query we can assure that the result is always returned in $result.d.results as described in this post.

In the script we assume that the default limit of 100 project / query has not been changed. Note, that we use the $firstBatch variable to decide, if we should append the data to the existing report file in the Export-Csv cmdlet, or to create a new one.

In the first version of the script we process the results (output them into a CSV) immediately after receiving the response.

  1. $urlPattern = 'http://YourProjectServer/PWA/_api/ProjectData/%5Ben-US%5D/Projects?$select=ProjectId,ProjectName,ProjectCreatedDate&$skip={0}&$top={1}&$inlinecount=allpages'
  2. $reportPath = "C:\Data\ProjServerReports\"
  3.  
  4. $firstItem = 0
  5. $batchSize = 100
  6. $firstBatch = $true
  7.  
  8. Do
  9. {
  10.   Write-Host Requesting next batch of $batchSize items, starting at $firstItem
  11.   $url = $urlPattern -f $firstItem, $batchSize
  12.   Write-Host $url
  13.   $request = [System.Net.WebRequest]::Create($url)
  14.   $request.UseDefaultCredentials = $true
  15.   $request.Accept = "application/json;odata=verbose"
  16.  
  17.   $response = $request.GetResponse()
  18.   $reader = New-Object System.IO.StreamReader $response.GetResponseStream()
  19.   $data = $reader.ReadToEnd()
  20.  
  21.  
  22.   $result = ConvertFrom-Json -InputObject $data
  23.   $count = $result.d.results.Count
  24.   Write-Host Item count is $count
  25.   $result.d.results | select ProjectId, ProjectName, ProjectCreatedDate |
  26.       Export-Csv -Path ($reportPath + 'ProjectsFromProjectData_20171130_03.csv') -Delimiter ";" -Encoding UTF8 -NoTypeInformation -Append:(-not $firstBatch)
  27.   $firstBatch = $false
  28.   $firstItem += $batchSize
  29. }
  30. While ($count -eq $batchSize)

In the second we aggregate the results into an array, and process the results only after receiving the last response.

  1. $urlPattern = 'http://YourProjectServer/PWA/_api/ProjectData/%5Ben-US%5D/Projects?$select=ProjectId,ProjectName,ProjectCreatedDate&$skip={0}&$top={1}&$inlinecount=allpages'
  2. $reportPath = "C:\Data\ProjServerReports\"
  3.  
  4. $results = @()
  5. $firstItem = 0
  6. $batchSize = 100
  7.  
  8. Do
  9. {
  10.   Write-Host Requesting next batch of $batchSize items, starting at $firstItem
  11.   $url = $urlPattern -f $firstItem, $batchSize
  12.   Write-Host $url
  13.   $request = [System.Net.WebRequest]::Create($url)
  14.   $request.UseDefaultCredentials = $true
  15.   $request.Accept = "application/json;odata=verbose"
  16.  
  17.   $response = $request.GetResponse()
  18.   $reader = New-Object System.IO.StreamReader $response.GetResponseStream()
  19.   $data = $reader.ReadToEnd()
  20.  
  21.   $result = ConvertFrom-Json -InputObject $data
  22.   $count = $result.d.results.Count
  23.   Write-Host Item count is $count
  24.   $results += $result.d.results
  25.   $firstItem += $batchSize
  26. }
  27. While ($count -eq $batchSize)
  28.  
  29. $results | select ProjectId, ProjectName, ProjectCreatedDate |
  30.       Export-Csv -Path ($reportPath + 'ProjectsFromProjectDataWithBatching.csv') -Delimiter ";" -Encoding UTF8 -NoTypeInformation

Although in this case both of the scripts have the same result, you can use the second version if you need the complete list of the projects at a later time, for example, you would like to extend it with data not available at the time of the query.

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

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 )

Google photo

You are commenting using your Google 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 )

Connecting to %s

Create a free website or blog at WordPress.com.

%d bloggers like this: