Visual Studio Team System (VSTS) Test Edition is a valuable tool to load test web applications. This post is about using VSTS Test Edition to load test SharePoint Excel Services report. In particular, I explain the solution to handle session time-out issues encountered with Excel Services web test that could invalidate the load test. A basic load test in VSTS would consist of – Web Tests, Performance Monitors, Network and Browser mix and the mighty Virtual users as shown in figure 1.   

The test scenario for my web test recording was to logon to SharePoint site, then navigate to Excel Web service report, then change one of the filter values and finally refresh the Excel report.  

The web test was recorded and immediately after the recording, the web test ran perfectly fine as shown below.  

 Something weird happened when I ran the web test after an extended coffee break. The web test ran successfully but the response was throwing exception messages due to Session Timeout. Screenshot 2 shows the response from EwrFilter.aspx page displaying “Your session has timed out because of inactivity.”  

The default time-out value for Excel Services is 300 sec and obviously the session variables have changed since the web test was recorded. The best way to fix this is to pass session dependant values as variables instead of using the recorded values.  

The challenge is to identify the session dependant fields/query string parameters request from page SharePoint page EwrFilter.aspx. With a bit of educated guess it seems that the contents of query string parameters sr and wb does resemble like session values.  

Based on this assumption, it’s time to hunt down the page that sources values for query string parameters sr and wb. Usually this can be found in the response of one of the previous requests. In this case, the response of ExcelRenderer.aspx contains a hidden variable called m_excelWebRenderer$workbookIdElement which has the values of sr (highlighted in blue) AND wb (highlighted in green) .  

<input name=”m_excelWebRenderer$workbookIdElement” type=”hidden” id=”m_excelWebRenderer_workbookIdElement” wbid=”true” value=”64.7615be94-5d17-4948-967c-50f216223d25B1LOejYmeAPtpTJVKrpSZkzoNvo=118.22.16gypnvebMtEpqXBwBs1x090.5.en-US5.en-US73.-0600#0000-03-00-05T03:00:00:0000#+0000#0000-10-00-05T02:00:00:0000#-0060” />  

The solution to tackle the session time-out problem would be to extract the values for sr and wb from the response of ExcelRenderer.aspx and pass this value instead of the recorded value to the query string parameters sr and wb of EwrFilter.aspx.  

To extract sr and wb values from the response of ExcelRenderer.aspx, I used a Regular Expression based Extraction rule.  

Regular Expression for sr

(?<=(<input name="m_excelWebRenderer\$workbookIdElement.*value=\"\d+\.))([^=])*=  

Regular Expression for wb

(?<=(<input name="m_excelWebRenderer\$workbookIdElement.*value=\"\d+\..*?\=\d+\.))([^"]*)  

   The web test now looks like Screenshot 6

 Note that you need to change the URL Encode attribute of sr and wb from False to True, since the response of ExcelRenderer.aspx is already URL Encoded and so are the extracted values of sr and wb.

With all the hard work done, it’s time to run the Web Test again. And it’s a grand success – the EwrFilter.aspx page no longer times out and displays the filter values as expected.

And the response from the subsequent pages is as expected.

BENNY AUSTIN