SQL variables

SQL Example

To use the SQL variables above create an SQL profile as shown below.

The SQL statment shown below must match the SQL table. The column types are shown above.

[speed] INSERT INTO global (recordid, testid, timeint, time, datetime, sid, ip, isp, dns, testto, cc, detaillink, contype, proxy) VALUES (%RECORDID%, %TESTID%, %TIMEINT%, %TIME%, '%DATETIME%', '%SID%', '%IP%', '%ISP%', '%DNSNAME%', '%TESTTO%', '%CC%', '%DETAILLINK%', '%CONTYPE%', '%VIAPROXY%');

SQL Example

To use the SQL variables above create an SQL profile as shown below.

The SQL statment shown below must match the SQL table. The column types are shown above.

[act] INSERT INTO quality (recordid, testid, dspeed, uspeed, qos, rtt, rttmax, rttavg, maxpause, maxroutespeed, maxlinespeed, concurrenttcp, tcpmtu, tcpforcedidle, pooo, booo, pow, bow, dop, dob, pdp, pdb, crcerr, boff, ts, probe, winzeroup, byteslost, rtxtmo, rtxfast, pretx, bretx, sendclose, rcvwinup, acktoomuch, duppack, sndprobe, presisttmo, interface, uqos, umaxpause, umaxlinespeed, uconcurrenttcp, utcpforcedidle, ftl, noal, sf, ethrxcrcerr, ovr, trunc, dtesttype, utesttype, netdown, netup, downpc, http3, http4, http5, httpq, version, runtime, freeq) VALUES (%RECORDID%, %TESTID%, %ACT.DSPEED%, %ACT.USPEED%, %ACT.QOS%, %ACT.RTT%, %ACT.RTTMAX%, %ACT.RTTAVG%, %ACT.MAXPAUSE%, %ACT.MAXROUTESPEED%, %ACT.MAXLINESPEED%, %ACT.CONCURRENTTCP%, %ACT.TCPMTU%, %ACT.TCPFORCEDIDLE%, %ACT.TCPRXPOOO%, %ACT.TCPRXBOOO%, %ACT.TCPRXPOW%, %ACT.TCPRXBOW%, %ACT.TCPRXDOP%, %ACT.TCPRXDOB%, %ACT.TCPRXPDP%, %ACT.TCPRXPDB%, %ACT.TCPRXCRCERR%, %ACT.TCPRXBOFF%, %ACT.TCPRXTS%, %ACT.TCPRXPROBE%, %ACT.TCPRXWINZEROUP%, %ACT.TCPBYTESLOST%, %ACT.TCPTXRTXTMO%, %ACT.TCPTXRTXFAST%, %ACT.TCPTXPRETX%, %ACT.TCPTXBRETX%, %ACT.TCPTXSNDWINCLOSE%, %ACT.TCPTXRCVWINUPD%, %ACT.TCPTXRCVACKTOOMUCH%, %ACT.TCPTXRCVDUPACK%, %ACT.TCPTXSNDPROBE%, %ACT.TCPTXPERSISTTIMEO%, '%ACT.INTERFACE%', %ACT.UQOS%, %ACT.UMAXPAUSE%, %ACT.UMAXLINESPEED%, %ACT.UCONCURRENTTCP%, %ACT.UTCPFORCEDIDLE%, %ACT.ETHRXFTL%, %ACT.ETHRXNOAL%, %ACT.ETHRXSF%, %ACT.ETHRXCRCERR%, %ACT.ETHRXOVR%, %ACT.ETHRXTRUNC%, '%ACT.DTESTTYPE%', '%ACT.UTESTTYPE%', %ACT.NETDOWNSEC%, %ACT.NETUPSEC%, %ACT.NETDOWNPC%, %ACT.HTTP3XXSEC%, %ACT.HTTP4XXSEC%, %ACT.HTTP5XXSEC%, %ACT.HTTPQXXSEC%, '%ACT.VERSION%', %ACT.RUNTIME%, %ACT.FREEQ%);

Below is the CREATE TABLE statement for MySQL.

CREATE TABLE accessct (recordid bigint, testid bigint, dspeed bigint, uspeed bigint, qos int, rtt int, rttmax int, rttavg int, maxpause int, maxroutespeed bigint, maxlinespeed bigint, concurrenttcp float, tcpmtu int, tcpforcedidle float, pooo int, booo int, pow int, bow int, dop int, dob int, pdp int, pdb int, crcerr int, boff int, ts int, probe int, winzeroup int, byteslost int, rtxtmo int, rtxfast int, pretx int, bretx int, sendclose int, rcvwinup int, acktoomuch int, duppack int, sndprobe int, presisttmo int, interface varchar(128), uqos int, umaxpause int, umaxlinespeed bigint, uconcurrenttcp float, utcpforcedidle float, ftl int, noal int, sf int, ethrxcrcerr int, ovr int, trunc int, dtesttype varchar(6), utesttype varchar(6), netdown int, netup int, downpc int, http3 int, http4 int, http5 int, httpq int, version varchar(128), runtime int, freeq int, PRIMARY KEY (recordid));

SQL Example

To use the SQL variables above create an SQL profile as shown below.

The SQL statment shown below must match the SQL table. The column types are shown above.

[voip] INSERT INTO myvoip (recordid, testid, jitter, loss, djitter, dloss, discards, pktorder, rttmin, rttavg, rttmax, rttcon, reg, inv, bye, sipalg, updist, downdist, maxjit, dmaxjit, mos, dmos, simlines) VALUES (%RECORDID%, %TESTID%, %VOIP.JITTER%, %VOIP.LOSS%, %VOIP.DJITTER%, %VOIP.DLOSS%, %VOIP.DISCARDS%, %VOIP.ORDER%, %VOIP.RTTMIN%, %VOIP.RTTAVG%, %VOIP.RTTMAX%, %VOIP.RTTCONSISTENCY%, %VOIP.REGISTER%, %VOIP.INVITE%, %VOIP.BYE%, '%VOIP.SIPALGFW%', %VOIP.UPDISTLOSS%, %VOIP.DOWNDISTLOSS%, %VOIP.MAXJITTER%, %VOIP.DMAXJITTER%, %VOIP.MOS%, %VOIP.DMOS%, %VOIP.LINES%);

Below is the CREATE TABLE statement for MySQL.

CREATE TABLE myvoip (recordid int, testid int, jitter float, loss float, djitter float, dloss float, discards float, pktorder float, rttmin int, rttavg int, rttmax int, rttcon float, reg int, inv int, bye int, sipalg varchar(8), updist float, downdist float, maxjit float, dmaxjit float, mos float, dmos float, simlines int);

SQL Example

To use the SQL variables above create an SQL profile as shown below.

The SQL statment shown below must match the SQL table. The column types are shown above.

[route] INSERT INTO myroute (recordid, testid, traceto, totalhops, targetms, maxms, targetloss, maxloss, dnstime, targetip, tracefrom) VALUES (%RECORDID%, %TESTID%, '%ROUTE.ENTEREDNAME%', %ROUTE.HOPS%, %ROUTE.ENDMS%, %ROUTE.MAXMS%, %ROUTE.ENDLOSS%, %ROUTE.MAXLOSS%, %ROUTE.DNSTIME%, '%ROUTE.IPTO%', '%ROUTE.IPFROM%');

Below is the CREATE TABLE statement for MySQL.

CREATE TABLE myroute (recordid int, testid int, traceto varchar(128), totalhops tinyint, targetms smallint, maxms smallint, targetloss smallint, maxloss smallint, dnstime smallint, targetip varchar(128), tracefrom varchar(128));

When running a bi-directional route test MCS will export one record for each direction. So, in the SQL table there will be a row for client-to-server and a row for server-to-client.

SQL Example

To use the SQL variables above create an SQL profile as shown below.

The SQL statment shown below must match the SQL table. The column types are shown above.

[capacity] INSERT INTO capacity (recordid, testid, dcap, ucap, dlimit, ulimit, dpkt, upkt, pktsize, cos) VALUES (%RECORDID%, %TESTID%, %CAPACITY.DCAPACITY%, %CAPACITY.UCAPACITY%, %CAPACITY.DMAXBANDWIDTH%, %CAPACITY.UMAXBANDWIDTH%, %CAPACITY.DPACKETS%, %CAPACITY.UPACKETS%, %CAPACITY.PACKETSIZE%, %CAPACITY.QOS%);

Below is the CREATE TABLE statement for MySQL.

CREATE TABLE capacity (recordid int, testid int, dcap bigint, ucap bigint, dlimit bigint, ulimit bigint, dpkt int, upkt int, pktsize int, cos tinyint);

SQL Example

To use the SQL variables above create an SQL profile as shown below.

The SQL statment shown below must match the SQL table. The column types are shown above.

[firewall] INSERT INTO myfirewall (recordid, testid, avgres, minres, maxres, port, protocol, popen, pblock, tpopen, tpblock) VALUES (%RECORDID%, %TESTID%, %FIREWALL.AVGRESPONSE%, %FIREWALL.MINRESPONSE%, %FIREWALL.MAXRESPONSE%, '%FIREWALL.PORTS%', '%FIREWALL.PROTOCOL%', %FIREWALL.POPEN%, %FIREWALL.PBLOCK%, %FIREWALL.TPOPEN%, %FIREWALL.TPBLOCK%);

Below is the CREATE TABLE statement for MySQL.

CREATE TABLE myfirewall (recordid int, testid int, avgres float, minres smallint, maxres smallint, port varchar(16), protocol varchar(8), popen int, pblock int, tpopen int, tpblock int);