Toad World® Forums

How to link a job/jobname or GUID to the corresponding test run, userload results, and the time series data using the latest benchmark factory REST server


#1

I’ve read Kevin Dalton’s blog posts which were good (Hoping that he explains this issue in part 3 [:)] ):
http://www.toadworld.com/products/benchmark-factory/b/weblog/archive/2017/05/15/benchmark-factory-rest-api-part-1
http://www.toadworld.com/products/benchmark-factory/b/weblog/archive/2017/05/15/benchmark-factory-rest-api-part-2

The issue i’m running into now is how do I link the given testrun route to a job name? Currently in the testrun information given back it has like the subtest name, SQL server connection profile, some IDs but unless i’m missing something there isn’t a connection to a job without delving into the subkeys of the job JSON object?

Test run info that i need to capture (I use the run id from this to pull the following results information) Also note that the testid relates to the test object for the TPC-C transaction mix for that job:

C:\Users\Administrator\git\vmwareSysbenchBMFAutomation [master]> (Invoke-RestMethod -Uri localhost:30110/…/testruns)[0]

classname : CTestRunHistory
testid : 2
testversion : 9
testname : TPC-C Transaction Mix
runid : 12
profileid : 11
profilename : MSSQL_10.108.4.77_ODBC
comment :
statusid : 1
starttime : Wed Nov 15 14:14:24 2017
endtime : Wed Nov 15 16:44:39 2017
elapsedtime : 02 hour(s), 30 minute(s), 15 second(s)

The Job that this result came from:

C:\Users\Administrator\git\vmwareSysbenchBMFAutomation [master]> invoke-restmethod -uri localhost:30110/…/TPC-C_BMF_JOB_1

classname : CJob
name : TPC-C_BMF_JOB_1
comment :
connectionname : MSSQL_10.108.4.77_ODBC
emailoperator : 0
operatoraddress :
emailpager : 0
pageraddress :
sendnet : 0
netsendname :
realtimereportinterval : 5
realtimereportcounters :
expanded : 1
stoponerror : 1
globalvariables : {}
tests : {@{classname=CBenchmarkObjectsTest; benchmarkid=10; create_objects=1; always_delete_before_create=0; benchmark_properties=; run_on_agents=1; virtualusers_for_table=200;
parallel_index_create=1; delete_after_run=0; reload_from_backup=0; refresh_using_inserts=0; name=Create Objects for TPC-C; testid=-1; guid={728F120F-4F6D-4E94-879E-0E1C64E4FFEB};
options=4096; enable_latency_scaling=0; latency_scaling_factor=1; commitnumber=0; expanded=1; instructions=System.Object[]; performcheckpoints=0; performcheckpointsatstart=0;
numberofcheckpoints=2; baselinerunid=-1; workloadid=-104; workloadinfor=; statisreportnames=; resultlogname=}, @{classname=CMixTest; name=TPC-C Transaction Mix; testid=2;
guid={D48CB95A-8003-4339-8C91-4174ED5631FA}; options=5120; enable_latency_scaling=0; latency_scaling_factor=1; commitnumber=0; expanded=1; instructions=System.Object[];
performcheckpoints=0; performcheckpointsatstart=0; numberofcheckpoints=2; baselinerunid=-1; workloadid=-104; workloadinfor=; statisreportnames=;
resultlogname=C:\Users\Administrator\Documents\My Benchmark Factory\8.0.0\x64\Result Logs\TPC-C_BMF_JOB_1.log; userloads=15000; duration=; enablepreiterationshelltimeout=1;
preiterationshelltimeout=30; preiterationshellfilename=; enablepostiterationshelltimeout=1; postiterationshelltimeout=30; postiterationshellfilename=}}
user_agent : {@{classname=CUserAgent; host=10.108.2.38; username=administrator; session_cred=0; version=; os=Microsoft Windows 10 Pro; processor=Intel® Xeon® CPU E5-2630 v3 @ 2.40GHz; no_core=0;
no_cpus=1; physical_memory=12884430848.0; virtual_memory=0.0; disk_size=33494659072.0; disk_free_size=5140746240.0; number_of_agent=5; max_virtual_user=0; checked=1; flags=0;
address_width=0}, @{classname=CUserAgent; host=10.108.2.204; username=administrator; session_cred=0; version=; os=Microsoft Windows 10 Pro; processor=Intel® Xeon® CPU E5-2630 v3 @
2.40GHz; no_core=0; no_cpus=1; physical_memory=12884430848.0; virtual_memory=0.0; disk_size=33494659072.0; disk_free_size=5140746240.0; number_of_agent=5; max_virtual_user=0; checked=1;
flags=0; address_width=0}}
schedule : @{classname=CJobSchedule; enable=0; occursonce=1; executenexttime=1508978435; occursdwm=0; occurseverydwm=1; everymonday=0; everytuesday=0; everywednesday=0; everythursday=0;
everyfriday=0; everysaturday=0; everysunday=0; monthlyparticularday=1; dayxofevery=1; monthly1stlast=0; monthlydayofweek=0; monthlyeverynmonths=1; occursdailyonce=1; occursdailyhm=0;
occursdailyeveryhm=1; dailystartat=335772000; dailyendat=335858399; executeonceperdayat=1508978435; scheduleends=0; scheduleend=1508997599; deleteafterrun=0}
connection : @{connectiontype=SQLServerODBC; name=MSSQL_10.108.4.77_ODBC; username=sa; datasourcename=10.108.4.77_ODBC; infinitetimeout=False; timeout=60; reconnectoption=;
customproperties=System.Object[]; serverproperties=System.Object[]}
resultlog_path :
agentlogfilenames : BMFConsole_Error.log,error_BMFAgent_1_12.log,error_BMFAgent_1_11.log,error_BMFAgent_1_10.log,error_BMFAgent_1_14.log,error_BMFAgent_1_13.log,error_BMFAgent_1_17.log,error_BMFAgent_1_16.lo
g,error_BMFAgent_1_15.log,error_BMFAgent_1_18.log,error_BMFAgent_1_19.log,GeneralInformations_BMFAgent_1.log,GeneralInformations_BMFAgent_1.log,

I use the previous testrun ID for pulling the test result summary (testrun id 12):

C:\Users\Administrator\git\vmwareSysbenchBMFAutomation [master]> Invoke-RestMethod -Method Get -Uri localhost:30110/…/UserloadResults

classname : CStationResults
stationid : 0
computername :
stationcfgid : 0
userid : 0
testid : 0
testversion : 0
userload : 15000
runid : 12
profileid : -1000
tps : 1701.6669
bps : 774610.0
rps : 9275.1689
avgtime : 4072
mintime : 1
maxtime : 296038
totaltime : 2147483647
testedtime : 0
totalrows : 16680634
totalbytes : 1393071565
executions : 3060309
totalxactexecutions : 0
totalerrors : 0
deadlocks : 17
phaseid : 1
totalrollback : 0
avgresponsetime : 4072
minresponsetime : 1
maxresponsetime : 296038
totalresponsetime : 2147483647
avgthinktime : 2333
minthinktime : 0
maxthinktime : 6012
totalthinktime : 0
avgarrivaltime : 0
minarrivaltime : 0
maxarrivaltime : 0
totalarrivaltime : 0
avgkeyingtime : 2407
minkeyingtime : 500
maxkeyingtime : 4500
totalkeyingtime : 0
ninetythresponsetime : 0
ninetythtransactiontime : 0
ninetyththinktime : 0
ninetytharrivaltime : 0
ninetythkeyingtime : 0
testguid : {900ECBE0-4319-456C-B31E-A8F43AB9AB38}
v_stationid : 0
node_number : -1

Also last question [:)]. I need the time series samples of the test as well from the sampling period. Is that part of the transactionResult route? If so what is the userloadresultindex? I’ve tried the following guesses on what that is and they don’t work?

C:\Users\Administrator\git\vmwareSysbenchBMFAutomation [master]> Invoke-RestMethod -Method Get -Uri localhost:30110/…/TransactionResults
Invoke-RestMethod : 404 Not FoundNot FoundThe requested URL was not found.
At line:1 char:1

  • Invoke-RestMethod -Method Get -Uri localhost:30110/…/testrun
  • CategoryInfo : InvalidOperation: (System.Net.HttpWebRequest:HttpWebRequest) [Invoke-RestMethod], WebException
  • FullyQualifiedErrorId : WebCmdletWebResponseException,Microsoft.PowerShell.Commands.InvokeRestMethodCommand

C:\Users\Administrator\git\vmwareSysbenchBMFAutomation [master]> Invoke-RestMethod -Method Get -Uri localhost:30110/…/TransactionResults
Invoke-RestMethod : 404 Not FoundNot FoundThe requested URL was not found.
At line:1 char:1

  • Invoke-RestMethod -Method Get -Uri localhost:30110/…/testrun
  • CategoryInfo : InvalidOperation: (System.Net.HttpWebRequest:HttpWebRequest) [Invoke-RestMethod], WebException
  • FullyQualifiedErrorId : WebCmdletWebResponseException,Microsoft.PowerShell.Commands.InvokeRestMethodCommand

#2

the URI should be localhost:30110/api/testruns//userloadresults

this will return all userload results, and you can add userload id to retrieve individual userload results. for example if you test’s userload is 1, 4, 8, 10, then you can issue userloadresults/1 retrieve userload 1’s results.


#3

Ok that helps me get the transaction data as we are using 15000 virtual users and it works great thanks!

c:>Invoke-RestMethod -Method Get -Uri http://localhost:30110/api/testruns/12/UserloadResults/15000/TransactionResults

classname : CXactResults
benchmarkid : 10
driverid : 0
testtypeid : -1
xactid : 801
runorder : 1
weight : 45
xact_interval : 0
description :
typetext :
name : New Order Transaction
parentguid : {00000000-0000-0000-0000-000000000000}
parentrunorder : 0
stationid : 6
computername : BMFAgent_1


The other issue i’m trying to resolve here is we are running 2-4 concurrent jobs each with 15000 virtual users depending on the storage device we are testing. So i need some way in REST to relate the **runid **to the **jobname **in order to store and differentiate the results in a database.


#4

Since test runs are associated with tests (jobs are just containers), the only way to get a list of runids for a specific job you will need to loop through all the tests in a job. Here is a sample script which can be used.

$bmfserver = “localhost”
$jobname = “BigJob”

# first get a collection of all the tests in a job
$uri = “http://”+$bmfserver+":30100/api/jobs/"+$jobname+"/tests"
$response = Invoke-RestMethod -Method Get -Uri $uri

foreach ($test in $response)
{
# Get the current results for the test
$uri = “http://”+$bmfserver+":30100/api/jobs/"+$jobname+"/tests/"+$test.name+"/CurrentResult"
$response2 = Invoke-RestMethod -Method Get -Uri $uri
Write-Host “Test '”$test.name"’ had a RunID of"$response2.runid"."
}

I hope this helps and let me know if you have any further questions.

PS. Thanks for your comments on my blogs! There are more coming!


#5

Ah that makes sense and is exactly what i needed thanks Kevin! Results for a recent run using your script. [Y]

Test ’ Create Objects for TPC-C ’ had a RunID of .
Test ’ TPC-C Transaction Mix ’ had a RunID of 14 .

I’m about 95% of the way of everything I need to automate at this point. My last question if we can answer it here or another forum (don’t mind either way) is I don’t see an option to pull the time series samples from like the TPC-C transaction mix? We are sampling the default 5-10 seconds or whatever and I know how to pull the data manually but I need access to it in the REST interface. Is there a way to do that?


#6

Unfortunately there isn’t a way to get the real-time data using the REST API. If you would like we can add this as an enhancement request, just let me know.


#7

Yup that would be great if that could be added in future releases. Thanks for the assistance I appreciate it.