Clean up unused TFS Work Item Fields

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."

Hope this helps somebody!

Licenses and the Audit Log - TFS 2017

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.