How to Generate Custom SonarQube Issues Report Using PowerShell

---

In this tutorial, we will discuss a PowerShell script that can be used to generate SonarQube issues report for multiple projects. The script will fetch data from SonarQube API and display the results in a CSV and an HTML file.

Complete code can be found below. For latest update to the code checkout Github

Step 1: Set the SonarQube Administrator Credentials 🔐

$username = "<sonarQubeAdminUsername>"
$password = ConvertTo-SecureString "<sonarQubeAdminPassword>" -AsPlainText -Force
$Cred = New-Object System.Management.Automation.PSCredential ($username, $password)

Explanation: In this step, we are setting the SonarQube administrator username and password. We are also converting the password to a secure string and storing it in the $password variable. Finally, we are creating a PSCredential object with the username and secure password.

Step 2: Set the SonarQube URL and Projects URI 🔗

$sonarQubeUrl = "<sonarQubeUrl>"
$projectsUri = "$sonarQubeUrl/api/components/search_projects?ps=100"

Explanation: In this step, we are setting the SonarQube URL and the project URI. The URI is used to fetch a list of projects from SonarQube.

Step 3: Fetch SonarQube Issues Data for Each Project 🔎

$Results = @()
$projects = Invoke-RestMethod -Method Get -Uri $projectsUri -Authentication Basic -Credential $Cred
foreach ($project in $projects.components) {
$totalIssuesUri = "$sonarQubeUrl/api/issues/search?componentKeys=" + $project.key + "&resolved=false&ps=1"
$HighIssuesUri = "$sonarQubeUrl/api/issues/search?componentKeys=" + $project.key + "&resolved=false&severities=MAJOR&ps=1"
$CriticalIssuesUri = "$sonarQubeUrl/api/issues/search?componentKeys=" + $project.key + "&resolved=false&severities=CRITICAL&ps=1"
$TotalIssues = (Invoke-RestMethod -Method Get -Uri $totalIssuesUri -Authentication Basic -Credential $Cred).total
$HighIssues = (Invoke-RestMethod -Method Get -Uri $HighIssuesUri -Authentication Basic -Credential $Cred).total
$CriticalIssues = (Invoke-RestMethod -Method Get -Uri $CriticalIssuesUri -Authentication Basic -Credential $Cred).total
$Results += [pscustomobject] @{
"ProjectName" = $project.name
"HighIssues" = $HighIssues
"CriticalIssues" = $CriticalIssues
"TotalIssues" = $TotalIssues
}
}

Explanation: In this step, we are fetching SonarQube issues data for each project. We are first fetching the projects using the URI set in step 2. Then, for each project, we are fetching the total issues, high-severity issues, and critical-severity issues using different URIs. Finally, we are storing the data in an array of custom objects.

Step 4: Generate Total Issues Data 💯

$Results += [pscustomobject] @{
"ProjectName" = "Total"
"HighIssues" = [int]($Results | Measure-Object -Property HighIssues -Sum ).Sum
"CriticalIssues" = [int]($Results | Measure-Object -Property CriticalIssues -Sum).Sum
"TotalIssues" = [int]($Results | Measure-Object -Property TotalIssues -Sum).Sum
}

Explanation: In this step, we are generating the total issues data in a custom object. We are calculating the total number of high-severity, critical-severity, and total issues using the Measure-Object cmdlet. Finally, we are storing the data in the array of custom objects.

Step 5: Export the Data to CSV 📁

$Results | Export-Csv -Path $PSScriptRoot\sonarResult.csv -Force

Explanation: In this step, we are exporting the SonarQube issues data to a CSV file in the user-specified directory.

Step 6: Convert the Data to HTML and Export 📊

$Header = @"

TABLE {border-width: 1px; border-style: solid; border-color: black; border-collapse: collapse;}
TH {border-width: 1px; padding: 3px; border-style: solid; border-color: black; background-color: #6495ED;}
TD {border-width: 1px; padding: 3px; border-style: solid; border-color: black;}
<style>
TABLE {border-width: 1px; border-style: solid; border-color: black; border-collapse: collapse;}
TH {border-width: 1px; padding: 3px; border-style: solid; border-color: black; background-color: #6495ED;}
TD {border-width: 1px; padding: 3px; border-style: solid; border-color: black;}
</style>
"@

$Results | ConvertTo-Html -body "<H2>SonarQube Issues report</H2>" -Head $Header | Out-File $PSScriptRoot\sonarResult.html -Force

Explanation: In this step, we are converting the SonarQube issues data to HTML format and exporting it to an HTML file in the user-specified directory. We are also adding a header with some HTML styling using the ConvertTo-Html cmdlet.

Output📈

ProjectName	HighIssues	CriticalIssues	TotalIssues
project1	14	9	29
project2	7978	737	10826
project3	425	693	2229
project4	493	365	1479
project5	16	1	19
project6	32	23	58
project7	1020	834	1998
Total	9978	2662	16638

Conclusion 🎉

With this PowerShell script, analyzing SonarQube issues report for multiple projects has become a lot easier. By saving the script and running it with the required arguments, users can fetch data from SonarQube API and organize it in CSV and HTML file formats.