Last year, I wrote a blog on how to get CouchDB Runtime Statistics and saving it as a json file. However, want I really wanted was to save and open the file in excel. Therefore, I wrote a Groovy script to read Runtime Statistics data from json file and save all the Key values into an excel file.
Below is the prerequisite to execute the script as well as the script.
NOTE: Their already exists a python script to capture the CouchDB runtime statistics.
Prerequisite:
Script steps:
Result:
Below is the prerequisite to execute the script as well as the script.
NOTE: Their already exists a python script to capture the CouchDB runtime statistics.
Prerequisite:
- Download and Install Groovy Console application.
- Download and copy jxl.jar file into the Groovy lib folder.
import groovy.json.*
import jxl.*
import jxl.write.*
Parameter = ["Group","Key","current","max", "mean", "min", "stddev","sum"]; //couchDB worksheet headers
exlfile = "C:\\Harinder\\Groovy\\couchDB.xls"; //excel file path
if (new File(exlfile).exists()) //check if file exists esle create a new one, write label and close the file
{
println "File already exists";
}
else
{
WritableWorkbook workbook1 = Workbook.createWorkbook(new File(exlfile))
WritableSheet sheet1 = workbook1.createSheet("couchDB", 0);
//Label label = new Label(column, row, "Text input in Excel");
for (int iheader=0; iheader<8;iheader++)
{
Label label = new Label(iheader, 0, Parameter[iheader]);
sheet1.addCell(label);
}
workbook1.write();
workbook1.close();
}
def reader =new BufferedReader(new FileReader("C:\\Harinder\\Groovy\\couchDB.json")); //create a json file into a buffer
def jparsedData =new JsonSlurper().parse(reader);
/*open an exisiting excel file, write Key values and close the file*/
Workbook workbook = Workbook.getWorkbook(new File(exlfile));
WritableWorkbook copy = Workbook.createWorkbook(new File(exlfile),workbook);
Groups = jparsedData.collect{a,b->a}.reverse(); //Groups=["couchdb","httpd_request_methods"...]
WritableSheet sheet = copy.getSheet(0);
int groupCount=1;
int rowCount=1;
for (int gCount=0;gCount<Groups.size();gCount++) //iterate through the Groups
{
tGroups=Groups[gCount]; //assign Groups[gCount] value to a temporary variable tGroups
sheet.addCell(new Label(0,groupCount, tGroups)) //save tGroups into the sheet
Keys = jparsedData."$tGroups".collect{a,b->a}.reverse(); //collect all the Keys associated to Group[gGroup]
for (int kCount=0;kCount<Keys.size();kCount++) // iterate through all the Keys and save their min,max,count,mean,stddev,sum values into the sheet
{
tKeys=Keys[kCount];
rowCount=kCount+groupCount;
sheet.addCell(new Label(1,rowCount, tKeys));
sheet.addCell(new Label(2,rowCount, jparsedData."$tGroups"."$tKeys".current.toString()));
sheet.addCell(new Label(3,rowCount, jparsedData."$tGroups"."$tKeys".max.toString()));
sheet.addCell(new Label(4,rowCount, jparsedData."$tGroups"."$tKeys".mean.toString()));
sheet.addCell(new Label(5,rowCount, jparsedData."$tGroups"."$tKeys".min.toString()));
sheet.addCell(new Label(6,rowCount, jparsedData."$tGroups"."$tKeys".stddev.toString()));
sheet.addCell(new Label(7,rowCount, jparsedData."$tGroups"."$tKeys".sum.toString()));
}
groupCount=rowCount;
}
copy.write()
copy.close()
Script steps:
- Check couchDB excel file exists. If it does not, create it and add all the necessary headers to a worksheet and close the file.
- Read and parse the json file.
- Open the couchDB file for writing.
- Navigate through the parsed json data and save all the Group names.
- Navigate through each group name in step 4 and save all the Keys associated to it.
- For each Keys saved in step 5, navigate through it and save all the associated values into excel file.
- Once done, close the worksheet.
Result:
