Marián Košťál – Page 5 – Blog about software development and passive income

List developers causing failed builds in TFS

I solved task how to create list of developers, which checkins causing failed builds in Team Foundation Server. I decided to solve this task using T-SQL query which is executed on team project collection database directly. Query uses common table expressions and window functions:

WITH BuildAssociatedChangeset AS
(
    SELECT 
        BuildId, 
        Fields.value('(/Fields/Field)[1]', 'int') AS ChangesetId, 
        Fields.value('(/Fields/Field)[3]', 'nvarchar(50)') AS CheckedInBy
    FROM 
        tbl_BuildInformation2 WITH (NOLOCK) 
    WHERE 
        NodeType = 12
),
BuildAssociatedChangesetWithRowNumber AS 
(
    SELECT
        BuildId,
        ChangesetId,
        CheckedInBy,
        ROW_NUMBER() OVER (PARTITION BY BuildId ORDER BY ChangesetId DESC) AS RowNumber
    FROM 
        BuildAssociatedChangeset
),
BuildTrigerringChangeset AS
(
    SELECT
        BuildId,
        ChangesetId,
        CheckedInBy,
        RowNumber
    FROM
        BuildAssociatedChangesetWithRowNumber 
    WHERE
        RowNumber = 1
)
SELECT
    ch.CheckedInBy,
    COUNT(b.BuildId) AS FailedBuilds
FROM 
    tbl_Build b WITH (NOLOCK)
JOIN
    BuildTrigerringChangeset ch ON b.BuildId = ch.BuildId
WHERE
    b.BuildStatus = 8 AND 
    b.StartTime > '2017-05-01'
GROUP BY
    ch.CheckedInBy
ORDER BY
    FailedBuilds DESC

Query was tested on TFS 2013.

PowerShell unit tests with Pester

Few days ago I have discover interesting PowerShell module Pester for creating unit tests in PowerShell. As a code for testing is used PowerShell script from my previous post dedicated to password generation. PowerShell module Pester is installed using command:

Install-Module -Name Pester -Force -SkipPublisherCheck

Installed PowerShell modules can be listed by:

Get-InstalledModule

Unit test of New-AzureSqlPassword.ps1 script is saved to New-AzureSqlPassword.Tests.ps1 file and has following code:

Describe "New-AzureSqlPassword" {
    It "Should return password 32 characters long" {
        $password = .\New-AzureSqlPassword.ps1
        $password.Length | Should -Be 32
    }

    Context "Password complexity" {
        It "Should contain lowercase letters" {
            $password = .\New-AzureSqlPassword.ps1
            $measure = $password.GetEnumerator() | Where-Object { [System.Char]::IsLower($_) } | Measure-Object 
            $measure.Count | Should -BeGreaterThan 0
        }

        It "Should contain uppercase letters" {
            $password = .\New-AzureSqlPassword.ps1
            $measure = $password.GetEnumerator() | Where-Object { [System.Char]::IsUpper($_) } | Measure-Object 
            $measure.Count | Should -BeGreaterThan 0
        }

        It "Should contain digits" {
            $password = .\New-AzureSqlPassword.ps1
            $measure = $password.GetEnumerator() | Where-Object { [System.Char]::IsDigit($_) } | Measure-Object 
            $measure.Count | Should -BeGreaterThan 0
        }

        It "Should contain special characters" {
            $password = .\New-AzureSqlPassword.ps1
            $measure = $password.GetEnumerator() | Where-Object { ($_ -eq '!') -or ($_ -eq '$') -or ($_ -eq '#') -or ($_ -eq '%') } | Measure-Object 
            $measure.Count | Should -BeGreaterThan 0
        }
    }

    Context "Password difference" {
        It "Should return different passwords when calling two times" {
            $password1 = .\New-AzureSqlPassword.ps1
            $password2 = .\New-AzureSqlPassword.ps1
            $password1 -eq $password2 | Should -BeFalse
        }
    }
}

PowerShell script and it’s unit test is placed in the same folder:

PowerShell command line

Unit test is run using command:

Invoke-Pester

Output of unit test is:

PowerShell command line

If you are interested in PowerShell automation, take my Udemy course Improve your productivity with PowerShell.

Generate strong passwords using PowerShell

Few days ago I need to generate set of passwords for Azure SQL Database. Password policy for Azure Active Directory https://docs.microsoft.com/en-us/azure/active-directory/active-directory-passwords-policy and MSSQL https://docs.microsoft.com/en-us/sql/relational-databases/security/password-policy specifies requirements for password generator. PowerShell script for strong password generation:

$specialChars = @("!", "$", "#", "%")

$guid = [System.Guid]::NewGuid().ToString("n")
$sb = New-Object System.Text.StringBuilder

for ($i = 0; $i -lt $guid.Length; $i++)
{
    $c = $guid[$i]

    if (($i % 2 -eq 0) -and [System.Char]::IsLetter($c))
    {
        $c = [System.Char]::ToUpper($c)
    }

    $random = Get-Random -Minimum 1 -Maximum 100

    if ($random % 7 -eq 0)
    {
        $c = Get-Random $specialChars
    }

    $sb.Append($c) | Out-Null
}

$sb.ToString()

Password generator is based on the System.Guid.NewGuid() method which generates 32 character length password base containing lowercase letters and numbers. In the next step are some letters transformed to uppercase or replaced by special characters. Passwords generated by script are like this:
B0F13098C34e42729d66%eD!$#41C56d 5aB4%a9cB6Bb4c319!8dB99aC631707% 485488028d5840D0AcA04$34D48a1921

If you are interested in PowerShell automation, take my Udemy course Improve your productivity with PowerShell.

PowerShell remoting over domain boundaries

Let’s imagine situation when you have server in domain and you want to execute remote PowerShell commands from the client which is not in the domain. Following recipe shows you how to configure client and server for described scenario.

On the client run PowerShell commands:

cd wsman:\localhost\Client
Set-Item .\AllowUnencrypted -Value $True -Force
Set-Item .\TrustedHosts -Value * -Force

On the server run PowerShell commands:

Enable-PSRemoting -Force
New-ItemProperty -Path HKLM:\SOFTWARE\Microsoft\Windows\CurrentVersion\WSMAN\Service -Name allow_unencrypted -Value 1 -PropertyType DWORD

On the client switch PowerShell provider to file system and run PowerShell commands to test remoting configuration:

$credential = Get-Credential -Message "Enter credentials" -UserName domain\user
Test-WSMan -ComputerName ServerName -Credential $credential -Authentication Negotiate
Enter-PSSession -ComputerName ServerName -Credential $credential

Note that this configuration is intended only for development or testing purposes and is not intended for production use.

If you are interested in PowerShell automation, take my Udemy course Improve your productivity with PowerShell.

Clean Visual Studio solution using PowerShell

At development sometimes I need to clean Visual Studio solution and remove all builded files of all build configurations (Debug, Release and other custom). To do this task manually means a lot of clicking in Visual Studio UI. To automate this boring task I created following PowerShell script:

param
(
    [string] $solution = "MyProduct.sln"
)

Set-Alias msbuild "${env:ProgramFiles(x86)}\MSBuild\14.0\Bin\MsBuild.exe"

$configurations = @("Debug", "Release")

foreach ($configuration in $configurations)
{
    msbuild $solution /t:clean /p:configuration=$configuration /v:minimal | Out-Null
}

$directories = @("bin", "obj")

foreach ($directory in $directories)
{
    Get-ChildItem -Directory -Recurse | ? { $_.Name -eq $directory } | Remove-Item -Recurse -Confirm:$false
}

Script uses following cmdlets:

Set-Alias – creates alias for msbuild
Out-Null – hides the command output
Get-ChildItem – gets the child items (directories) in specified location
Remove-Item – removes the specified items (directories)

If you are interested in PowerShell automation, take my Udemy course Improve your productivity with PowerShell.