Exporting Sonar reported issues to excel

Recently, I was asked by one of my blog reader to share an approach for exporting issues reported by Sonar to excel. Though the same can be achieved using Sonar plugins having commercial licences & may be few by open source plugins, it is as well possible to fetch the same using the webservice API exposed by the Sonar platform.

More on this webservice java API & javadoc can be found here – http://docs.sonarqube.org/display/SONAR/Using+the+Web+Service+Java+client

I have written a simple java client which makes a webservice call to Sonar platform running in my local machine & then uses Apache POI (http://poi.apache.org/download.html) to generate the corresponding excel.

Here is the java code (Note – The below code snippet is not exhaustive because of the time constraint. Currently, it just try to fetch all the Critical, Major & Minor issues of the projects analysed by Sonar. I’ll try to enhance the same in future!) –

package com.sanjit;

import java.io.FileOutputStream;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.sonar.wsclient.SonarClient;
import org.sonar.wsclient.issue.Issue;
import org.sonar.wsclient.issue.IssueClient;
import org.sonar.wsclient.issue.IssueQuery;
import org.sonar.wsclient.issue.Issues;

public class Sample {

public static void main(String args[]) {

String login = "admin";
String password = "admin";

SonarClient client = SonarClient.create("http://localhost:9000");
client.builder().login(login);
client.builder().password(password);

IssueQuery query = IssueQuery.create();
query.severities("CRITICAL", "MAJOR", "MINOR");

IssueClient issueClient = client.issueClient();
Issues issues = issueClient.find(query);
List<Issue> issueList = issues.list();
createExcel(issueList);
}

private static void createExcel(List<Issue> issueList) {
// TODO Auto-generated method stub

try {
String filename = "D:/SonarIssues.xls";

HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = workbook.createSheet("FirstSheet");

HSSFRow rowhead = sheet.createRow((short) 0);
rowhead.createCell(0).setCellValue("Project Key");
rowhead.createCell(1).setCellValue("Component");
rowhead.createCell(2).setCellValue("Line");
rowhead.createCell(3).setCellValue("Rule Key");
rowhead.createCell(4).setCellValue("Severity");
rowhead.createCell(5).setCellValue("Message");

for (int i = 0; i < issueList.size(); i++) {
HSSFRow row = sheet.createRow((short) i+1);
row.createCell(0).setCellValue(issueList.get(i).projectKey());
row.createCell(1).setCellValue(issueList.get(i).componentKey());
row.createCell(2).setCellValue(
String.valueOf(issueList.get(i).line()));
row.createCell(3).setCellValue(issueList.get(i).ruleKey());
row.createCell(3).setCellValue(issueList.get(i).severity());
row.createCell(3).setCellValue(issueList.get(i).message());
}

FileOutputStream fileOut = new FileOutputStream(filename);
workbook.write(fileOut);
fileOut.close();
System.out.println("Your excel file has been generated!");

} catch (Exception ex) {
System.out.println(ex);

}
}
}

You will need to put the below jars in your classpath –

http://central.maven.org/maven2/org/codehaus/sonar/sonar-ws-client/4.3/sonar-ws-client-4.3.jar
http://www.apache.org/dyn/closer.cgi/poi/dev/bin/poi-bin-3.11-beta3-20141111.zip

Published by

Sanjit ...

Engineering Manager, Broadcom | Views expressed on my blogs are solely mine; not that of present/past employers | Support my work @https://ko-fi.com/sanjitmohanty

11 thoughts on “Exporting Sonar reported issues to excel”

    1. Hi,
      You can use the “GET api/issues/search” which is available since sonar web service API version 3.6.

      This API takes an optional parameter called “componentKeys” to retrieve issues associated to a specific list of components sub-components (comma-separated list of component keys). A component can be a view, developer, project, module, directory or file.

      Below is an example URL –

      http://localhost:9000/api/issues/search?componentKeys=sanjit:JavaProject

      where “sanjit:JavaProject” is my sonar project key.

      The above URL will fetch all the issues for the given project and return as JSON.

      Note:- There exist a limitation with this API. If the number of issues is greater than 10,000, only the first 10,000 ones are returned by the web service.

      _Sanjit

      Like

      Reply

  2. Hi sanjit,
    Your post helped me create an excel that can be used for our groups requirement with assignment of issues to teams. Thanks.
    Is there a way to export all the configured rules with their id, title & description into an excel? I want to be able to send this list to a few people who can filter out the really relevant ones for us.
    I tried using ProfileQuery & Profile.Rule but it doesn’t provide title & descr.
    Regards,
    Deepak

    Like

    Reply

    1. Hi,
      The blog is little outdated. Now you shouldn’t be using the sonar web service java client APIs. It is soon going to be deprecated.

      More of it here ->
      http://docs.sonarqube.org/display/DEV/Using+the+Web+Service+Java+client

      Now you should directly use their web service APIs using wink or jersey libraries & then parse the JSON from the results (use JSON libraries like gson) to get the needed data in your java client program.

      For your purpose, you should be using “api/rules/search” API,

      Below is an example URL –

      http://localhost:9000/api/rules/search

      The above URL will fetch all the configured sonar rules in JSON.

      _Sanjit

      Like

      Reply

  4. this code only fetching few issue not all. shall i know how to get all the issue.
    excel has only 173 but issue is around 37,000.

    Like

    Reply

    1. Hi Salva,
      Yes, you can retrieve issues filter by “types” as well.

      Below is a sample code which should give you all the issues filtered by type “CODE_SMELL”. Similarly, you can filter by “BUG”, “VULNERABILITY” & “SECURITY_HOTSPOT”.

      Note that the support for “types” was added from version 5.5. So, make sure you have the apt versions at client & server sides. For your reference, you can find the client side jar for version “5.6.3” over here – “https://jar-download.com/artifacts/org.sonarsource.sonarqube/sonar-ws/5.6.3/source-code”.

      Hope that helps!

      ————-clip————-
      import org.sonarqube.ws.Issues;
      import org.sonarqube.ws.client.HttpConnector;
      import org.sonarqube.ws.client.WsClient;
      import org.sonarqube.ws.client.WsClientFactories;
      import org.sonarqube.ws.client.issue.SearchWsRequest;

      import java.util.ArrayList;
      import java.util.List;

      public class SonarWebClient {
      public static void main(String args[]) {

      /*
      Possible values –
      1. CODE_SMELL
      2. BUG
      3. VULNERABILITY
      4. SECURITY_HOTSPOT
      */
      List types = new ArrayList();
      types.add(“CODE_SMELL”);

      HttpConnector httpConnector = HttpConnector.newBuilder()
      .url(“http://localhost:9000”)
      .credentials(“admin”, “admin”)
      .build();
      WsClient wsClient = WsClientFactories.getDefault().newClient(httpConnector);

      SearchWsRequest searchWsRequest = new SearchWsRequest();
      searchWsRequest.setTypes(types);

      Issues.SearchWsResponse response = wsClient.issues().search(searchWsRequest);

      List issuesList = response.getIssuesList();
      System.out.println(issuesList.size());
      }
      }
      ————-clap————-

      Like

      Reply

  8. Page size and Page Index have changed! I managed to get it work by modifying the queryMap directly (not the best way though)

    Map queryMap = query.urlParams();
    // 500 is max val
    queryMap.put(“ps”, EncodingUtils.toQueryParam( new String[]{“”+args[1] })); // page size
    queryMap.put(“p”, EncodingUtils.toQueryParam( new String[]{“”+args[2] })); // page index
    query = query.severities(“BLOCKER”,”CRITICAL”,”MAJOR”,”MINOR”);

    Like

    Reply

Leave a comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.