Exporting from SonarQube to a CSV file (hence Excel XLSX file) for several projects, defaulted to the authenticated user's Favorites. Last run with success on 7.6.0.21501

Export-SonarQubeProject.ps1

Function Export-SonarQubeProject {
<#
.DESCRIPTION
    Export projects from SonarQube into a .CSV (hence after Excel .XSLX file)
    Script takes care of retrieving up to 10000 hits iteratively for your search criteria. And will warn if you have over 10000 hits which is SonarQube's limitation.

    You can adapt the default parameters below for easy usage. In the 'Process' section there are parameters for more or less filtering on SonarQube projects.

.EXAMPLE
    Export by user's favorites. Make sure you run this .PS1 file so it is loaded into your session.
    
    Export-SonarQubeProject

.EXAMPLE
    Export by user's favorites. Make sure you run this .PS1 file so it is loaded into your session.
    
    Export-SonarQubeProject -baseUrl http://amscdsonar:9000 -user admin -password admin -csvFilePath C:\TEMP\SonarQubeProject--Vulnerability.20190305.csv

.EXAMPLE
    Export by explicit componentKeys, so Favorites are skipped. Make sure you run this .PS1 file so it is loaded into your session.
    
    Export-SonarQubeProject -componentArray @("lcr")

.EXAMPLE
    Export by explicit componentKeys, so Favorites are skipped. Make sure you run this .PS1 file so it is loaded into your session. You can catch the resulting JSON objects as output.
    
    $resultingObjects = Export-SonarQubeProject -componentArray @("lcr") -PassThru

.NOTE
    ddemeyer/20190306  Initial
    ddemeyer/20190319  Added PassThru raw JSON to allow comparisons
#>


[cmdletbinding()]
param(
    [Parameter(Mandatory=$False)]
    $baseUrl = "http://localhost:9000",
    [Parameter(Mandatory=$False)]
    $user = "admin",
    [Parameter(Mandatory=$False)]
    $password = "admin",
    [Parameter(Mandatory=$False)]
    $csvFilePath = "C:\TEMP\SonarQubeProject--Vulnerability.20190305.csv",
    [Parameter(Mandatory=$False)]
    $componentArray,
    [Parameter(Mandatory=$False)]
    [switch]$PassThru = $False
)
Process
{
    #
    # The parameters we filter with to generate the report headers and content
    # Remember total search hits are maxed out at 10000, so query rewrites are required then
    # 
    $additionalFields = "comments,languages,actionPlans" # _all
    #$owaspTop10 = "a1,a2,a3,a4,a5,a6,a7,a8,a9,a10"
    #$resolutions = "FALSE-POSITIVE,WONTFIX,FIXED,REMOVED"
    $severities = "CRITICAL,BLOCKER" # INFO,MINOR,MAJOR,
    $statuses = "OPEN,CONFIRMED,REOPENED" #,RESOLVED,CLOSED"
    $types = "VULNERABILITY" # BUG,SECURITY_HOTSPOT,CODE_SMELL
    #$resolved = "false"
    $ps = 500


    #
    # Basic Test
    #
    $headers = @{
        "Authorization"=("Basic " + ([Convert]::ToBase64String([Text.Encoding]::ASCII.GetBytes(("{0}:{1}" -f $user,$password)))))
        "Content-Type"=("application/json")
        "Accept" = ("application/json")
    }
    Write-Host ("Connecting to baseUrl[$baseUrl] user[$user]...")
    $result = Invoke-RestMethod -Headers $headers -UseBasicParsing -Method Get -Uri "$baseUrl/api/server/version"
    Write-Host ("Connected to version[$result]")


    #
    # Either explicitly fill up the $componentArray with SonarQube project keys
    # Or use all the keys stored as Favorites on the authenticated user
    #
    Write-Host ("Retrieving project keys over the $user's Favorites...")
    if ($componentArray.Count -eq 0)
    {
        $result = Invoke-RestMethod -Headers $headers -UseBasicParsing -Method Get -Uri "$baseUrl/api/favorites/search"
        $componentArray = $result.favorites.key
    }
    Write-Host ("Retrieved "+$result.favorites.key.Count+" keys. componentArray["+($componentArray -join ",")+"]")


    #
    # iterate over pages (p) of page size (ps=500), of every componentKeys
    #
    $allIssues = @()
    foreach ($componentKeys in $componentArray)
    {
        Write-Host ("Retrieving for componentKeys[$componentKeys]...")
        $query = "ps=$ps&componentKeys=$componentKeys&additionalFields=$additionalFields&severities=$severities&types=$types&statuses=$statuses"
        $request = "$baseUrl/api/issues/search?$query"
        Write-Verbose ("request[$request]")
        $json = Invoke-RestMethod -Headers $headers -UseBasicParsing -Method Get -Uri $request
        $issues = $json.issues # aggregating
        if ($json.total -ge 10000) { Write-Warning ("More results than expected, "+$json.total+">10000") }
        Write-Host ("Retrieved "+$issues.Count+"/"+$json.total+"")

        $totalPages = [math]::ceiling($json.total / $ps)
        for ($pageIndex=2; $pageIndex -le $totalPages; $pageIndex++)
        {
            $json = Invoke-RestMethod -Headers $headers -UseBasicParsing -Method Get -Uri "$request&p=$pageIndex"
            $issues += $json.issues # aggregating
            Write-Host ("Retrieved "+$issues.Count+"/"+$json.total+"")
        }
        $allIssues += $issues
    }

    #
    # If switch passthrough is activated, then put on the pipeline
    # 
    if ($PassThru)
    {
        Write-Host ("Pass Through of raw json objects to the pipeline")
        Write-Output $allIssues
    }

    #
    # Exporting the chosen properties to a CSV file
    #
    Write-Host ("Exporting to csvFilePath[$csvFilePath]")
    $allIssues | 
    Select-Object -Property key,type,rule,message,severity,status,project,component,line,textRange,author,creationDate,updateDate | 
    Export-Csv -Path $csvFilePath
    Write-Host ("Done")
}
}