TFS Reporting using Power BI and REST services

I've been getting the question around reporting options in TFS, specifically on-premises, fairly often.  Currently we have a few options, using queries and charts or widgets, try to use the warehouse / analysis services setup or wait for the Analytics services to come on-prem. A few clients need more details than the queries can offer and do not want to spend the overhead to configure and run the warehouse but also do not want to wait indefinitely for Analytics to make the on-premises appearance.
I have been doing some high level stuff with Power BI Desktop and though I would give it a go. The problem I faced is that there is not a lot of material around how to do it. So let's see if we can help out a bit.

Firstly you would need to get Power BI Desktop.
After you have created a new empty report you need to decide what information you are looking for. As you know, you can get a lot of detail from the TFS/Azure DevOps REST services. This post should give you the basics to get started and report on information derived from pretty much any one of those sources.
After you have decided what information you are looking for, we can start. The example I'm going to use is going to be historic information around work history. That is a particularly cruel thing to get out of TFS and report on.
To start, we need to go and add the queries we need. To do this we will "Edit Queries".

In the Query Editor, we create a new blank source.

Give it a name and then select the advanced editor.

In the advanced editor we will create a "function" that will take in the TFS server URL, the Team Project, and in this case a start date as parameters.
The start date is when we start retrieving the history from.
The "source" will look something like this:

Line 4: This is where the REST service is being invoked and the resulting JSON is returned
Line 5: Next we need to extract the "values" element, which is an array, from the resulting JSON
Line 6: Convert the JSON array to a table with records, each record still contains a JSON like record object
Line 7: The object, in this case, looks something like this We want to see more detail, so we will expand the "fields" property into a fields column
Line 8: If you look at the result now, it would like this . A "fields" column with rows of "records", simply meaning we need to expand even further to extract the columns that we are looking for. You can see I have specified the columns and their columns headings in the Table.ExpandRecordColumn function
Line 9 and 10: Just doing some data type conversions on the columns that will make the report behave appropriately (allow summing or average on number fields for example)
Because this is a "function" we have just created, we can invoke it and pass in the correct parameters. If you click "done" on the advanced editor you should see your "query" in the left had pane. Selecting it will prompt you for parameters that you can enter and then after clicking "Invoke" you should see some results

You can then rename and use that new table to create the best looking reports ever by clicking "Apply and Close" and start creating reports and charts etc…
Just as an example, the attached Power BI report has the work item history that we have just covered, as well as commit history on GIT repos. You may notice that the initial JSON format is slightly different, but the concepts are still the same.

Get the sample here

Team Foundation Server Anti-Virus Exclusions

A while back I had big problems with a client's on-premise TFS environment suddenly running the CPU to near 100% for 2 – 5 minutes at a time.  This slowed down the entire system and caused TFS to slow down to the extent that developers could not accomplish much on TFS. This happened intermittently and we took a few weeks to pin it down to the anti-virus. It seems TFS uses a local cache, and when you check out large repos (especially GIT repositories) it does a lot of churning and caching. The anti-virus insists on scanning each action and it starts to slow down the entire process.

I searched online and found some guidance around anti-virus exclusions, but I decided to add to it with a few of my opinions included of course.

Team Foundation Server

There are primarily 3 things that need to be excluded on the TFS server itself.

1. W3wp.exe process. This is the Internet Information Services (IIS) worker process and is typically located here : C:\Windows\System32\inetsrv\w3wp.exe
2. TFSJobAgent.exe process. This is the TFS background worker process and is typically located under the TFS install path under the "\Application Tier\TFSJobAgent" folder
3. TFS file cache location. This is where we had issues. TFS does a lot of caching on the server, and uses this location to write it to disk. It is one of the configurations that is asked for during the install and you can see what it is if you open the TFS administration console under the "File Cache Location" property. The default location is "C:\TfsData\ApplicationTier\_fileCache". You want to exclude on access scanning for this location.

TFS Build Machine

The TFS build machine may also take a bit of strain when it comes to the agent's working directory. This may be a contentious discussion, because it is possible to have malicious code executed under this location. In your secure environment it may increase your build process slightly.

SQL

When the preceding exclusions are made, and you want to move to SQL, there is a pretty decent overview of what needs to be excluded here.

After we added these exclusions the TFS servers were humming along without any issues.

Tips and Tricks: User has Allow Delete work items, but no delete button on work item

Problem:

The user in TFS/VSTS has all the rights enabled to delete work items. The problem is that when you open the work items the "Delete" button is missing, and you do not have the option to delete in query lists.

When you look at the inherited rights you see something like this:

All indication is that the permission is allowed, but the end result is that it is denied.

Solution:

This may be that the user is in the "Stakeholder" access level. If you pay close attention to the "unavailable features" you will notice that deleting work items is one of the things a stakeholder can't do.

You can now either acquire a license for the user and move him/her into the basic access level or higher, or the user will need to ask someone that does hove those rights to perform the deletions.

Tips and Tricks: TFS Excel Plugin not loading

Problem:

Close an Excel spreadsheet with/or without having a TFS/VSTS connected list. When you open the spreadsheet again, the Team tab is missing in the ribbon bar.

You then need to go through and re-enable the plugin through Excel options to reload the plugin and get the Team tab back.

This also disconnects your TFS/VSTS linked worksheets, causing you to have to reconnect or re-open a query to carry on working with the work items.

Solution:

1. With Excel closed, open up the registry editor (regedit)
2. Then navigate to HKEY_CURRENT_USER\Software\Microsoft\Office\Excel\Addins , and find the TFCOfficeShim entries:
   
   There may be more than one, and the version number at the end may differ to the image above
3. I simply remove/delete the “older versions”  (I surely do not have to remind you to take a backup of your registry before you make any changes),
4. and then find the “LoadBehavior” in the “folder” and make sure that the value is 3

Now re-open the spreadsheet and see if it will load automatically.

Using Office UI Fabric to create a VSTS Extension

In the beginning

In the beginning god wanted to make earth. So, god found a cool looking bootstrapper and loaded up his favorite command line.
God then stepped through and downloaded all 100’s of packages to be able to run the bootstrapper and then the bootstrapper itself:

npm install create-planet -g

God created earth create-planet earth and all was good for a few days. God then decided he wanted to do more. Humans were missing.

npm install human

+ <human@0.0.1>
added 1702 packages in 5904.903s

God then referenced "humans" in earth.js and tried to compile.

Module not found: Error: Can't resolve sin

Ok, no biggy, npm install sin. Compile.

[at-loader] ./node_modules/@types/Inhabitants/index.d.ts:90:13
TS2403: Subsequent variable declarations must have the same type. Variable
'intellegentlife' must be of type 'Homosapiens', but here has type 'Neanderthal'

Ok, remove package Neanderthal. Compile.

earthpack error -ERROR in earth.js from UglifyUniverse Unexpected token: name (continentParts)

Earthpack.. where did that come from? Ok...
5 hours later, god found the Earthpack config and was able to fix the configuration, and the compile was good.

So god ran earth and was promptly told:
Universe.js Error: Invariant Violation: Minified World error #379.

Then there was a big bang.

An now…

I took on a project a while back to summarise and write a “quick start” series of posts for the ALM/DevOps Rangers to highlight the usage of Office UI Fabric. This meant that I needed to get my head around changing an existing extension from “simple” Typescript to React while incorporating Office UI components.  As you may have summised from the above analogy, I have spent way to much time trying to get things running the way that I want them to, based off of someone else's concepts, intentions and bootstrapping.

If you are interested in the outcomes, please follow the series posted on the msdn blog:

1. The start

Containers on Azure as part of a CI/CD pipeline

In my previous posts I was speaking about the journey to go from setting up a container and ultimately publishing it in a continuous fashion to a registry using VSTS.

You may have noticed that a lot of time has elapsed since my last post, and there are a couple of reasons for that.

First of all, the impediment of work, secondly, I noticed a trend where it became fairly popular to blog about the journey from where I ended off and I hung back and followed those for a while.

Instead of me going ahead and creating a bunch of posts to show how to publish your container and run it in production, I’m going to hand it off to a bunch of other capable people :-)

To delve deeper, or just for more information, these are all good reads:

• Windows finally came to the party with Windows based containers
• Introduction to Azure Container Service for Kubernetes
• Walk through on managing a Kubernetes container service on Azure
• Webcasts giving a good overview of the DevOps processes around leveraging containers
• Create a CI/CD pipeline for containerized ASP.NET Core projects
• Setting up CI/CD for Docker Container Running in Kubernetes using VSTS
• Running Web API using Docker and Kubernetes
• Team Yoman generator by Donovan Brown, that will create most of the infrastructure for you
• Continuous Deployment, Kubernetes to Azure Container Service

And if you are interested in a brief discussion on how to move to a microservices based architecture this is a good read : Modernizing a Monolithic Application using Microservices and Azure

 

JDeveloper and TFS

Oracle developers should be well versed with JDeveloper.
On the SQL side of things, Oracle actually has a plugin available for Visual Studio "Oracle Developer Tools for Visual Studio" giving you a very similar experience to the SQL Server Data Tools.
JDeveloper on the other has is "a freeware IDE supplied by Oracle Corporation. It offers features for development in Java, XML, SQL and PL/SQL, HTML, JavaScript, BPEL and PHP".

This should not stop you from using it with TFS though…
The only thing that intrigues me is the use of workspaces to establish the connection as apposed to creating a connection directly to TFS.

Enable the plugin:

1. 1) In JDeveloper, go to the extension management page by selecting Help->Check for Updates
2. Click Next on the sources view
3. Wait for the extension to load and then type in “Team System” in the search box. This should highlight the Team System extension.
   Select it and click next.
4. Wait for it to download and install, you will probably have to restart JDeveloper.
5. Create a workspace either using Visual Studio or via command line :
   https://msdn.microsoft.com/en-us/library/ms181383%28v=vs.120%29.aspx
6. Connect to Team System ( Team->Connect to Team System). It may seem that nothing has happened, but go to the next step…
7. Select the workspace created in step 5 (Team -> Team System->Set Workspace and then select the workspace from the list presented):
8. When you open your applications, you should be able to add, check out etc. you files from TFS: by right clicking any files and selecting “Versioning”
9. You can preview the pending changes from Pending Changes (Team-Team System -> Pending Changes) pane.

TFS is no longer "just" a Microsoft / .Net tool… To prove it, have a look at http://java.visualstudio.com.

Multiple TFS_Configuration databases on a single SQL instance

I have been doing a number of upgrades over the last couple of months and have found that doing a migration to a test/dev environment is a good way to gauge the downtime needed to perform an upgrade on the TFS Collection databases.

This works well if you have a spare set of servers / VM's lying around and you are able to do a full migration. I have been in a situation where a new application tier was not much of a problem, but they had an enterprise SQL server setup that we "had to" use.
Even though the "duplicating" of the collection databases is no problem at all, the tfs_configuration database is a different story.

The options are:

1. Install a temporary SQL server on the application tier and simply use that for the tfs_config database or
2. Duplicate the tfs_config and then either "point" tfs to the instance in question or select the correct database during (re)configuration

To perform option 2,  simply create a backup of the tfs_configuration database and restore under a different name (on the same server). When you perform an upgrade or (re)configure the TFS application tier you have the option to select the "correct" config database:

If TFS is already configured and you want to change the database after the fact you can follow these steps:

1. Open up command prompt (run-as admin)
2. Go to the TFS tools directory (for 2015 the default is C:\Program Files\Microsoft Team Foundation Server 14.0\Tools )
3. Put TFS to "sleep" : TfsServiceControl.exe quiesce
4. Backup and restore the config database to the new name
5. Re-register the database : tfsconfig.exe registerdb /sqlInstance:<<sql-server>> /databaseName:<<new config db name>>
6. Finally, wake TFS up : TfsServiceControl.exe unquiesce

That should have TFS pointing to the new config database, meaning you can have multiple TFS instances using different configuration database hosted on the same SQL server.

Need help upgrading ? : give us a shout

ALM Days Event : TFS 2015 - Open for all!?

I can hardly believe we are heading towards the end of the year.
With this brings the time for our final event this year on the 19th of November.

This time we are going to have a look at how TFS is opening up to everyone/everything around it, both in terms of functionality and flexibility.
We will briefly explore how TFS is opening up its doors to embrace extensibility and 3rd party systems, platforms and languages a like.
We will then delve into how we can manage technical debt with TFS and SonarQube.
If time permits, we will see some of the new features (yes even more) in the pipeline (TFS 2015 Update 1 is already in RC and there are some really cool features bundled in this update!).

Register on Eventbrite

Hope to see you all there !

Presented by Team Foundation Consulting
Thanks to Microsoft for sponsoring the venue.

TFS 2015 Update 1 RC

In case you have not noticed RC 1 for the first update to on-premise TFS 2015 is in release candidate.

Amongst others, my favourite new bits are:

1. Dashboards
2. Card colouring

Yours?

Need help upgrading ? : give us a shout

TFS 2015 Finally released!

After waiting what seems like forever, TFS 2015 has finally been released after Brian Harry decided to hold back and get some more testing done.

This time it seems to be the real thing : http://blogs.msdn.com/b/bharry/archive/2015/08/06/team-foundation-server-2015-final-release.aspx

I've had some people ask me if they should upgrade or wait a while to have all the "kinks" worked out. My answer is twofold:

1. Evaluate the release first and make sure that there is value to be had out of the upgrade
2. Secondly, I would not worry toooo much about encountering any "kinks". TFS is pretty much production tested in VSO so there is not much of a chance that you will encounter any significant issues

If it makes sense for you to upgrade, by all means do (of course taking necessary precautions as you would in any upgrade situation!).

That said, it is always a good idea to keep up to date with updates and upgrades. This will make future updates less cumbersome and quicker, as well as keep you in the support band (TFS 2010 is not supported anymore and Visual Source Safe should not exist anymore!! (even though the tragic truth is I'm still doing the odd VSS migration ))

It should be noted that this will probably be a longer update/upgrade than most. There are some significant database changes due to the project rename (finally!) capability that was added. In most small TSF instances this will not be much of an issue, but I have dealt with some large instances that needed careful planning! Luckily there are ways and means to make it a bit easier.

Need help upgrading ? : give us a shout

Visual Studio 2015 RTM on July 20th

woohoo.. finally the RTM date for Team Foundation Server 2015 and Visual Studio 2015 has been made public…

http://blogs.msdn.com/b/somasegar/archive/2015/06/29/save-the-date-visual-studio-2015-rtm-on-july-20th.aspx

Edit: Visual Studio will be released, but Brian has decided that quality is more important than timelines.. So we will have to wait a bit longer for TFS 2015 to land, but be assured the quality will be right up there

Need help upgrading ? : give us a shout

Installing and configuring TFS 2015 on-premise xplat build agent

One of the things that really got me exited about TFS 2015 was the cross platform build capability, and that was the first thing that I started to play with as soon as I got hold of the RC.

Interestingly enough, in VSO it is fairly easy to setup when you have the "alternate credentials" on VSO, but, as I was to discover, there are a few more things that you need to do before you can get it working on-prem.

So with my TFS server and newly installed Ubuntu box ready, I started the xplat (pronounced "cross plat(form)" if you were wondering) configuration. The install was fairly straight forward as per the steps indicated. Then came the configuration.

1) Authentication
The first thing the vsoagent configuration asks for is an account to run the xplat agent under. Linux does not play well in an Active Directory environment, and I have spent waaayy to much of my life trying to get linux working on AD. This hinted towards authentication problems…
But there is a Solution
VSO has the concept of alternate credentials, which is basically a "Basic Authentication" mechanism. What we need to do on-prem is to enable this type of behaviour.

1. Log onto your TFS Server and install Basic auth on IIS 
2. Go to the TFS application in IIS (Sites\Team Foundation Server\tfs) and enable basic auth
3. Edit and set the domain and realm to the domain used for authentication

2) Security
Once you have the authentication mechanisms setup you need to pick the account that the agent is going to run under and assign the correct rights in TFS and in the build pool. To set the rights for the build pool you need to

1. Navigate to TFS server web access admin screen (http://<<server>>:8080/tfs/_admin)
2. Go to the "Agent Pools" tab and either create a new pool or select the default pool
3. Then assign the account that the agent is going to run under into the "Agent Pool Service Account"

Not doing this may give you a "Failed Request: Forbidden (403)" error when you try and run the agent

3) Boot her up…
Finally you need to "boot her up". If you have followed the steps outlined here, you should be able to run "node vsoagent" in the agent folder of the newly "installed" agent and you should see something like this beauty..

and in TFS :

and "thar she blows"..

Please note that it… .. use at own risk

ALM days 2015 - Byte sized sessions : 11 June 2015

The first of 4 ALM sessions planned for Cape Town will take place on 19 March 2015* 11 June 2015 at the Microsoft Office in Pinelands.
Session topics:
What’s new in TFS ALM
We look at what has happened in the world of TFS in the last year. The new bits and pieces and some of the licensing changes that came into play making TFS much more affordable and accessible.

Real World Session: Let’s talk Release Management
MS Release Management is a great tool for pushing software into different environments. We hear some real world experiences on how RM can be used to move issues into production very quickly or alternatively leverage it to make your life a lot easier.
Register to join us.
Thank you to Microsoft for sponsoring the venue for this session.

*Note: Due to unforeseen circumstances we have had to re-schedule. We are looking forward to seeing you on the new date!

TFS 2013 Update 4 finally released

For those of you who do not know, Microsoft released TFS 2013 update 4 with their Connect(); event yesterday/last night.

Some of the really cool stuff that I have been waiting for is the introduction of the Stakeholder licensing and Trend charts.

They also gave us a sneak peak of what is in the pipeline, and I for one am once again getting really excited.

One thing that caught my eye was some vNext features, for example the new build infrastructure that is going to be introduced. YES the build agent is finally going cross platform…
As per Brian Harry's post:

Sneak peek – Updated build service

I (Brian Harry) showed a preview of a major update to the VS Online/TFS build service that we’ve been working on.  We believe it will address a large portion of the suggestions to improve it that we’ve received.  Improvements include:

• A much simpler customization experience that doesn’t require XAML/Workflow – just a simple sequence of tasks to execute.
• A real time build output window to easily track the progress on your build
• Build definition versioning/auditing so you can know who changed your build definition, what changes they made and why.
• A web based editing/administration experience
• The ability to share build agents across projects and collections, making shared build pools far more viable.
• A cross platform build agent so that you can automate builds for Mac and Linux too (or even builds than span a PC, Mac and Linux).

See more exiting new from Brian's post.

Aaarrgghh; the wait…

Move from GIT to TFS Version Control

I have dealt with a couple of clients recently who were very keen to get into the GIT repo's that are available in TFS 2013. The unfortunate truth is that GIT is not the "same" as the widely used TFS Version Control (TFSVC), SVN, VSS source control systems. Some teams tend to have a problem with this and, after numerous missing changes/files and problems with branching, commit vs pushing and merging issues, will inevitably ask : "How do we go back to what we know?".

There are tools to move from VSS & SVN to TFSVC and there are instructions to move from TFVC to GIT, but what about GIT to TFSVC? Well luckily, for a simple migration, you do not need any commercial or "3rd party" tools.

You do need to get your hand on GIT-TF and optionally the git client, and you are "a" for away.

1) Clone the repository

git clone http://<<servername>>:8080/tfs/<<Project collection>>/<<team project>>/_git/<<repo name>>

2) Replace the repository bindings

git remote rm origin

git tfs configure http://<<servername>>:8080/tfs/<<Project collection>>     $/<<team project>>/<<destination folder path>>

3) Finally check in the changes

git tfs checking --deep

Remember the --deep parameter - this will replay the commits as check-ins in TFS

The only thing you should be aware of here is that the check-ins will happen in chronological order, but the time will reflect the time and date that the check-in is occurring and not when they were committed into the GIT repo.

TFS 2013.4 Stakeholder access

The stakeholder access has been mentioned a couple of times here and here by Brian Harry.

If you recall correctly the Limited access group means that if you are in that group, you do not need a CAL to access TFS Web Access. It also meant that you can only see a very scaled down version of web front end which basically  gave you access to the work items that you created.

Things have changed.. Now you have power!
Instead of the previous "Limited Access" group, there is now a Stakeholder group.
The allowed features have gone from "View My Work Items" to :

• View, add, and modify items on the backlog
• View team home pages and portfolio backlogs
• View, create, and modify work items such as stories, features, and bugs
• View, create, and save queries
• Sign up and receive alerts when changes are made to work items.

Where TFS has  been hiding in the developer realm within corporates, this move IMHO opens up TFS to adoption in big business, where getting everyone on board from a licensing perspective can be a huge expense.

Don't get me wrong, trend charts and pull requests in TFS 2013.4 CTP 1 are big, but you can have all the charts in the world, if the bar to entry for the stakeholder is too high, it is not going to give you the benefit that you need.
I can't wait until the CTP2 when the Stakeholder licensing is suppose to be finalised!

TFS 2013 Pre Build Script Assembly Version Updates

I was looking at a "simple" way to update the TFS build definitions and adjust the Assembly versions of the projects being compiled. I came across this article (http://msdn.microsoft.com/en-us/library/dn376353.aspx) leveraging the pre and post build scripts that are available in the newer build definitions.

The problem I had with the scripts included in the TFS Build Extensions is that it uses the Build Number or Label. This does not really make sense to me so I started hacking at it.
Fortunately it was not as big a task as trying to delve into the new build definition's workflow and trying to find the variables that "used to" exists.

If you are interested, I have posted the resulting PowerShell.
Simply add this to the TFS repository, and then reference it as a pre-build step in your build definition and you will notice that your AssemblyVersionInfo.cs files are updated before the build, replacing the revision to the changeset number.
This will give you a reference from the actual assemblies back to the code or changeset that they originate from.



Enjoy!

Comparing TFS and Rational Team Concert

A while ago I did a comparison between TFS and Rational Team Concert.
It came up in a discussion amongst the ALM Rangers and I posted it here for information purposes.

ALM Discipline	TFS	Rational Team Concert
Requirements Management
Project Management
Change Management
Configuration Management
Build Management
Test Case Management	(MS Test Manager)	(Rational Quality Manager)
Software Deployment
Design	(VS Ultimate)	(Rational Software Architect)
Release Management	(Release Management)	(Rational Build Forge)

Feature / Component	TFS	Rational Team Concert
Automated Testing	Visual Studio & MS Test Manager	Rational Functional Tester
Visual Studio Integration		(VS 2012 as of v4.0.1)
Build Infrastructure	Windows Workflow Foundation and MSBuild	MSBuild
Check-in policies
Code review workflow	(TFS 2012)
Branching & Merging

This is a very high level and brief overview, if you have any comments corrections or additional information, feel free to give me a shout.