Excel tips and tricks

[Sonam Chauhan <so...@ce.com.au>]

Hi - What Peter said below got me thinking... a few people here (including
me) use Excel to analyse JMeter logs. It would be good for us to share tips
and tricks, or even jointly develop macros, etc. 

Here's what I do, please feel free to continue this thread...

==============================================
1.	First, I have to get the data into Excel. I use Perl to parse the
XML logs and generate a delimited file Excel imports. 

The heart of my Perl data-mangler is this regular expression:
/timeStamp="(\d+)".+?threadName="(.*?)".+?label="(.+?)"
time="(\d+?)".+?success="(.+?)"/;

Here are the comments for it:
# Grab the relevant details from the log entry. 
# A normal log entry line logs one HTTP 'sampler' operation:
# 	<sampleResult timeStamp="..." 
#		... threadName="..." label="..." time="..." />
# In case the opertion had HTTP redirects, the redirects show up as 
# nested <sampleResult> elements,  but still on the same line:
# 	<sampleResult timeStamp= ... > <sampleResult timeStamp=.... > 
#		... </sampleResult>
# We are only interested in the data in the first <sampleResult> 
# element, so we use non-greedy pattern match operator ( '.*?' 
# or '.+?') to ignore any latter <sampleResult> elements,

Here are the variables the regex generates:
my $timestamp = $1; # unix timestamp 
my $threadname = $2; # thread label
my $label = $3; # operation label 
my $time = $4; # operation time in milliseconds
my $success = $5; # boolean success indicator for this operation


2.	Once the data is in Excel, I convert the timestamp column from
Jmeter's Unix timestamp format (base year 1970) to the Excel format (base
year 1900) using this following formula:	
=(x/1000+((365*70+17)*86400))/86400
This formula is applied to the entire timestamp column.
NOTE: a better formula mentioned something back on this list.

3.	I then sort rows are sorted by operation name (i.e. JMeter sampler
name)

4.	I can now generate suitable reports. For instance, I generate a
graph of page load times v/s time for different operations (e.g.: login, add
1 line to the order, etc). A different series for each operation type is
used. 

==============================================

Steps #3 and 4 are quite painstaking. It would be nice to have a macro to
automate those steps - however, it is beyond my Excel abilities at the
moment.

Sonam Chauhan
-- 
Electronic Commerce, Corporate Express Australia Ltd.
Phone: +61-2-9335-0725, Email: sonam.chauhan@ce.com.au
 

> -----Original Message-----
> From: Peter Lin [mailto:woolfel@gmail.com]
> Sent: Friday, 26 November 2004 11:30 PM
> To: JMeter Users List
> Subject: Re: Users of JMeter?
> 
> that's definitely an area where loadrunner shines in my opinion. they
> produce pretty charts and graphs that managers love. Even though I've
> added the ability to save listeners and added the distribution graph,
> honestly jmeter doesn't look nearly as pretty. It's not java's fault
> either, the developer prefer to work on functionality and making
> jmeter solid.
> 
> things like making jmeter prettier aren't high on the list of things
> to do. You can always use excel to generate charts, but some people
> prefer to pay 20K+ for a site license. I don't know what it costs
> today, but one of the division where I work has a license for 3
> workstations. I was told it was over 20K, but you should check with
> mercury directly to get a quote. It's definitely not cheap.
> 
> peter
> 
> 
> On Fri, 26 Nov 2004 07:39:25 +0000 (GMT), Paul Keeble <cs...@yahoo.co.uk>
> wrote:
> > It has been used by a very large organisation that looks after the UK's
> health.
> > That's a 4 million user organisation! Not that they use it directly, but
> the
> > people writing their software do.
> >
> > JMeter works, does what it says on the tin but it isn't load runner. It
> > produces similiar results but obviously doesn't come with the feature
> richness
> > that load runner does.
> >
> > I use it for running decent loading tests, but the final results for
> User
> > Acceptance and Non functional sign off are always done with Load runner.
> Why?
> > Because it produces lovely looking reports and the brand name adds to
> the
> > validility of the results (You can produce garbage results just as
> easily with
> > load runner as JMeter).
> >
> > That's my 2 cents.
> >
> >
> >
> >  --- greggy@exemail.com.au wrote:
> > > Hi all,
> > >
> > > I'm trying to convince senior management that JMeter would be good to
> use
> > > in our organisation for volume testing.
> > >
> > > For reasons best know to them, they want to know what large and
> government
> > > organisations out there use JMeter already.
> > >
> > > I've googled for quite a while and can't really come up with many at
> all -
> > > but there must be heaps. So if you've got a spare moment and work for
> a
> > > large or government organisation, please just reply back to this with
> the
> > > organisation name, so I can start compiling a list.
> > >
> > > Thanks for you time!
> > >
> > > G
> > >
> > > ---------------------------------------------------------------------
> > > To unsubscribe, e-mail: jmeter-user-unsubscribe@jakarta.apache.org
> > > For additional commands, e-mail: jmeter-user-help@jakarta.apache.org
> > >
> > >
> >
> >
> > ___________________________________________________________
> > Moving house? Beach bar in Thailand? New Wardrobe? Win £10k with Yahoo!
> Mail to make your dream a reality.
> > Get Yahoo! Mail www.yahoo.co.uk/10k
> >
> >
> >
> > ---------------------------------------------------------------------
> > To unsubscribe, e-mail: jmeter-user-unsubscribe@jakarta.apache.org
> > For additional commands, e-mail: jmeter-user-help@jakarta.apache.org
> >
> >
> 
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: jmeter-user-unsubscribe@jakarta.apache.org
> For additional commands, e-mail: jmeter-user-help@jakarta.apache.org

---------------------------------------------------------------------
To unsubscribe, e-mail: jmeter-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: jmeter-user-help@jakarta.apache.org

Re: Excel tips and tricks

[sebb <se...@gmail.com>]

On Wed, 1 Dec 2004 11:45:12 +1100, Sonam Chauhan
<so...@ce.com.au> wrote:
> Hi - What Peter said below got me thinking... a few people here (including
> me) use Excel to analyse JMeter logs. It would be good for us to share tips
> and tricks, or even jointly develop macros, etc.
> 

Agreed.

> Here's what I do, please feel free to continue this thread...
> 

Would it be OK to put your script and instructions on the JMeter Wiki?
[http://wiki.apache.org/jakarta-jmeter/FrontPage]
Seems to me that this would make it easier to find the instructions
and scripts in the future.

I've created the page:
http://wiki.apache.org/jakarta-jmeter/LogAnalysis as a starting point.

I'm not suggesting continuing the discussions there - just storing the
scripts and instructions etc - which can then be updated as and when.

OK?

---------------------------------------------------------------------
To unsubscribe, e-mail: jmeter-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: jmeter-user-help@jakarta.apache.org

Re: Excel tips and tricks

[nlunebur <nl...@lisasoft.com>]

yeah that's what i thought. dont need Perl to put it into right format. 
just go to excel and import data, select the delimiter and off u go!
as explained by Guiseppe.

nicole

Giuseppe Ielpa wrote:

>Are you sure it is needed to filter the log by Perl?
>I made a try with my Excel 2002 installation, and I
>proved that it is able to open XML files.
>
>Just try File|Open, by setting file type as XML file
>and it should work.
>
>Best
>
>Giuseppe Ielpa 
>g.ielpa@idtech.it
> 
>
>
>  
>
>>-----Original Message-----
>>From: Sonam Chauhan [mailto:sonam.chauhan@ce.com.au] 
>>Sent: Wednesday, December 01, 2004 1:45 AM
>>To: 'JMeter Users List'
>>Subject: Excel tips and tricks
>>
>>
>>Hi - What Peter said below got me thinking... a few people 
>>here (including
>>me) use Excel to analyse JMeter logs. It would be good for us 
>>to share tips and tricks, or even jointly develop macros, etc. 
>>    
>>
>
>
>
>---------------------------------------------------------------------
>To unsubscribe, e-mail: jmeter-user-unsubscribe@jakarta.apache.org
>For additional commands, e-mail: jmeter-user-help@jakarta.apache.org
>
>  
>


---------------------------------------------------------------------
To unsubscribe, e-mail: jmeter-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: jmeter-user-help@jakarta.apache.org

RE: Excel tips and tricks

[Giuseppe Ielpa <g....@idtech.it>]

Are you sure it is needed to filter the log by Perl?
I made a try with my Excel 2002 installation, and I
proved that it is able to open XML files.

Just try File|Open, by setting file type as XML file
and it should work.

Best

Giuseppe Ielpa 
g.ielpa@idtech.it
 


> -----Original Message-----
> From: Sonam Chauhan [mailto:sonam.chauhan@ce.com.au] 
> Sent: Wednesday, December 01, 2004 1:45 AM
> To: 'JMeter Users List'
> Subject: Excel tips and tricks
> 
> 
> Hi - What Peter said below got me thinking... a few people 
> here (including
> me) use Excel to analyse JMeter logs. It would be good for us 
> to share tips and tricks, or even jointly develop macros, etc. 



---------------------------------------------------------------------
To unsubscribe, e-mail: jmeter-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: jmeter-user-help@jakarta.apache.org