LoadRunner Script Generation Spreadsheet

This spreadsheet writes VuGen script code for use within Mercury LoadRunner for performance testing. This is a companion to my pages on Vu Scripting, performance monitoring, and tuning.

Topics this page:

Site Map  
About this site  

The Annoyance

When working with a large number of screens for an application to be automated, it is often time consuming to record each screen and then tweak the script with transaction identifiers, confirmation text, etc.

The other problem stems from my desire to keep a unique and sequential identifier of each transaction/screen that reflects the hierarchy of my test plan.

It is too time consuming to go through the whole script to renumber items by hand each time I add or remove an item or to make the same changes to make changes that applies to all items.

My Solution

So I came up with an Excel spreadsheet containing VBA macros.

Get it Download sample Excel spreadsheet with macros file lrxls2vu.xls

Using a spreadsheet to list URLs allows me to build the details of an application that is easy to see and work with. Now I can start earlier, and even before code is constructed. if I focused only on scripts within VuGen — perhaps

This spreadsheet has now become a key part of my application performance assurance (test) plans.

Roadmap

However, this spreadsheet does not have all possible VuGen script coding/tweaks and these limitations:

• it does not include EXTRARES items recognized during manual recording. This can pose a serious hidden danger which results in under-reporting of response time.
• it does not generate script to submit forms.

In a future version I plan to:

1. Add a column in the worksheet to specify a sub-routine that generates more lines (such as EXTRARES).
2. digitally sign the file with PVK format Digital Certificate Files Importer "pvkimprt.exe" to avoid annoying messages. The public part of the digital certificate is stored in a SPC file. The private part is stored in the PVK file.

   I haven't bothered with this because Microsoft makes it so scary for users to accept certificates.

3. something else readers like you request!

Spreadsheet Columns

Below is a description of each column in the spreadsheet:

Do not insert or delete columns — the VBA script works by column sequence number.

1. App. (Application) — uniquely identifies a portion or an entire application, such as a private administration area or a public storefront, each of which may be operating from a different set of servers. These appear in transaction names before a colon (:).

2. Scr. (Screen) — uniquely identifies an entire screen, such as the landing page, login page, sub-menus, listings, shopping cart, checkout, etc. These appear in transaction names before a dash (-).

3. Group — an identifier for a collection of related actions on the same screen, such as a series of similar links or submittal of different values. These appear in transaction names before a dot (.).

4. Item — uniquely identifies a link, image, field, or other item to be acted upon from an app, screen, or group.

5. Run Anything in this column will cause the whole row to be ignored. The VBA script only generates code for rows which have this column blank. Examples include link to "Logout" or submit actions in the spreadsheet for documentation. This is also useful to temporarily disable an item.

6. Script Action identifies the action file specified within This column is also the leftmost column because the spreasheet should be sorted by this column so that items for each action block are generated together. Gaps will result in multiple blocks begin generated for the same script action.

7. Resulting Screen Name — the name (function) of the screen obtained if the action is successful. Typically this name contains abbreviations such as tab, link, img, popup, etc. to designate the behavior expected.

8. Manual Step — describes what users do to get to the screen (such as click tab, etc.). This is added to script output as comments.

9. Pct. (Percent) Use — specifies the percentage of all iterations that the item is actually invoked by the randomizer code controlling script execution. A blank value or "100%" (100 percent, internal value 1.0) specifies that the script always runs. "10%" specifies that the script has a 1 in 10 chance of being picked to run.

10. Use Case — to designate logical use cases. Several may be designated (separated by commas).

11. Impor. (Importance) is included as comments in LR scripts.

12. Parm — designates a unique identifier that will not be changed. This may be used in the future as that basis for creating unique parameter names.

13. Invoke Text used in LR web_text function calls to make application requests based on text supplied in this column.

14. Invoke URL used in LR web_url function calls to make application requests using the http supplied in this column.

15. Confirm Text — used for loadrunner web_reg_find function to ensure that the page returned with the (unique) text anticipated.

16. Ret. (HTTP Return Code) — the return code from HTTP headers, normally 200 or 301.

17. Snapshot — the file name of the ".inf" file used by LoadRunner. The script automatically adds the ".inf" suffix to values in this column. If this is blank, the "Snapshot=" line is not generated.

Work It: Run the spreasheet

1. Open the spreadsheet using Microsoft Excel. The sheet was written in Excel 2003.
2. If prompted, Enable Macros to run.

   Since the whole point of this file is to run macros, click "Enable Macros".

   There are several macros in the module. The "heart" of the macros are the (for lack of a better name) "PrintIt" and "Final" subs.

   The macro "All" invokes all the individual "Create..." macros that each create a c action script file.

   Sub "Create...Noise" creates an action file that invokes the URLs according to the percent of iterations specified in the "Noise" worksheet.

   Sub "Create...G1" creates an action file that invokes only one of the URLs in the "G1" worksheet on each iteration.

3. Specify your own information.

4. If you have sorted the spreadsheet a different way, resort by the first three columns (Screen, Section, Item). This is the sort order in the sample spreadsheet.

5. Press Alt-F5 or select menu Tools, Macro, Macros...
6. Select subroutine Create_LoadRunner_VuScript() and click Run
7. Change the file name or path in the "Output Text File Path" pop-up dialog. The default path is the path of the Excel sheet itself.
8. click OK to generate the file.

9. Use Windows Explorer to navigate to the file.
10. Use Notepad (or other text editor) to open the file.

11. Highlight the code to copy. If the action already exists, do not copy the action name, brackets, or "return 0;" line.
12. In VuGen, create the script action if the action has not been created.
13. click the location where the code should be pasted.
14. Press Ctrl-V to paste the code.
15. Execute the VuGen script.

Sample Scripts Generated

lr_start_transaction and corresponding lr_stop_transaction functions are always executed so that results from LoadRunner reports can be consistently copied and pasted to a spreadsheet, which has a fixed number of rows.

Individual "Noise" Script

Here is an example of the output generated by the VBA module:

Noise()
{
// File H:\Wilsonmarcom\Noise.c generated 1/25/2005 2:29:02 PM
// by Excel file H:\Wilsonmarcom\lrxls2vu.xls containing 5 rows 
// Assuming definition of ptDateTime.
// See http://www.wilsonmar.com/LRXls2VU.htm for updates from this version.

int i, iRecRand, iMixRand;
float floatThinkRand;
long longReturnCode, longBytesReturned, longLastByteMSecs;
char last[30];

   lr_continue_on_error(1); // to process all despite an error. 
// Save message level from user's Runtime Settings:
iMsg_level=lr_get_debug_message();
sprintf( cMsg_level, "%d", iMsg_level);
lr_save_string( cMsg_level, "pMsg_level" );
   floatThinkRand = 0.5;

   srand(time(NULL)+rand()%100);
   iMixRand = rand()%100;
   iRecRand = 0;


// In Action  for Use Case HealthCheck ManualStep= Priority=
// From cell $A$2 Run= Parm= MixPct=31

if( 31>=iMixRand ){ // Execute 31 Percent of iterations.

   web_reg_find("Text=Hello World!",
       "ID=HealthCheck N1", "FAIL=NotFound",LAST);

   lr_start_transaction("HealthCheck N1");

   web_url("HealthCheck N1",
       "URL=http://www.godaddy.com/healthcheck.html",
       "Resource=0",
       "RecContentType=text/html",
       "Referer=",
       "Mode=HTML",
       LAST);

   lr_end_transaction("HealthCheck N1", LR_AUTO);
   lr_think_time(0.5);

} //if iMixRand()

// In Action  for Use Case Keepalive ManualStep= Priority=
// From cell $A$3 Run= Parm= MixPct=11

if( 11>=iMixRand ){ // Execute 11 Percent of iterations.

   web_reg_find("Text=",
       "ID=Keepalive N2", "FAIL=NotFound",LAST);

   lr_start_transaction("Keepalive N2");

   web_url("Keepalive N2",
       "URL=http://www.godaddy.com/gdshop/mya_keepalive.asp",
       "Resource=0",
       "RecContentType=text/html",
       "Referer=",
       "Mode=HTML",
       LAST);

   lr_end_transaction("Keepalive N2", LR_AUTO);
   lr_think_time(0.5);

} //if iMixRand()

// In Action  for Use Case Notfound ManualStep= Priority=
// From cell $A$4 Run= Parm= MixPct=10

// Note: http assumed
if( 10>=iMixRand ){ // Execute 10 Percent of iterations.

   web_reg_find("Text=This site is currently unavailable.",
       "ID=Notfound N3", "FAIL=NotFound",LAST);

   lr_start_transaction("Notfound N3");

   web_url("Notfound N3",
       "URL=http://www.godaddy.com/sites.html",
       "Resource=0",
       "RecContentType=text/html",
       "Referer=",
       "Mode=HTML",
       LAST);

   lr_end_transaction("Notfound N3", LR_AUTO);
   lr_think_time(0.5);

} //if iMixRand()

// In Action  for Use Case  ManualStep= Priority=
// From cell $A$5 Run= Parm= MixPct=0

   // Invoke Text and URL not specified.


   lr_continue_on_error(0); // to stop again on any error.
return 0;
} // End of generated script.

Sample Output - One of Many ("G1")

Here is an example of the output generated by the default VBA code:

LandAround()
{
// File H:\Wilsonmarcom\LandAround.c generated 1/25/2005 2:22:42 PM
// by Excel file H:\Wilsonmarcom\lrxls2vu.xls containing 4 rows 
// Assuming definition of ptDateTime.
// See http://www.wilsonmar.com/LRXls2VU.htm for updates from this version.

int i, iRecRand, iMixRand;
float floatThinkRand;
long longReturnCode, longBytesReturned, longLastByteMSecs;
char last[30];

   lr_continue_on_error(1); // to process all despite an error. 
// Save message level from user's Runtime Settings:
iMsg_level=lr_get_debug_message();
sprintf( cMsg_level, "%d", iMsg_level);
lr_save_string( cMsg_level, "pMsg_level" );
   floatThinkRand = 0.5;

   srand(time(NULL)+rand()%100);
   iMixRand = rand()%100;
   iRecRand = rand()%3 +1; // to begin from 1.
// Entire section:
if( 20>=iMixRand ){



// In Action  for Use Case  ManualStep= Priority=1
// From cell $A$2 Run= Parm= MixPct=0

if( 1== iRecRand ){

   web_reg_find("Text=Sorry, the page you requested does not exist.",
       "ID=1- mercury home", "FAIL=Found",LAST);
   web_reg_find("Text=Copyright © 2005 Yahoo! Inc. All rights reserved.",
       "ID=1- mercury home", "FAIL=NotFound",LAST);

   web_reg_save_param("pHTTPRet","LB=HTTP/1.1 ",
      "RB= ","Search=Header","ORD=All","NOTFOUND=Warning",LAST);

   lr_start_transaction("1- mercury home");

   web_url("1- mercury home",
       "URL=http://www.mercury.com",
       "Resource=0",
       "RecContentType=text/html",
       "Referer=",
       "Mode=HTML",
       LAST);

   lr_end_transaction("1- mercury home", LR_AUTO);
   lr_think_time(0.5);

   if( atoi(lr_eval_string("{pHTTPRet_count}")) != 0 ){
      sprintf(last, "{pHTTPRet_%s}", 
         lr_eval_string("{pHTTPRet_count}"));
      lr_save_string( lr_eval_string(last) ,"pHTTPRet_last");
      if( atoi(lr_eval_string("{pHTTPRet_last}")) != 200){
          lr_error_message("HTTP %s (last of %s) not the 200 expected for 1- mercury home",
              lr_eval_string("{pHTTPRet_last}"),
              lr_eval_string("{pHTTPRet_count}"));
      } //if
   } //if

   longReturnCode=web_get_int_property( HTTP_INFO_RETURN_CODE );
   longBytesReturned=web_get_int_property( HTTP_INFO_DOWNLOAD_SIZE );
   longLastByteMSecs=web_get_int_property( HTTP_INFO_DOWNLOAD_TIME );
   // Allow messages (Advanced trace) regardless:
       lr_set_debug_message( LR_MSG_CLASS_JIT_LOG_ON_ERROR, LR_SWITCH_OFF);
       lr_set_debug_message( 10,LR_MSG_ON );

   lr_output_message("%s\t1- mercury home\t TTime=\t%4.2f\t RC=\t%d\t Bytes=\t%d\t MSecs=\t %d",
      lr_eval_string("{ptDateTime}"),
      floatThinkRand,
      longReturnCode,
      longBytesReturned,
      longLastByteMSecs );
   // Reset message level to Runtime Settings:
       if( iMsg_level & LR_MSG_CLASS_JIT_LOG_ON_ERROR)
           lr_set_debug_message( LR_MSG_CLASS_JIT_LOG_ON_ERROR, LR_SWITCH_ON);
       lr_set_debug_message( iMsg_level, LR_MSG_ON);

} //if iRecRand()

// In Action  for Use Case  ManualStep= Priority=2
// From cell $A$3 Run= Parm= MixPct=0

if( 2== iRecRand ){

   web_reg_find("Text=Sorry, the page you requested does not exist.",
       "ID=Y2- yahoo group", "FAIL=Found",LAST);
   // No Confirm text specified for web_reg_find

   web_reg_save_param("pHTTPRet","LB=HTTP/1.1 ",
      "RB= ","Search=Header","ORD=All","NOTFOUND=Warning",LAST);

   lr_start_transaction("Y2- yahoo group");

   web_url("Y2- yahoo group",
       "URL=http://groups.yahoo.com/group/LoadRunner/",
       "Resource=0",
       "RecContentType=text/html",
       "Referer=",
       "Mode=HTML",
       LAST);

   lr_end_transaction("Y2- yahoo group", LR_AUTO);
   lr_think_time(0.5);

   if( atoi(lr_eval_string("{pHTTPRet_count}")) != 0 ){
      sprintf(last, "{pHTTPRet_%s}", 
         lr_eval_string("{pHTTPRet_count}"));
      lr_save_string( lr_eval_string(last) ,"pHTTPRet_last");
      if( atoi(lr_eval_string("{pHTTPRet_last}")) != 200){
          lr_error_message("HTTP %s (last of %s) not the 200 expected for Y2- yahoo group",
              lr_eval_string("{pHTTPRet_last}"),
              lr_eval_string("{pHTTPRet_count}"));
      } //if
   } //if

   longReturnCode=web_get_int_property( HTTP_INFO_RETURN_CODE );
   longBytesReturned=web_get_int_property( HTTP_INFO_DOWNLOAD_SIZE );
   longLastByteMSecs=web_get_int_property( HTTP_INFO_DOWNLOAD_TIME );
   // Allow messages (Advanced trace) regardless:
       lr_set_debug_message( LR_MSG_CLASS_JIT_LOG_ON_ERROR, LR_SWITCH_OFF);
       lr_set_debug_message( 10,LR_MSG_ON );

   lr_output_message("%s\tY2- yahoo group\t TTime=\t%4.2f\t RC=\t%d\t Bytes=\t%d\t MSecs=\t %d",
      lr_eval_string("{ptDateTime}"),
      floatThinkRand,
      longReturnCode,
      longBytesReturned,
      longLastByteMSecs );
   // Reset message level to Runtime Settings:
       if( iMsg_level & LR_MSG_CLASS_JIT_LOG_ON_ERROR)
           lr_set_debug_message( LR_MSG_CLASS_JIT_LOG_ON_ERROR, LR_SWITCH_ON);
       lr_set_debug_message( iMsg_level, LR_MSG_ON);

} //if iRecRand()

// In Action  for Use Case  ManualStep= Priority=3
// From cell $A$4 Run= Parm= MixPct=0

// Note: http assumed
if( 3== iRecRand ){

   web_reg_find("Text=Sorry, the page you requested does not exist.",
       "ID=3- qaforums", "FAIL=Found",LAST);
   // No Confirm text specified for web_reg_find

   web_reg_save_param("pHTTPRet","LB=HTTP/1.1 ",
      "RB= ","Search=Header","ORD=All","NOTFOUND=Warning",LAST);

   lr_start_transaction("3- qaforums");

   web_url("3- qaforums",
       "URL=http://qaforums.com",
       "Resource=0",
       "RecContentType=text/html",
       "Referer=",
       "Mode=HTML",
       LAST);

   lr_end_transaction("3- qaforums", LR_AUTO);
   lr_think_time(0.5);

   if( atoi(lr_eval_string("{pHTTPRet_count}")) != 0 ){
      sprintf(last, "{pHTTPRet_%s}", 
         lr_eval_string("{pHTTPRet_count}"));
      lr_save_string( lr_eval_string(last) ,"pHTTPRet_last");
      if( atoi(lr_eval_string("{pHTTPRet_last}")) != 200){
          lr_error_message("HTTP %s (last of %s) not the 200 expected for 3- qaforums",
              lr_eval_string("{pHTTPRet_last}"),
              lr_eval_string("{pHTTPRet_count}"));
      } //if
   } //if

   longReturnCode=web_get_int_property( HTTP_INFO_RETURN_CODE );
   longBytesReturned=web_get_int_property( HTTP_INFO_DOWNLOAD_SIZE );
   longLastByteMSecs=web_get_int_property( HTTP_INFO_DOWNLOAD_TIME );
   // Allow messages (Advanced trace) regardless:
       lr_set_debug_message( LR_MSG_CLASS_JIT_LOG_ON_ERROR, LR_SWITCH_OFF);
       lr_set_debug_message( 10,LR_MSG_ON );

   lr_output_message("%s\t3- qaforums\t TTime=\t%4.2f\t RC=\t%d\t Bytes=\t%d\t MSecs=\t %d",
      lr_eval_string("{ptDateTime}"),
      floatThinkRand,
      longReturnCode,
      longBytesReturned,
      longLastByteMSecs );
   // Reset message level to Runtime Settings:
       if( iMsg_level & LR_MSG_CLASS_JIT_LOG_ON_ERROR)
           lr_set_debug_message( LR_MSG_CLASS_JIT_LOG_ON_ERROR, LR_SWITCH_ON);
       lr_set_debug_message( iMsg_level, LR_MSG_ON);

} //if iRecRand()

} // if( entire section 

   lr_continue_on_error(0); // to stop again on any error.
return 0;
} // End of generated script.

Make It Your Own

1. To edit the VBA code, press Alt-F11 or select menu Tools, Macro, Visual Basic Editor.

2. To insert a row, highlight the entire row below where you want to insert,
   then select menu "Insert", "Row".

3. If you would like to rearrange or insert columns, in Sub Create_LoadRunner_VuScript():
   1. At the top of the code, add a Dim for each new variable
   2. In section "2.1 Assign variables from spreadsheet:" renumber the cells in the same left-to-right order as in the spreadsheet.

4. To make the code ignore a column, such as "MixPct", change the line from

    MixPct = Val(rw.Cells(1, 8)) 'convert to single

   to

    MixPct = 0 ' Val(rw.Cells(1, 8)) 'convert to single

   by adding two double-quotes or a zero (0) followed by a quote.
   This approach allows you to easily activate it again later.

5. Sort the spreadsheet by the first set of columns (App, Screen, Section, Item) by selecting menu Data, Sort. Click the "Header row" radio button. With numbers, select "Ascending".

6. To generate all files, select macro "All", then click the blue Run arrow button or press F5 to execute the subroutine.

7. To generate a single file, select macro "Create...", then click the blue Run arrow button or press F5 to execute the subroutine.

   If your cursor is at sub "PrintIt" or "Final", a new file will not be generated.

Engine Parameters

"Create..." subroutines specify parameters that control the behavior of the "PrintIt" engine for a particular script file.
1. Specify the file name to be output in line

   strFileName = ThisWorkbook.Path & "\noise.c"

2. Specify the text that will be verified for every entry. Example:

   ConfirmAllText = "Sorry"

3. To specify creation of lr_start_transaction and lr_end_transaction code:

   boolIsTrans = True

4. To override the think time specified in the data worksheet with a fixed number of seconds for every transaction:

   StdThinkTime = 0.5

5. To specify creation of code to check all HTTP return codes:

   boolCheckAllHTTPRC = True

6. To generate code to print information about every request made:

   boolPrintOut = True

7. The "G1only" subroutine uses this parameter to specify the maximum random number to be created that each transaction references to see if it should be invoked. This should be the record count or records in the G1 data worksheet:

   RecTot = 19

   The "Noise" subroutine has RecTot = 0

8. To specify the percentage of time that the entire block of URLs are invoked, replace the 0 (zero) with a whole percentage number:

   singleSectionPct = 10

   This is used by the sample "G1only" sub.

9. To override the percentage of time that a URL is invoked, replace the 0 (zero) with a whole percentage number:

   MixPct = 0

   To make use of the percentage Use specified in the worksheet:

   MixPct = Val(rw.Cells(1, 10)) * 100 'convert to single - 0 to output all lines.