HP LoadRunner results analysis. Queries
Published: 2023-07-27 15:30:09

#About
I've been working as a performance engineer for a number of years. I started my PE career with JMeter, spent lots of time with HP LoadRunner and culminated with k6. The most annoying of the three was HP LR. Mostly, because it is very rigid (at least from a user's perspective), only runs on Windows and can't be talked into doing anything the OSS way. Our team was lean, fast and innovative, so it was only a matter of time before LR had to either budge and do it my way, or hit the highway. 

I won.

# Struggles
HP LR is a massive, very powerful tool with proper customer support and quite a large community. However, it's married to MS Windows. If you need anything more than the defaults it can provide, you have to open results' files with the HP LR tool, creatively (/s) called "Analysis". And it only works on MS Windows. Want to integrate performance testing in your CI? Not happening! Unless you are willing to run a dedicated Windows server with Analysis installed in it, acting as a SPOF, chomping on your resources on free time.

# I said NO
Running a Windows machine 24/7, or even worse - spinning it up on demand and hoping it will start up correctly before the CI times out - was not an option for me. Not to mention licencing nuances I'd have to address/request in the corp. I worked for. I needed a lean, simple and easily maintainable approach. I searched for hints in the HPLR community forums, asked others for assistance, but the answers I got either were along the lines of "forget it" or "use a windows server for that - Analysis has a CLI and <... I stopped reading at that point>".

# Discovery
I decided to have a darn good look at the resources I have. My main question was: is the HPLR results' file proprietary or can I perhaps open it somehow and extract what I need from it myself? I downloaded all the run files and analyzed them closely. One of the zip archives contained a particularly big file with an extension: `.mdb`. A quick google search told me that's an Access DB file. Now, how do I access it with Java....? Then I found the [ucanaccess](https://ucanaccess.sourceforge.net/site.html) JDBC driver.

I added 2 + 2 and a potential solution was born in my head.

# Struggles, again
The .mdb file structure is not well docummented, so it took quite some time to understand what's where. The "meat" is in the table `event_meter`, but its structure is not immediatelly obvious. I struggled with the `acount` concept (to be fair, I no longer remember what it means, but I know it's something about data aggregation - perhaps how many such events happened in that single second? IDK). A few weeks later, the dragon was tamed and the queries were born.

# Queries

https://gitlab.com/-/snippets/2575521

## run_details
This query returns a run summary:

```
+--------+---------------+-------------+-----------------+---------------+----------+----------+-------------+-----+-----------+
|TOTAL_VU|VU_RAMPUP_START|VU_RAMPUP_END|VU_RAMPDOWN_START|VU_RAMPDOWN_END|test_start|test_end  |test_duration|tz   |result_name|
+--------+---------------+-------------+-----------------+---------------+----------+----------+-------------+-----+-----------+
|25000   |82             |294          |948              |1327           |1690377335|1690378663|1328         |28800|res9694.lrr|
+--------+---------------+-------------+-----------------+---------------+----------+----------+-------------+-----+-----------+

```

```sql
with run_details(test_start, test_end, test_duration, tz, result_name)
         as (select (result.[start time])                            as test_start,
                    (result.[result end time])                       as test_end,
                    (result.[result end time] - result.[start time]) as test_duration,
                    (result.[time zone])                             as tz,
                    (result.[result name])                           as result_name
             from result),
     vuser_events(inout_flag, status_name, time)
         as (SELECT (events.[inout flag])        as inout_flag,
                    (status.[vuser status name]) as status_name,
                    (events.[end time])          as time
             FROM vuserevent_meter events
                      JOIN vuserstatus status ON (events.[vuser status id]) = (status.[vuser status id])
             order by time asc)
select (select sum(inout_flag) from vuser_events where status_name = 'RUN' and inout_flag > 0) as total_vu,
       (select min(time) from vuser_events where status_name = 'RUN' and inout_flag > 0)       as vu_rampup_start,
       (select max(time) from vuser_events where status_name = 'RUN' and inout_flag > 0)       as vu_rampup_end,
       (select min(time) from vuser_events where status_name = 'RUN' and inout_flag < 0)       as vu_rampdown_start,
       (select max(time) from vuser_events where status_name = 'RUN' and inout_flag < 0)       as vu_rampdown_end,
       details.test_start                                                                      as test_start,
       details.test_end                                                                        as test_end,
       details.test_duration                                                                   as test_duration,
       details.tz                                                                              as tz,
       details.result_name                                                                     as result_name
from run_details details;

```

## run_errors
This query returns test run errors linked to scripts, actions, VU ID and with a test run window offset (sort of a timestamp)

```
+--------+------------+-----+-------+---------+-------------+-----------------+------------+---------------------------------------------------------------------------------+
|END_TIME|EVENT_NAME  |VU_ID|LINE_NO|ITERATION|SCRIPT_NAME  |ACTION_NAME      |LG_NAME     |MESSAGE                                                                          |
+--------+------------+-----+-------+---------+-------------+-----------------+------------+---------------------------------------------------------------------------------+
|84.365  |Error -26366|47   |23     |1        |01_MyScript_1|Create_Cart_Items|srvhplrlg001|Create_Cart_Items.c(23) Error -26366 "Text="cart_id1"" not found for web_reg_find|
+--------+------------+-----+-------+---------+-------------+-----------------+------------+---------------------------------------------------------------------------------+
<........>
```

```sql
with test_script(id, name) as (select
                                   [script id] as id,
                                   [script name] as name
                               from Script),
     event(id, type, name) as (select
                                   [event id] as id,
                                   [event type] as type,
                                   [event name] as name
                               from event_map),
     err(event_id, end_time, error_id, vu_id, host_id, script_id, line_no, action_id, iteration) as (select
                                                                                                         [event id] as event_id,
                                                                                                         [end time] as end_time,
                                                                                                         [error id] as error_id,
                                                                                                         [vuser id] as vu_id,
                                                                                                         [host id] as host_id,
                                                                                                         [script id] as script_id,
                                                                                                         [line number] as line_no,
                                                                                                         [action id] as action_id,
                                                                                                         [iteration number] as iteration
                                                                                                     from Error_meter),
     error(id, message) as (select
                                [error id] as id,
                                [error message] as message
                            from errormessage),
     action(id, name) as (select
                              [action id] as id,
                              [action name] as name
                          from ScriptActions),
     lg(id, name) as (select
                          [host id] as id,
                          [host name] as name
                      from host),
     sorted_errors(end_time, event_name, vu_id, line_no, iteration, script_name, action_name, lg_name, message)
         as (select err.end_time,
                    event.name       as event_name,
                    err.vu_id,
                    err.line_no,
                    err.iteration,
                    test_script.name as script_name,
                    action.name      as action_name,
                    lg.name          as lg_name,
                    error.message
             from err
                      join error on err.error_id = error.id
                      left join test_script on err.script_id = test_script.id
                      left join action on err.action_id = action.id
                      left join lg on err.host_id = lg.id
                      join event on err.event_id = event.id
             order by end_time, vu_id asc)
select *
from sorted_errors;
```

## run_results
This is a big one. It's the core query my automation revolves around. It summarizes test results for each Action (transaction) and makes it rather easy to pluck those numbers out of the database and use them in my report. It also becomes quite easy to compare runs with each other.

**NOTE BENE** this query is memory-hungry and I ended up alocating 8GB to the alpine containers running it. I guess your memory requirements will depend on how long your tests run for and how many scripts/actions you have.

```
+-------------------------------+------------+------------+-----------+------------+------------+-----------+-------+----------+----------+----------+----------------+----------------+
|TRANSACTION_NAME               |PASSED_COUNT|FIRST_PASSED|LAST_PASSED|FAILED_COUNT|FIRST_FAILED|LAST_FAILED|AVG_TPS|MAXIMUM_RT|MINIMUM_RT|AVERAGE_RT|RT_PERCENTILE_90|RT_PERCENTILE_50|
+-------------------------------+------------+------------+-----------+------------+------------+-----------+-------+----------+----------+----------+----------------+----------------+
|#TOTAL                         |616917      |81          |198        |2963        |84          |198        |3427   |29.829    |0.026     |1.059     |0               |0               |
|01_MyScript_1_Create_Cart_Items|3403        |81          |198        |283         |84          |198        |18     |23.546    |0.337     |4.082     |10.5            |2.559           |
+-------------------------------+------------+------------+-----------+------------+------------+-----------+-------+----------+----------+----------+----------------+----------------+
<........>
```


**NOTE** in the lines 3 and 4 set the correct timeframe to generate the report for. Values are seconds from the start of the test.


```sql
with tally as (select rownum() as num from event_meter),
     test_configuration(timeframe_start, timeframe_end)
         as (select %d as timeframe_start,
                 %d as timeframe_end
             from dual),
     run_details(test_start, test_end, test_duration, tz, result_name)
         as (select (result.[start time])                            as test_start,
                    (result.[result end time])                       as test_end,
                    (result.[time zone])                             as tz,
                    (result.[result name])                           as result_name,
                    (result.[result end time] - result.[start time]) as test_duration
             from result),
     all_events (event_id, value, acount, end_time, event_name, event_type, status1, describe_id)
         as (select (data_raw.[event instance id]) as event_id,
                    data_raw.value                 as value,
                    data_raw.acount                as acount,
                    (data_raw.[end time])          as end_time,
                    (event.[event name])           as event_name,
                    (event.[event type])           as event_type,
                    data_raw.status1               as status1,
                    (event.[describe id])          as describe_id
             from event_meter data_raw
                      join event_map event
                           on (event.[event id]) = (data_raw.[event id])
             order by value asc),
     all_events_steady (event_id, value, acount, end_time, event_name, event_type, status1, describe_id)
         as (select event_id    as event_id,
                    value       as value,
                    acount      as acount,
                    end_time    as end_time,
                    event_name  as event_name,
                    event_type  as event_type,
                    status1     as status1,
                    describe_id as describe_id
             from all_events
             where end_time >= (select timeframe_start from test_configuration)
               and end_time <= (select timeframe_end from test_configuration)),
     passed_events (event_id, value, acount, end_time, event_name, event_type, status1, describe_id, tx_status)
         as (select data_all_passed.event_id,
                    data_all_passed.value,
                    data_all_passed.acount,
                    data_all_passed.end_time,
                    data_all_passed.event_name,
                    data_all_passed.event_type,
                    data_all_passed.status1,
                    data_all_passed.describe_id,
                    (tx_status.[transaction end status]) as tx_status
             from all_events_steady data_all_passed
                      JOIN transactionendstatus tx_status
                           ON data_all_passed.status1 = tx_status.status1
                      left join tally on tally.num <= data_all_passed.acount
             where data_all_passed.event_type = 'Transaction'
               AND (tx_status.[transaction end status]) = 'Pass'
             ORDER BY data_all_passed.value ASC),
     failed_events(event_id, value, acount, end_time, event_name, event_type, status1, describe_id, tx_status)
         as (select data_all_failed.event_id,
                    data_all_failed.value,
                    data_all_failed.acount,
                    data_all_failed.end_time,
                    data_all_failed.event_name,
                    data_all_failed.event_type,
                    data_all_failed.status1,
                    data_all_failed.describe_id,
                    (tx_status.[transaction end status]) as tx_status
             from all_events_steady data_all_failed
                      JOIN transactionendstatus tx_status
                           ON data_all_failed.status1 = tx_status.status1
                      left join tally on tally.num <= data_all_failed.acount
             where data_all_failed.event_type = 'Transaction'
               AND (tx_status.[transaction end status]) = 'Fail'),
     grouped_events (event_name,
                     event_count, first_event, last_event,
                     value_max, value_min, value_avg,
                     pctl_id_98, pctl_id_95, pctl_id_90, pctl_id_50) as (select data_passed.event_name                as event_name,
                                                                                count(data_passed.value)              as event_count,
                                                                                min(data_passed.end_time)             as first_event,
                                                                                max(data_passed.end_time)             as last_event,
                                                                                max(data_passed.value)                as value_max,
                                                                                min(data_passed.value)                as value_min,
                                                                                avg(data_passed.value)                as value_avg,
                                                                                (count(data_passed.value) * 98 / 100) as pctl_id_98,
                                                                                (count(data_passed.value) * 95 / 100) as pctl_id_95,
                                                                                (count(data_passed.value) * 90 / 100) as pctl_id_90,
                                                                                (count(data_passed.value) * 50 / 100) as pctl_id_50
                                                                         from passed_events data_passed
                                                                         GROUP BY data_passed.describe_id,
                                                                                  data_passed.event_name),
     summary_table(transaction_name,
                   passed_count, first_passed, last_passed,
                   failed_count, first_failed, last_failed,
                   avg_tps,
                   maximum_rt, minimum_rt, average_rt,
                   rt_percentile_90_id, rt_percentile_90,
                   rt_percentile_50_id, rt_percentile_50) as (select grouped.event_name                                    as transaction_name,
                                                                     grouped.event_count                                   as passed_count,
                                                                     grouped.first_event                                   as first_passed,
                                                                     grouped.last_event                                    as last_passed,
                                                                     (select count(failed.acount)
                                                                      from failed_events failed
                                                                      where failed.event_name = grouped.event_name)        as failed_count,
                                                                     (select min(failed.end_time)
                                                                      from failed_events failed
                                                                      where failed.event_name = grouped.event_name)        as first_failed,
                                                                     (select max(failed.end_time)
                                                                      from failed_events failed
                                                                      where failed.event_name = grouped.event_name)        as last_failed,
                                                                     (grouped.event_count /
                                                                      (select timeframe_end - timeframe_start
                                                                       from test_configuration)
                                                                         )                                                 as avg_tps,
                                                                     grouped.value_max                                     as maximum_rt,
                                                                     grouped.value_min                                     as minimum_rt,
                                                                     grouped.value_avg                                     as average_rt,
                                                                     grouped.pctl_id_90                                    as rt_percentile_90_id,
                                                                     (select rows_numbered.value
                                                                      from (select sorted_by_value.value, rownum() as rownum
                                                                            from passed_events sorted_by_value
                                                                            where sorted_by_value.event_name = grouped.event_name) as rows_numbered
                                                                      where rows_numbered.rownum = grouped.pctl_id_90 + 1) as rt_percentile_90,
                                                                     grouped.pctl_id_50                                    as rt_percentile_50_id,
                                                                     (select rows_numbered.value
                                                                      from (select sorted_by_value.value, rownum() as rownum
                                                                            from passed_events sorted_by_value
                                                                            where sorted_by_value.event_name = grouped.event_name) as rows_numbered
                                                                      where rows_numbered.rownum = grouped.pctl_id_50 + 1) as rt_percentile_50
                                                              from grouped_events grouped)
select transaction_name           transaction_name,
       passed_count               passed_count,
       first_passed               first_passed,
       last_passed                last_passed,
       failed_count               failed_count,
       first_failed               first_failed,
       last_failed                last_failed,
       round(avg_tps, 3)          avg_tps,
       round(maximum_rt, 3)       maximum_rt,
       round(minimum_rt, 3)       minimum_rt,
       round(average_rt, 3)       average_rt,
       round(rt_percentile_90, 3) rt_percentile_90,
       round(rt_percentile_50, 3) rt_percentile_50
from summary_table
UNION
select '#TOTAL'                                                       as transaction_name,
       count(all_passed.value)                                        as passed_count,
       min(all_passed.end_time)                                       as first_passed,
       max(all_passed.end_time)                                       as last_passed,
       (select count(failed.acount) from failed_events failed)        as failed_count,
       (select min(failed.end_time) from failed_events failed)        as first_failed,
       (select max(failed.end_time) from failed_events failed)        as last_failed,
       round(count(all_passed.acount) / (select timeframe_end - timeframe_start
                                         from test_configuration), 3) as avg_tps,
       round(max(all_passed.value), 3)                                as maximum_rt,
       round(min(all_passed.value), 3)                                as minimum_rt,
       round(avg(all_passed.value), 3)                                as average_rt,
       0.0                                                            as rt_percentile_90,
       0.0                                                            as rt_percentile_50
from passed_events all_passed
ORDER BY transaction_name
;
```


There. Have a blast! 

One caveat: MS Access doesn't seem to like CTEs in my queries, but `ucanaccess` deals with them just fine.

# Resources:
- https://www.myloadtest.com/blog/loadrunner-analysis-sql-query/

- https://gitlab.com/-/snippets/2575521

-