Second Life of a Hungarian SharePoint Geek

May 12, 2017

Getting a List of Checked-Out Projects from PowerShell via REST

Filed under: PowerShell, Project Server, REST — Tags: , , — Peter Holpar @ 05:22

We have an application running as a monthly scheduled batch process that updates enterprise fields of projects on our Project Server implementation based on values taken from various satellite systems. As a prerequisite, all affected projects should be checked-in. Projects checked-out to users won’t be updated. Of course, technically it would be possible to force check-in on those projects, but it was a business decision not to do that as we wanted to avoid data inconsistency by checking in a project that is not yet meant to be ready for that by the project manager.

Our application iterates through the projects, and if they are checked-in, it checks them out, updates the values, checks the project back and publishes it. If the project is checked-out, it sends a warning to the owner that the project was not updated due to its state. Occasionally project owners are doubting this warning, saying they are sure to check-in their projects, so I decided to create a sort-of report running just before starting the updating process to prove the list of projects left checked-out. It is something similar administrators already have on the Force Check-in Enterprise Objects page under PWA Settings.

image

Recently I wrote about how we can use PowerShell to create simple reports based on the data we query via the REST interface. This time I applied the very same technique to get the list of projects that are checked-out, including the name of the projects, the check-out description, checked-out time and the name and e-mail address of the user checked-out the project. The key was to assemble the REST query URL, including the $expand expression for the CheckedOutBy field.

$url = ‘http://YourProjectServerPWA/_api/ProjectServer/Projects?$expand=CheckedOutBy&$select=Name,CheckOutDescription,CheckedOutDate,CheckedOutBy/Title,CheckedOutBy/Email&$filter=IsCheckedOut’

$request = [System.Net.WebRequest]::Create($url)
$request.UseDefaultCredentials = $true
$request.Accept = ‘application/json;odata=verbose’

$response = $request.GetResponse()
$reader = New-Object System.IO.StreamReader $response.GetResponseStream()
$data = $reader.ReadToEnd()

$result = ConvertFrom-Json -InputObject $data

$result.d.results | % {
select -Input $_ -Prop `
    @{ Name=’Name’; Expression={$_.Name} },
    @{ Name=’User’; Expression={$_.CheckedOutBy.Title} },
    @{ Name=’EMail’; Expression={$_.CheckedOutBy.Email} },
    @{ Name=’Date’; Expression={[DateTime]::Parse($_.CheckedOutDate).ToString(‘g’)} },
    @{ Name=’Description’; Expression={$_.CheckOutDescription} }
    } | Export-Csv -Path CheckedOutProjects.csv -Delimiter ";" -Encoding UTF8 –NoTypeInformation

The result is a comma separated value (.csv) file, that one can open in Excel easily as well.

March 26, 2017

Generating Pseudo GUIDs for Your Project Server Entities

Filed under: PowerShell, Project Server, Tips & Tricks — Tags: , , — Peter Holpar @ 06:24

As you might have known, since the version 2013, Project Server utilizes pseudo-GUIDs to improve Project Server performance. These ones has the format of a “classical” GUID, but actually generated sequentially. As Microsoft states in this TechNet article:

"We handle GUIDs a little better in Project Server 2013 – and in many places they are sequential GUIDs which cause less index fragmentation"

This topic is quite good described in the Project Conference 2014 presentation Project Worst Practice – Learning from other peoples mistakes by Brian Smith. See the video recording between 6:08-13:54, or the slides 10-14.

One of the main components of the pseudo-GUID generation is the NewSequentialUid method of the Microsoft.Office.Project.Server.Library.PSUtility class:

public static Guid NewSequentialUid() 

  Guid guid; 
  if (NativeMethods.UuidCreateSequential(out guid) != 0) 
    return Guid.NewGuid(); 
  byte[] b = guid.ToByteArray(); 
  Array.Reverse((Array) b, 0, 4); 
  Array.Reverse((Array) b, 4, 2); 
  Array.Reverse((Array) b, 6, 2); 
  return new Guid(b); 
}

So if you want to use the same kind of pseudo-GUIDs for your own custom entities you create from code, you can get the IDs by invoking the method (for example, via PowerShell). The code sample below illustrates, how to get a single ID, or a batch of  IDs (in this case, 5 of them):

# load the necessary assembly
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.Office.Project.Shared")
# generate a single sequential ID
[Microsoft.Office.Project.Server.Library.PSUtility]::NewSequentialUid()
# or generate a range of sequential IDs, in this case, five of them
(1..5) | % { [Microsoft.Office.Project.Server.Library.PSUtility]::NewSequentialUid().Guid }

March 4, 2017

Using PowerShell and REST with Project Server (or SharePoint) for Reporting

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

If you are working with Project Server or SharePoint Server, you should not ignore the potential provided by PowerShell and the REST (OData) interface to create simple reports. You should although at the same time be aware of  a few pitfalls of this combination as well.

Let’s see the next code example first. Its goal is to output the list of projects to the screen, including their Id, Name and ProjectSiteUrl properties:

$url = "http://YourProjectServer/PWA/_api/ProjectServer/Projects?$select=Id,Name,ProjectSiteUrl"

$request = [System.Net.WebRequest]::Create($url)
$request.UseDefaultCredentials = $true
$request.Accept = "application/json;odata=verbose"

$response = $request.GetResponse()
$reader = New-Object System.IO.StreamReader $response.GetResponseStream()
$data = $reader.ReadToEnd()

$result = ConvertFrom-Json -InputObject $data
$result.d.results | select Id, Name, ProjectSiteUrl

If you test the URL http://YourProjectServer/PWA/_api/ProjectServer/Projects?$select=Id,Name,ProjectSiteUrl from the browser, you see, that all of these three properties are returned. However, if you run the above script from console, you find, that the ProjectSiteUrl column is empty for all of the projects.

If you use the ProjectData OData endpoint instead of the ProjectServer endpoint, and select the corresponding properties, all of the properties will be omitted by the script:

$url = "http://YourProjectServer/PWA/_api/Projects?$select=ProjectId,ProjectName,ProjectWorkspaceInternalUrl"

$request = [System.Net.WebRequest]::Create($url)
$request.UseDefaultCredentials = $true
$request.Accept = "application/json;odata=verbose"

$response = $request.GetResponse()
$reader = New-Object System.IO.StreamReader $response.GetResponseStream()
$data = $reader.ReadToEnd()

$result = ConvertFrom-Json -InputObject $data
$result.d.results | select ProjectId, ProjectName, ProjectWorkspaceInternalUrl

Note: If you have a localized version of Project Server, you can either use an OData query including the localized entity and property names, like:

http://YourProjectServer/PWA/_api/ProjectData/Projekte?$select=ProjektID,ProjektName,ProjektArbeitsbereichInterneURL

or switch back to the English version by injecting [en-US] segment after the ProjectData endpoint:

http://YourProjectServer/PWA/_api/ProjectData/[en-US]/Projects?$select=ProjectId,ProjectName,ProjectWorkspaceInternalUrl

Of course, in the first case you should change the property names used in the select statement in the PowerShell script to match the names used in the REST query.

Let’s see another example. In the next case, our goal is to create a .csv file, that one can easily import to Excel, including the name and the RBS (resource breakdown structure) of the resources.

  1. $baseUrl = "http://YourProjectServer/PWA/_api/ProjectServer"
  2. $rbsUrl = $baseUrl + "/LookupTables?$filter=Name eq 'RBS'&$expand=Entries&$select=Entries/InternalName,Entries/Value"
  3. $resourceUrl = $baseUrl + "/EnterpriseResources?$select=Name,Custom_000039b78bbe4ceb82c4fa8c0c400284"
  4.  
  5. #rbs
  6. $rbsRequest = [System.Net.WebRequest]::Create($rbsUrl)
  7. $rbsRequest.UseDefaultCredentials = $true
  8. $rbsRequest.Accept = "application/json;odata=verbose"
  9.  
  10. $rbsResponse = $rbsRequest.GetResponse()
  11. $rbsReader = New-Object System.IO.StreamReader $rbsResponse.GetResponseStream()
  12. $rbsData = $rbsReader.ReadToEnd()
  13.  
  14. $rbsResult = ConvertFrom-Json -InputObject $rbsData
  15. $rsbEntries = $rbsResult.d.results.Entries.results
  16.  
  17. #resources
  18. $resRequest = [System.Net.WebRequest]::Create($resourceUrl)
  19. $resRequest.UseDefaultCredentials = $true
  20. $resRequest.Accept = "application/json;odata=verbose"
  21.  
  22. $resResponse = $resRequest.GetResponse()
  23. $resReader = New-Object System.IO.StreamReader $resResponse.GetResponseStream()
  24. $resData = $resReader.ReadToEnd()
  25.  
  26. $resResult = ConvertFrom-Json -InputObject $resData
  27.  
  28. $resResult.d.results | % {
  29. select -Input $_ -Prop `
  30.     @{ Name='Name'; Expression={$_.Name} },
  31.     @{ Name='RBS'; Expression={$rbs = $_.Custom_x005f_000039b78bbe4ceb82c4fa8c0c400284; If ($rbs.results -is [System.Object[]]) {$rsbEntries | ? { $_.InternalName -eq $rbs.results[0] } | % { $_.Value } } Else {''} } }
  32.     } | Export-Csv -Path ResourceRBS.csv -Delimiter ";" -Encoding UTF8 -NoTypeInformation

Note: The –NoTypeInformation switch of Export-Csv ensures that no type information would be emitted as header into the .csv file. The -Delimiter ";" and the -Encoding UTF8 settings help to produce a .csv file in a format and encoding that can be opened in Excel simply by clicking on the file.

The symptoms are similar as in the first case, only the resource name is included in the file, but the RBS value not.

I’ve included this last code sample in a code block not just because it is a bit longer as the former ones, but because I help that the highlighting helps you to understand the base problem with our scripts, even if you did not catch it at the first example. Have you recognized, that the query options ($filter, $select and $expand) have a different color, as the rest of the query text? Actually, they have the very same color as the variable names (like $baseUrl or $resRequest) in the code. It is because they are handled really as variable names. Since we used double quotes in the code to define the string literals for URLs, and it means PowerShell should parse the string and replace possible variable names with the values of the variable. As we didn’t define variables like $filter, $select or $expand, they are simply removed from the string (replaced by an empty string). See this short explanation for details.

Instead of double quotation marks we should use single quotation marks to leave the query options intact, but in this case we should escape the single quotes (using two single quotation marks) used in the REST query itself.

For example, instead of:

$url = "http://YourProjectServer/PWA/_api/ProjectServer/Projects?$select=Id,Name,ProjectSiteUrl"

we should simply use:

$url = ‘http://YourProjectServer/PWA/_api/ProjectServer/Projects?$select=Id,Name,ProjectSiteUrl’

and instead of::

$rbsUrl = $baseUrl + "/LookupTables?$filter=Name eq ‘RBS’&$expand=Entries&$select=Entries/InternalName,Entries/Value"

we should use:

$rbsUrl = $baseUrl + ‘/LookupTables?$filter=Name eq ”RBS”&$expand=Entries&$select=Entries/InternalName,Entries/Value’

Note, that the value RBS is enclosed by two single quotation marks on both sides, and not by a double quotation mark!

Alternatively, you can use the double quotation marks to define the strings for the REST queries (for example, if you still would like PowerShell to parse it from some reason), but in this case, you should escape the dollar sign in the query options to disable parsing them out from the string.

For example, instead of:

$url = "http://YourProjectServer/PWA/_api/ProjectServer/Projects?$select=Id,Name,ProjectSiteUrl"

we should simply use:

$url = "http://YourProjectServer/PWA/_api/ProjectServer/Projects?`$select=Id,Name,ProjectSiteUrl"

and instead of::

$rbsUrl = $baseUrl + "/LookupTables?$filter=Name eq ‘RBS’&$expand=Entries&$select=Entries/InternalName,Entries/Value"

we should use:

$rbsUrl = $baseUrl + "/LookupTables?`$filter=Name eq ‘RBS’&`$expand=Entries&`$select=Entries/InternalName,Entries/Value"

See this description for more details about PowerShell string parsing and escaping methods.

If you compare our first two examples (the one with the ProjectServer and the other one with the ProjectData endpoint) the results are different, because in the first case the ProjectSiteUrl property is not part of the standard set of properties returned by default for projects via the ProjectServer endpoint, but ProjectData returns all properties, the ProjectWorkspaceInternalUrl property too, even if it is not specified in a $select query option.

In the third case, our query should have returned the entries of the RBS lookup table, but since the query options got lost, it simply return an overview about all lookup tables.

March 15, 2016

How to get the value of a Project Server Enterprise Custom Field via the Project Server Managed Client Object Model

Filed under: Managed Client OM, Project Server — Tags: , — Peter Holpar @ 22:20

About two years ago I posted a code about how to set the value of a Project Server Enterprise Field via the managed client OM. Again and again I get the question how to get the value, once it is set already.

In the first case I assume, you already know the ID of your project and the internal name of the field you would like to query. In this case, you need only send a single request to the server, as shown in this code:

  1. var url = @"http://YourProjectServer/pwa";
  2. var projectContext = new ProjectContext(url);
  3.  
  4. var projId = new Guid("98138ffd-d0fa-e311-83c6-005056b45654");
  5. var cfInternalName = "Custom_b278fdf35d16e4119568005056b45654";
  6.  
  7. var proj = projectContext.Projects.GetByGuid(projId);
  8. projectContext.Load(proj, p => p[cfInternalName], p => p.Name);
  9.  
  10. projectContext.ExecuteQuery();
  11.  
  12. Console.WriteLine(proj.Name, proj.FieldValues[cfInternalName]);

If either the ID of your project or the internal name of the field is unknown, you need an extra round-trip before the query shown in the previous code to determine their value. In the code below I assume you know none of these values:

  1. var url = @"http://YourProjectServer/pwa";
  2. var projectContext = new ProjectContext(url);
  3. var projName = "Your Project Name";
  4. var fieldName = "NameOfTheField";
  5.  
  6. projectContext.Load(projectContext.Projects, ps => ps.Include(p => p.Id, p => p.Name));
  7. projectContext.Load(projectContext.CustomFields, cfs => cfs.Include(cf => cf.InternalName, cf => cf.Name));
  8. projectContext.ExecuteQuery();
  9.  
  10. var projId = projectContext.Projects.First(p => p.Name == projName).Id;
  11. var cfInternalName = projectContext.CustomFields.First(cf => cf.Name == fieldName).InternalName;
  12.  
  13. var proj = projectContext.Projects.GetByGuid(projId);
  14. projectContext.Load(proj, p => p[cfInternalName], p => p.Name);
  15.  
  16. projectContext.ExecuteQuery();
  17.  
  18. Console.WriteLine(proj.Name, proj.FieldValues[cfInternalName]);

I hope it helps to read the custom field values, for example the values set by the code in the former post.

February 27, 2016

Getting the Item Count of all Lists of all Sub-Sites via a Single Request from Client Code

Filed under: Managed Client OM, OData, Project Server, SP 2013 — Tags: , , , — Peter Holpar @ 14:43

Recently I had a task to get the item count of all lists of all Project Web Sites (PWS) of a Project Server instance from a client-side application. Note, that the PWSs are located directly under the Project Web Access (PWA) site, so there is no deeper site structure in this task to deal with, so I was pretty sure that it can be achieved in a single request. Although in my case the task was Project Server related, one can use the same method in the case of SharePoint Server as well, it is only important, that you should not have a multiple level site structure, for a deeper site structure this method simply does not work.

I show you both the REST (OData) and the managed client object model approach. Let’s start with the client OM sample:

  1. string siteUrl = "http://YourProjectServer/PWA";
  2. using (var clientContext = new ClientContext(siteUrl))
  3. {
  4.     var rootWeb = clientContext.Web;
  5.  
  6.     clientContext.Load(rootWeb, rw => rw.Webs.Include(w => w.Title, w => w.ServerRelativeUrl, w => w.Lists.Include(l => l.Title, l => l.ItemCount)));
  7.     clientContext.ExecuteQuery();
  8.  
  9.     foreach(var web in rootWeb.Webs)
  10.     {
  11.         if (web.Lists.Any())
  12.         {
  13.             Console.WriteLine("Lists of web '{0}' [{1}]", web.Title, web.ServerRelativeUrl);
  14.             foreach (var list in web.Lists)
  15.             {
  16.                 Console.WriteLine("'{0}' [Item count: {1}]", list.Title, list.ItemCount);
  17.             }
  18.         }
  19.     }
  20. }

The corresponding REST query can be submitted as a GET request sent to this URL:

http://YourProjectServer/PWA/_api/web/webs?$expand=Lists&$select=ServerRelativeUrl,Title,Lists/ItemCount,Lists/Title

If you need the item count only from a specific list (for example, the lists with title ‘Risks’) for all subsites, you can easily achieve that in the client OM sample by including a Where clause in the query:

clientContext.Load(rootWeb, rw => rw.Webs.Include(w => w.Title, w => w.ServerRelativeUrl, w => w.Lists.Include(l => l.Title, l => l.ItemCount).Where(l => l.Title == "Risks")));

The corresponding REST query would be:

http://YourProjectServer/PWA/_api/web/webs?$expand=Lists&$filter=Lists/Title eq ‘Risks’&$select=ServerRelativeUrl,Title,Lists/ItemCount,Lists/Title

However, when submitting this request I get a response with status HTTP 400 and the message: The field or property ‘Title’ does not exist.

I’m working on a solution and update this post as soon as I found one. Feel free to help me by sending it as a comment. Winking smile

February 17, 2016

List View Threshold Causing Project Server Synchronization Failures

Filed under: Project Server — Tags: — Peter Holpar @ 22:01

Yesterday we found a lot of errors in the Project Server queue job history (PWA Settings / Manage Queue Jobs). The Job State was Failed But Not Blocking Correlation, the Job Type was of type Reporting (Project Sync).

image

To understand the reason, you should know that Project Server synchronizes changes in several lists from the project web sites (PWS) to its reporting database. This kind of synchronization is performed by the Project Server Queue Service (Microsoft.Office.Project.Server.Queuing.exe).

By clicking on the Click to view the error details link we became more information about the background of the problem.

Queue Job Error Details
General
Reporting Wss list sync failed
ReportingWssSyncListFailed (24018) – 101. Details: id=’24018′ name=’ReportingWssSyncListFailed’ uid=’5b39b9cd-bad4-e511-8c5c-005056b45654′ SPListType=’0e2d89a0-e57a-e411-9568-005056b45654′ Error=’101′.
Reporting message processor failed
ReportingWSSSyncMessageFailed (24016) – RDS failed while trying to sync one or more SP lists. The RDS queue message will be retried.. Details: id=’24016′ name=’ReportingWSSSyncMessageFailed’ uid=’6239b9cd-bad4-e511-8c5c-005056b45654′ QueueMessageBody=’ProjectUID=’0e2d89a0-e57a-e411-9568-005056b45654′. ForceFullSync=’False’. SynchronizationType=’Documents” Error=’RDS failed while trying to sync one or more SP lists. The RDS queue message will be retried.’.
Queue
GeneralQueueJobFailed (26000) – ReportingWSSSync.WSSSyncMessageEx. Details: id=’26000′ name=’GeneralQueueJobFailed’ uid=’ade7bdd3-bad4-e511-8c5c-005056b45654′ JobUID=’485a0869-b8d4-e511-8c5c-005056b45654′ ComputerName=’d30c825e-996d-4a92-a60d-1d66f5340b1b’ GroupType=’ReportingWSSSync’ MessageType=’WSSSyncMessageEx’ MessageId=’1′ Stage=” CorrelationUID=’0fca5f9d-75e8-507f-40b1-bf88f739c79d’. For more details, check the ULS logs on machine d30c825e-996d-4a92-a60d-1d66f5340b1b for entries with JobUID 485a0869-b8d4-e511-8c5c-005056b45654.

The value 101 in the Error property and the value Documents in the SynchronizationType suggest that it is something wrong with the synchronization of the document library list (101 is the ID of the document library list template).

The relevant part from the ULS logs:

02/16/2016 15:37:35.19     Microsoft.Office.Project.Server (0x136D8)    0x16F88    SharePoint Server                 General                           a2tj    Medium      RowLimit unset…using default RowLimit for query: <Where><Geq><FieldRef Name=’Modified’/><Value Type=’DateTime’ IncludeTimeValue=’TRUE’ StorageTZ=’TRUE’>2016-02-16T14:29:19</Value></Geq></Where>    0fca5f9d-75e8-507f-40b1-bf88f739c79d
02/16/2016 15:37:35.19     Microsoft.Office.Project.Server (0x136D8)    0x125EC    Project Server                    Project Server Database           ah91z    Medium      Successfully got the connection string (database name=[YourProjectServerDB], id=ae38d4f0-3d16-430b-8cec-c76290a640df, type=Consolidated). Requested access level=ReadWrite: Data Source=YourProjectServerServiceApp;Initial Catalog=YourProjectServerDB;Integrated Security=True;Enlist=False;Pooling=True;Min Pool Size=0;Max Pool Size=100;Connect Timeout=15    
02/16/2016 15:37:35.19     Microsoft.Office.Project.Server (0x136D8)    0x16F88    SharePoint Foundation             Health                            46ri    High        Throttled:Big list slow query. List item query elapsed time: 0 milliseconds, Additional data (if available): Query HRESULT: 80070024 List internal name, flags, and URL: {7903E344-47BC-42B5-A1BF-14E3E58D817F}, flags=0x0080000000001008, URL="
http://YourProjectServer/PWA/Proj1&quot; Current User: 1073741823 Query XML: "<Query><Where><Geq><FieldRef Name="Modified"/><Value Type="DateTime" IncludeTimeValue="TRUE" StorageTZ="TRUE">2016-02-16T14:29:19</Value></Geq></Where></Query>" SQL Query: "N/A"     0fca5f9d-75e8-507f-40b1-bf88f739c79d
02/16/2016 15:37:35.19     Microsoft.Office.Project.Server (0x136D8)    0x16F88    SharePoint Foundation             General                           xxpm    High        Unable to execute query: Error 0x80070024    0fca5f9d-75e8-507f-40b1-bf88f739c79d
02/16/2016 15:37:35.19     Microsoft.Office.Project.Server (0x136D8)    0x16F88    SharePoint Foundation             General                           8kh7    High        The attempted operation is prohibited because it exceeds the list view threshold enforced by the administrator.<nativehr>0x80070024</nativehr><nativestack></nativestack>    0fca5f9d-75e8-507f-40b1-bf88f739c79d
02/16/2016 15:37:35.19     Microsoft.Office.Project.Server (0x136D8)    0x16F88    SharePoint Foundation             General                           aix9j    High        SPRequest.GetListItemDataWithCallback2: UserPrincipalName=i:0).w|s-1-5-21-3634847118-1559816030-2180994487-3302, AppPrincipalName= ,pSqlClient=<null> ,bstrUrl=
http://YourProjectServer/PWA/Proj1 ,bstrListName={7903E344-47BC-42B5-A1BF-14E3E58D817F} ,bstrViewName=<null> ,bstrViewXml=<View Scope=’RecursiveAll’><Query><Where><Geq><FieldRef Name=’Modified’/><Value Type=’DateTime’ IncludeTimeValue=’TRUE’ StorageTZ=’TRUE’>2016-02-16T14:29:19</Value></Geq></Where></Query><ViewFields><FieldRef Name=’ID’/><FieldRef Name=’UniqueId’/><FieldRef Name=’FileRef’/><FieldRef Name=’FSObjType’/> ,fSafeArrayFlags=SAFEARRAYFLAG_NONE    0fca5f9d-75e8-507f-40b1-bf88f739c79d
02/16/2016 15:37:35.19     Microsoft.Office.Project.Server (0x136D8)    0x16F88    SharePoint Foundation             General                           ai1wu    Medium      System.Runtime.InteropServices.COMException: The attempted operation is prohibited because it exceeds the list view threshold enforced by the administrator.<nativehr>0x80070024</nativehr><nativestack></nativestack>, StackTrace:    at Microsoft.SharePoint.SPListItemCollection.EnsureListItemsData()     at Microsoft.SharePoint.SPListItemCollection.get_Count()     at Microsoft.Office.Server.Utilities.ContentIterator.ProcessListItems(SPList list, SPQuery query, ItemsProcessor itemsProcessor, ItemsProcessorErrorCallout errorCallout)     at Microsoft.Office.Project.Server.BusinessLayer.ReportingLayer.WSSDataTransfer.BuildIncrementalTransfer(Guid projectUid, SPList splist, SPChangeToken previousChangeToken, WSSListMetadata wssListMetadata, Int32& syncItemsCount)     at Microsoft.Office.Project.Ser…    0fca5f9d-75e8-507f-40b1-bf88f739c79d
02/16/2016 15:37:35.19*    Microsoft.Office.Project.Server (0x136D8)    0x16F88    SharePoint Foundation             General                           ai1wu    Medium      …ver.BusinessLayer.ReportingLayer.WSSDataTransfer.BuildTransferData(Guid projectUid, SPList splist, WSSListMetadata wssListMetadata, Boolean forceFullSync, Boolean& syncWasIncremental, Int32& syncItemsCount)     at Microsoft.Office.Project.Server.BusinessLayer.ReportingLayer.RdsWssSyncProcessor.TransferWSSListData(Guid projectUid, SPList splist, WSSListMetadata wssListMetadata, DateTime dtStartSyncTime, Boolean forceFullSync, SPChangeToken currentChangeToken)     at Microsoft.Office.Project.Server.BusinessLayer.ReportingLayer.RdsWssSyncProcessor.SyncWSSList(Guid projectUid, SPWeb spweb, WSSListMetadata wssListMetadata, Boolean forceFullSync)     at Microsoft.Office.Project.Server.BusinessLayer.ReportingLayer.RdsWssSyncProcessor.SyncWSSData(Guid projectUid, SPWeb spweb, WSSTransferMetadata w…    0fca5f9d-75e8-507f-40b1-bf88f739c79d
02/16/2016 15:37:35.19*    Microsoft.Office.Project.Server (0x136D8)    0x16F88    SharePoint Foundation             General                           ai1wu    Medium      …ssTransferMetadata, Boolean forceFullSync)     at Microsoft.Office.Project.Server.BusinessLayer.ReportingLayer.RdsWssSyncProcessor.runRDSTransformation(WSSSyncMessageEx wssSyncMessage)     at Microsoft.Office.Project.Server.BusinessLayer.ReportingLayer.RdsWssSyncProcessor.HandleMessage(Message msg, Group messageGroup, JobTicket jobTicket, MessageContext mContext)     at Microsoft.Office.Project.Server.BusinessLayer.Queue.ProcessMessageThread.ThreadEntry()     at Microsoft.Office.Project.Server.BusinessLayer.Queue.MessageProcessor.ProcessMessage(Message msg, MessageContext mContext, ProcessMessageThread processMessageThread)     at Microsoft.Office.Project.Server.BusinessLayer.Queue.MessageProcessor.HandleMessages()     at Microsoft.Office.Project.Server.BusinessLayer.Queue.MessageProcessor…    0fca5f9d-75e8-507f-40b1-bf88f739c79d
02/16/2016 15:37:35.19*    Microsoft.Office.Project.Server (0x136D8)    0x16F88    SharePoint Foundation             General                           ai1wu    Medium      ….ThreadEntry()     at Microsoft.Office.Project.Server.BusinessLayer.Queue.MessageProcessor.<>c__DisplayClassb.<Execute>b__a()     at Microsoft.Office.Project.Server.Library.SafeCallbackManager.RunCallbackAction(Action action, Func`1 safePredicate)     at Microsoft.Office.Project.Server.BusinessLayer.Queue.MessageProcessor.Execute(Group& nextGroupInCorrelation, JobTicket& nextGroupJobTicket)     at Microsoft.Office.Project.Server.BusinessLayer.Queue.Receiver.ProcessMessageGroup(CancellationToken schedulerCancellationToken, Group messageGroup, JobTicket jobTicket, Group& nextGroupInCorrelation, JobTicket& nextGroupJobTicket)     at Microsoft.Office.Project.Server.BusinessLayer.Queue.Receiver.ExecuteJobInternal(CancellationToken schedulerCancellationToken, Group& messageGroup)     at Microsof…    0fca5f9d-75e8-507f-40b1-bf88f739c79d
02/16/2016 15:37:35.19*    Microsoft.Office.Project.Server (0x136D8)    0x16F88    SharePoint Foundation             General                           ai1wu    Medium      …t.Office.Project.Server.BusinessLayer.Queue.Receiver.<>c__DisplayClass1d.<>c__DisplayClass21.<ExecuteJob>b__1b()     at Microsoft.Office.Project.Server.BusinessLayer.Queue.QueueUtils.RunActionWithIgnoreExceptionPredicate(Action action, Predicate`1 ignoreExceptionPredicate, Boolean& isIgnorableException)     at Microsoft.Office.Project.Server.BusinessLayer.Queue.Receiver.<>c__DisplayClass1d.<ExecuteJob>b__1a()     at Microsoft.Office.Project.Server.Library.SafeCallbackManager.RunCallbackAction(Action action, Func`1 safePredicate)     at Microsoft.Office.Project.Server.BusinessLayer.Queue.Receiver.ExecuteJob(CancellationToken schedulerCancellationToken, Int32 siteJobCount)     at Microsoft.Office.Project.Server.Services.SiteQueue.<>c__DisplayClass2.<ExecuteJob>b__1()     at Microsoft.Office….    0fca5f9d-75e8-507f-40b1-bf88f739c79d
02/16/2016 15:37:35.19*    Microsoft.Office.Project.Server (0x136D8)    0x16F88    SharePoint Foundation             General                           ai1wu    Medium      …Project.Server.Library.SafeCallbackManager.RunCallbackAction(Action action, Func`1 safePredicate)     at Microsoft.Office.Project.Server.Services.SiteQueue.ExecuteJob(CancellationToken ct, Int32 siteJobCount)     at Microsoft.Office.Project.Server.Services.QueueJobRunner.ExecuteSiteJob(Object data)     at System.Threading.Tasks.Task.Execute()     at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)     at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)     at System.Threading.Tasks.Task.ExecuteWithThreadLocal(Task& currentTaskSlot)     at System.Threading.Tasks.Task.ExecuteEntry(Boolean bPreventDoubleExecut…    0fca5f9d-75e8-507f-40b1-bf88f739c79d
02/16/2016 15:37:35.19*    Microsoft.Office.Project.Server (0x136D8)    0x16F88    SharePoint Foundation             General                           ai1wu    Medium      …ion)     at System.Threading.ThreadPoolWorkQueue.Dispatch()      0fca5f9d-75e8-507f-40b1-bf88f739c79d
02/16/2016 15:37:35.19     Microsoft.Office.Project.Server (0x136D8)    0x16F88    SharePoint Foundation             Health                            46ri    High        big list slow query, list:{7903E344-47BC-42B5-A1BF-14E3E58D817F}  viewname: viewxml: <View Scope=’RecursiveAll’><Query><Where><Geq><FieldRef Name=’Modified’/><Value Type=’DateTime’ IncludeTimeValue=’TRUE’ StorageTZ=’TRUE’>2016-02-16T14:29:19</Value></Geq></Where></Query><ViewFields><FieldRef Name=’ID’/><FieldRef Name=’UniqueId’/><FieldRef Name=’FileRef’/><FieldRef Name=’FSObjType’/><FieldRef Name=’Author’/><FieldRef Name=’Created’/><FieldRef Name=’Editor’/><FieldRef Name=’Modified’/><FieldRef Name=’Title’/></ViewFields><RowLimit Paged="TRUE">2000</RowLimit></View>    0fca5f9d-75e8-507f-40b1-bf88f739c79d
02/16/2016 15:37:35.19     Microsoft.Office.Project.Server (0x136D8)    0x16F88    Project Server                    Reporting                         auos    Warning     Standard Information:PSI Entry Point:   Project User: PROJECTSERVER\system  Correlation Id: 495a0869-b8d4-e511-8c5c-005056b45654  PWA Site URL:
http://YourProjectServer/PWA/Proj1  SA Name: ProjectServerApplication  PSError: NoError (0) RDS: Problem transferring SharePoint data to the Project Server. Unable to prepare the incremental transfer data for the SharePoint list  ‘101’ associated with the project ‘0e2d89a0-e57a-e411-9568-005056b45654’. Error: The attempted operation is prohibited because it exceeds the list view threshold enforced by the administrator..  Full transfer will be used.    0fca5f9d-75e8-507f-40b1-bf88f739c79d
02/16/2016 15:37:35.19     Microsoft.Office.Project.Server (0x136D8)    0x16F88    Project Server                    Reporting                         auos    High        PWA:
http://YourProjectServer/PWA/Proj1, ServiceApp:ProjectServerApplication, User:PROJECTSERVER\system, PSI: ULS Event: WSSIncrementalTransferFailed, tag 1635086195 was associated with exception: Microsoft.SharePoint.SPQueryThrottledException: The attempted operation is prohibited because it exceeds the list view threshold enforced by the administrator. —> System.Runtime.InteropServices.COMException: The attempted operation is prohibited because it exceeds the list view threshold enforced by the administrator.<nativehr>0x80070024</nativehr><nativestack></nativestack>     at Microsoft.SharePoint.Library.SPRequestInternalClass.GetListItemDataWithCallback2(IListItemSqlClient pSqlClient, String bstrUrl, String bstrListName, String bstrViewName, String bstrViewXml, SAFEARRAYFLAGS fSafeAr…    0fca5f9d-75e8-507f-40b1-bf88f739c79d
02/16/2016 15:37:35.19*    Microsoft.Office.Project.Server (0x136D8)    0x16F88    Project Server                    Reporting                         auos    High        …rayFlags, ISP2DSafeArrayWriter pSACallback, ISPDataCallback pPagingCallback, ISPDataCallback pPagingPrevCallback, ISPDataCallback pFilterLinkCallback, ISPDataCallback pSchemaCallback, ISPDataCallback pRowCountCallback, Boolean& pbMaximalView)     at Microsoft.SharePoint.Library.SPRequest.GetListItemDataWithCallback2(IListItemSqlClient pSqlClient, String bstrUrl, String bstrListName, String bstrViewName, String bstrViewXml, SAFEARRAYFLAGS fSafeArrayFlags, ISP2DSafeArrayWriter pSACallback, ISPDataCallback pPagingCallback, ISPDataCallback pPagingPrevCallback, ISPDataCallback pFilterLinkCallback, ISPDataCallback pSchemaCallback, ISPDataCallback pRowCountCallback, Boolean& pbMaximalView)     — End of inner exception stack trace —     at Microsoft.SharePoint.SPGlobal.HandleThrottleException(…    0fca5f9d-75e8-507f-40b1-bf88f739c79d
02/16/2016 15:37:35.19*    Microsoft.Office.Project.Server (0x136D8)    0x16F88    Project Server                    Reporting                         auos    High        …COMException comEx)     at Microsoft.SharePoint.Library.SPRequest.GetListItemDataWithCallback2(IListItemSqlClient pSqlClient, String bstrUrl, String bstrListName, String bstrViewName, String bstrViewXml, SAFEARRAYFLAGS fSafeArrayFlags, ISP2DSafeArrayWriter pSACallback, ISPDataCallback pPagingCallback, ISPDataCallback pPagingPrevCallback, ISPDataCallback pFilterLinkCallback, ISPDataCallback pSchemaCallback, ISPDataCallback pRowCountCallback, Boolean& pbMaximalView)     at Microsoft.SharePoint.SPListItemCollection.EnsureListItemsData()     at Microsoft.SharePoint.SPListItemCollection.get_Count()     at Microsoft.Office.Server.Utilities.ContentIterator.ProcessListItems(SPList list, SPQuery query, ItemsProcessor itemsProcessor, ItemsProcessorErrorCallout errorCallout)     at Microsoft.Office.P…    0fca5f9d-75e8-507f-40b1-bf88f739c79d
02/16/2016 15:37:35.19*    Microsoft.Office.Project.Server (0x136D8)    0x16F88    Project Server                    Reporting                         auos    High        …roject.Server.BusinessLayer.ReportingLayer.WSSDataTransfer.BuildIncrementalTransfer(Guid projectUid, SPList splist, SPChangeToken previousChangeToken, WSSListMetadata wssListMetadata, Int32& syncItemsCount)     at Microsoft.Office.Project.Server.BusinessLayer.ReportingLayer.WSSDataTransfer.BuildTransferData(Guid projectUid, SPList splist, WSSListMetadata wssListMetadata, Boolean forceFullSync, Boolean& syncWasIncremental, Int32& syncItemsCount), LogLevelManager Warning-ulsID:0x61756F73 has no entities explicitly specified.    0fca5f9d-75e8-507f-40b1-bf88f739c79d

The most important message from the logs:

The attempted operation is prohibited because it exceeds the list view threshold enforced by the administrator

That means, the value set for the List View Threshold (configurable in Resource Throttling in Web Application Management site of the Central Administration, defaults to 5000) is less than the document count in the library. You should increase the value to re-enable synchronization, using PowerShell, for example:

$webApp = Get-SPWebApplication http://YourProjectServer
$webApp.MaxItemsPerThrottledOperation = 10000
$webApp.Update()

Or alternatively convince your users not to store so many files in the document library.

February 9, 2016

Display Closed Risks Using Strikethrough Text on Project Server

As you probably have already seen, the name of the closed tasks (the ones having 100% completeness) is displayed using a strikethrough text in the All Tasks view of a task list in SharePoint. For example, from the tasks below, only Task 2 is 100 % complete.

image

Our users would like to have the same functionality in the Risks list on Project Server project sites, but out of the box, the title of the closed risks (ones having Status = "(3) Closed") is displayed without strikethrough, using the same formatting as any other risks:

image

Note: I assume you are familiar with client-side rendering. If not, and would like to understand how our solution works, I suggest you to read about it first, for example in the excellent post of Chris O’Brien.

After a short research, I found that this functionality of the Task lists is implemented in 15\TEMPLATE\LAYOUTS\hierarchytaskslist.debug.js. See the CompletedTitleTemplate template in that .js file.

Based on that template it was easy to implement the required functionality:

  1. (function () {
  2.     if (typeof window.CompletedRiskTitleTemplate == "object") {
  3.         return;
  4.     }
  5.     window.CompletedRiskTitleTemplate = {
  6.         RenderTitleField: function (inCtx, field, listItem, listSchema) {
  7.             var titleHtml = ComputedFieldWorker[field.Name](inCtx, field, listItem, listSchema);
  8.  
  9.             var result = (listItem["Status"] == "(3) Closed") ?
  10.                  '<span style="text-decoration: line-through">' + titleHtml + '</span>' :
  11.                  titleHtml;
  12.  
  13.             return result;
  14.         }
  15.     };
  16.     function _registerCompletedRiskTitleTemplate() {
  17.         var TitleFieldContext = {
  18.             Templates: {
  19.                 Fields: {
  20.                     'LinkTitle': {
  21.                         'View': window.CompletedRiskTitleTemplate.RenderTitleField
  22.                     }
  23.                 },
  24.                 ListTemplateType: 1101
  25.             }
  26.         };
  27.  
  28.         SPClientTemplates.TemplateManager.RegisterTemplateOverrides(TitleFieldContext);
  29.     }
  30.     ExecuteOrDelayUntilScriptLoaded(_registerCompletedRiskTitleTemplate, 'clienttemplates.js');
  31. })();

Note, that in this case we are using ListTemplateType 1101 for the Risks list instead of the value 171 for the original Task list type (Tasks with Timeline and Hierarchy to be exact). We get this list template value using the BaseTemplate property of our Risks list.

To ensure that the script is loaded on all views that include the Title field, we should set the JSLink property of the field with InternalName LinkTitle”.

Assuming you deployed your .js file to a path under the layout folder as /YourHive/js/strikeThroughClosedRisks.js, you can register your script using the following PowerShell code:

$web = Get-SPWeb http://YourProjServer/PWA/Proj1
$list = $web.Lists["Risks"]

$field = $list.Fields.GetFieldByInternalName("LinkTitle")
$field.JSLink = "~sitecollectionlayouts/YourHive/js/strikeThroughClosedRisks.js"
$field.Update()

Of course, this script affects only the web site of the project Proj1. If you would like to deploy it to all of your projects, you should iterate through the project web sites, but even better, you can prepare a project web site template based on this PWS in advance as described in my post last year, and use this template for your projects.

After successfully deploying our script, the text of the Title field of the closed risk is display using a strikethrough:

image

July 23, 2015

Managing Project Server Views via PSI from PowerShell

Filed under: ALM, PowerShell, Project Server, PSI — Tags: , , , — Peter Holpar @ 07:17

If you would like to manage Project Server views from code you will find very few helpful resources (if any) on the web. The object models simply do not include classes related to this (neither on the server side nor on the client side). Although the PSI contains a View service, it is intended for internal use. Of course, that intention could not stop us to use the service at our own risk. Below I give you some useful code samples to illustrate the usage of the View service.

First of all, we create the proxy assembly, load the required Microsoft.Office.Project.Server.Library assembly in the process as well, and define some shortcuts to make it easier to reference enum and property values later on.

$pwaUrl = "http://YourProjectServer/pwa&quot;
$svcPSProxy = New-WebServiceProxy -Namespace PSIProxy -Uri ($pwaUrl + "/_vti_bin/PSI/View.asmx?wsdl") -UseDefaultCredential
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.Office.Project.Server.Library")
$ViewConstants = [Microsoft.Office.Project.Server.Library.ViewConstants]
$ViewType = [Microsoft.Office.Project.Server.Library.ViewConstants+ViewType]

If you now the unique ID of your view, it is easy to display all of the fields and security categories associated with the view:

$viewId = [Guid]"63d3499e-df27-401c-af58-ebb9607beae8"
$view = $svcPSProxy.ReadView($viewId)
$view.ViewReportFields | % { $_.CONV_STRING }
$view.SecurityCategoryObjects | % { $_.WSEC_CAT_NAME }

If the view ID is unknown, you can get it based on the name and type of the view:

$viewName = "Your Report"
$viewType = $ViewType::PORTFOLIO

$views = $svcPSProxy.ReadViewSummaries()
$viewId = ($views.ViewReports | ? { $_.WVIEW_NAME -eq $viewName -and $_.WVIEW_TYPE -eq $viewType }).WVIEW_UID

You can list all of the views:

$views = $svcPSProxy.ReadViewSummaries()
$views.ViewReports | % {
  Write-Host $_.WVIEW_NAME ($ViewType$_.WVIEW_TYPE)
}

To change the order of the first two fields in the view:

$view = $svcPSProxy.ReadView($viewId)
$view.ViewReportFields[0].WVIEW_FIELD_ORDER = 1
$view.ViewReportFields[1].WVIEW_FIELD_ORDER = 0
$svcPSProxy.UpdateView($view)

To change the order of two arbitrary fields (based on their name) in the view:

$fieldName1 = "Finish"
$fieldName2 = "Owner"
$view = $svcPSProxy.ReadView($viewId)
$field1 = $view.ViewReportFields | ? { $_.CONV_STRING -eq $fieldName1 }
$field2 = $view.ViewReportFields | ? { $_.CONV_STRING -eq $fieldName2 }
$field1Order = $field1.WVIEW_FIELD_ORDER
$field2Order = $field2.WVIEW_FIELD_ORDER
$field1.WVIEW_FIELD_ORDER = $field2Order
$field2.WVIEW_FIELD_ORDER = $field1Order
$svcPSProxy.UpdateView($view)

To remove a field from a view:

$fieldToRemoveName = "Ende"
$view = $svcPSProxy.ReadView($viewId)
$fieldToRemove = $view.ViewReportFields | ? { $_.CONV_STRING -eq $fieldToRemoveName }
$fieldToRemove.Delete()
$svcPSProxy.UpdateView($view)

To delete the view itself:

[Void]$svcPSProxy.DeleteViewReports($viewId)

To create a new view using an existing view as a template:

$newViewName = "New View"
[Void]$svcPSProxy.CopyViewReports($viewId, $newViewName)
$newView = $svcPSProxy.ReadViewSummaries().ViewReports | ? { $_.WVIEW_NAME -eq $newViewName -and $_.WVIEW_TYPE -eq $viewType }

To list all of the fields available in a given type (in this case, for tasks):

$svcPSProxy.ReadProjectFields($ViewConstants::ViewTABLE_TASK_UID ).ViewFields | % { $_.CONV_STRING }

To append a new field at the end of the fields in the view:

$fieldToAppendName = "% Work Complete"

$fieldToAppend = $svcPSProxy.ReadProjectFields($ViewConstants::ViewTABLE_TASK_UID ).ViewFields | ? { $_.CONV_STRING -eq $fieldToAppendName }
$view = $svcPSProxy.ReadView($viewId)
$maxFieldOrder = ($view.ViewReportFields | % { $_.WVIEW_FIELD_ORDER } | measure -Maximum).Maximum

$newField = $view.ViewReportFields.NewViewReportFieldsRow()

$newField.WFIELD_UID = $fieldToAppend.WFIELD_UID
$newField.CONV_STRING = $fieldToAppend.CONV_STRING
$newField.WFIELD_TEXTCONV_TYPE = $fieldToAppend.WFIELD_TEXTCONV_TYPE
$newField.WTABLE_UID = $fieldToAppend.WTABLE_UID
$newField.WFIELD_IS_CUSTOM_FIELD = $fieldToAppend.WFIELD_IS_CUSTOM_FIELD
$newField.WFIELD_NAME_SQL = $fieldToAppend.WFIELD_NAME_SQL
$newField.WFIELD_IS_MULTI_VALUE = $fieldToAppend.WFIELD_IS_MULTI_VALUE
$newField.WFIELD_LOOKUP_TABLE_UID = $fieldToAppend.WFIELD_LOOKUP_TABLE_UID
$newField.WVIEW_UID = $view.ViewReports.WVIEW_UID
$newField.WVIEW_FIELD_ORDER = $maxFieldOrder + 1
$newField.WVIEW_FIELD_WIDTH = 100
$newField.WVIEW_FIELD_AUTOSIZE = 1
$newField.WVIEW_FIELD_CUSTOM_LABEL = [System.DBNull]::Value
$newField.WVIEW_FIELD_IS_READ_ONLY = 0

$view.ViewReportFields.AddViewReportFieldsRow($newField)
$svcPSProxy.UpdateView($view)

To inject a new field in the view before another field having a specified name:

$fieldInjectBeforeName = "% Complete"
$fieldToInjectName = "% Work Complete"

$fieldToInject = $svcPSProxy.ReadProjectFields($ViewConstants::ViewTABLE_TASK_UID ).ViewFields | ? { $_.CONV_STRING -eq $fieldToInjectName }

$view = $svcPSProxy.ReadView($viewId)

$fieldInjectBeforeOrder = ($view.ViewReportFields | ? { $_.CONV_STRING -eq $fieldInjectBeforeName }).WVIEW_FIELD_ORDER

$view.ViewReportFields | ? { $_.WVIEW_FIELD_ORDER -ge $fieldInjectBeforeOrder } | % { $_.WVIEW_FIELD_ORDER++ }

$newField = $view.ViewReportFields.NewViewReportFieldsRow()

$newField.WFIELD_UID = $fieldToInject.WFIELD_UID
$newField.CONV_STRING = $fieldToInject.CONV_STRING
$newField.WFIELD_TEXTCONV_TYPE = $fieldToInject.WFIELD_TEXTCONV_TYPE
$newField.WTABLE_UID = $fieldToInject.WTABLE_UID
$newField.WFIELD_IS_CUSTOM_FIELD = $fieldToInject.WFIELD_IS_CUSTOM_FIELD
$newField.WFIELD_NAME_SQL = $fieldToInject.WFIELD_NAME_SQL
$newField.WFIELD_IS_MULTI_VALUE = $fieldToInject.WFIELD_IS_MULTI_VALUE
$newField.WFIELD_LOOKUP_TABLE_UID = $fieldToInject.WFIELD_LOOKUP_TABLE_UID
$newField.WVIEW_UID = $view.ViewReports.WVIEW_UID
$newField.WVIEW_FIELD_ORDER = $fieldInjectBeforeOrder
$newField.WVIEW_FIELD_WIDTH = 100
$newField.WVIEW_FIELD_AUTOSIZE = 1
$newField.WVIEW_FIELD_CUSTOM_LABEL = [System.DBNull]::Value
$newField.WVIEW_FIELD_IS_READ_ONLY = 0

$view.ViewReportFields.AddViewReportFieldsRow($newField)
$svcPSProxy.UpdateView($view)

The last code sample shows how to create a new Gantt-view from scratch, appending a single field and a single security category to it:

$viewRepDS = New-Object PSIProxy.PWAViewReportsDataSet
$newView = $viewRepDS.ViewReports.NewViewReportsRow()
$newView.WVIEW_UID = [Guid]::NewGuid()
$newView.WVIEW_NAME = "New Report 2"
$newView.WVIEW_DESCRIPTION = "Test report description"

$fieldToAppendName = "% Arbeit abgeschlossen"

$fieldToAppend = $svcPSProxy.ReadProjectFields($ViewConstants::ViewTABLE_TASK_UID ).ViewFields | ? { $_.CONV_STRING -eq $fieldToAppendName }

$newField = $viewRepDS.ViewReportFields.NewViewReportFieldsRow()

$newField.WFIELD_UID = $fieldToAppend.WFIELD_UID
$newField.CONV_STRING = $fieldToAppend.CONV_STRING
$newField.WFIELD_TEXTCONV_TYPE = $fieldToAppend.WFIELD_TEXTCONV_TYPE
$newField.WFIELD_IS_CUSTOM_FIELD = $fieldToAppend.WFIELD_IS_CUSTOM_FIELD
$newField.WFIELD_NAME_SQL = $fieldToAppend.WFIELD_NAME_SQL
$newField.WFIELD_IS_MULTI_VALUE = $fieldToAppend.WFIELD_IS_MULTI_VALUE
$newField.WFIELD_LOOKUP_TABLE_UID = $fieldToAppend.WFIELD_LOOKUP_TABLE_UID
$newField.WVIEW_UID = $newView.WVIEW_UID
$newField.WVIEW_FIELD_ORDER = 0
$newField.WVIEW_FIELD_WIDTH = 100
$newField.WVIEW_FIELD_AUTOSIZE = 1
$newField.WVIEW_FIELD_CUSTOM_LABEL = [System.DBNull]::Value
$newField.WVIEW_FIELD_IS_READ_ONLY = 0
$viewRepDS.ViewReportFields.AddViewReportFieldsRow($newField)

$newSecCat = $viewRepDS.SecurityCategoryObjects.NewSecurityCategoryObjectsRow()
$newSecCat.WSEC_CAT_UID = [Microsoft.Office.Project.Server.Library.PSSecurityCategory]::MyProjects
$newSecCat.WSEC_OBJ_TYPE_UID = [Microsoft.Office.Project.Server.Library.PSSecurityObjectType]::View
$newSecCat.WSEC_OBJ_UID = $newView.WVIEW_UID
$viewRepDS.SecurityCategoryObjects.AddSecurityCategoryObjectsRow($newSecCat)

$newView.WVIEW_TYPE = $ViewType::PORTFOLIO
$newView.WVIEW_DISPLAY_TYPE = $ViewConstants::ViewDISPLAYTYPE_GANTT
$newView.WGANTT_SCHEME_UID =  $ViewConstants::GanttSchemeUidProjectCenter
$newView.WVIEW_SPLITTER_POS = 250
#  Group by (see [pub].[MSP_WEB_GROUP_SCHEMES] table in Project DB for possible values)
$newView.WGROUP_SCHEME_UID = [Guid]::Empty

$viewRepDS.ViewReports.AddViewReportsRow($newView)
$svcPSProxy.UpdateView($viewRepDS)

July 22, 2015

Create Project Server Enterprise Custom Fields via PSI from PowerShell

Filed under: ALM, PowerShell, Project Server, PSI — Tags: , , , — Peter Holpar @ 22:38

Last year I already wrote about how one can manage the Project Server Enterprise Custom Fields via the Managed Client Object Modell. We could transfer the code samples of that post from C# to PowerShell, but because of the limitations of the Managed Client Object Modell I use the PSI interface instead in this case. What are those limitations? Not all of the properties available in PSI are exposed by the Client OM, see for example the MD_PROP_SUMM_GRAPHICAL_INDICATOR field, that we can use to set the rules of graphical indicators defined for the fields. I’ll show you an example for getting and setting the indicator rules in a later post, in the current one I only show you the technique we can use to create the Enterprise Custom Fields via PSI.

One can find an existing description with code sample in Step 3 and 4 of this post, that achieves the same goal, however, I don’t like that approach for several reasons, for example, because of  we have to generate the proxy assembly based on the WSDL in the code itself. Instead of that I find the following code much more simple:

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.Office.Project.Server.Library")

$pwaUrl = "http://YourProjectServer/pwa&quot;

# create shortcuts
#
http://stackoverflow.com/a/1049010
$PSDataType = [Microsoft.Office.Project.Server.Library.PSDataType]
$Entities = [Microsoft.Office.Project.Server.Library.EntityCollection]::Entities

$svcPSProxy = New-WebServiceProxy -Namespace PSIProxy -Uri ($pwaUrl + "/_vti_bin/psi/CustomFields.asmx?wsdl") -UseDefaultCredential

$customFieldDataSet = New-Object PSIProxy.CustomFieldDataSet 

$customFieldRow = $customFieldDataSet.CustomFields.NewCustomFieldsRow()   
$customFieldRow.MD_PROP_UID = [Guid]::NewGuid()
$customFieldRow.MD_PROP_NAME = "Custom Project Field"
$customFieldRow.MD_PROP_TYPE_ENUM = $PSDataType::STRING
$customFieldRow.MD_ENT_TYPE_UID = $Entities.ProjectEntity.UniqueId
$customFieldRow.MD_PROP_IS_REQUIRED = $false
$customFieldRow.MD_PROP_IS_LEAF_NODE_ONLY = $false
$customFieldRow.MD_PROP_DESCRIPTION = "Test Field Desc."
$customFieldRow.SetMD_LOOKUP_TABLE_UIDNull()
$customFieldRow.SetMD_PROP_DEFAULT_VALUENull()
$customFieldDataSet.CustomFields.AddCustomFieldsRow($customFieldRow)

$svcPSProxy.CreateCustomFields($customFieldDataSet, $false, $true)

If you have casting issues when using the Namespace parameter of the New-WebServiceProxy cmdlet, you should read this post.

Creating a PowerShell-based Monitoring and Alerting System for Project Server

Filed under: PowerShell, Project Server — Tags: , — Peter Holpar @ 22:08

A few months ago I published a post about how to find the jobs in the Project Server queue programmatically. In the current post I will show you, how can you use PowerShell to track the number of jobs in queue, and send an e-mail alert, if the count is higher than a predefined limit for a longer period. Although the example in this post is Project Server specific, you can use the same technique to create other types of alerts as well.

Since the PowerShell script will be run by Windows Task Scheduler (for example on a 5-minute schedule) it was an important question, how to solve the communication between the runs. For example, how the current session can find out, since when the counter is higher than the limit? Of course, if the limit is reached, and we have already sent a mail, we would not like to send further mails for every and each runs till the counter is higher than the limit. But how to inform the forthcoming sessions from the current session, that we have sent a mail? Of course, there are many possible solutions for this problem. We could use a database, or a file (either XML or any custom format) to persist the information between the sessions. I’ve chosen an even simpler approach. I’ve create empty files (QueueLimitReached.txt and MailSent.txt), and check their existence and / or creation date to check when the limit has been reached and if the alert mail has been already sent. If the counter goes below the limit again, I simply delete these semaphore files.

Having this background, the script itself should be already straightforward.

  1. Add-PSSnapin "Microsoft.SharePoint.PowerShell"
  2.  
  3. $folderPath = "D:\ScheduledTasks\"
  4. $limitReachedFileName = "QueueLimitReached.txt"
  5. $mailSentFileName = "MailSent.txt"
  6. $ageOfFileLimit = 15 # in minutes
  7. $counterValueLimit = 50
  8.  
  9. $emailTo = "admins@company.com"
  10. $emailCc = "helpdesk@company.com;projmans@company.com"
  11. $emailSubject = "Project Server Queue Alert"
  12. $emailBody = @"
  13. Hi,
  14.  
  15. the count of the jobs in the Project Server Queue is very high. Please, fix the issue!
  16.  
  17. Regards,
  18. The PowerShell Monitor
  19.   "@
  20.  
  21. $limitReachedFilePath = $folderPath + $limitReachedFileName
  22. $mailSentFilePath = $folderPath + $mailSentFileName
  23.  
  24. function HasAlertState()
  25. {
  26.   $counter = Get-Counter -Counter "\ProjectServer:QueueGeneral(_Total)\Current Unprocessed Jobs"
  27.   $counterValue = $counter.CounterSamples[0].CookedValue
  28.   return ($counterValue -gt $counterValueLimit)
  29. }
  30.  
  31. function SendAlert()
  32. {   
  33.   $globalAdmin = New-Object Microsoft.SharePoint.Administration.SPGlobalAdmin
  34.  
  35.   $smtpMail = New-Object Net.Mail.MailMessage
  36.   $smtpMail.From = $globalAdmin.MailFromAddress
  37.   $smtpMail.Subject = $emailSubject
  38.   $smtpMail.Body = $emailBody
  39.   $emailTo.Split(";") | % { $mailAddr = New-Object Net.Mail.MailAddress($_); $smtpMail.To.Add($mailAddr) }
  40.   $emailCc.Split(";") | % { $mailAddr = New-Object Net.Mail.MailAddress($_); $smtpMail.Cc.Add($mailAddr) }
  41.   $smtpMail.ReplyTo = New-Object Net.Mail.MailAddress($globalAdmin.MailReplyToAddress)
  42.   $smtpMail.BodyEncoding = [System.Text.Encoding]::GetEncoding($globalAdmin.MailCodePage)
  43.   $smtpMail.SubjectEncoding = [System.Text.Encoding]::GetEncoding($globalAdmin.MailCodePage)
  44.  
  45.   $smtpClient = New-Object Net.Mail.SmtpClient($globalAdmin.OutboundSmtpServer)
  46.   $smtpClient.Send($smtpMail)
  47. }
  48.  
  49. $alertCondition = HasAlertState
  50.  
  51. If ($alertCondition)
  52. {
  53.   If (Test-Path $limitReachedFilePath)
  54.   {
  55.     $creationTime = (Get-ChildItem $limitReachedFilePath).CreationTime
  56.     $ageOfFile = ([DateTime]::Now – $creationTime).Minutes
  57.     Write-Host $ageOfFile
  58.     If ($ageOfFile -gt $ageOfFileLimit)
  59.     {
  60.       Write-Host Limit reached
  61.       If (-not (Test-Path $mailSentFilePath))
  62.       {
  63.         Write-Host Mail has not yet been sent. Send it now.
  64.         SendAlert
  65.         # suppress return value via casting it to null
  66.         [void] (New-Item -name $mailSentFileName -path $folderPath -itemType File)
  67.       }
  68.     }
  69.   }
  70.   # create a new file, if no former one exists
  71.   else
  72.   {
  73.     If (-not (Test-Path $limitReachedFilePath))
  74.     {
  75.       # suppress return value via casting it to null
  76.       [void] (New-Item -name $limitReachedFileName -path $folderPath -itemType File)
  77.     }
  78.   }
  79. }
  80. # delete the former files, if they exist
  81. Else
  82. {
  83.   If (Test-Path $limitReachedFilePath)
  84.   {
  85.     Remove-Item $limitReachedFilePath
  86.   }
  87.   If (Test-Path $mailSentFilePath)
  88.   {
  89.     Remove-Item $mailSentFilePath
  90.   }
  91. }

In the sample we check the value of the Current Unprocessed Jobs counter of Project Server. You can easily change the limit of  job count (50), and the time period (15 minutes) in the code, or customize the addressees, subject and body of the mail. If you would like to create other types of alerts, you should simply implement your own version of the HasAlertState method.

Older Posts »

Blog at WordPress.com.