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

Advertisements

Sonar analysis for an Ant based Java project

Background:

In my previous post, i had explained the steps for installation and configuration of Sonar to analyse a maven based project in eclipse. In an another post, i had explained the usage of sonar-runner for non-maven based project.

In this post, I’m explaining the steps for sonar analysis of an ant based java project. Even if your java project is not Ant based, it is very simple to do the conversion in eclipse.

You just need to import your java project in eclipse and then right-click on your java project and click on export and select “Ant Buildfiles”. With this eclipse will automatically generate “build.xml” file for your java project. As a last step you have to let Eclipse know that it should use “Ant Builder” instead of the default java builder. For this right click on your java project, select “Builders” and click on “New” and select “Ant Builders”. Uncheck all other selected Builders in this window and click OK.

Steps:

  1. Start Sonar server

bin\windows-x86-32\StartSonar.bat  (for 32 bit Windows)

2. Check if Sonar server is running by opening Sonar admin page “http://localhost:9000“. Default credentials – admin/admin.

3. Below is how my Java Project structure looks in eclipse:

4. Download sonar Ant task jar from here. Place this jar in “${ECLIPSE_HOME}\plugins\org.apache.ant_<some_version>\lib” directory.

5. Edit your java project’s Build.xml file to include sonar task as target. You can refer my Build.xml and make changes as per your need:

<?xml version="1.0" encoding="UTF-8" standalone="no"?>
 <!-- WARNING: Eclipse auto-generated file.
 Any modifications will be overwritten.
 To include a user specific buildfile here, simply create one in the same
 directory with the processing instruction <?eclipse.ant.import?>
 as the first entry and export the buildfile again. -->
 <project basedir="." default="build" name="CalculatorAntJavaProject" xmlns:sonar="antlib:org.sonar.ant">
 <property environment="env"/>
 <property name="ECLIPSE_HOME" value="../../../eclipse"/>
 <property name="debuglevel" value="source,lines,vars"/>
 <property name="target" value="1.7"/>
 <property name="source" value="1.7"/>
 <property name="src.dir" value="src"/>
 <property name="test.dir" value="test"/>
 <property name="build.dir" value="target"/>
 <property name="classes.dir" value="${build.dir}/classes"/>
 <property name="reports.dir" value="${build.dir}/reports"/>
 <property name="reports.junit.xml.dir" value="${reports.dir}/junit"/>

<path id="JUnit 3.libraryclasspath">
 <pathelement location="${ECLIPSE_HOME}/plugins/org.junit_3.8.2.v3_8_2_v20100427-1100/junit.jar"/>
 </path>

<target depends="build-subprojects,build-project" name="build"/>
 <target name="build-subprojects"/>
 <target depends="init" name="build-project">
 <echo message="${ant.project.name}: ${ant.file}"/>
 <javac debug="true" debuglevel="${debuglevel}" destdir="bin" source="${source}" target="${target}">
 <src path="src"/>
 <classpath refid="CalculatorAntJavaProject.classpath"/>
 </javac>
 <javac debug="true" debuglevel="${debuglevel}" destdir="bin" source="${source}" target="${target}">
 <src path="test"/>
 <classpath refid="CalculatorAntJavaProject.classpath"/>
 </javac>
 </target>
 <target description="Build all projects which reference this project. Useful to propagate changes." name="build-refprojects"/>

<target name="clean" description="Remove all files created by the build/test process.">
 <delete dir="${build.dir}" />
 <delete dir="${reports.dir}" />
 </target>

<target name="init">
 <mkdir dir="${build.dir}"/>
 <mkdir dir="${classes.dir}"/>
 <mkdir dir="${reports.dir}"/>
 <mkdir dir="${reports.junit.xml.dir}"/>
 </target>

<target name="compile" depends="init">
 <javac srcdir="${src.dir}" destdir="${classes.dir}" classpathref="JUnit 3.libraryclasspath" fork="true" debug="true" includeAntRuntime="false" />
 <javac srcdir="${test.dir}" destdir="${classes.dir}" classpathref="JUnit 3.libraryclasspath" fork="true" debug="true" includeAntRuntime="false" />
 </target>

<target name="run-tests" depends="compile">
 <taskdef name="junit" classname="org.apache.tools.ant.taskdefs.optional.junit.JUnitTask">
 <classpath>
 <path refid="JUnit 3.libraryclasspath" />
 </classpath>
 </taskdef>

<junit fork="yes" dir="${basedir}" failureProperty="test.failed">
 <classpath location="${classes.dir}" />
 <classpath refid="JUnit 3.libraryclasspath" />

<formatter type="xml" />
 <batchtest todir="${reports.junit.xml.dir}">
 <fileset dir="${test.dir}">
 <include name="**/*Test.java" />
 </fileset>
 </batchtest>
 </junit>
 </target>

<target name="sonar" depends="compile">
 <!-- Define the Sonar task -->
 <taskdef uri="antlib:org.sonar.ant" resource="org/sonar/ant/antlib.xml">
 <classpath path="${ECLIPSE_HOME}/plugins/org.apache.ant_1.8.2.v20120109-1030/lib/sonar-ant-task-1.0.jar" />
 </taskdef>

<property name="sonar.projectDescription" value="Example application using Ant and Jacoco" />
 <property name="sonar.sources" value="${src.dir}" />
 <property name="sonar.tests" value="${test.dir}" />
 <property name="sonar.binaries" value="${classes.dir}" />
 <property name="sonar.surefire.reportsPath" value="${reports.junit.xml.dir}" />

<property name="sonar.core.codeCoveragePlugin" value="jacoco" />
 <property name="sonar.jacoco.antTargets" value="run-tests" />

<sonar:sonar key="com.sanjit.test.CalculatorAntJavaProject" version="0.1-SNAPSHOT" xmlns:sonar="antlib:org.sonar.ant" />
 </target>
 </project>

6. Add “sonar” to Ant command line argument.  Right click on java project -> Select Builders -> Select Ant Builder -> Edit -> Select Main tab -> Add “sonar” to “Arguments” -> Apply -> OK.

7. Build your java project – Select java project -> Click on “Project” from the eclipse main tab -> Click “Build Project”. This will compile your java project, run your unit test cases and do a sonar analysis.

8. Login to your java project dashboard in sonar. This should be now showing all the violations, code coverage and unit test case details for your java project.

Sonar analysis for a non-maven based Java project

Background:

In my previous post, i had explained the steps for installation and configuration of Sonar to analyse a maven based project in eclipse. You can refer the same  here.

With Sonar version greater than 2.6, there has been many refactoring done. One of the major thing was removal of tight coupling of Sonar with maven.  So, what this means is now you can analyse your non-maven based project without much of trouble!

Steps:

  1. Start Sonar server

bin\windows-x86-32\StartSonar.bat  (for 32 bit Windows)

2. Check if Sonar server is running by opening Sonar admin page “http://localhost:9000“. Default credentials – admin/admin.

3. Download “Sonar Java Runner” from here (1.3 zipped version).

4. Unzip the above file and add the “bin” dir to your windows PATH.

5. Create a “sonar-project.properties” file and put this file to the root folder of your java project.

Below is a skeleton of this file. Edit this file as per your project.

# required metadata

sonar.projectKey=sanjit:JavaProject

sonar.projectName=Java Project

sonar.projectVersion=1.0

# path to source directories (required)

sources=D:\\sanjit\\workspace\\test\\JavaProject\\src

# path to test source directories (optional)

#tests=testDir1,testDir2

# path to project binaries (optional), for example directory of Java bytecode

#binaries=binDir

# optional comma-separated list of paths to libraries. Only path to JAR file and path to directory of classes are supported.

#libraries=path/to/library.jar,path/to/classes/dir

# Uncomment those lines if some features of java 5 or java 6 like annotations, enum, ...

# are used in the source code to be analysed

#sonar.java.source=1.5

#sonar.java.target=1.5

# Uncomment this line to analyse a project which is not a java project.

# The value of the property must be the key of the language.

#sonar.language=cobol

# Advanced parameters

#my.property=value

6. Go to the root of your java project and run this command : sonar-runner.bat

Upon successful execution of above command, check in the sonar dashboard. It should now be showing sonar analysis of your project. Below is a snippet of the sonar-runner.bat output for your reference:-

D:\sanjit\workspace\test\JavaProject>sonar-runner.bat

D:\sanjit\app\sonar-runner-1.3\bin\..

Runner configuration file: D:\sanjit\app\sonar-runner-1.3\bin\..\conf\sonar-runner.properties

Project configuration file: D:\sanjit\workspace\test\JavaProject\sonar-project.properties

Runner version: 1.3

Java version: 1.6.0_31, vendor: Sun Microsystems Inc.

OS name: "Windows 7", version: "6.1", arch: "amd64"

Server: http://localhost:9000

Work directory: D:\sanjit\workspace\test\JavaProject\.sonar

17:48:47.493 WARN  .c.p.DefaultDatabase - Derby database should be used for evaluation purpose only

17:48:47.524 INFO      o.s.c.p.Database - Create JDBC datasource

17:48:51.211 INFO  actDatabaseConnector - Initializing Hibernate

17:49:04.913 INFO  .s.b.b.ProjectModule - -------------  Analyzing Java Project

17:49:06.491 INFO  .s.b.ProfileProvider - Selected quality profile : [name=Sonar way,language=java]

17:49:06.521 INFO  nPluginsConfigurator - Configure maven plugins...

17:49:06.650 INFO        org.sonar.INFO - Compare to previous analysis

17:49:06.748 INFO        org.sonar.INFO - Compare over 5 days (2012-05-18)

17:49:06.767 INFO        org.sonar.INFO - Compare over 30 days (2012-04-23)

17:49:06.818 INFO  .b.p.SensorsExecutor - Initializer ProjectFileSystemLogger...

17:49:06.821 INFO  jectFileSystemLogger - Source directories:

17:49:06.822 INFO  jectFileSystemLogger -   D:\sanjit\workspace\test\JavaProject\src

17:49:06.823 INFO  .b.p.SensorsExecutor - Initializer ProjectFileSystemLogger done: 5 ms

17:49:06.866 INFO  p.PhasesTimeProfiler - Sensor JavaSourceImporter...

17:49:07.221 INFO  p.PhasesTimeProfiler - Sensor JavaSourceImporter done: 355 ms

17:49:07.222 INFO  p.PhasesTimeProfiler - Sensor SquidSensor...

17:49:07.354 INFO  .s.p.s.SquidExecutor - Java AST scan...

17:49:07.930 INFO  .s.p.s.SquidExecutor - Java AST scan done: 576 ms

17:49:07.932 INFO  .s.p.s.SquidExecutor - Java Squid scan...

17:49:07.936 INFO  .s.p.s.SquidExecutor - Java Squid scan done: 4 ms

17:49:07.936 INFO  .s.p.s.SquidExecutor - Squid extraction...

17:49:07.967 INFO  .s.p.s.SquidExecutor - Squid extraction done: 31 ms

17:49:07.970 INFO  p.PhasesTimeProfiler - Sensor SquidSensor done: 748 ms

17:49:07.971 INFO  p.PhasesTimeProfiler - Sensor SurefireSensor...

17:49:07.973 INFO  s.p.s.SurefireSensor - parsing D:\sanjit\workspace\test\JavaProject\.sonar\target\surefire-reports

17:49:08.003 INFO  p.PhasesTimeProfiler - Sensor SurefireSensor done: 32 ms

17:49:08.003 INFO  p.PhasesTimeProfiler - Sensor CpdSensor...

17:49:08.004 INFO        org.sonar.INFO - SonarEngine is used

17:49:08.005 INFO  s.p.c.i.IndexFactory - Cross-project analysis disabled

17:49:08.182 INFO  p.PhasesTimeProfiler - Sensor CpdSensor done: 179 ms

17:49:08.183 INFO  p.PhasesTimeProfiler - Sensor CheckstyleSensor...

17:49:08.186 INFO        org.sonar.INFO - Execute Checkstyle 5.5...

17:49:08.227 INFO  ckstyleConfiguration - Checkstyle configuration: D:\sanjit\workspace\test\JavaProject\.sonar\checkstyle.xml

17:49:08.884 INFO        org.sonar.INFO - Execute Checkstyle 5.5 done: 698 ms

17:49:08.891 INFO  p.PhasesTimeProfiler - Sensor CheckstyleSensor done: 708 ms

17:49:08.891 INFO  p.PhasesTimeProfiler - Sensor PmdSensor...

17:49:08.901 INFO        org.sonar.INFO - Execute PMD 4.3...

17:49:08.907 INFO   o.s.p.p.PmdExecutor - Java version: 1.5

17:49:08.950 INFO   o.s.p.p.PmdExecutor - PMD configuration: D:\sanjit\workspace\test\JavaProject\.sonar\pmd.xml

17:49:10.494 INFO        org.sonar.INFO - Execute PMD 4.3 done: 1594 ms

17:49:10.803 INFO  p.PhasesTimeProfiler - Sensor PmdSensor done: 1912 ms

17:49:10.803 INFO  p.PhasesTimeProfiler - Sensor ProfileSensor...

17:49:11.894 INFO  p.PhasesTimeProfiler - Sensor ProfileSensor done: 1091 ms

17:49:11.895 INFO  p.PhasesTimeProfiler - Sensor ProfileEventsSensor...

17:49:12.024 INFO  p.PhasesTimeProfiler - Sensor ProfileEventsSensor done: 129 ms

17:49:12.025 INFO  p.PhasesTimeProfiler - Sensor VersionEventsSensor...

17:49:12.336 INFO  p.PhasesTimeProfiler - Sensor VersionEventsSensor done: 311 ms

17:49:12.338 INFO  p.PhasesTimeProfiler - Sensor CoberturaSensor...

17:49:12.339 INFO  p.PhasesTimeProfiler - Sensor CoberturaSensor done: 1 ms

17:49:12.861 INFO  p.PhasesTimeProfiler - Execute decorators...

17:49:14.875 INFO  .b.p.UpdateStatusJob - ANALYSIS SUCCESSFUL, you can browse http://localhost:9000

17:49:14.882 INFO  b.p.PostJobsExecutor - Executing post-job class org.sonar.plugins.core.batch.IndexProjectPostJob

17:49:16.229 INFO  b.p.PostJobsExecutor - Executing post-job class org.sonar.plugins.dbcleaner.ProjectPurgePostJob

17:49:16.483 INFO  .p.d.p.KeepOneFilter - -> Keep one snapshot per day between 2012-04-25 and 2012-05-23

17:49:16.486 INFO  .p.d.p.KeepOneFilter - -> Keep one snapshot per week between 2011-05-25 and 2012-04-25

17:49:16.489 INFO  .p.d.p.KeepOneFilter - -> Keep one snapshot per month between 2007-05-30 and 2011-05-25

17:49:16.491 INFO  .d.p.DeleteAllFilter - -> Delete data prior to: 2007-05-30

17:49:16.513 INFO  o.s.c.purge.PurgeDao - -> Clean Java Project [id=1]

Total time: 47.133s

Final Memory: 8M/116M

Configuring Sonar In Your Window’s Desktop

1.  Install Sonar Server In Windows

In order to connect sonar maven plugin and/or sonar eclipse plugin to the local sonar server (for example in windows PC), you need to install the sonar server.

Here are the steps :

  • Download and unpack the sonar server zip file from here.
  • Start sonar server:

          bin\windows-x86-32\StartSonar.bat  (for 32 bit Windows)

  • Open Sonar admin page “http://localhost:9000“. Default credentials – admin/admin
  • Either create a new sonar profile by importing all rules files –                                                                                                               (a) Edit configuration given in top right web page: Configuration-> Create . Enter a profile name and select Checkstyle, Findbugs and PMD configuration files.

          (b) Click “Create” and set the new profile as default

  • Or restore the configuration from a backup – Configuration->System->Backup->Restore configuration.

2.  Install Sonar plugin in the Eclipse IDE

  • Help> Install New Software
  • Paste “http://dist.sonar-ide.codehaus.org/eclipse/ into the field named “Work with:” and press Enter.
  • Select: “Sonar Integration for Eclipse..
  • Click Next. Eclipse will then check to see if there are any issues which would prevent a successful installation.
  • Click Finish to begin the installation process. Eclipse will then download and install the necessary components.
  • Once the installation process is finished, Eclipse will ask you if you want to restart the IDE. It’s strongly recommended that you restart IDE.

3.  Eclipse Configuration After Sonar plugin installation

    (a) Server Configuration In Eclipse:-

  • Window > Preferences > Sonar
  • Add
  • Check if Sonar server lists shows a link like  http://localhost:9000. If it doesn’t then add the link as New Sonar Server Connection > Sonar Server URL.
  • Default credentials: admin/admin
  • Click Test Connection . (will get Successfully connected popup)
  • Ok …
  • Finish
  • Apply
  • Ok

   (b) Download “clover.licence” file from here. This is a FREE TRIAL VERSION and it is valid for only 30 days.

   (c) Edit the pom.xml of the maven project to include the below for which you need to run the sonar. Edit proper “licenceLocation” field.


<plugin>

<groupId>com.atlassian.maven.plugins</groupId>

<artifactId>maven-clover2-plugin</artifactId>

<version>2.6.3</version>

<configuration>

<licenseLocation>Give the absolute location of the “clover.license” file</licenseLocation>

</configuration>

<executions>

<execution>

<phase>verify</phase>

<goals>

<goal>instrument</goal>

<goal>check</goal>

</goals>

</execution>

</executions>

</plugin>

4.  Running the Maven project for analysing the code through Sonar.

     (a) Run Sonar on the maven project

          mvn clean install sonar:sonar

     (b) Once the build is succesfull, login to sonar admin page to see the violations. Below is a snippet for the same:

 

5. Configuring Sonar Eclipse

    (a) Link projects to Sonar Eclipse

          This linkage operation can be done by right-clicking on the the project into the “Package explorer”, and then by choosing “Configure ->   Associate with Sonar…”

     

You are now all done with configuration and can start using Sonar Eclipse. Change your eclipse perspective to “Sonar”.