Support Overview

Tutorials

Client API Usage Examples

Upgrade Information

FAQ

New Release History

You are here:   Visualware >   MyConnection Server >   Support >   Online Manual Overview >   SQL Examples

SQL Examples

Setting up MySQL Database Tables

Sub section menu:

The commands in this section are for MySQL and assumes a MySQL database has already been installed and created.

The CREATE TABLE commands on the right have to be entered separately.

It's NOT NECESSARY to have every test, just pick and choose the results and tables required.

If for any reason these commands aren't working or an error has been spotted please don't hesitate to let us know.

CREATE TABLE tests (recordid int, testid int, time varchar(40), timeint bigint, detaillink varchar(128), sid varchar(255), ip varchar(64), dnsname varchar(64), cc varchar(2), isp varchar(64), PRIMARY KEY (recordid), UNIQUE KEY (testid));

CREATE TABLE error (recordid int, testid int, source varchar(128), type varchar(128), id varchar(128), code int, description varchar(255), PRIMARY KEY (recordid), UNIQUE KEY (testid));

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), UNIQUE KEY (testid));

CREATE TABLE myfirewall (recordid int, testid int, avgresponse int, minresponse int, maxresponse int, ports int, protocol varchar(6), PRIMARY KEY (recordid), UNIQUE KEY (testid));

CREATE TABLE myhispeed (recordid int, testid int, dspeed bigint, uspeed bigint, qos int, uqos int, rtt int, maxpause int, avgpause int, bandwidth bigint, routespeed bigint, forcedidle int, routeconc int, dtesttype varchar(6), utesttype varchar(6), PRIMARY KEY (recordid), UNIQUE KEY (testid));

CREATE TABLE mycapacity (recordid int, testid int, dcapacity bigint, ucapacity bigint, dpackets int, upackets int, packetsize int, qos int, PRIMARY KEY (recordid), UNIQUE KEY (testid));

CREATE TABLE myiptv (recordid int, testid int, jitter1 float, loss1 float, order1 float, jitter2 float, loss2 float, order2 float, jitter3 float, loss3 float, order3 float, rttmin int, rttavg int, rttmax int, PRIMARY KEY (recordid), UNIQUE KEY (testid));

CREATE TABLE myroute (recordid int, testid int, enteredname varchar(128), hops int, endms int, maxms int, endloss int, maxloss int, dnstime int, ipto varchar(64), ipfrom varchar(64), nameto varchar(128), PRIMARY KEY (recordid), UNIQUE KEY (testid));

CREATE TABLE myspeed (recordid int, testid int, dspeed bigint, uspeed bigint, qos int, uqos int, rtt int, rttmax int, rttavg int, rttconsistency int, maxpause int, avgpause int, bandwidth bigint, routespeed bigint, forcedidle int, routeconc float, dtesttype varchar(6), utesttype varchar(6), PRIMARY KEY (recordid), UNIQUE KEY (testid));

CREATE TABLE myvideo (recordid int, testid int, ajitter float, vjitter float, asjitter float, vsjitter float, aloss int, vloss int, adiscards int, vdiscards int, maxdelay int, rtt int, setuptime int, describetime int, playtime int, 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, winclose int, winupd int, acktoomuch int, rcvduppkt int, sndprobe int, persistmo int, ftl int, noal int, sf int, ethcrcerr int, ovr int, trunc int, interface varchar(128), tcpmtu int, PRIMARY KEY (recordid), UNIQUE KEY (testid));

CREATE TABLE myvoip (recordid int, testid int, jitter float, djitter float, loss float, dloss float, uorder float, dorder float, discards float, mos float, reg int, invite int, bye int, PRIMARY KEY (recordid), UNIQUE KEY (testid));

CREATE TABLE myconference (recordid int,testid int,dlatency int,slatency int,avgres int,pass int,fail int,jitter1 float,jittermax1 float,loss1 float,dloss1 float,discard1 float,jitter2 float,jittermax2 float,loss2 float,dloss2 float,discard2 float,jitter3 float,jittermax3 float,loss3 float,dloss3 float,discard3 float,rttmin int,rttavg int,rttmax int,rttconsistecny float,PRIMARY KEY (recordid), UNIQUE KEY (testid));

Monitoring Plugins:

CREATE TABLE dns (recordid bigint, testid int, qname varchar(128), latency int, failure float, latency2 int, failure2 float, latency3 int, failure3 float, dnsresult varchar(128), dnspacket varchar(128), rttmin int, rttavg int, rttmax int, dnsresult2 varchar(128), dnspacket2 varchar(128), rttmin2 int, rttavg2 int, rttmax2 int, dnsresult3 varchar(128), dnspacket3 varchar(128), rttmin3 int, rttavg3 int, rttmax3 int, PRIMARY KEY (recordid), UNIQUE KEY (testid));

CREATE TABLE ftp (recordid bigint, testid int, latency int, failure int, getput varchar(128), kbread int, kbwrite int, kbsecread int, kbsecwrite int, rttmin int, rttavg int, rttmax int, PRIMARY KEY (recordid), UNIQUE KEY (testid));

CREATE TABLE ping (recordid bigint, testid int, avglat int, minlat int, maxlat int, loss int, PRIMARY KEY (recordid), UNIQUE KEY (testid));

CREATE TABLE port (recordid bigint, testid int, avglat int, failure int, minlat int, port varchar(128), maxlat int, opened int, failed int, refused int, timedout int, PRIMARY KEY (recordid), UNIQUE KEY (testid));

CREATE TABLE router (recordid bigint, testid int, latency int, failure int, ipios int, icmpios int, tcpios int, udpios int, snmpios int, totalios int, globalutilin int, globaloctin int, globalutilout int, globaloctout int, portutilin int, portoctin int, portutilout int, portoctout int, rttmin int, rttavg int, rttmax int, PRIMARY KEY (recordid), UNIQUE KEY (testid));

CREATE TABLE sip (recordid bigint, testid int, rlatency int, olatency int, ilatency int, clatency int, loss int, rttmin int, rttavg int, rttmax int, PRIMARY KEY (recordid), UNIQUE KEY (testid));

CREATE TABLE snmp (recordid bigint, testid int, latency int, failure int, PRIMARY KEY (recordid), UNIQUE KEY (testid));

CREATE TABLE web (recordid bigint, testid int, url varchar(128), latencyms int, failurems int, connectms int, findms int, readms int, pagesize int, kbsec int, PRIMARY KEY (recordid), UNIQUE KEY (testid));

Creating an SQL Profile to pass results to the newly formed table(s)

The commands on the right can be entered into MyConnection Server (MCS) when creating a new SQL Profile, as shown here.

It's IMPORTANT that the names proceeding the INSERT INTO statement match those of the SQL database.

If for any reason these commands aren't working or an error has been spotted please don't hesitate to let us know.

INSERT INTO tests (recordid, testid, time, timeint, detaillink, sid, ip, dnsname, cc, isp) VALUES (%RECORDID%, %TESTID%, '%TIME%', %TIMEINT%, '%DETAILLINK%', '%SID%', '%IP%', '%DNSNAME%', '%CC%', '%ISP%');

[error] INSERT INTO error (recordid, testid, source, type, id, code, description) VALUES (%RECORDID%, %TESTID%, '%ERROR.SOURCE%', '%ERROR.TYPE%', '%ERROR.ID%', '%ERROR.CODE%', '%ERROR.DESC%');

[speed] INSERT INTO myspeed (recordid, testid, dspeed, uspeed, qos, uqos, rtt, rttmax, rttavg, rttconsistency, maxpause, avgpause, bandwidth, routespeed, forcedidle, routeconc, dtesttype, utesttype) VALUES (%RECORDID%, %TESTID%, %SPEED.DSPEED%, %SPEED.USPEED%, %SPEED.QOS%, %SPEED.UQOS%, %SPEED.RTT%, %SPEED.RTTMAX%, %SPEED.RTTAVG%, %SPEED.RTTCONSISTENCY%, %SPEED.MAXPAUSE%, %SPEED.AVGPAUSE%, %SPEED.BANDWIDTH%, %SPEED.ROUTESPEED%, %SPEED.FORCEDIDLE%, %SPEED.ROUTECONC%, '%SPEED.DTESTTYPE%', '%SPEED.UTESTTYPE%');

[firewall] INSERT INTO myfirewall (recordid, testid, avgresponse, minresponse, maxresponse, ports, protocol) VALUES (%RECORDID%, %TESTID%, %FIREWALL.AVGRESPONSE%, %FIREWALL.MINRESPONSE%, %FIREWALL.MAXRESPONSE%, %FIREWALL.PORTS%, '%FIREWALL.PROTOCOL%');

[hispeed] INSERT INTO myhispeed (recordid, testid, dspeed, uspeed, qos, uqos, rtt, maxpause, avgpause, bandwidth, routespeed, forcedidle, routeconc, dtesttype, utesttype) VALUES (%RECORDID%, %TESTID%, %HISPEED.DSPEED%, %HISPEED.USPEED%, %HISPEED.QOS%, %HISPEED.UQOS%, %HISPEED.RTT%, %HISPEED.MAXPAUSE%, %HISPEED.AVGPAUSE%, %HISPEED.BANDWIDTH%, %HISPEED.ROUTESPEED%, %HISPEED.FORCEDIDLE%, %HISPEED.ROUTECONC%, '%HISPEED.DTESTTYPE%', '%HISPEED.UTESTTYPE%');

[voip] INSERT INTO myvoip (recordid, testid, jitter, djitter, loss, dloss, uorder, dorder, discards, mos, reg, invite, bye) VALUES (%RECORDID%, %TESTID%, %VOIP.JITTER%, %VOIP.DJITTER%, %VOIP.LOSS%, %VOIP.DLOSS%, %VOIP.ORDER%, %VOIP.DORDER%, %VOIP.DISCARDS%, %VOIP.MOS%, %VOIP.REGISTER%, %VOIP.INVITE%, %VOIP.BYE%);

[video] INSERT INTO myvideo (recordid, testid, ajitter, vjitter, asjitter, vsjitter, aloss, vloss, adiscards, vdiscards, maxdelay, rtt, setuptime, describetime, playtime, pooo, booo, pow, bow, dop, dob, pdp, pdb, crcerr, boff, ts, probe, winzeroup, byteslost, rtxtmo, rtxfast, pretx, bretx, winclose, winupd, acktoomuch, rcvduppkt, sndprobe, persistmo, ftl, noal, sf, ethcrcerr, ovr, trunc, interface, tcpmtu) VALUES (%RECORDID%, %TESTID%, %VIDEO.AJITTER%, %VIDEO.VJITTER%, %VIDEO.ASJITTER%, %VIDEO.VSJITTER%, %VIDEO.ALOSS%, %VIDEO.VLOSS%, %VIDEO.ADISCARDS%, %VIDEO.VDISCARDS% , %VIDEO.MAXDELAY%, %VIDEO.RTT%, %VIDEO.SETUPTIME%, %VIDEO.DESCRIBETIME%, %VIDEO.PLAYTIME%, %VIDEO.TCPRXPOOO%, %VIDEO.TCPRXBOOO% , %VIDEO.TCPRXPOW%, %VIDEO.TCPRXBOW%, %VIDEO.TCPRXDOP%, %VIDEO.TCPRXDOB%, %VIDEO.TCPRXPDP%, %VIDEO.TCPRXPDB%, %VIDEO.TCPRXCRCERR%, %VIDEO.TCPRXBOFF%, %VIDEO.TCPRXTS%, %VIDEO.TCPRXPROBE%, %VIDEO.TCPRXWINZEROUP%, %VIDEO.TCPBYTESLOST%, %VIDEO.TCPTXRTXTMO%, %VIDEO.TCPTXRTXFAST%, %VIDEO.TCPTXPRETX%, %VIDEO.TCPTXBRETX%, %VIDEO.TCPTXSNDWINCLOSE%, %VIDEO.TCPTXRCVWINUPD%, %VIDEO.TCPTXRCVACKTOOMUCH%, %VIDEO.TCPTXRCVDUPACK%, %VIDEO.TCPTXSNDPROBE%, %VIDEO.TCPTXPERSISTTIMEO%, %VIDEO.ETHRXFTL%, %VIDEO.ETHRXNOAL%, %VIDEO.ETHRXSF%, %VIDEO.ETHRXCRCERR%, %VIDEO.ETHRXOVR%, %VIDEO.ETHRXTRUNC%, '%VIDEO.INTERFACE%', %VIDEO.TCPMTU%);

[iptv] INSERT INTO myiptv (recordid, testid, jitter1, loss1, order1, jitter2, loss2, order2, jitter3, loss3, order3, rttmin, rttavg, rttmax) VALUES (%RECORDID%, %TESTID%, %IPTV.JITTER1%, %IPTV.LOSS1%, %IPTV.ORDER1%, %IPTV.JITTER2%, %IPTV.LOSS2%, %IPTV.ORDER2%, %IPTV.JITTER3%, %IPTV.LOSS3%, %IPTV.ORDER3%, %IPTV.RTTMIN%, %IPTV.RTTAVG%, %IPTV.RTTMAX%);

[route] INSERT INTO myroute (recordid, testid, enteredname, hops, endms, maxms, endloss, maxloss, dnstime, ipto, ipfrom, nameto) VALUES (%RECORDID%, %TESTID%, '%ROUTE.ENTEREDNAME%', %ROUTE.HOPS%, %ROUTE.ENDMS%, %ROUTE.MAXMS%, %ROUTE.ENDLOSS%, %ROUTE.MAXLOSS%, %ROUTE.DNSTIME%, '%ROUTE.IPTO%', '%ROUTE.IPFROM%', '%ROUTE.NAMETO%');

[capacity] INSERT INTO mycapacity (recordid, testid, dcapacity, ucapacity, dpackets, upackets, packetsize, qos) VALUES (%RECORDID%, %TESTID%, %CAPACITY.DCAPACITY%, %CAPACITY.UCAPACITY%, %CAPACITY.DPACKETS%, %CAPACITY.UPACKETS%, %CAPACITY.PACKETSIZE%, %CAPACITY.QOS%);

[act] INSERT INTO accessct (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%);

[conference] INSERT INTO myconference (recordid, testid, dlatency, slatency, avgres, pass, fail, jitter1, jittermax1, loss1, dloss1, discard1, jitter2, jittermax2, loss2, dloss2, discard2, jitter3, jittermax3, loss3, dloss3, discard3, rttmin, rttavg, rttmax, rttconsistency) VALUES (%RECORDID%, %TESTID%, %CONFERENCE.DLATENCY%, %CONFERENCE.SLATENCY%, %CONFERENCE.AVGRESPONSE%, %CONFERENCE.PASS%, %CONFERENCE.FAIL%, %CONFERENCE.JITTER1%, %CONFERENCE.JITTERMAX1%, %CONFERENCE.LOSS1%, %CONFERENCE.DLOSS1%, %CONFERENCE.DISCARDS1%, %CONFERENCE.JITTER2%, %CONFERENCE.JITTERMAX2%, %CONFERENCE.LOSS2%, %CONFERENCE.DLOSS2%, %CONFERENCE.DISCARDS2%, %CONFERENCE.JITTER3%, %CONFERENCE.JITTERMAX3%, %CONFERENCE.LOSS3%, %CONFERENCE.DLOSS3%, %CONFERENCE.DISCARDS3%, %CONFERENCE.RTTMIN%, %CONFERENCE.RTTAVG%, %CONFERENCE.RTTMAX%, %CONFERENCE.RTTCONSISTENCY%);

Monitoring Plugins:

[dnsns] INSERT INTO dns (recordid, testid, qname, latency, failure, latency2, failure2, latency3, failure3, dnsresult, dnspacket, rttmin, rttavg, rttmax, dnsresult2, dnspacket2, rttmin2, rttavg2, rttmax2, dnsresult3, dnspacket3, rttmin3, rttavg3, rttmax3) VALUES (%RECORDID%, %TESTID%, '%DNSNS.QNAME%', %DNSNS.LATENCY%, %DNSNS.FAILURE%, %DNSNS.LATENCY2%, %DNSNS.FAILURE2%, %DNSNS.LATENCY3%, %DNSNS.FAILURE3%, '%DNSNS.NSRSLT%', '%DNSNS.NSPCKT%', %DNSNS.RTTMIN%, %DNSNS.RTTAVG%, %DNSNS.RTTMAX%, '%DNSNS.NSRSLT2%', '%DNSNS.NSPCKT2%', %DNSNS.RTTMIN2%, %DNSNS.RTTAVG2%, %DNSNS.RTTMAX2%, '%DNSNS.NSRSLT3%', '%DNSNS.NSPCKT3%', %DNSNS.RTTMIN3%, %DNSNS.RTTAVG3%, %DNSNS.RTTMAX3%);

[ftp] INSERT INTO ftp (recordid, testid, latency, failure, getput, kbread, kbwrite, kbsecread, kbsecwrite, rttmin, rttavg, rttmax) VALUES (%RECORDID%, %TESTID%, %FTP.LATENCY%, %FTP.FAILURE%, '%FTP.GETPUT%', %FTP.KBREAD%, %FTP.KBWRITE%, %FTP.KBSECREAD%, %FTP.KBSECWRITE%, %FTP.RTTMIN%, %FTP.RTTAVG%, %FTP.RTTMAX%);

[ping] INSERT INTO ping (recordid, testid, avglat, minlat, maxlat, loss) VALUES (%RECORDID%, %TESTID%, %PING.AVGLATENCY%, %PING.MINLATENCY%, %PING.MAXLATENCY%, %PING.LOSS%);

[port] INSERT INTO port (recordid, testid, avglat, failure, minlat, port, maxlat, opened, failed, refused, timedout) VALUES (%RECORDID%, %TESTID%, %PORT.AVGLATENCY%, %PORT.FAILURE%, %PORT.MINLATENCY%, '%PORT.PORT%', %PORT.MAXLATENCY%, %PORT.OPENED%, %PORT.FAILED%, %PORT.REFUSED%, %PORT.TIMEDOUT%);

[router] INSERT INTO router (recordid, testid, latency, failure, ipios, icmpios, tcpios, udpios, snmpios, totalios, globalutilin, globaloctin, globalutilout, globaloctout, portutilin, portoctin, portutilout, portoctout, rttmin, rttavg, rttmax) VALUES (%RECORDID%, %TESTID%, %ROUTER.LATENCY%, %ROUTER.FAILURE%, %ROUTER.IPIOS%, %ROUTER.ICMPIOS%, %ROUTER.TCPIOS%, %ROUTER.UDPIOS%, %ROUTER.SNMPIOS%, %ROUTER.TOTALIOS%, %ROUTER.GLOBALUTILIN%, %ROUTER.GLOBALOCTIN%, %ROUTER.GLOBALUTILOUT%, %ROUTER.GLOBALOCTOUT%, %ROUTER.PORTUTILIN%, %ROUTER.PORTOCTIN%, %ROUTER.PORTUTILOUT%, %ROUTER.PORTOCTOUT%, %ROUTER.RTTMIN%, %ROUTER.RTTAVG%, %ROUTER.RTTMAX%);

[sip] INSERT INTO sip (recordid, testid, rlatency, olatency, ilatency, clatency, loss, rttmin, rttavg, rttmax) VALUES (%RECORDID%, %TESTID%, %SIP.RLATENCY%, %SIP.OLATENCY%, %SIP.ILATENCY%, %SIP.CLATENCY%, %SIP.LOSS%, %SIP.RTTMIN%, %SIP.RTTAVG%, %SIP.RTTMAX%);

[snmp] INSERT INTO snmp (recordid, testid, latency, failure) VALUES (%RECORDID%, %TESTID%, %SNMP.LATENCY%, %SNMP.FAILURE%);

[web] INSERT INTO web (recordid, testid, url, latencyms, failurems, connectms, findms, readms, pagesize, kbsec) VALUES (%RECORDID%, %TESTID%, '%WEB.URL%', %WEB.LATENCY%, %WEB.FAILURE%, %WEB.CONNECT%, %WEB.FIND%, %WEB.READ%, %WEB.PAGESIZE%, %WEB.KBSEC%);

 

MyConnection Server

Home
Online Testing Portal
Download
Purchase
Resources
Support

Visualware Products

VisualRoute
eMailTrackerPro
Visual IP Trace

   

© Visualware Inc. 2017 - All Rights Reserved