Update #ProjectOnline Resource custom field values using #PowerShell with data from #AzureAD user attributes #PPM #Offce365 #ProjectServer #CSOM

November 7, 2016 at 9:40 pm | Posted in Add-on, Administration, Configuration, Customisation, Functionality, Information, PowerShell, Reporting | Leave a comment
Tags: , , , , , , , ,

This blog post will demonstrate a simple example of how Microsoft’s PPM tool, Project Online, can include / sync metadata from Azure AD user attributes to resource custom fields in Project Online. This example script will update the resource custom field “Job Title” on my test tenant with the data from the Azure AD user “Title” attribute.

This script example can be downloaded here: https://gallery.technet.microsoft.com/Update-Online-Resource-83137b7f

The script does require some additional modules / DLLs to work. Firstly you will need the Azure AD module installed, this can be downloaded here: http://connect.microsoft.com/site1164/Downloads/DownloadDetails.aspx?DownloadID=59185. You will also need the SharePoint Online and Project Online CSOM DLLs. The DLL’s used are from the NuGet package here: https://www.nuget.org/packages/Microsoft.SharePointOnline.CSOM/16.1.5521.1200. There are later version available, check here: https://www.nuget.org/packages/Microsoft.SharePointOnline.CSOM. Download those as required. These modules can be seen here on line 3,5 and 14 in the example script, the location will need to be updated to reference the correct location for your SP / PS Online CSOM DLLs.

The user setting up the script will need to update the correct location to the DLLs required, the AD username, AD password, CSV file location, PWA instance URL, username, password and the custom field internal name. The PWA account specified will need edit access to all of the resources in PWA and the AD account will need access to read all the users in Azure AD. Ensure the variables have been updated correctly, placeholder values seen below:

image_thumb.png

In this example all users in the Azure AD will be exported, clearly if you have a large organisation it would be efficient the filter for only those users that exist in Project Online. The code matches users and resources based on the AD display name and PWA resource name when updating the resources in PWA.

To get the correct custom field internal ID I use the REST API, <PWA Site URL> + /_api/ProjectServer/CustomFields. Find the resource level custom field and copy the InternalName as seen below:

image_thumb.png

Once the PowerShell script has been updated, save it and it can then be tested on a test / non-production environment. Once executed in PowerShell, each resource found in the CSV file will be outputted as seen in the example below. In this example there are many AD users in the CSV export that do not exist in the PWA instance.

image_thumb.png

Checking in the Resource Center after the script has run you can see for the resources that exist in the CSV file created, the Job Title has been updated with the values found in Azure AD / the export CSV file:

image_thumb.png

The CSV file generated can be seen here:

image_thumb.png

This PowerShell script could be run on a schedule from a server on-prem or even in Azure / webjob. The script would just need access to save / access a CSV file and the DLLs / modules required. Fully test this script on a test / non-production PWA instance before running on a Production PWA instance.

The script is provided “As is” with no warranties etc.

Update #ProjectOnline Project custom field values using #PowerShell with data from a CSV file #PPM #ProjectServer #CSOM

November 6, 2016 at 4:44 pm | Posted in Add-on, Administration, Configuration, Customisation, Functionality, Information, PowerShell | 1 Comment
Tags: , , , , , , , ,

This blog post will demonstrate a simple example of how Microsoft’s PPM tool, Project Online, can include data from an external system. This is a very simple example using a demo CSV mock up of data that could be from an external system. The CSV part could easily be replaced by a SQL query or a REST API query to the source system etc.

Example CSV data used can be seen below:

image

As you can see, my imagination for example project names has not improved! The code uses the Project Name as the key between the two data sets – the CSV file and PWA so the names will need to match.

This script example can be downloaded here: https://gallery.technet.microsoft.com/Update-Online-Custom-Field-12f034f4

The user setting up the script will need to update the correct location to the DLLs required, the PWA instance URL, username, password, the custom field internal name and the location of the CSV file. The account specified will need edit access to all of the projects in PWA. Ensure the variables have been updated correctly, placeholder values seen below:

image

image

The DLL’s used are from the NuGet package here: https://www.nuget.org/packages/Microsoft.SharePointOnline.CSOM/16.1.5521.1200. There are later version available, check here: https://www.nuget.org/packages/Microsoft.SharePointOnline.CSOM. Download those as required.

The get the correct custom field internal ID I use the REST API, <PWA Site URL> + /_api/ProjectServer/CustomFields. Find the project level custom field and copy the InternalName as seen below:

image

Once the PowerShell script has been updated, save it and it can then be tested on a test / non-production environment. Once executed in PowerShell, each project found in the CSV file will be outputted as seen in the example below. In this example two projects exist in the CSV data but not in my example Project Online PWA instance (remember for this example, the names need to match in the CSV file and PWA):

image

Checking in the Project Center after the script has run you can see for the projects that exist in the CSV file, the project budget includes the values found in the CSV file:

image

This PowerShell script could be run on a schedule from a server on-prem or even in Azure. The script would just need access to the data to import (CSV file etc.) and the DLLs. Fully test this script on a test / non-production PWA instance before running on a Production PWA instance.

The script is provided "As is" with no warranties etc.

#ProjectOnline data capture / snapshot capability with #PowerShell #SharePoint #Office365 #PPM #BI

August 26, 2016 at 11:03 am | Posted in Add-on, Administration, Configuration, Customisation, Functionality, Information, PowerShell, Reporting | 1 Comment
Tags: , , , , , ,

This blog post will show an option to capture Microsoft’s PPM Project Online data into a SharePoint list on a schedule to enable time driven data capture for snapshot / trend reporting capability. This example makes use of a PowerShell script I created to get the data and write this to a SharePoint list. The PowerShell script will use the Project Reporting OData API to get all of the specified project data in the PWA Site Collection then create a list item on the specified SharePoint list. The user setting up the script will need to update the source PWA instance URL, username, password and list name. The account specified will need access to the OData API in PWA and contribute access to the target SharePoint list. The SharePoint list will also need to be created beforehand with the required columns. The OData query will need to be updated to include the correct project level fields required and the part of the script that creates the list items will need to be updated too. This is covered below.

This script example can be downloaded here: https://gallery.technet.microsoft.com/Online-Snapshot-example-0437b680

To get the script to work you will need to reference the DLL as seen in the image below:

image

This can be installed from the SharePoint Online Client components / management shell. I used the dll from the SharePoint Online Management Shell in this example.

Please note, this has only been tested in PowerShell 3.0 and might not work in other versions. If you have any issues try this in PowerShell 3.0.

Firstly decide what project level fields you want to capture, this will determine the list column requirements. Then create the SharePoint list in the PWA site collection with the required columns, for this example I created a list called ProjectSnapShots with the columns below:

image

The SnapshotDate column was set to have today as the default value so that when the items are created the date is set automatically.

Update the Project OData query to include the correct fields that you want to capture, in this simple example I have included the following:

image

$url = $PWAInstanceURL + "/_api/ProjectData/Projects()?`$Filter=ProjectType ne 7&`$Select=ProjectId,ProjectName,ProjectPercentCompleted,ProjectHealth,ROI,RiskRating&`$orderby=ProjectName"

As you can see I have a list column for each project level field. The next part of the script that needs to be updated is the item creation:

image

This is where you map the project level fields to the correct SharePoint list columns based on the data you are capturing.

Also ensure the variables have been updated correctly, placeholder values seen below:

image

Save and run the PowerShell script (fully test on a non-production PWA site collection before Production) to ensure the data is captured correctly in the target SharePoint list. You could then set this up to run as a scheduled task on a local server or a scheduled WebJob in Azure to capture the data weekly or monthly etc. For details on a scheduled WebJob see: https://azure.microsoft.com/en-gb/documentation/articles/web-sites-create-web-jobs/

Once the script is run you will see the data in the SharePoint list, below I have run this 3 times:

image

You could then create a snapshot / trend report as required from the list data and even join to the live Project OData API if you capture the Project ID as I have in the example script / list.

A simple time driven data capture to enable snapshot / trending reports for Project Online. This script could easily be modified to capture the data into a SQL database, either on-prem or in Azure SQL if required. Also if this was to be run in production the script should be updated for error handling with try / catch blocks etc.

The script is provided "As is" with no warranties etc.

Access #ProjectOnline #Project sites using #PowerShell and #SharePoint CSOM #Office365

July 8, 2016 at 8:26 pm | Posted in Add-on, Administration, Configuration, Customisation, Functionality, Information, PowerShell | Leave a comment
Tags: , , , , ,

This PowerShell script will use the Project Reporting OData API to get a list of all the Project Sites in the PWA Site Collection linked to a project. It will then access each of those project sites to enable you to easily update / review the site if needed. This example just lists all of the list titles for each site but can be used as a starting script for modifying a particular list or adding a new list on all project sites etc. This could be used to help rollout changes to existing Project Sites, the code here will help you get connected and access each Project Site object using CSOM. You could even use this example to get the project site URLs then use the approach I blogged about the other month with the PnP PowerShell to month the sites. The example is for Project Online but could easily be updated for Project Server 2013 / 2016.

The user running the script will need to update the source PWA instance URL, username and password. The account will need access to the OData API in PWA and access to all of the project sites.

This script example can be downloaded here: https://gallery.technet.microsoft.com/Access-all-Online-Projects-568c7c59

To get the script to work you will need to reference the DLL as seen in the image below:

image

This can be installed from the SharePoint Online Client components / management shell. I used the dll from the SharePoint Online Management Shell in this example.

Please note, this has only been tested in PowerShell 3.0 and might not work in other versions. If you have any issues try this in PowerShell 3.0.

The good thing about using the OData API to return the list of Project Site URLs is that if you have certain sites for each Enterprise Project Type you could easily filter the OData query to only return the project site URLs for the correct Enterprise Project Type.

Once the variables have been updated correctly, placeholder values seen below:

image

Save and run the PowerShell script (fully test on a non-production PWA site collection before Production), firstly this example will give you a count of project sites:

image

Then for each of those sites it will list all of the SharePoint lists on each site:

image

As mentioned, this just lists all of the lists so probably isn’t that useful unless you wanted to check each Project Site had the correct lists. The script could be modified to update those project sites or lists fairly easily depending on what the changes were. All I would say is that if you are looking to update / make changes to the project sites, fully test your modified script on a non-production PWA instance first as you could cause a lot of damage if you weren’t careful!

The script is provided "As is" with no warranties etc.

Extract #ProjectOnline or #ProjectServer 2013 / 2016 Timesheet data #PowerShell #Office365

May 17, 2016 at 4:32 pm | Posted in Add-on, Administration, Configuration, Customisation, Functionality, Information, PowerShell, Reporting | Leave a comment
Tags: , , , , ,

This PowerShell script will use the Project Reporting OData API to extract the timesheet data between the given start and end dates. The user running the script specifies the source PWA instance URL, Username and password. They then enter the start and finish dates in yyyy-mm-dd format and run. The data will then be displayed in the console and output to a CSV file.

This script example can be downloaded here: https://gallery.technet.microsoft.com/Extract-Online-or-Server-c1cba361

To get the script to work you will need to reference the DLL as seen in the image below:

image

This can be installed from the SharePoint Online Client components / management shell. I used the dll from the SharePoint Online Management Shell in this example.

Please note, this has only been tested in PowerShell 3.0 and might not work in other versions. If you have any issues try this in PowerShell 3.0.

Firstly it will prompt for the source PWA URL:

image

Then the username and password:

image

Then the start and finish dates in yyyy-mm-dd format:

image

The script will output the data to the console:

image

It will also create a CSV file in the same folder that the PowerShell script is run from:

image

The CSV file:

image

This was only run against a test PWA instance in Project Online and only my account had timesheet data for the given period, it will return all of the timesheet data for all resources for the given start and finish dates.

This example requires the user to enter the environment details when running but it could easily be updated to hard code these then the PowerShell script could be scheduled to run weekly or monthly etc. The start and finish dates could be made dynamic too.

Whilst this only reads data, as always, this script is provided as is with no warranties etc. use at your own risk and test on a test environment before using on a production environment.

#Office365: quickly see which #SharePoint sites are #ProjectOnline sites #PowerShell

April 5, 2016 at 11:44 am | Posted in Administration, Configuration, Customisation, Functionality, Information, PowerShell | Leave a comment
Tags: , , ,

In Office 365 from the SharePoint admin center it is not possible to quickly see which site collections are PWA site collections. The only way currently to check in the UI is the select a site collection then click the Project Web App button on the Site Collections ribbon and see if the Remove and Settings options are enabled:

image

If you select a normal SharePoint site collection these two options are disabled:

image

As you can imagine, if you have many site collections this could be quite a slow process. There is a simple and quick answer to this, PowerShell. Using the SharePoint Online Management Shell you can access the properties of the site collections, details on the SharePoint Online Management Shell can be seen below:

https://support.office.com/en-us/article/Introduction-to-the-SharePoint-Online-Management-Shell-c16941c3-19b4-4710-8056-34c034493429

I have used the PowerShell ISE to create and run the script, to use the ISE you will need to firstly install the SharePoint Online Management Shell on your machine then import the module using the command below:

Import-Module Microsoft.Online.SharePoint.PowerShell -DisableNameChecking

Once loaded, a simple PowerShell script can easily identify the PWA site collections:

image

There is a –Filter parameter for the Get-SPOSite command but it doesn’t allow you to filter on the Template property so I created a simple foreach loop and did the filter there.

Or if you want to see all of the site collections and the templates used to create the sites see the script below:

image

In both examples just update the site collection admin site URL and the global administrator username for the Connect-SPOService command.

**** There is a caveat to this approach (thanks to Brian Smith for letting me know), if you have a normal SharePoint site then enable PWA on the site after it is created these sites will not show as a PWA site in this script. In that scenario the PWA site is a sub site and the Get-SPOSite only gets the root site collections. I guess you could look at using CSOM / REST to get all sites. ****

If would be good to get this information visible in the view from the SharePoint Admin Center, someone has already suggested this on the Office 365 uservoice, see the link below if you want to vote:

https://office365.uservoice.com/forums/273493-office-365-admin/suggestions/9374838-enable-split-view-in-sharepoint-admin-center-to-di

Check entities from multiple #ProjectOnline PWA instances using #PowerShell and CSOM #Office365

November 4, 2015 at 10:32 pm | Posted in Add-on, Administration, Configuration, Customisation, Functionality, Information, PowerShell | 1 Comment
Tags: , , , , ,

This PowerShell script enables admins to read entities from from multiple Project Online Project Web App instances using CSOM. The particular example reads the list of Enterprise Custom fields but can easily be updated to display other entities such as projects. The script example can be downloaded here:

https://gallery.technet.microsoft.com/Check-entities-from-a1cb87e4

To get the script to work you will need to reference two DLLs as seen in the image below:

image

The first one is the ProjectSever Client DLL, as I ran this on my Project Server 2013 server so just referenced the default location of the DLL.

The second DLL is the SharePoint client DLL from the SharePoint Online Management Shell: https://www.microsoft.com/en-us/download/details.aspx?id=35588 

Once the references to the DLL’s are updated the script can be run as seen below. Please note, this has only been tested in PowerShell 3.0 and might not work in other versions. I have seen issues in the past loading some SharePoint / Project Server DLL’s in certain versions of PowerShell so if you have issues try this in PowerShell 3.0.

Firstly it will prompt for how many PWA instances, enter the number:

 image

It will then prompt for the PWA URL:

 image

Then the username:

 image

Then enter the password:

 image

The custom fields from this instance will then display:

 image

Depending on how PWA instances you specific the script may or may not prompt for another PWA instance URL, username and password. If you specified more than 1 PWA instance the output will look like this:

 image

As seen, this displays custom fields but could easily be updated to display another entity, for example to see a list of projects. This can be done by changing the following lines:

From:

image

To:

image

Whilst this is very simple by just reading data, this could be updated to create new custom fields for example and push that out to multiple PWA instances without having to navigate to multiple PWA instances. I have created an example .NET C# console application that I will blog about soon that creates new custom fields on multiple PWA Project Online instances.

Whilst this only reads data, as always, this script is provided as is with no warranties etc. use at your own risk and test on a test environment before using on a production environment.

#ProjectServer #PS2010 / #PS2013 delete #Project site using #PowerShell #SP2013 #SP2010

June 26, 2015 at 9:13 am | Posted in Add-on, Administration, Configuration, Customisation, Functionality, Information, PowerShell | Leave a comment
Tags: , , , , , , ,

Following on from a post / script I created a year or so ago to bulk create project sites, there have been requests to be able to bulk delete projects sites. I have been reluctant to do that as i am always cautious when “bulk” deleting! The script i have created reads the list of projects from a text file so that the user running the script knows exactly what project sites they are going to be delete. The script can be downloaded from the Microsoft Script gallery below:

https://gallery.technet.microsoft.com/projectserver/Delete-Server-Sites-9c3d3e95

The text file will need to be created with a list of projects names from your PWA instance – ONLY INCLUDE project names for the project sites you want to delete – all site data (lists, documents etc.) will be deleted as the site will be deleted! The only scenario I would really use a script like this is if I wanted to delete all of my project sites, if I only needed to remove a handful I would do it manually via the UI. The example text file can be seen below:

image

Update the location of the text file and text file name for the Get-Content command.

The web service proxy URLs will need to be updated for your PWA instance, replace the http://vm753/pwa with the correct URL.

image

The script can be seen running and deleting sites below:

From the PowerShell ISE:

image

All data from the project sites will be lost so please use this with care and only enter project names in the text file for projects sites and project site data you no longer need.

Run the script with a PWA administrator account.

Fully test this script on a test / non-production PWA instance before running on any production environment. As a precaution, take full database backups (PWA and SharePoint Content) before running the script on the production environment so that you can roll back if needed.

The script is provided "As is" with no warranties etc.

#ProjectServer #PS2010 / #PS2013 bulk #Project site creation using #PowerShell 3.0 or later #SP2013 #SP2010

March 5, 2014 at 11:18 am | Posted in Add-on, Administration, Configuration, Customisation, Functionality, PowerShell, PSI | 4 Comments
Tags: , , , , , , , , ,

I recently had to bulk delete and then bulk create project sites for a client. Rather than engaging one of our devs I wrote a PowerShell script to do this. This does need PowerShell 3.0 or later to work. This post covers the bulk create script as this is generic, the bulk delete was specific to the client so I haven’t published that. The script can be downloaded from the script galley below:

http://gallery.technet.microsoft.com/scriptcenter/Bulk-create-Server-Sites-784f7b29

The web service proxy URLs will need to be updated for your PWA instance, replace the http://vm753/pwa with the correct URL.

The script can be seen running and creating sites below:

From the PowerShell ISE:

image

From PowerShell:

image

Project Sites are created using the correct site template that is associated to the EPT.

If all projects have a site associated, the following is returned:

image

Test this on a test / dev farm before running a live production farm.

#ProjectServer 2013 Build OLAP Cube via #PowerShell #SP2013 #PPM #PS2013

February 18, 2014 at 1:18 pm | Posted in Administration, Configuration, Customisation, Functionality, Information, PowerShell | Leave a comment
Tags: , , , ,

One potential issue for many organisations with Project Server 2013 is that users need access to Central Admin if they want to build the OLAP Cube. Usually the PMO / PWA Admins would not have access to Central Admin, they only have access to Project Web App. If they want to build the OLAP Cube on demand, they would need to raise a request to the team that manages SharePoint Central Admin. To get around this, they can use PowerShell from the local workstation. The script can be downloaded from the script gallery below:

http://gallery.technet.microsoft.com/scriptcenter/Server-2013-build-OLAP-Cube-13a4346c

image

Once the script is downloaded, the uri will need to be updated to use the correct URL, update the http://vm753/pwa part of the URL. You will also need to update the cube GUID. You can get the cube GUID from the URL in Central Admin when you access the OLAP cube settings page. See the highlighted URL below:

image

Obviously someone with access to Central Admin will need to get the OLAP Cube GUID for you.

The users running the script will need to correct permissions in Project Server. They will need Manage Cube Build service and Mange Site Services.

Next Page »

Blog at WordPress.com.
Entries and comments feeds.