Public/TestTrend/New-TeamcityTrendReport.ps1
|
<#
The MIT License (MIT) Copyright (c) 2015 Objectivity Bespoke Software Specialists Permission is hereby granted, free of charge, to any person obtaining a copy of this software and associated documentation files (the "Software"), to deal in the Software without restriction, including without limitation the rights to use, copy, modify, merge, publish, distribute, sublicense, and/or sell copies of the Software, and to permit persons to whom the Software is furnished to do so, subject to the following conditions: The above copyright notice and this permission notice shall be included in all copies or substantial portions of the Software. THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE. #> function New-TeamcityTrendReport { <# .SYNOPSIS Generates Teamcity tests trend reports (CSV / HTML) basing on data available in TeamCity database. .DESCRIPTION It runs a sql command directly in TeamCity database and generates csv / html reports basing on that. .PARAMETER TeamcityBuildId Id of currently running Teamcity build (%teamcity.build.id%). .PARAMETER TeamcityDbConnectionString Connection string that will be used to connect to TeamCity database. .PARAMETER TeamcityCurrentBuildNumber Current Teamcity build number (%build.number%) - required for reporting test results for current build. .PARAMETER OutputDir Output directory where .html / .png / .csv files will be generated. .PARAMETER TestNameConversionRegex Regex for shortening test names, e.g. if test is named 'Category: LongName-TestName', and you provide regex 'Category: LongName-(.*)', only 'TestName' will be displayed. .PARAMETER TestNameIncludeRegex Regex for filtering tests - only names matching this regex will be included. .PARAMETER TestNameExcludeRegex Regex for filtering tests - names matching this regex will be excluded. .PARAMETER OutputCsvName Name of output csv file. .PARAMETER OutputHtmlName Name of output html file. .PARAMETER InputThresholdCsvPath Path to the CSV with test time thresholds - columns TestName,PassedTime,FailedTime. Tests with time <= PassedTime will be marked green, Passed Time < time < FailedTime amber, and time >= FailedTime red. .PARAMETER NumberOfLastBuilds Number of builds that will be trended - all earlier builds will be ignored. .PARAMETER GenerateCsvFile If true, CSV file will be generated. .EXAMPLE New-TeamcityTrendReport TestNames @('test1','test2') -TeamcityBuildTypeId 'Client_Project_ConfName' -TeamcityDbServer 'Teamcity' -TeamcityDbUser 'TeamCityExtensions' -TeamcityDbPassword 'XXX' -OutputCsvPath 'c:\output\jmeter-trend.csv' -OutputHtmlPath 'c:\output\jmeter-trend.html' #> [CmdletBinding()] [OutputType([string[]])] param( [Parameter(Mandatory=$true)] [string] $TeamcityBuildId, [Parameter(Mandatory=$true)] [string] $TeamcityDbConnectionString, [Parameter(Mandatory=$false)] [string] $OutputDir, [Parameter(Mandatory=$false)] [string[]] $TestNameConversionRegex, [Parameter(Mandatory=$false)] [string] $TestNameIncludeRegex, [Parameter(Mandatory=$false)] [string] $TestNameExcludeRegex, [Parameter(Mandatory=$false)] [string] $OutputCsvName = 'TestTrendReport.csv', [Parameter(Mandatory=$false)] [string] $OutputHtmlName = 'TestTrendReport.html', [Parameter(Mandatory=$false)] [string] $InputThresholdCsvPath, [Parameter(Mandatory=$false)] [int] $NumberOfLastBuilds = 30, [Parameter(Mandatory=$false)] [switch] $GenerateCsvFile ) $testTimeThresholdData = Get-TestTimeThresholdData -InputThresholdCsvPath $InputThresholdCsvPath if (!(Test-Path -LiteralPath $OutputDir)) { Write-Log -Info "Creating directory '$OutputDir'" [void](New-Item -Path $OutputDir -ItemType Directory) } $csvOutputPath = Join-Path -Path $OutputDir -ChildPath $OutputCsvName $htmlOutputPath = Join-Path -Path $OutputDir -ChildPath $OutputHtmlName $sql = Get-TeamCityTrendReportSql $sql = $sql -f $TeamcityBuildId, $NumberOfLastBuilds Write-Log -Info "Getting trend data from TeamCity database, BuildId: '$TeamcityBuildId', NumberOfLastBuilds: $NumberOfLastBuilds" $sqlResult = Invoke-Sql -ConnectionString $TeamcityDbConnectionString -Query $sql if (!$sqlResult -or !$sqlResult.Tables -or $sqlResult.Tables.Count -lt 2) { Write-Log -Warn "No trend data returned from TeamCity database. Please ensure parameters are correct. Sql: $sql" return } $sqlResult = $sqlResult.Tables # there are two tables returned from sql - buildIdData (containing build id - build name - success) and trendData (containing pivoted build_id / test_name -> duration table) $buildIdData = $sqlResult[0] $trendData = $sqlResult[1] if ($TestNameConversionRegex) { foreach ($entry in $trendData) { foreach ($regex in $TestNameConversionRegex) { if ($entry.test_name -imatch $regex -and $matches[1]) { $entry.test_name = $matches[1] } } } } if ($TestNameIncludeRegex) { $trendData = $trendData | Where-Object { $_.test_name -imatch $TestNameIncludeRegex } } if ($TestNameExcludeRegex) { $trendData = $trendData | Where-Object { $_.test_name -inotmatch $TestNameExcludeRegex } } $buildIdMap = @{} foreach ($row in $buildIdData) { $buildIdMap[[string]($row.build_id)] = $row } Write-Log -Info "Generating html report" if ($GenerateCsvFile) { ConvertTo-CsvInBuildNameOrder -BuildIdMap $buildIdMap -TrendData $trendData -CsvOutputPath $csvOutputPath } $htmlChartData = $trendData | ConvertTo-EnhancedHTMLFragmentRickshawJavascriptData -JavascriptVariableName 'TestData' -PropertySeriesName 'test_name' -BuildIdMap $buildIdMap ` -PrefixCode "var palette = new Rickshaw.Color.Palette({ scheme: 'munin' } );" $htmlTestTimeThresholdData = $testTimeThresholdData | ConvertTo-EnhancedHTMLFragmentJavascriptHashtable -JavascriptVariableName 'TestTimeThresholdData' $htmlChart = ConvertTo-EnhancedHTMLFragmentRickshawChart -JavascriptDataVariableName 'TestData' -JavascriptTestTimeThresholdDataVariableName 'TestTimeThresholdData' $javascriptUri= @('http://code.jquery.com/jquery-1.11.3.min.js', 'http://cdn.datatables.net/1.10.9/js/jquery.dataTables.min.js', 'http://cdn.datatables.net/fixedcolumns/3.1.0/js/dataTables.fixedColumns.min.js', 'http://cdnjs.cloudflare.com/ajax/libs/d3/3.5.6/d3.min.js', 'https://code.jquery.com/ui/1.11.4/jquery-ui.min.js', 'http://cdnjs.cloudflare.com/ajax/libs/rickshaw/1.5.1/rickshaw.min.js') Write-Log -Info "Generating Test Trend HTML report at '$htmlOutputPath'." $params = @{'HTMLFragments' = @($htmlChartData, $htmlTestTimeThresholdData, $htmlChart); 'JavascriptUri' = $javascriptUri; 'CssStyleSheet' = @((Get-DefaultJqueryDataTableCss), (Get-DefaultRickshawCss)); 'CssUri' = @('http://cdn.datatables.net/1.10.9/css/jquery.dataTables.css', 'https://cdn.datatables.net/fixedcolumns/3.1.0/css/fixedColumns.dataTables.min.css', 'http://ajax.googleapis.com/ajax/libs/jqueryui/1.11.4/themes/smoothness/jquery-ui.min.css') } ConvertTo-EnhancedHTML @params | Out-File -FilePath $htmlOutputPath -Encoding UTF8 } function ConvertTo-CsvInBuildNameOrder { <# .SYNOPSIS Generates a CSV file containing trend data. .DESCRIPTION The columns in CSV file are ordered by build names. .PARAMETER BuildIdMap Hashmap mapping build_id to sql row. .PARAMETER TrendData Raw trend data as taken from sql command. .PARAMETER CsvOutputPath Path to the output CSV file. .EXAMPLE ConvertTo-CsvInBuildNameOrder -BuildIdMap $buildIdMap -TrendData $trendData -CsvOutputPath $csvOutputPath #> [CmdletBinding()] [OutputType([string])] param( [Parameter(Mandatory=$true)] [hashtable] $BuildIdMap, [Parameter(Mandatory=$true)] [object[]] $TrendData, [Parameter(Mandatory=$true)] [string] $CsvOutputPath ) Write-Log -Info "Generating Test Trend CSV report at '$csvOutputPath'." $trendData | Foreach-Object { $newRow = New-Object PSObject $row = $_ Add-Member -InputObject $newRow -Name 'test_name' -Value $_['test_name'] -MemberType NoteProperty Get-Member -InputObject $row -MemberType Properties | Where-Object { $_.Name -ne 'test_name' } | ` Foreach-Object { Add-Member -InputObject $newRow -Name $BuildIdMap[$_.Name].build_number -Value $row[$_.Name] -MemberType NoteProperty } $newRow } | ConvertTo-Csv -NoTypeInformation | Out-File -FilePath $CsvOutputPath } function Get-TeamCityTrendReportSql { <# .SYNOPSIS Returns a sql that returns trend data from Teamcity database. .EXAMPLE $sql = Get-TeamCityTrendReportSql #> [CmdletBinding()] [OutputType([string])] param() return @" -- Test Trend Report (PSCI / New-TeamcityTrendReport) SET QUOTED_IDENTIFIER ON SET NOCOUNT ON DECLARE @cols AS NVARCHAR(MAX), @query AS NVARCHAR(MAX) if object_id('tempdb..#builds') is not null drop table #builds; if object_id('tempdb..#tests') is not null drop table #tests; select top {1} build_id, build_number, cast(row_number() over (partition by build_number order by build_number) as varchar) as build_row, count(build_number) over (partition by build_number order by build_number) non_distinct_build_numbers, success into #builds from ( select build_id, build_number, cast(1 as bit) as success from dbo.running r where build_id = {0} union all select h.build_id, h.build_number, cast(case when h.status = 1 then 1 else 0 end as bit) as success from dbo.history h inner join (select build_type_id from dbo.running where build_id = {0} union select build_type_id from dbo.history where build_id = {0} ) currentBuild on currentBuild.build_type_id = h.build_type_id where h.status <> 0 -- cancelled ) x where exists (select 1 from dbo.test_info where build_id = x.build_id) order by build_id desc; select build_id, case when non_distinct_build_numbers = 1 then b.build_number else b.build_number + '_' + build_row end build_number, success from #builds b select b.build_id, tn.test_name, ti.duration into #tests from #builds b inner join dbo.test_info ti on ti.build_id = b.build_id inner join dbo.test_names tn on tn.id = ti.test_name_id /*inner join dbo.test_info tiFilter on tiFilter.test_name_id = tn.id and tiFilter.build_id = (select top 1 build_id from #builds order by build_id desc)*/ where ti.status <> 0 -- ignored tests select @cols = STUFF((SELECT ',' + QUOTENAME(build_id) from #tests group by build_id order by build_id FOR XML PATH(''), TYPE ).value('.', 'NVARCHAR(MAX)') ,1,1,'') set @query = 'select test_name, ' + @cols + ' from ( select test_name, build_id, duration from #tests ) x pivot ( max(duration) for build_id in (' + @cols + ') ) p order by test_name' execute(@query) "@ } |