Here with another script for TFS! This time it's for cleanup. Occasionally we find ourselves with unused work item fields in TFS, either after projects get deleted, when fields become obsolete and are removed from WITs, or when you made a typo and have to create a new field. I made a simple Powershell script to find and delete all unused work item fields in your TFS collection.
The entire script is as follows:
DeleteUnusedWorkItemFields.ps1
#location of WitdAdmin.exe
cd "C:\Program Files (x86)\Microsoft Visual Studio\2017\Professional\Common7\IDE\CommonExtensions\Microsoft\TeamFoundation\Team Explorer"
#input your collection URL here
$collectionURL = "https://www.fabrikam.con:8080/tfs/collectionA"
#save unused fields output to a variable/array
$fieldsToDelete = .\witadmin listfields /collection:$collectionURL /unused
#listfields command nearly outputs to a StringData type; just need to replace colons with equal signs. then convert from StringData to PowerShell format
$powershellFieldsToDelete = $fieldsToDelete -replace ":", "=" | ConvertFrom-StringData
#Show me all of the unused fields; stop after this Output if you do not wish to delete them yet
Write-Output $powershellFieldsToDelete."Field"
#loop through each field in the array with delete command
ForEach ($unusedField IN $powershellFieldsToDelete."Field") {
.\witadmin deletefield /collection:$collectionURL /noprompt /n:$unusedField
Write-Output "Deleted unused field: $unusedField"
}
Write-Output "All unused fields in this collection have been deleted."
It's been a while since my last post and quite a bit has changed.
Same job (nearly; trimmed some responsibilities, pick up others), new environment! We are on TFS 2017 Update 1, soon to jump to Update 3, with plans in the works to finally make the move over to VSTS. Lots of work this year to make that happen!
On that note, one of our bigger issues in regards to on-premises TFS has been keeping track of our licenses; who is using what level, and when is the last time they logged in because we shouldn't be licensing users that haven't used the system in a year! In order to track this without going through a bunch of manual processes each month using the TFS "Export Audit Log" button on the Server level admin page, I've made the dive into Powershell. I wrote a pretty basic script that:
A) accesses the server web access using current credentials,
B) downloads the current Audit Log,
C) weeds out the non-user materials (build accounts, user groups/teams, service accounts, etc),
D) outputs a CSV file of all users that have logged in the past n days with their license level listed
The entire script is as follows, saved as a .ps1 and ready to be set up as a monthly job:
User Audit 30/90-Day with Licenses
$url = "https://[my.domain.com]/tfs/_api/_licenses/Export?__v=5" #version number may change
$dateToday = Get-Date -Format "MM-dd-yyyy"
$outputFolder = "$SystemDrive\TFSUserReports" #in the event you have more than one report
$auditOutput = "$outputFolder\TFSAuditLog_$dateToday.csv"
$start_time = Get-Date
New-Item -ItemType Directory -Force -Path "$SystemRoot\TFSUserReports"
$wc = New-Object System.Net.WebClient
$wc.UseDefaultCredentials = $true
$wc.DownloadFile($url, $auditOutput)
#OR
#(New-Object System.Net.WebClient).DownloadFile($url, $output)
Write-Output "Time taken to download full Audit: $((Get-Date).Subtract($start_time).Seconds) second(s)"
$licenseValue = "None"
$accounts = Import-Csv $auditOutput | foreach {
if ($_.Stakeholder -eq "1") {$licenseValue = "Stakeholder"} #convert 1s and 0s to human data
if ($_.Advanced -eq "1") {$licenseValue = "Advanced"}
if ($_.{VS Enterprise} -eq "1") {$licenseValue = "Stakeholder"}
if (($_.Basic -eq "1") -or ($_.IsDefault -eq "1")) {$licenseValue = "Basic"} #Basic is our default
New-Object PSObject -prop @{
DisplayName = $_.{Display Name};
UniqueName = $_.{Unique Name};
LastAccessed = $_.{Last Accessed (UTC)};
License = $licenseValue
}
}
#weed out accounts that aren't actual users by searching user domains used by login
$users = $accounts | where { ($_.LastAccessed -like "*M") -and (($_.UniqueName -like "DOMAIN1*") -or ($_.UniqueName -like "DOMAIN2*") -or ($_.UniqueName -like "DOMAIN3*") -or ($_.UniqueName -like "DOMAIN4*")) }
#parse the dateTime stamp and store only users that have logged in within a set period of time from today's date
$lastThirtyDayUsers = $users | where { ([DateTime]::ParseExact($_.LastAccessed,'M/d/yyyy h:mm:ss tt',$null)) -gt ((Get-Date).AddDays(-30)) }
$lastNinetyDayUsers = $users | where { ([DateTime]::ParseExact($_.LastAccessed,'M/d/yyyy h:mm:ss tt',$null)) -gt ((Get-Date).AddDays(-90)) }
#output the last 30 days of last user logins to CSV file
$lastThirtyDayUsers | Export-Csv $outputFolder\TFSUsers-Last30DayLogins_$dateToday.csv -notype
$lastNinetyDayUsers | Export-Csv $outputFolder\TFSUsers-Last90DayLogins_$dateToday.csv -notype
Write-Output "Time taken for report script to run: $((Get-Date).Subtract($start_time).Seconds) seconds"
[my.domain.com] would obviously be the address of your TFS server, and DOMAIN1 (2, 3, 4) would be the names of your user domains (i.e. DOMAIN1\User.Name = Unique Name in User Audit log).
Hope this helps somebody! I'm starting to attempt a bigger dive into the APIs TFS has to offer to get even more monthly statistics. I hope to update with more of that later on.
It's been a while and our TFS environment has changed a lot. I'm currently in the process of getting my company on board with using GIT via TFS during my phased upgrade to TFS 2015 from TFS 2013. We are still on-prem rather than VS Online, mainly due to the freedom of being able to edit work item and process templates. That being said, there's a bit of setup needed in order to maintain this ability in TFS 2015, so don't create any new team projects without this!
My understanding through research is that the new TFS 2015 out-of-the-box process templates do NOT allow editing of work item templates. To get around this, you must create your own process templates to create projects from. Follow along for each out-of-the-box template (Scrum, Agile, CMMI):
Download the process template to a local directory using the Process Template Manager
Choose local folder to download to (ex: C:\temp)
Open file location (C:\temp) and change template folder name (ex: Agile changed to My Agile)
Open folder and find Classification.xml (C:\temp\My Agile\Classification\Classification.xml)
Edit this file: Under Properties>Property name>"Process Template", change value to new template name (ex: "My Agile")
Edit this file: ProcessTemplate>metadata>name: [My Agile]
Also edit the GUID under version type! (This is where I got hung up; every time I attempted to upload the new Process Template folder before changing this GUID I would get an error that the template already existed.) Create your own GUID or simply change a couple characters; I have not seen any repercussions for this. I also changed the version to minor="1", for clarity: <version type="6B724908-EF14-45CF-84F8-768B5384XXXX" major="1" minor="1" />
Now you will be able to upload your new process template.
So far I have not found any other page explaining this GUID change, so I hope I've helped someone.
A specific user requested branching permissions for TFS yesterday, but it took me a little while to find exactly where those specific permission settings were (I am doing this through Visual Studio; specifically VS 2012 because I prefer it, but the options should be similar in VS 2010).
As it turns out, you'll have to:
Open the Source Control Explorer, find the folder that needs branching
Right-click the folder and find the security option
Add the user to the list here and ensure they have the Merge & Manage Branch options selected
(they should have inherited other permissions if they're part of the contributer group for the project already; if not, select those permissions that contributors are given by default as well).
Afterwards, the user created a branch they did not intend to and needed to delete it, but found that the "delete" option in the right-click menu was greyed out. Contrary to popular belief, this does not mean the user does not have permission to delete it. To delete a branch:
Right-click the branch, select "Get Latest Version" and select a local folder to place it
Right-click the branch, select "Delete"; notice it is marked for deletion after this
Right-click again and select "Check In Pending Changes" and complete the steps for check-in
It should disappear from the source explorer now!
I know, this seems remedial, but it took a while to figure out, so I can't be the only one.
Found a cool tool for TFS administrators to change user permissions across all aspects of TFS (Team Foundation roles, SharePoint roles, & Reporting Services roles). The TFS Administration Tool allows you to add, edit, delete and import users for any TFS project.
There was a hitch I had when using it for the first time which had to do with permissions. Since I didn't create all of the projects that are currently in our TFS collections, I didn't automatically have permissions for the SharePoint roles, and therefore couldn't administrate any settings on most projects. Our SharePoint instance for TFS is on the TFS application server, so I had to access SharePoint 3.0 Central Administration on that server. From there:
Click Application Management
Under Application Security -> Policy for Web application
Add user
choose the web application (mine was already set)
(All zones)
Choose Users -> put your username in
Permissions -> Full Control
Click Save
Unfortunately it just isn't enough to be in the Farm Administrators group.
This tool is worth checking out if you do a lot of permissions changes! It's really quick and easy to give muliple users the same permissions (like if you've just created a new project and need to add a dozen people to it).
At first, after fixing the reports on our TFS 2010 report server, I thought reports weren't running properly when changing the parameters of the report. For instance, I would change the "From (date)" field, and then click "View Report" (or hit Enter); the screen would flicker, but no reports would show. I found that if I simply clicked "View Report" (or hit Enter) again after this screen flicker, the report would load.
As it turns out, Microsoft posted some time ago about this being a normal function:
----
Posted by Microsoft
Thank you for your feedback.
We have reviewed your issue. The issue that you have reported is by design because we do need a click outside of the textbox (and that could be the first View Report click) to validate and calculate any parameter dependency. And then after that, you need to click View Report to run the report.
I received an email from a user telling me reports weren't working properly for her TFS 2010 project (keep in mind, I've found that a lot of TFS users don't seem to use reports in this division). When certain reports were run (not all of them though), this error would come up in the report area:
An error has occurred during report processing. (rsProcessingAborted) Query execution failed for dataset 'dsTestData'. (rsErrorExecutingCommand) For more information about this error navigate to the report server on the local server machine, or enable remote errors
Now there are multiple solutions to this, many of which available on the MSDN forums:
Enable remote errors and find out what's throwing the error (I didn't get to this)
Ensure the proper permissions are granted
Make sure the data sources are set up properly, and that the data source types are
TfsReportsDS -> Microsoft SQL Server
TfsOlapReportDS -> Microsoft SQL Server Analysis Services
(Sometimes these data source types are erroneously switched)
(A cool tool for checking the status of your TFS 2010 Warehouse and Analysis Services sync statuses: TFS 2010 Warehouse Service Utility from Benday.com)
There may be other issues as well; those above are ones I was in the process of exploring when I noticed something through SQL Server Management Studio...the SQL Server version number of the Analysis Server running the Tfs_Reports database (I believe Tfs_Analysis is the default name of this db); it was SQL Server 2005! SQL Server 2008 is needed in order run all reports properly.
Steps I took to fix:
Have the SQL team install Analysis Services on an instance of SQL Server 2008 R2.
Connect to the Analysis Services of that server
Create a new empty database
Through the TFS Administration Console, Reporting tab, "Stop Jobs"
"Edit" -> Analysis Services tab
Change server/instance settings (& database name if necessary), Test Connection
Empty database will be filled with data when OK is clicked
Waited a few minutes to ensure the database was updated properly, then "Start Rebuild"
Caution: I've been told this can take up to a few hours. It only took a couple minutes for me.
Using TFS 2010 Warehouse Service Utility, I told the server to Process Analysis to sync up the database with the TFS data.
After the app told me the process was finished and successful, I went to the report site and tested the reports I saw were not working. Viola! All working with proper data.
Side note: the processes to update the analysis database and allow the cube to process may take some time. If the reports don't work right away, wait 10 minutes or so after completing the steps.
I hope you find this useful! Moral of the story: when tossed into an already-functioning production environment, take the time to check all of your systems make sure everything is up to date, or at least at minimum requirements for your system to work properly. :)
