This mini series introduces you to various sources of software data
SonarQube is the de facto standard when Java development teams want check the internal software quality of their software systems. It's an easy tools that integrates well into an existing continuous integration landscape. SonarQube is also a huge gold mine with hundreds of metrics and rules that check you code.
Albeit I'm personally not a big fan of context-free software analysis, we can use the data source from SonarQube to enrich our specific data analysis if needed. Let's have a look at the API that let's us retrieve some data that could help us to improve software systematically.
import requests
from pandas.io.json import json_normalize
ALL_ISSUES_URL = "https://sonarcloud.io/api/issues/search?languages=java&componentKeys=org.springframework.samples:spring-petclinic:boundedcontexts"
# load issues
json_data = requests.get(ALL_ISSUES_URL).json()['issues']
# flatten json structure and create DataFrame
df = json_normalize(json_data)
df.head()
component | creationDate | debt | effort | flows | fromHotspot | hash | key | line | message | ... | rule | severity | status | tags | textRange.endLine | textRange.endOffset | textRange.startLine | textRange.startOffset | type | updateDate | |
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
0 | org.springframework.samples:spring-petclinic:b... | 2018-03-07T07:45:21+0100 | 2min | 2min | [] | False | 48c1dff4bc3cd8f9b9b8c76f49484c2a | AWLJtvMj-pl6AHs2EogL | 5 | Rename this field "INSTANCE" to match the regu... | ... | squid:S3008 | MINOR | OPEN | [convention] | 5 | 42 | 5 | 34 | CODE_SMELL | 2018-04-15T16:28:40+0200 |
1 | org.springframework.samples:spring-petclinic:b... | 2018-03-07T07:45:21+0100 | 20min | 20min | [] | False | 48c1dff4bc3cd8f9b9b8c76f49484c2a | AWLJtvMj-pl6AHs2EogM | 5 | Make this "public static INSTANCE" field final | ... | squid:S1444 | MINOR | OPEN | [cert, cwe] | 5 | 42 | 5 | 34 | VULNERABILITY | 2018-04-15T16:28:40+0200 |
2 | org.springframework.samples:spring-petclinic:b... | 2018-03-07T07:45:21+0100 | 10min | 10min | [] | False | 48c1dff4bc3cd8f9b9b8c76f49484c2a | AWLJtvMj-pl6AHs2EogN | 5 | Make INSTANCE a static final constant or non-p... | ... | squid:ClassVariableVisibilityCheck | MINOR | OPEN | [cwe] | 5 | 42 | 5 | 34 | VULNERABILITY | 2018-04-15T16:28:40+0200 |
3 | org.springframework.samples:spring-petclinic:b... | 2017-11-22T17:54:34+0100 | 5min | 5min | [{'locations': [{'component': 'org.springframe... | False | 815a0864eec54976893cbe650dfba48d | AWLJtvMo-pl6AHs2EogP | 11 | Remove this unused method parameter "id". | ... | squid:S1172 | MAJOR | OPEN | [cert, misra, unused] | 11 | 60 | 11 | 58 | CODE_SMELL | 2018-04-15T16:28:40+0200 |
4 | org.springframework.samples:spring-petclinic:b... | 2017-11-22T17:54:34+0100 | 15min | 15min | [] | False | eb375774c265dedeefeb29283ceea9bc | AWLJtvMo-pl6AHs2EogR | 15 | Either re-interrupt this method or rethrow the... | ... | squid:S2142 | MAJOR | OPEN | [cwe, multi-threading] | 15 | 39 | 15 | 17 | BUG | 2018-04-15T16:28:40+0200 |
5 rows × 22 columns
df.info()
<class 'pandas.core.frame.DataFrame'> RangeIndex: 55 entries, 0 to 54 Data columns (total 22 columns): component 55 non-null object creationDate 55 non-null object debt 55 non-null object effort 55 non-null object flows 55 non-null object fromHotspot 55 non-null bool hash 55 non-null object key 55 non-null object line 55 non-null int64 message 55 non-null object organization 55 non-null object project 55 non-null object rule 55 non-null object severity 55 non-null object status 55 non-null object tags 55 non-null object textRange.endLine 55 non-null int64 textRange.endOffset 55 non-null int64 textRange.startLine 55 non-null int64 textRange.startOffset 55 non-null int64 type 55 non-null object updateDate 55 non-null object dtypes: bool(1), int64(5), object(16) memory usage: 9.2+ KB
import pandas as pd
df.to_excel("sonar_issues_spring_petclinic.xlsx", columns=['project','component', 'rule', 'type', 'severity', 'debt', 'status', 'message'], index=None)
import requests
url = "https://sonarcloud.io/api/issues/search?componentKeys=net.java.openjdk:jdk9"
data = []
start = 1
end = 499
def get_url(start, end):
return url + "&p={}&ps={}".format(start,end)
total = requests.get(get_url(1,1)).json()['total']
total
348770
(698, 468)
divident = 499
integer = int(total / divident)
remainder = total % divident
(integer,remainder)
counter = 1
data = []
def add_data(counter):
u = url + "&p={}&ps=499".format(counter)
print(u)
data.extend(requests.get(u).json()['issues'])
while counter < 698 + 1:
add_data(counter)
counter = counter + 1
req = u = url + "&p={}&ps={}".format(counter, remainder)
data.extend(requests.get(u).json()['issues'])
len(data)
https://sonarcloud.io/api/issues/search?componentKeys=net.java.openjdk:jdk9&p=1&ps=499 https://sonarcloud.io/api/issues/search?componentKeys=net.java.openjdk:jdk9&p=2&ps=499 https://sonarcloud.io/api/issues/search?componentKeys=net.java.openjdk:jdk9&p=3&ps=499 https://sonarcloud.io/api/issues/search?componentKeys=net.java.openjdk:jdk9&p=4&ps=499 https://sonarcloud.io/api/issues/search?componentKeys=net.java.openjdk:jdk9&p=5&ps=499 https://sonarcloud.io/api/issues/search?componentKeys=net.java.openjdk:jdk9&p=6&ps=499 https://sonarcloud.io/api/issues/search?componentKeys=net.java.openjdk:jdk9&p=7&ps=499 https://sonarcloud.io/api/issues/search?componentKeys=net.java.openjdk:jdk9&p=8&ps=499 https://sonarcloud.io/api/issues/search?componentKeys=net.java.openjdk:jdk9&p=9&ps=499 https://sonarcloud.io/api/issues/search?componentKeys=net.java.openjdk:jdk9&p=10&ps=499 https://sonarcloud.io/api/issues/search?componentKeys=net.java.openjdk:jdk9&p=11&ps=499 https://sonarcloud.io/api/issues/search?componentKeys=net.java.openjdk:jdk9&p=12&ps=499 https://sonarcloud.io/api/issues/search?componentKeys=net.java.openjdk:jdk9&p=13&ps=499 https://sonarcloud.io/api/issues/search?componentKeys=net.java.openjdk:jdk9&p=14&ps=499 https://sonarcloud.io/api/issues/search?componentKeys=net.java.openjdk:jdk9&p=15&ps=499 https://sonarcloud.io/api/issues/search?componentKeys=net.java.openjdk:jdk9&p=16&ps=499 https://sonarcloud.io/api/issues/search?componentKeys=net.java.openjdk:jdk9&p=17&ps=499 https://sonarcloud.io/api/issues/search?componentKeys=net.java.openjdk:jdk9&p=18&ps=499 https://sonarcloud.io/api/issues/search?componentKeys=net.java.openjdk:jdk9&p=19&ps=499 https://sonarcloud.io/api/issues/search?componentKeys=net.java.openjdk:jdk9&p=20&ps=499 https://sonarcloud.io/api/issues/search?componentKeys=net.java.openjdk:jdk9&p=21&ps=499
--------------------------------------------------------------------------- KeyError Traceback (most recent call last) <ipython-input-36-456825bf66f8> in <module>() 14 15 while counter < 698 + 1: ---> 16 add_data(counter) 17 counter = counter + 1 18 <ipython-input-36-456825bf66f8> in add_data(counter) 11 u = url + "&p={}&ps=499".format(counter) 12 print(u) ---> 13 data.extend(requests.get(u).json()['issues']) 14 15 while counter < 698 + 1: KeyError: 'issues'
import json
with open ("jdk9_issues_excerpt.json", mode='w') as f:
f.write(json.dumps(data))
from pandas.io.json import json_normalize
# flatten json structure and create DataFrame
df = json_normalize(data)
df.head()
author | closeDate | component | creationDate | debt | effort | flows | hash | key | line | ... | severity | status | subProject | tags | textRange.endLine | textRange.endOffset | textRange.startLine | textRange.startOffset | type | updateDate | |
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
0 | mchung | NaN | net.java.openjdk:jdk9:jdk:src/java.base/share/... | 2016-11-10T16:11:08+0100 | 5min | 5min | [] | ceb3b90ca0cef1868923530915cc87b9 | AVhO6lMH0AIB2Vhfjr-u | 226.0 | ... | MINOR | OPEN | net.java.openjdk:jdk9:jdk | [convention] | 226.0 | 29.0 | 226.0 | 25.0 | CODE_SMELL | 2016-11-10T16:11:08+0100 |
1 | mchung | NaN | net.java.openjdk:jdk9:jdk:src/java.base/share/... | 2016-11-10T16:11:08+0100 | 5min | 5min | [] | b640fdfabab5d1b62582d4e25d59c606 | AVhO6lMI0AIB2Vhfjr-v | 386.0 | ... | MINOR | OPEN | net.java.openjdk:jdk9:jdk | [convention] | 386.0 | 25.0 | 386.0 | 14.0 | CODE_SMELL | 2016-11-10T16:11:08+0100 |
2 | duke | NaN | net.java.openjdk:jdk9:jdk:src/java.base/share/... | 2016-11-10T16:11:08+0100 | 13min | 13min | [{'locations': [{'component': 'net.java.openjd... | 5eb1048a65cef9f848fe69a1337d77e1 | AVhO6lSS0AIB2Vhfjr-x | 322.0 | ... | CRITICAL | OPEN | net.java.openjdk:jdk9:jdk | [brain-overload] | 322.0 | 26.0 | 322.0 | 18.0 | CODE_SMELL | 2016-11-10T16:11:08+0100 |
3 | mchung | NaN | net.java.openjdk:jdk9:jdk:src/java.base/share/... | 2016-11-10T16:11:08+0100 | 2min | 2min | [] | f0130b8994e5c47e50e1a2e79d2a7236 | AVhO6lSR0AIB2Vhfjr-w | 484.0 | ... | MINOR | OPEN | net.java.openjdk:jdk9:jdk | [convention] | 484.0 | 41.0 | 484.0 | 27.0 | CODE_SMELL | 2016-11-10T16:11:08+0100 |
4 | smarks | NaN | net.java.openjdk:jdk9:jdk:src/java.base/share/... | 2016-11-10T16:11:08+0100 | 5min | 5min | [] | 2ebc8d0c1f68c70bb912de5d30f06d41 | AVhO6myN0AIB2Vhfjr-y | 95.0 | ... | MINOR | OPEN | net.java.openjdk:jdk9:jdk | [convention] | 95.0 | 21.0 | 95.0 | 16.0 | CODE_SMELL | 2016-11-10T16:11:08+0100 |
5 rows × 25 columns
df.type.value_counts()
CODE_SMELL 8883 BUG 951 VULNERABILITY 146 Name: type, dtype: int64
v = df[df.type == 'VULNERABILITY']
v.head()
author | closeDate | component | creationDate | debt | effort | flows | hash | key | line | ... | severity | status | subProject | tags | textRange.endLine | textRange.endOffset | textRange.startLine | textRange.startOffset | type | updateDate | |
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
306 | vromero | NaN | net.java.openjdk:jdk9:langtools:src/jdk.compil... | 2016-11-03T16:10:03+0100 | 10min | 10min | [] | 1fbe8f7a2d5b0d6eb6e60053c78abe06 | AVgq2iKqc0c4MVP5bbg7 | 2854.0 | ... | MINOR | OPEN | net.java.openjdk:jdk9:langtools | [cwe] | 2854.0 | 51.0 | 2854.0 | 46.0 | VULNERABILITY | 2016-11-03T16:10:03+0100 |
308 | vromero | NaN | net.java.openjdk:jdk9:langtools:src/jdk.compil... | 2016-11-03T16:10:03+0100 | 10min | 10min | [] | 7835c4aaa972d2ee661a3fb912743535 | AVgq2iKqc0c4MVP5bbg8 | 2890.0 | ... | MINOR | OPEN | net.java.openjdk:jdk9:langtools | [cwe] | 2890.0 | 42.0 | 2890.0 | 27.0 | VULNERABILITY | 2016-11-03T16:10:03+0100 |
716 | jlahoda | NaN | net.java.openjdk:jdk9:nashorn:src/jdk.scriptin... | 2016-10-27T16:10:33+0200 | 10min | 10min | [] | fa31896ede321997dd0bc03a6e788f6c | AVgGm45Ec0c4MVP5YKpW | 81.0 | ... | MINOR | OPEN | net.java.openjdk:jdk9:nashorn | [error-handling] | 81.0 | 42.0 | 81.0 | 27.0 | VULNERABILITY | 2016-10-27T16:10:33+0200 |
956 | ant | NaN | net.java.openjdk:jdk9:jdk:src/java.desktop/mac... | 2016-10-14T16:12:15+0200 | 10min | 10min | [] | 5c5bde86fe26ed1be60a8b58f305bfb5 | AVfDqqJob6bLTkrkqbLq | 103.0 | ... | MINOR | OPEN | net.java.openjdk:jdk9:jdk | [error-handling] | 103.0 | 55.0 | 103.0 | 40.0 | VULNERABILITY | 2016-10-14T16:12:15+0200 |
1015 | ksrini | NaN | net.java.openjdk:jdk9:langtools:src/jdk.javado... | 2016-10-14T16:12:15+0200 | 10min | 10min | [] | 2dd9b2ef22845fa74d57e565807327fb | AVfDqktTb6bLTkrkqbKa | 284.0 | ... | MINOR | OPEN | net.java.openjdk:jdk9:langtools | [cwe] | 284.0 | 30.0 | 284.0 | 19.0 | VULNERABILITY | 2016-10-14T16:12:15+0200 |
5 rows × 25 columns
v2 = v.groupby(['severity', 'component'])[['type']].count()
v2.sort_values(by='type')
type | ||
---|---|---|
severity | component | |
BLOCKER | net.java.openjdk:jdk9:jdk:src/java.base/share/classes/com/sun/security/ntlm/NTLM.java | 1 |
MINOR | net.java.openjdk:jdk9:jdk:src/java.base/share/classes/java/util/concurrent/LinkedTransferQueue.java | 1 |
net.java.openjdk:jdk9:jdk:src/java.base/share/classes/java/util/concurrent/PriorityBlockingQueue.java | 1 | |
net.java.openjdk:jdk9:jdk:src/java.base/share/classes/java/util/zip/ZipFile.java | 1 | |
net.java.openjdk:jdk9:langtools:src/jdk.jshell/share/classes/jdk/internal/jshell/tool/ConsoleIOContext.java | 1 | |
net.java.openjdk:jdk9:jdk:src/java.base/share/classes/sun/security/util/AnchorCertificates.java | 1 | |
net.java.openjdk:jdk9:jdk:src/java.desktop/macosx/classes/sun/lwawt/macosx/CAccessibility.java | 1 | |
net.java.openjdk:jdk9:jdk:src/java.desktop/share/classes/javax/imageio/ImageIO.java | 1 | |
net.java.openjdk:jdk9:jdk:src/java.desktop/share/classes/javax/imageio/spi/ServiceRegistry.java | 1 | |
net.java.openjdk:jdk9:jdk:src/java.desktop/share/classes/javax/imageio/stream/FileCacheImageInputStream.java | 1 | |
net.java.openjdk:jdk9:jdk:src/java.desktop/share/classes/javax/imageio/stream/FileCacheImageOutputStream.java | 1 | |
net.java.openjdk:jdk9:jdk:src/java.desktop/share/classes/sun/applet/Main.java | 1 | |
net.java.openjdk:jdk9:jdk:src/java.desktop/share/classes/sun/awt/SunToolkit.java | 1 | |
net.java.openjdk:jdk9:jdk:src/java.desktop/share/classes/sun/awt/util/PerformanceLogger.java | 1 | |
net.java.openjdk:jdk9:jdk:src/java.desktop/share/classes/sun/font/CreatedFontTracker.java | 1 | |
net.java.openjdk:jdk9:jdk:src/java.desktop/share/classes/sun/font/FileFont.java | 1 | |
net.java.openjdk:jdk9:jdk:src/java.desktop/share/classes/sun/font/Type1Font.java | 1 | |
net.java.openjdk:jdk9:jdk:src/java.desktop/share/classes/sun/print/PrintJob2D.java | 1 | |
net.java.openjdk:jdk9:jdk:src/java.desktop/share/classes/sun/print/ServiceDialog.java | 1 | |
net.java.openjdk:jdk9:jdk:src/java.desktop/unix/classes/sun/awt/X11/InfoWindow.java | 1 | |
net.java.openjdk:jdk9:jdk:src/java.desktop/unix/classes/sun/print/PrintServiceLookupProvider.java | 1 | |
net.java.openjdk:jdk9:jdk:src/java.desktop/unix/classes/sun/print/UnixPrintJob.java | 1 | |
net.java.openjdk:jdk9:jdk:src/jdk.jlink/share/classes/jdk/tools/jlink/builder/DefaultImageBuilder.java | 1 | |
net.java.openjdk:jdk9:jdk:src/jdk.jlink/share/classes/jdk/tools/jlink/internal/ImagePluginStack.java | 1 | |
net.java.openjdk:jdk9:langtools:src/jdk.compiler/share/classes/com/sun/tools/javac/file/Locations.java | 1 | |
net.java.openjdk:jdk9:langtools:src/jdk.javadoc/share/classes/jdk/javadoc/internal/doclets/toolkit/Configuration.java | 1 | |
net.java.openjdk:jdk9:jdk:src/java.base/share/classes/java/io/DeleteOnExitHook.java | 1 | |
net.java.openjdk:jdk9:jdk:src/java.base/share/classes/com/sun/java/util/jar/pack/UnpackerImpl.java | 1 | |
net.java.openjdk:jdk9:nashorn:src/jdk.scripting.nashorn.shell/share/classes/jdk/nashorn/tools/jjs/Console.java | 1 | |
MAJOR | net.java.openjdk:jdk9:jdk:src/java.httpclient/share/classes/sun/net/httpclient/hpack/Huffman.java | 1 |
... | ... | |
net.java.openjdk:jdk9:jdk:src/java.base/share/classes/sun/security/tools/keytool/Main.java | 2 | |
MINOR | net.java.openjdk:jdk9:jdk:src/jdk.jlink/share/classes/jdk/tools/jlink/internal/ImageFileCreator.java | 2 |
net.java.openjdk:jdk9:jdk:src/java.httpclient/share/classes/java/net/http/WSTransmitter.java | 2 | |
net.java.openjdk:jdk9:jdk:src/jdk.attach/aix/classes/sun/tools/attach/VirtualMachineImpl.java | 2 | |
net.java.openjdk:jdk9:jdk:src/java.desktop/share/classes/sun/print/PSPrinterJob.java | 2 | |
net.java.openjdk:jdk9:jdk:src/jdk.jartool/share/classes/sun/tools/jar/Main.java | 2 | |
net.java.openjdk:jdk9:jdk:src/jdk.attach/linux/classes/sun/tools/attach/VirtualMachineImpl.java | 2 | |
net.java.openjdk:jdk9:jdk:src/java.base/share/classes/sun/nio/fs/AbstractWatchService.java | 2 | |
MAJOR | net.java.openjdk:jdk9:jdk:src/jdk.jartool/share/classes/sun/security/tools/jarsigner/Main.java | 2 |
MINOR | net.java.openjdk:jdk9:jdk:src/java.base/share/classes/java/util/concurrent/DelayQueue.java | 2 |
net.java.openjdk:jdk9:jdk:src/java.base/share/classes/java/util/concurrent/ThreadPoolExecutor.java | 2 | |
net.java.openjdk:jdk9:jdk:src/java.base/share/classes/jdk/internal/module/SystemModules.java | 2 | |
net.java.openjdk:jdk9:corba:src/java.corba/share/classes/com/sun/corba/se/impl/naming/pcosnaming/ServantManagerImpl.java | 2 | |
net.java.openjdk:jdk9:jdk:src/java.desktop/share/classes/sun/font/SunFontManager.java | 2 | |
net.java.openjdk:jdk9:jdk:src/java.desktop/share/classes/java/awt/Font.java | 2 | |
net.java.openjdk:jdk9:jdk:src/java.desktop/share/classes/java/awt/Desktop.java | 2 | |
MAJOR | net.java.openjdk:jdk9:langtools:src/jdk.jshell/share/classes/jdk/jshell/execution/Util.java | 2 |
MINOR | net.java.openjdk:jdk9:jdk:src/java.desktop/share/classes/javax/swing/TimerQueue.java | 2 |
net.java.openjdk:jdk9:jdk:src/java.desktop/share/classes/sun/awt/image/OffScreenImageSource.java | 2 | |
MAJOR | net.java.openjdk:jdk9:langtools:src/jdk.jshell/share/classes/jdk/jshell/execution/ExecutionControlForwarder.java | 3 |
MINOR | net.java.openjdk:jdk9:jdk:src/java.base/share/classes/java/util/concurrent/ScheduledThreadPoolExecutor.java | 3 |
net.java.openjdk:jdk9:jdk:src/java.logging/share/classes/java/util/logging/FileHandler.java | 3 | |
MAJOR | net.java.openjdk:jdk9:langtools:src/jdk.jshell/share/classes/jdk/jshell/execution/DirectExecutionControl.java | 4 |
net.java.openjdk:jdk9:jdk:src/java.httpclient/share/classes/java/net/http/AsyncSSLDelegate.java | 4 | |
MINOR | net.java.openjdk:jdk9:jdk:src/java.base/aix/classes/sun/nio/ch/AixPollPort.java | 4 |
net.java.openjdk:jdk9:jdk:src/java.base/linux/classes/sun/nio/ch/EPollPort.java | 4 | |
net.java.openjdk:jdk9:jdk:src/java.base/macosx/classes/sun/nio/ch/KQueuePort.java | 4 | |
net.java.openjdk:jdk9:jdk:src/java.base/share/classes/com/sun/java/util/jar/pack/Driver.java | 5 | |
net.java.openjdk:jdk9:jdk:src/java.prefs/unix/classes/java/util/prefs/FileSystemPreferences.java | 9 | |
MAJOR | net.java.openjdk:jdk9:jdk:src/java.base/share/classes/java/util/concurrent/CompletableFuture.java | 9 |
80 rows × 1 columns