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:
$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.
