[Python3] Outputting JMeter execution results to a Google Spreadsheet [gspread]

Hello,
I'm Shimeji from the SS team.
I occasionally work on load testing projects, but I was too lazy to record the results of each JMeter run, so I created a script to output the results to a Google spreadsheet.
Let's automate it!

Advance preparation

You need to prepare the following three things in advance:
・Enabling the Google Drive API / Google Sheets API
・Downloading the secret key (JSON data)
・Sharing settings for the spreadsheet

For more information, please visit the link below:
https://console.developers.google.com/

Also, please make sure to install the necessary packages beforehand.
*If you are using CentOS7, just use the command below!

yum install python3 python-devel jq pip3 install gspread pip3 install oauth2client

Script 1 (Python)

This is a Python script that outputs a CSV file specified as a command line argument to a spreadsheet.
Python has a convenient library for working with spreadsheets called "gspread," so I am grateful to use it.

#!/usr/bin/python3 import gspread import json import csv import sys import itertools # Specify the secret key with an absolute path SECRETJSON = "/usr/local/jmeter/bin/sacred-drive.json" # Define the spreadsheet key SPREADSHEET_KEY = 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx' ############################################################################## ## Functions # Number and character converter def num2alpha(num): if num<=26: return chr(64+num) elif num%26==0: return num2alpha(num//26-1)+chr(90) else: return num2alpha(num//26)+chr(64+num%26) ####################################################################### ## Authentication # Promises from oauth2client.service_account import ServiceAccountCredentials scope = ['https://spreadsheets.google.com/feeds','https://www.googleapis.com/auth/drive'] # Define the downloaded json file credentials = ServiceAccountCredentials.from_json_keyfile_name(SECRETJSON, scope) # Log in to Google API gc = gspread.authorize(credentials) # Open sheet 1 of the spreadsheet worksheet = gc.open_by_key(SPREADSHEET_KEY).sheet1 ################################################################################## ## Processing # Get command line arguments args = sys.argv csvfile = args[1] # Assign the contents of the CSV file to an array with open(csvfile) as fp: results_list_ex = list(csv.reader(fp)) # Convert a 2D array to a 1D array results_list = list(itertools.chain.from_iterable(results_list_ex)) # Initialize the count variable COUNT_NUM = 1 # Search for blank rows while str(len(worksheet.cell(COUNT_NUM, 1).value)) != "0": COUNT_NUM += 1 # Specify the range to edit cell_list = worksheet.range('A'+str(COUNT_NUM)+':'+num2alpha(len(results_list))+str(COUNT_NUM)) # Assign the results_list array to cell_list for i,cell in enumerate(cell_list): cell.value = results_list[i] # Save the results worksheet.update_cells(cell_list)

The following two points must be adjusted appropriately for your environment.
・Specify the Google API secret key file in "SECRETJSON".
・Specify the key of the sheet to output the results in "SPREADSHEET_KEY".

This Python script will be used in the shell script described below

Script 2 (shell script)

#!/bin/sh DATE=$(date +"%Y%m%d") OPTIME=$(date +"%Y%m%d-%H%M%S") # Specify the directory to output the results LOGDIR=/var/www/html/${DATE} # Specify the JMX file FILE_JMX=/usr/local/jmeter/bin/templates/build-web-test-plan.jmx # Create the date directory mkdir -p ${LOGDIR} # Run JMeter /usr/local/jmeter/bin/jmeter -Dsun.net.inetaddr.ttl=0 -n -t ${FILE_JMX} -j ${LOGDIR}/${OPTIME}.log -l ${LOGDIR}/${OPTIME}.jtl -e -o ${LOGDIR}/${OPTIME}/ -r # Create a CSV file cat ${LOGDIR}/${OPTIME}/statistics.json | jq -r ". [] | [.transaction,.sampleCount,.errorCount,.errorPct,.meanResTime,.minResTime,.maxResTime,.pct1ResTime,.pct2ResTime,.pct3ResTime,.throughput,.receivedKBytesPerSec,.sentKBytesPerSec] | @csv" | grep "Total" > ${LOGDIR}/${OPTIME}/statistics.csv # Output results to a spreadsheet /usr/local/bin/main.py ${LOGDIR}/${OPTIME}/statistics.csv

The json output is converted to csv and passed to a Python script.
The script does the following:
1. Run JMeter
2. Convert the json results to csv

append it to a spreadsheet

Usage example

Let's try it out!

[root@test-server2-1 bin]# ./start-controller_cui.sh Creating summariser<summary> Created the tree successfully using /usr/local/jmeter/bin/templates/build-web-test-plan.jmx Configuring remote engine: 192.168.33.11 Starting remote engines the test @ Thu Jun 25 04:59:38 JST 2020 (1593028778488) Remote engines have been started Waiting for possible Shutdown/StopTestNow/HeapDump/ThreadDump message on port 4445 summary = 2 in 00:00:02 = 1.2/s Avg: 215 Min: 139 Max: 291 Err: 0 (0.00%) Tidying up remote @ Thu Jun 25 04:59:41 JST 2020 (1593028781684) ... end of run [root@test-server2-1 bin]# ls /var/www/html/20200625/20200625-033715_th/statistics.csv /var/www/html/20200625/20200625-033715_th/statistics.csv

Yep, it ran successfully! It looks like a CSV file has also been created.
Let's check the important spreadsheet.

Done!
Let's run it again! The

output is properly displayed on the next line. OK!

Conclusion

What do you think?
By using the abundant libraries in Python, it is quite easy to implement.

In the future, I would like to improve the functionality so that the following items can also be output at the same time

1. Execution time (YYYY/MM/DD hh:mm:ss)
2. JMeter execution parameters
: number of controllers, number of slaves
, number of
threads,
number of loops
, ramp-up time
3. Link to the HTML file output by JMeter

Well then