Tuesday, May 26, 2015

Merging Wireshark files

Note to myself, If you want to merge multiple wireshark files, save & execute the following command from a batch file.
Cmd /V:on /c {mergecap wireshark utility} -w {mergefile name} {files to merge}

{mergecap wireshark utility} - mergecap.exe file path
{mergefile name} - name of the merged file to be generated
{files to merge} - wireshark files that need to be merged

Cmd /V:on /c "c:\Program Files\Wireshark\mergecap.exe" -w allWireshark.pcap wiresharkDump*.pcap

Run the batch file from within the same folder where all wireshark files are located.

Tuesday, September 24, 2013

SoapUI - using WHERE IN clause and Groovy code to concatenate strings

Recently, I worked on a project that was using SoapUI Pro to test an application. One of the testers had a working test case in SoapUI but wanted it done differently and therefore, approached me for a solution. Following outlines the scenario and a quick groovy code I wrote to address what he really wanted.

He was connecting to a database using SoapUI JDBC step and retrieving more than one result for a SQL query. He was then using FOR loop to iterate through all the values. In the FOR loop, he had another JDBC step which took result value as a parameter and returned an appropriate response (using assertion to validate the response).

What he wanted:
He wanted to get rid of FOR loop and pass all the values returned from the first JDBC step into second JDBC step.

The solution I came up with was to concatenate all the values from the first JDBC step using a groovy code and then pass the returned string from the code into a separate JDBC step. Also change the SQL query in the final JDBC step to use WHERE IN clause.

For blogging purpose, I am connecting to MySQL database on my local machine using SoapUI Pro 4.5.2 (trial version).

I have created two tables in MySQL database. First table is a class which contains StudentName and ClassName. Second table is a Subject with StudentName and Subject fields. For code demonstration purpose, I will be querying the database to return me names of all the stundent that are in Class 3. Then I will concatenate the names using groovy code and pass it to a separate JDBC step to get me the StudentName and the Subject they are enrolled in.

  • This code does not cater for all the possibilities as it is only for blogging purpose. 
  • Also make sure you are adding the MySQL JDBC driver in SoapUI ext folder so you can connect to the database.
  • I am sure the code below can be refined further. saving xmlRecCount.toInteger() value to a parameter.
  1. Add a JDBC Step to the test case. This JDBC step with query the class table and return student names that are in class 3. 
  2. Add a DataGen step to the test case. In this step create a parameter with "Type" as "Script". This Script will take response from the first step, concatenate all the student names into one string and pass it to the parameter. 
  3. Finally add another JDBC step to the test case. This step will query the Subject table uisng WHERE IN Clause and IN value will be the parameter created in step 2 above.
def groovyUtils = new com.eviware.soapui.support.GroovyUtils(context)
def holder = groovyUtils.getXmlHolder("GetStudentName#ResponseAsXml")

def concSqlString=""   //initialize parameter string concSqlString
def cnt =1             //initialize counter

//Get the total count of records that have the CLASS.STUDENTNAME
def xmlRecCount=holder["count(//Results[1]/ResultSet[1]/Row/CLASS.STUDENTNAME)"]

//check if only one row is returned
if (xmlRecCount.toInteger()==1)
      node = holder.getNodeValue('//Results[1]/ResultSet[1]/Row/CLASS.STUDENTNAME')
     concSqlString=concSqlString +"\""+node+"\""
        //for each node, concatenate the node value to concSqlString variable
 for (node in holder['//Results[1]/ResultSet[1]/Row/CLASS.STUDENTNAME'])
 concSqlString=concSqlString +"\""+node+"\""

//return the concSqlString 
return concSqlString

Saturday, August 10, 2013

LoadRunner - Selecting random value using lr_paramarr_random function

In performance testing, it is really important to simulate a realistic user path through an application. For example, randomly select an image link from a gallery or select a share from a share list. In such situations, you can use the LoadRunner lr_paramarr_random function to select a random value from a captured parameter array. Similarly, you can also write a code to do the same.

Before you use the above function, you will need to use web_reg_save_param function to capture all the ordinal values. This can be achieved by passing "ORD=ALL" into the function.

The following code demonstrates the use of lr_paramarr_random function. The code saves link Ids using  web_reg_save_param function and then uses lr_paramarr_random function to randomly select an Id value. The value is passed as a parameter in web_text_link function to navigate to the appropriate webpage.


 //Capture all the Link IDs
 web_reg_save_param("LinkID","LB=id=\"","RB=\" href=\"default.aspx?content=business_","ORD=ALL",LAST);




 //Save a randomly selected ID to a Parameter

 //Printout the randomly selected parameter

 //Navigate to the appropriate webpage associated to the ID



 return 0;
Following is a screenshot of replay log, displaying the random id's selected for each iteration. It also displays the values captured using web_reg_save_param function.

Sunday, June 23, 2013

SoapUI pro - DB2 Invalid database URL syntax issue

Recently, I was trying to connect to DB2 database using SoapUI pro 4.5.1 and got the following error message "com.ibm.db2.jcc.am.SqlSyntaxErrorException:[jcc]...Invalid database URL syntax: ...ERRORCODE=-4461,SQLSTATE=42815".

On further analysis, I discovered that the default connection string for IBM DB2 Drivers in SoapUI did not have a semicolon at the end of password variable.

Therefore to solve the above issue, you can use any of the following approaches:

1: add the semicolon at the end of the connection string template for DB2. The connection string templates are available under the option soapUI Preferences - JDBC Driver Properties.

2: Create a custom connection string with semicolon in your test case.

3: If you don't want to use either  of the above approaches, third approach is to add semicolon at the end of the Password variable text.

NOTE: To connect to DB2 database, you will require to add db2jcc.jar and db2jcc_licence_cisuz.jar files in the SoapUI ext folder.

Tuesday, May 14, 2013

Groovy - Saving CouchDB runtime statistics json file to excel file

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.

  • 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";
    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]);

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
       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()));


Script steps:
  1. Check couchDB excel file exists. If it does not, create it and add all the necessary headers to a worksheet and close the file.
  2. Read and parse the json file.
  3. Open the couchDB file for writing.
  4. Navigate through the parsed json data and save all the Group names.
  5. Navigate through each group name in step 4 and save all the Keys associated to it.
  6. For each Keys saved in step 5, navigate through it and save all the associated values into excel file.
  7. Once done, close the worksheet.