== Analyzing Results == [[TOC(Tutorials/a0Basic*)]] It is important to understand how measurements were collected and organized to be able to interpret them. The [wiki:Documentation/OML ORBIT Measurement Framework] provides tools to insert points to tap available information and to effectively collect that information in a timely manner. The [wiki:Software/dOML/CollectingMeasurements previous part] of this tutorial presented information about defining and setting up this information collection points. The collected information and measurements are then stored in a [http://www.mysql.com SQLite] database. After the experiment completion, the user have access to the generated experiment database. In general, the results of a given experiment are stored in a single database (users can use more than one databases if required). Different participating nodes populate different tables of this generated database. Usually, user would like to post-process or visualize those raw measurements for further analysis. A number of different tools are available to interpret experimental results. The choice of tools depends upon their availability on your platform, the nature of the measurements, and your own preferences. Excel and Matlab connections from machines outside Winlab to our database server are blocked by a firewall. We are working on a system to safely and securely export databases using these tools outside Winlab. Until then, please use any of the other following approaches to retrieve your data. The user will need to know the Experiment ID from a successfully executed OMF script which utilized and OML-enabled application - for example iperf-oml2. The Experiment Id is a long string prefix with ''default_slice'' followed by date and time stamp. An example Experiment ID is ''default_slice-2014-10-20t10.59.11.720-04.00''. === Using Result Service === The console has a Result Service running on port 5054. For example on the grid console the service can be accessed at the URL: ''grid.orbit-lab.org:5054/result''. From the console the user can use a non-interactve network downloader (ie. wget) to retrieve results. A web browser (ie. Chrome) on a different machine can also be used however tunneling through the console is required. To view all the services provided use command: {{{ nilanjan@console.grid:~$ wget -qO- grid.orbit-lab.org:5054/result | xml_pp }}} The services will be displayed in XML format with a brief description between the ''info'' tags and required argument(s). {{{ Service to access and query experiment measurement databases Dump the complete database holding the measurement results for a given experiment ID of the Experiment Get the Schema of a given experiment measurement database ID of the Experiment Get the list of tables in given experiment measurement database ID of the Experiment Get the Schema of a given experiment measurement database ID of the Experiment An SQLite query to run against the database Format to return result in. }}} Listed below are a few example uses of the services for a given Experiment ID (''default_slice-2014-10-20t10.59.11.720-04.00''). Assume all commands are case-sensitive. 1) Dump the contents of the database in sqlite3 text format: {{{ nilanjan@console.grid:~$ wget -qO- grid.orbit-lab.org:5054/result/dumpDatabase?expID=default_slice-2014-10-20t10.59.11.720-04.00 }}} 2) Print all tables in the contained in the database in XML format: {{{ nilanjan@console.grid:~$ wget -qO- grid.orbit-lab.org:5054/result/listTables?expID=default_slice-2014-10-20t10.59.11.720-04.00 | xml_pp _experiment_metadata
iperf_connection
iperf_packets
_senders
iperf_jitter
iperf_settings
iperf_application
iperf_losses
iperf_transfer
 }}} 3) Run a query on a database table and display contents in XML format: {{{ nilanjan@console.grid:~$ wget -qO- grid.orbit-lab.org:5054/result/queryDatabase?expID=default_slice-2014-10-20t10.59.11.720-04.00\&format=xml\&query="select * from iperf_transfer" | xml_pp select * from iperf_transfer oml_tuple_id oml_sender_id oml_seq oml_ts_client oml_ts_server pid connection_id begin_interval end_interval size 1 1 1 5.741186998784542 5.747786 1511 6 0.0 5.0 11534336 2 1 2 10.765060991048813 10.771641 1511 6 5.0 10.0 12058624 3 1 3 15.74006099998951 15.746616 1511 6 10.0 15.0 12189696 4 1 4 20.73482397198677 20.741354 1511 6 15.0 20.0 12713984 : : : 48 1 24 181.04624199867249 181.170476 1535 7 115.0 120.0 12320768 }}} === Using Direct Database Access === The sqlite database is store in /var/lib/oml2 of the console. Use the command line front-end tool (sqlite3) to query the database. For a detailed overview on sqlite3 CLI please refer [http://www.sqlite.org/cli.html http://www.sqlite.org/cli.html]. To get started a few examples are shown below using sqlite3 command line options from the console: Use the following command to view the entire database contents. This dumps out all the information in sqlite3 text format. A truncated version of the output is shown. {{{ nilanjan@console.grid:~/EXP$ sqlite3 /var/lib/oml2/default_slice-2014-10-20t10.59.11.720-04.00.sq3 ".dump" PRAGMA foreign_keys=OFF; BEGIN TRANSACTION; CREATE TABLE _senders (name TEXT PRIMARY KEY, id INTEGER UNIQUE); INSERT INTO "_senders" VALUES('default_slice-2014-10-20t10.25.22.955-04.00',1); CREATE TABLE "_experiment_metadata" (oml_tuple_id INTEGER PRIMARY KEY, oml_sender_id INTEGER, oml_seq INTEGER, oml_ts_client REAL, oml_ts_server REAL, "subject" TEXT, "key" TEXT, "value" TEXT); INSERT INTO "_experiment_metadata" VALUES(1,NULL,NULL,NULL,NULL,NULL,'table__experiment_metadata','0 _experiment_metadata subject:string key:string value:string'); INSERT INTO "_experiment_metadata" VALUES(2,NULL,NULL,NULL,NULL,NULL,'start_time','1413815140'); INSERT INTO "_experiment_metadata" VALUES(3,NULL,NULL,NULL,NULL,NULL,'table_iperf_packets','1 iperf_packets pid:int32 connection_id:int32 packet_id:int32 packet_size:int32 packet_time_s:int32 packet_time_us:int32 packet_sent_time_s:int32 packet_sent_time_us:int32'); INSERT INTO "_experiment_metadata" VALUES(4,NULL,NULL,NULL,NULL,NULL,'table_iperf_jitter','2 iperf_jitter pid:int32 connection_id:int32 begin_interval:double end_interval:double jitter:double'); INSERT INTO "_experiment_metadata" VALUES(5,NULL,NULL,NULL,NULL,NULL,'table_iperf_losses','3 iperf_losses pid:int32 connection_id:int32 begin_interval:double end_interval:double total_datagrams:int32 lost_datagrams:int32'); INSERT INTO "_experiment_metadata" VALUES(6,NULL,NULL,NULL,NULL,NULL,'table_iperf_transfer','4 iperf_transfer pid:int32 connection_id:int32 begin_interval:double end_interval:double size:uint64'); INSERT INTO "_experiment_metadata" VALUES(7,NULL,NULL,NULL,NULL,NULL,'table_iperf_connection','5 iperf_connection pid:int32 connection_id:int32 local_address:string local_port:int32 remote_address:string remote_port:int32'); INSERT INTO "_experiment_metadata" VALUES(8,NULL,NULL,NULL,NULL,NULL,'table_iperf_settings','6 iperf_settings pid:int32 server_mode:int32 bind_address:string multicast:int32 multicast_ttl:int32 transport_protocol:int32 window_size:int32 buffer_size:int32'); INSERT INTO "_experiment_metadata" VALUES(9,NULL,NULL,NULL,NULL,NULL,'table_iperf_application','7 iperf_application pid:int32 version:string cmdline:string starttime_s:int32 starttime_us:int32'); CREATE TABLE "iperf_packets" (oml_tuple_id INTEGER PRIMARY KEY, oml_sender_id INTEGER, oml_seq INTEGER, oml_ts_client REAL, oml_ts_server REAL, "pid" INTEGER, "connection_id" INTEGER, "packet_id" INTEGER, "packet_size" INTEGER, "packet_time_s" INTEGER, "packet_time_us" INTEGER, "packet_sent_time_s" INTEGER, "packet_sent_time_us" INTEGER); INSERT INTO "iperf_packets" VALUES(1,1,1,0.411181999836117,0.415859,1236,6,0,131072,1413815140,411180,-1243478864,0); INSERT INTO "iperf_packets" VALUES(2,1,2,0.474676999729127,0.479372,1236,6,0,131072,1413815140,474676,-1243478864,0); INSERT INTO "iperf_packets" VALUES(3,1,3,0.538958999328315,0.543652,1236,6,0,131072,1413815140,538958,-1243478864,0); : : : INSERT INTO "iperf_packets" VALUES(3429,1,1722,184.396865963936,184.390128,1261,6,0,131072,1413815324,396865,-1243478864,0); CREATE TABLE "iperf_jitter" (oml_tuple_id INTEGER PRIMARY KEY, oml_sender_id INTEGER, oml_seq INTEGER, oml_ts_client REAL, oml_ts_server REAL, "pid" INTEGER, "connection_id" INTEGER, "begin_interval" REAL, "end_interval" REAL, "jitter" REAL); CREATE TABLE "iperf_losses" (oml_tuple_id INTEGER PRIMARY KEY, oml_sender_id INTEGER, oml_seq INTEGER, oml_ts_client REAL, oml_ts_server REAL, "pid" INTEGER, "connection_id" INTEGER, "begin_interval" REAL, "end_interval" REAL, "total_datagrams" INTEGER, "lost_datagrams" INTEGER); CREATE TABLE "iperf_transfer" (oml_tuple_id INTEGER PRIMARY KEY, oml_sender_id INTEGER, oml_seq INTEGER, oml_ts_client REAL, oml_ts_server REAL, "pid" INTEGER, "connection_id" INTEGER, "begin_interval" REAL, "end_interval" REAL, "size" UNSIGNED BIGINT); INSERT INTO "iperf_transfer" VALUES(1,1,1,5.37069699913263,5.375371,1236,6,0.0,5.0,12451840); INSERT INTO "iperf_transfer" VALUES(2,1,2,10.3784499913454,10.383083,1236,6,5.0,10.0,12713984); : : : INSERT INTO "iperf_transfer" VALUES(47,1,23,175.713173985481,175.706422,1261,6,110.0,115.0,12976128); INSERT INTO "iperf_transfer" VALUES(48,1,24,180.751036882401,180.744307,1261,6,115.0,120.0,12713984); CREATE TABLE "iperf_connection" (oml_tuple_id INTEGER PRIMARY KEY, oml_sender_id INTEGER, oml_seq INTEGER, oml_ts_client REAL, oml_ts_server REAL, "pid" INTEGER, "connection_id" INTEGER, "local_address" TEXT, "local_port" INTEGER, "remote_address" TEXT, "remote_port" INTEGER); INSERT INTO "iperf_connection" VALUES(1,1,1,0.324951999820769,0.33002,1236,6,'192.168.0.1',57694,'192.168.0.254',5001); INSERT INTO "iperf_connection" VALUES(2,1,1,60.7023439994082,60.695348,1261,6,'192.168.0.2',49302,'192.168.0.254',5001); CREATE TABLE "iperf_settings" (oml_tuple_id INTEGER PRIMARY KEY, oml_sender_id INTEGER, oml_seq INTEGER, oml_ts_client REAL, oml_ts_server REAL, "pid" INTEGER, "server_mode" INTEGER, "bind_address" TEXT, "multicast" INTEGER, "multicast_ttl" INTEGER, "transport_protocol" INTEGER, "window_size" INTEGER, "buffer_size" INTEGER); INSERT INTO "iperf_settings" VALUES(1,1,1,0.324930999893695,0.32958,1236,1,'0.0.0.0',0,0,6,21480,131072); INSERT INTO "iperf_settings" VALUES(2,1,1,60.7023199992254,60.695187,1261,1,'0.0.0.0',0,0,6,21480,131072); CREATE TABLE "iperf_application" (oml_tuple_id INTEGER PRIMARY KEY, oml_sender_id INTEGER, oml_seq INTEGER, oml_ts_client REAL, oml_ts_server REAL, "pid" INTEGER, "version" TEXT, "cmdline" TEXT, "starttime_s" INTEGER, "starttime_us" INTEGER); INSERT INTO "iperf_application" VALUES(1,1,1,0.320253999903798,0.329126,1236,'2.0.5+oml2.9.pre0','/usr/bin/iperf-oml2 -c 192.168.0.254 -i 5 -y O -t 99999',1413815140,320246); INSERT INTO "iperf_application" VALUES(2,1,1,60.3850499996915,60.378299,1261,'2.0.5+oml2.9.pre0','/usr/bin/iperf-oml2 -c 192.168.0.254 -i 5 -y O -t 99999',1413815200,385046); COMMIT; }}} To list all the the tables in the database: {{{ nilanjan@console.grid:~/EXP$ sqlite3 /var/lib/oml2/default_slice-2014-10-20t10.25.22.955-04.00.sq3 ".tables" _experiment_metadata iperf_connection iperf_packets _senders iperf_jitter iperf_settings iperf_application iperf_losses iperf_transfer }}} Let's view the contents in the iperf_transfer table: {{{ nilanjan@console.grid:~/EXP$ sqlite3 -header -column /var/lib/oml2/default_slice-2014-10-20t10.25.22.955-04.00.sq3 "select * from iperf_transfer" oml_tuple_id oml_sender_id oml_seq oml_ts_client oml_ts_server pid connection_id begin_interval end_interval size ------------ ------------- ---------- ---------------- ------------- ---------- ------------- -------------- ------------ ---------- 1 1 1 5.37069699913263 5.375371 1236 6 0.0 5.0 12451840 2 1 2 10.3784499913454 10.383083 1236 6 5.0 10.0 12713984 3 1 3 15.3470119982958 15.3516 1236 6 10.0 15.0 12320768 4 1 4 20.3752109706402 20.379763 1236 6 15.0 20.0 12845056 5 1 5 25.3491049706936 25.353631 1236 6 20.0 25.0 12582912 6 1 6 30.3527579903603 30.35724 1236 6 25.0 30.0 12320768 7 1 7 35.3587439656258 35.363198 1236 6 30.0 35.0 12713984 8 1 8 40.3642519712448 40.368689 1236 6 35.0 40.0 12582912 9 1 9 45.3395289778709 45.343928 1236 6 40.0 45.0 11665408 10 1 10 50.3661079406738 50.370467 1236 6 45.0 50.0 11534336 11 1 11 55.3729719519615 55.377313 1236 6 50.0 55.0 11534336 12 1 12 60.3800199627876 60.384375 1236 6 55.0 60.0 11403264 13 1 13 65.3507549762726 65.355045 1236 6 60.0 65.0 8126464 14 1 1 65.7505009993911 65.743343 1261 6 0.0 5.0 3538944 15 1 14 70.3302088975906 70.33448 1236 6 65.0 70.0 5898240 16 1 2 70.7465729862452 70.739399 1261 6 5.0 10.0 5898240 17 1 15 75.3316609859467 75.33591 1236 6 70.0 75.0 5767168 18 1 3 75.8462129980326 75.83902 1261 6 10.0 15.0 5898240 19 1 16 80.4429728984833 80.447197 1236 6 75.0 80.0 5898240 20 1 4 80.8001589775085 80.792952 1261 6 15.0 20.0 5767168 21 1 17 85.4174538850784 85.421657 1236 6 80.0 85.0 5767168 22 1 5 85.7791629731655 85.771938 1261 6 20.0 25.0 5767168 23 1 18 90.3622509241104 90.366441 1236 6 85.0 90.0 5898240 24 1 6 90.7223399877548 90.715142 1261 6 25.0 30.0 5898240 25 1 19 95.3966519832611 95.400828 1236 6 90.0 95.0 5898240 26 1 7 95.8051519989967 95.797987 1261 6 30.0 35.0 5898240 27 1 20 100.39771091938 100.4019 1236 6 95.0 100.0 5767168 28 1 8 100.76890796423 100.761771 1261 6 35.0 40.0 5767168 29 1 21 105.401404976845 105.405547 1236 6 100.0 105.0 5767168 30 1 9 105.723350942135 105.716245 1261 6 40.0 45.0 5636096 31 1 22 110.330290913582 110.334424 1236 6 105.0 110.0 5767168 32 1 10 110.759348988533 110.752272 1261 6 45.0 50.0 6029312 33 1 23 115.358618974686 115.362742 1236 6 110.0 115.0 6029312 34 1 11 115.765810966492 115.758763 1261 6 50.0 55.0 5898240 35 1 24 120.341752886772 120.345869 1236 6 115.0 120.0 5767168 36 1 12 120.729885995388 120.722867 1261 6 55.0 60.0 5767168 37 1 13 125.745458960533 125.738468 1261 6 60.0 65.0 7340032 38 1 14 130.762330889702 130.755367 1261 6 65.0 70.0 12976128 39 1 15 135.714812994003 135.707874 1261 6 70.0 75.0 12451840 40 1 16 140.713323950768 140.706414 1261 6 75.0 80.0 12582912 41 1 17 145.724961996079 145.718074 1261 6 80.0 85.0 12845056 42 1 18 150.709739923477 150.702877 1261 6 85.0 90.0 13238272 43 1 19 155.721814990044 155.714976 1261 6 90.0 95.0 12976128 44 1 20 160.733428955078 160.726618 1261 6 95.0 100.0 12845056 45 1 21 165.726515889168 165.719722 1261 6 100.0 105.0 12713984 46 1 22 170.711465954781 170.704687 1261 6 105.0 110.0 12976128 47 1 23 175.713173985481 175.706422 1261 6 110.0 115.0 12976128 48 1 24 180.751036882401 180.744307 1261 6 115.0 120.0 12713984 }}} === Microsoft Excel Example === Alternatively Excel Sheet can be used to fetch data associated with an Experiment ID. ResultServiceVBA.xlsm (under the attachment) is an example to access experiment data previously run on the grid console. To retrieve data using this file: 1) Before running the file use Putty to set up a tunnel from ''localhost:6064'' to ''grid.orbit-lab.org:5054''. 2) Once the tunnel is set up, run the ResultServiceVBA file and enable any macros/ActiveX controls when prompted. 3) In cell B1 (under Sheet1) copy and paste your Experiment ID, then click on ''Fetch'' to retrieve the data. ''Sheet1'' will contain the ''List of Tables'' that are available. A new sheet will be created for each table and populated with available data. [[Image(Tutorials/a0Basic:ResultServiceVBA_Sheet1)]] [[Image(Tutorials/a0Basic:ResultServiceVBA_IperfTransfer)]] This excel file was run using Microsoft Office 2010 on Windows 7 64-bit. A successful execution of this file may be affected by several factors - Window Operating System, Microsoft Office version, Windows security updates, permission level on User Account, ActiveX controls configuration/updates, etc... === Matlab Example ===