Posted by: craigtech | January 26, 2010

Providing analysis on top of the BrowserMob data

One of the things that the BrowserMob system doesn’t do too well is provide analytics on top of the data that they generate during their load tests.  Luckily they do give you a mysql install script that allows you to get the data into a local database & use whatever tools you want to get the data you need.

Unfortunately, being a Microsoft guy, I don’t want to use MySql if I can help it! Luckily, it isn’t too difficult to modify the script so you can use it in Sql Server.

I also want to keep the historical data, so that I can compare test runs against each other & track progress towards our goals over time.

To start with I’ve just created the tables by slightly modifying the Create table scripts :

CREATE TABLE test (

test_id int NOT NULL,

[name] varchar (255),

unique_id varchar (255),

CONSTRAINT PK_TEST PRIMARY KEY CLUSTERED(test_id ASC)

)

CREATE TABLE run (

run_id int NOT NULL,

starttime datetime DEFAULT GETDATE(),

comments varchar (255),

run_by varchar (255),

CONSTRAINT PK_RUN PRIMARY KEY CLUSTERED(run_id ASC)

)

CREATE TABLE [tx](

[tx_id] [bigint] NOT NULL,

[run_id] [int] NULL,

[browser_num] [int] NOT NULL,

[bytes] [bigint] NOT NULL,

[end_time] [datetime] NOT NULL DEFAULT (getdate()),

[err_line_num] [int] NULL DEFAULT (NULL),

[err_msg] [varchar](255) DEFAULT (NULL),

[err_screenshot_id] [varchar](255) DEFAULT (NULL),

[instance_id] [varchar](255) NOT NULL,

[script_id] [varchar](255) NOT NULL,

[start_time] [datetime] NOT NULL DEFAULT (‘0000-00-00 00:00:00’),

[step_count] [int] NULL DEFAULT (NULL),

[success] [bit] NOT NULL,

[time_active] [bigint] NOT NULL,

[time_paused] [bigint] NOT NULL,

CONSTRAINT PK_TX PRIMARY KEY CLUSTERED(tx_id ASC)

)

ALTER TABLE tx

WITH CHECK ADD CONSTRAINT FK_TX_RUN FOREIGN KEY(run_id)

REFERENCES run (run_id)

CREATE TABLE step (

step_id bigint NOT NULL,

bytes bigint NOT NULL,

end_time datetime NOT NULL DEFAULT GETDATE(),

obj_count int DEFAULT NULL,

start_time datetime NOT NULL DEFAULT ‘0000-00-00 00:00:00’,

step int NOT NULL,

time_active bigint NOT NULL,

time_paused bigint NOT NULL,

tx_id bigint NOT NULL,

CONSTRAINT PK_STEP PRIMARY KEY CLUSTERED(step_id ASC)

)

ALTER TABLE step

WITH CHECK ADD CONSTRAINT FK_STEP_TX FOREIGN KEY(tx_id)

REFERENCES tx (tx_id)

CREATE TABLE object (

[obj_id] [bigint] NOT NULL,

[bytes] [bigint] NOT NULL,

[dns_lookup_time] [bigint] NULL DEFAULT (NULL),

[end_time] [datetime] NOT NULL DEFAULT (getdate()),

[err_msg] [varchar](256)  NULL DEFAULT (NULL),

[host] [varchar](256)  NOT NULL,

[ip_address] [varchar](256)  NULL DEFAULT (NULL),

[method] [varchar](16)  NULL DEFAULT (NULL),

[obj_num] [int] NOT NULL,

[path] [varchar](4096)  NOT NULL,

[protocol] [varchar](16)  NOT NULL,

[query_string] [varchar](4096)  NULL DEFAULT (NULL),

[start_time] [datetime] NOT NULL DEFAULT (‘0000-00-00 00:00:00’),

[status_code] [int] NOT NULL,

[time_active] [bigint] NULL DEFAULT (NULL),

[time_to_first_byte] [bigint] NULL DEFAULT (NULL),

[url] [varchar](4096)  NOT NULL,

[step_id] [bigint] NOT NULL,

CONSTRAINT PK_OBJECT PRIMARY KEY CLUSTERED(obj_id ASC)

)

ALTER TABLE object

WITH CHECK ADD CONSTRAINT FK_OBJECT_STEP FOREIGN KEY(step_id)

REFERENCES step (step_id)

Then start cleaning the insert scripts & running them in, do so in the same order as the table order above.  To do this I’ve broken them out so that I just have the insert commands for each table in text files & start doctoring them like so :

  1. SQL server doesn’t support the ` character, i.e INSERT INTO `object` – easily solved by a quick find and replace, replacing it with nothing.
  2. Some of my data contains \’, so I needed to replace \’ with \”
  3. SQL Server doesn’t support multiple sets of values as part of INSERT INTO so in each file I replace ),( with a new INSERT INTO statement, e.g
    Replace : ),(
    With : )\nINSERT INTO step VALUES (
The tx tables inserts have a problem with the success bit value (the last but two column), so
Replace : ”
With zero
And replace the other value with 1.
As I have added a run table to track the historical data I need to modify the tx insert script to a new run_id column I do another replace on those lines:
Replace : INSERT INTO tx VALUES (
With : INSERT INTO tx ([run_id],[tx_id],[browser_num],[bytes],[end_time],[err_line_num],[err_msg],[err_screenshot_id],[instance_id],[script_id],[start_time],[step_count],[success],[time_active],[time_paused])\nVALUES (1,
I will upload a zip file with the 3 files when I have a moment.
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Categories

%d bloggers like this: