SQL Variables

The data stored in the MCS database from tests being run can be used in various ways. The mechanism for referencing the data is what we call a dollar ($) variable. It allows the user to extract test results for use in things like emails and a web page.

This tutorial details the available variables by type and, when applicable, how to use them.

Variables like date, session ID, and connection type.

SQL Example

To use the SQL variables above, create a SQL profile. The SQL statement 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%');

Variables related to throughput tests, like download and upload speed.

SQL Example

To use the SQL variables above, create a SQL profile. The SQL statement shown below must match the SQL table.

[act] INSERT INTO quality (recordid, testid, dspeed, uspeed, qos, rtt, rttmax, rttavg, maxpause, maxroutespeed, maxlinespeed, concurrenttcp, tcpmtu, tcpforcedidle, interface, uqos, umaxpause, umaxlinespeed, uconcurrenttcp, utcpforcedidle, dtesttype, utesttype) 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.INTERFACE%', %ACT.UQOS%, %ACT.UMAXPAUSE%, %ACT.UMAXLINESPEED%, %ACT.UCONCURRENTTCP%, %ACT.UTCPFORCEDIDLE%, '%ACT.DTESTTYPE%', '%ACT.UTESTTYPE%');

Variables related to VoIP tests, like jitter and packet loss.

SQL Example

[voip] INSERT INTO myvoip (recordid, testid, jitter, loss, djitter, dloss, discards, pktorder, rttmin, rttavg, rttmax, rttcon, reg, inv, bye, sipalg, 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.MOS%, %VOIP.DMOS%, %VOIP.LINES%);

CREATE TABLE Statement (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), mos float, dmos float, simlines int);

Variables related to route tests, like hops and ISP.

SQL Example

[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%');

CREATE TABLE Statement (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));

Variables related to capacity tests, like download and upload capacity.

SQL Example

[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%);

CREATE TABLE Statement (MySQL)

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

Variables related to firewall tests, like ports open and closed.

SQL Example

[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%);

CREATE TABLE Statement (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);