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.

Linux and Windows event more love with containers (LCOW)

I have noticed "Linux containers on Windows built with LinuxKit" (LCOW)  a while back. Today I found a blog post from briantweet actually running a Windows and Linux container together.
There are a few steps that you need to follow to get things working though.

1. You need to be working on a later release of docker (at least 18.03 I believe)
2. Switch to windows containers:
   
3. and have the experimental features enabled :
   
4. You need to then install the LinuxKit on your machine, which is luckily fairly simple:
1. Download the latest release
2. Run an elevated powershell prompt and execute the following:
   Remove-Item "$env:ProgramFiles\Linux Containers" -Force -Recurse
Expand-Archive release.zip -DestinationPath "$Env:ProgramFiles\Linux Containers\."
5. Now, the first thing I tried was to run the MS-SQL container on Linux (as did brian) and we both ran into the same memory issue. It is a bit hairy to fix, and you may not even need to do this depending on what you are trying to run, but just in case:
1. We need to recompile the docker deamon. Luckily there are steps to do exactly that, in a docker container. Brian explains it quite well, but missed a few steps that I will fill in:
1. You will find the client_local_windows.go file in the libcontainerd directory under the root of the docker source code
2. You will need to find the configuration := &hcsshim.ContainerConfig{
HvPartition: true,
Name: id,
SystemType: "container",
ContainerType: "linux",
Owner: defaultOwner,
TerminateOnLastHandleClosed: true,
} section, round about line 360. Then insert the MemoryMaximumInMB: 4096, in that config
3. When running step 4 (from the steps) to build the images, add the "-m 2GB" arg., else you will have an "out of memory" error
4. Finally, only copy over the "dockerd.exe" file to the original docker install folder under the resources directory. On my machine : C:\Program Files\Docker\Docker\resources

This allowed me to run both the Linux ms-sql image and a windows based dotnet sample image together..

Magic!

Tips and Tricks: Open Workitem in Visual Studio

Problem:

Getting annoyed when working in Visual Studio 2017 and each time you you open a work item from Team Explorer pane, it opens it in the web?

Well according to recent conversations I have had, it appears that not to many people know about the ability to set the behaviour.

Solution:

By default, VS2017 will open the work items in the browser, but there is a way to change it. When you open Visual Studio and select the Tools > Options from the menu. Find the "Work Items" section and under General you can change the behaviour.

Setting it to "Visual Studio (compatibility mode)" will open the work items in Visual Studio as before.

Be warned though, this is option is due to be removed in the next major release of Visual Studio where the default behaviour will be to open the work item in the browser. So use it while you can

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