MySQL/Oracle, DNS and HTTP Plugins


Generic packet header-based traffic monitoring is no longer enough. Network administrators need to pin-point problems, understand bottlenecks but in particular to know exactly what is the cause of a certain problem. For this reason it is now necessary to inspect specific protocols in order to understand what’s happened. nProbe™ currently features HTTP, Oracle and MySQL that in addition to exporting information via NetFlow, it also allows administrators to create log of activities that can help undertstanding what’s really happening on the network.

NetFlow Export


Information can be both exported via NetFlow/IPFIX

Plugin HTTP Protocol Dissector templates:
[NFv9 57652][IPFIX 35632.180] %HTTP_URL HTTP URL
[NFv9 57653][IPFIX 35632.181] %HTTP_RET_CODE HTTP return code (e.g. 200, 304…)
[NFv9 57654][IPFIX 35632.182] %HTTP_REFERER HTTP Referer
[NFv9 57655][IPFIX 35632.183] %HTTP_UA HTTP User Agent
[NFv9 57656][IPFIX 35632.184] %HTTP_MIME HTTP Mime Type
[NFv9 57657][IPFIX 35632.185] %HTTP_HOST HTTP Host Name

Plugin MySQL Plugin templates:
[NFv9 57667][IPFIX 35632.195] %MYSQL_SERVER_VERSION MySQL server version
[NFv9 57668][IPFIX 35632.196] %MYSQL_USERNAME MySQL username
[NFv9 57669][IPFIX 35632.197] %MYSQL_DB MySQL database in use
[NFv9 57670][IPFIX 35632.198] %MYSQL_QUERY MySQL Query
[NFv9 57671][IPFIX 35632.199] %MYSQL_RESPONSE MySQL server response

Plugin Oracle Protocol Dissector templates:
[NFv9 57672][IPFIX 35632.200] %ORACLE_USERNAME Oracle Username
[NFv9 57673][IPFIX 35632.201] %ORACLE_QUERY Oracle Query
[NFv9 57674][IPFIX 35632.202] %ORACLE_RSP_CODE Oracle Response Code
[NFv9 57675][IPFIX 35632.203] %ORACLE_RSP_STRING Oracle Response String
[NFv9 57676][IPFIX 35632.204] %ORACLE_QUERY_DURATION Oracle Query Duration (msec)

Plugin DNS Protocol Dissector templates:
[NFv9 57677][IPFIX 35632.205] %DNS_QUERY DNS query
[NFv9 57678][IPFIX 35632.206] %DNS_QUERY_ID DNS query transaction Id
[NFv9 57679][IPFIX 35632.207] %DNS_QUERY_TYPE DNS query type (e.g. 1=A, 2=NS..)
[NFv9 57680][IPFIX 35632.208] %DNS_RET_CODE DNS return code (e.g. 0=no error)
[NFv9 57681][IPFIX 35632.209] %DNS_NUM_ANSWER DNS # of returned answers

 

File Export


The same above information can also be dumped on files in text format (see below for examples)

[DNS Protocol Dissector]
–dns-dump-dir | Directory where DNS logs will be dumped

[HTTP Protocol Dissector]
–http-dump-dir | Directory where HTTP logs will be dumped
–http-exec-cmd | Command executed whenever a directory has been dumped
–dont-hash-cookies | Dump cookie string instead of cookie hash
–dont-nest-dump-dirs | Don’t create subdirs on the dump directory
–max-http-log-lines | Max number of lines per log file (default 10000)
[MySQL Plugin]
–mysql-dump-dir | Directory where MySQL logs will be dumped
–mysql-exec-cmd | Command executed whenever a directory has been dumped
–max-mysql-log-lines | Max number of lines per log file (default 10000)

[Oracle Protocol Dissector]
–oracle-dump-dir | Directory where Oracle logs will be dumped
–oracle-exec-cmd | Command executed whenever a directory has been dumped
–dont-nest-dump-dirs | Don’t create subdirs on the dump directory
–max-oracle-log-lines | Max number of lines per log file (default 10000)

that can be used for further processing.

#
# Client Server Protocol Method URL HTTPReturnCode Location Referer UserAgent ContentType Bytes BeginTime EndTime Flow Hash Cookie Terminator ApplLatency(ms) ClientLatency(ms) ServerLatency(ms) Application BalancerHostServerIP RetransmittedPkts
#
192.168.0.200 elyrics.net http GET /go/f/Franco-Battiato-lyrics/Povera-Patria-lyrics/ 302 www.elyrics.net/inc/404.html curl/7.13.1 (powerpc-apple-darwin8.0) libcurl/7.13.1 OpenSSL/0.9.7i zlib/1.2.3 1186 1133966832.527 1133966832.908 2413138730 0 S 114 0.079 51.337 Unknown 207.44.206.43 0
192.168.0.200 api.leoslyrics.com http GET /api_search.php?auth=mindquirk_harmonic&artist=Franco+Battiato&songtitle=Povera+Patria 200 curl/7.13.1 (powerpc-apple-darwin8.0) libcurl/7.13.1 OpenSSL/0.9.7i zlib/1.2.3 10244 1133966831.996 1133966832.910 2423982224 0 C 152 0.079 56.678 Unknown 207.210.67.146 0
192.168.0.200 www.macintouch.com http GET /images/filewave01.gif 200 www.macintouch.com Mozilla/5.0 (Macintosh; U; PPC Mac OS X; en) AppleWebKit/416.12 (KHTML, like Gecko) Safari/416.13 27750 1133966828.928 1133966830.606 26992029 0 S 261 0.080 114.095 Unknown 64.243.24.160 0
192.168.0.200 www.macintouch.com http GET /images/iwas01b.gif 200 www.macintouch.com Mozilla/5.0 (Macintosh; U; PPC Mac OS X; en) AppleWebKit/416.12 (KHTML, like Gecko) Safari/416.13 12469 1133966828.574 1133966829.932 26992028 0 S 369 0.080 69.586 Unknown 64.243.24.160 0
192.168.0.200 www.macintouch.com http GET /images/filewave02.gif 200 www.macintouch.com Mozilla/5.0 (Macintosh; U; PPC Mac OS X; en) AppleWebKit/416.12 (KHTML, like Gecko) Safari/416.13 25505 1133966827.681 1133966829.196 26992027 0 S 387 0.141 69.580 Unknown 64.243.24.160 0
192.168.0.200 www.macintouch.com http GET / 200 Mozilla/5.0 (Macintosh; U; PPC Mac OS X; en) AppleWebKit/416.12 (KHTML, like Gecko) Safari/416.13 52474 1133966827.127 1133966829.339 26992026 0 S 308 0.093 85.949 Unknown 64.243.24.160 0
deri


HTTP Logging
 

#
# Client Server User Query ResponseCode ResponseMsg Bytes BeginTime EndTime QueryDuration(sec) ClientLatency(ms) ServerLatency(ms)
#
10.6.6.10 10.2.6.1 SELECT t0.JDOID, t0.JDOCLASS, t0.JDOVERSION, t0.AGGEXCHANGERATEVERSION, t0.CURRENCYCODE, t0.DECIMALPLACES, t0.IDXCURRENCY_KEYFORCURRENCY, t0.INTNUMBER, t0.NAME0, t0.PARTECIPATEEURO, t0.BEFORELASTCHANGEDATE, t0.BEFORELASTC 1403 no data found 5911 1333555046.896 1333555046.905 0.000 0.100 0.020
10.6.6.10 10.2.6.1 SELECT t0.JDOID, t0.JDOCLASS, t0.JDOVERSION, t0.AGGQUANTVERSION, t0.ASSPURCHASEORDERPROPOSALOPENVE, t0.ASSPURCHASEORDERLINEOPENVERSIO, t0.AGGDEMANDVERSION, t0.AGGLIFOCOSTPRICEVERSION, t0.ABCACCESS, t0.ABCGROSSPROFIT, t0. 0 4209 1333555087.140 1333555087.144 0.001 0.120 0.020
10.6.6.10 10.2.6.1 SELECT t0.JDOID, t0.JDOCLASS, t0.JDOVERSION, t0.ASSSTOCKTAKINGORDERVERSION, t0.AGGABSTRACTLOCATIONVERSION, t0.AGGWAREHOUSEPROTOCOLVERSION, t0.ALLOWAUTOMATICSTOCKADJUSTMENT, t0.ASSBUSINESSADDRESS_JDOID, t0.ASSPACKAGETYPEDE 0 4227 1333555087.144 1333555087.148 0.101 0.000 0.020
10.6.6.10 10.2.6.1 SELECT t0.KEY0, t1.JDOID, t1.JDOCLASS, t1.JDOVERSION, t1.AGGQUANTVERSION, t1.ASSPURCHASEORDERPROPOSALOPENVE, t1.ASSPURCHASEORDERLINEOPENVERSIO, t1.AGGDEMANDVERSION, t1.AGGLIFOCOSTPRICEVERSION, t1.ABCACCESS, t1.ABCGROSSPR 0 4300 1333555087.148 1333555087.151 0.101 0.000 0.020


Oracle Logging
 

#
# Client Server User Database Query ResponseCode Bytes BeginTime EndTime
#
192.168.0.254 192.168.0.254 tfoerste select @@version_comment limit 1 0 802 1216281025 1216281025
192.168.0.254 192.168.0.254 tfoerste SELECT DATABASE() 0 390 1216281025 1216281030
192.168.0.254 192.168.0.254 tfoerste test use database test 0 292 1216281025 1216281030
192.168.0.254 192.168.0.254 tfoerste test show databases 0 294 1216281025 1216281030
192.168.0.254 192.168.0.254 tfoerste test show tables 0 374 1216281025 1216281030
192.168.0.254 192.168.0.254 tfoerste test create table foo (id BIGINT( 10 ) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, animal VARCHAR(64) NOT NULL, name VARCHAR(64) NU 0 843 1216281025 1216281048
192.168.0.254 192.168.0.254 tfoerste test insert into foo (animal, name) values (“dog”, “Goofy”) 0 289 1216281025 1216281057
192.168.0.254 192.168.0.254 tfoerste test insert into foo (animal, name) values (“cat”, “Garfield”) 0 292 1216281025 1216281061
192.168.0.254 192.168.0.254 tfoerste test select * from foo 0 431 1216281025 1216281066
192.168.0.254 192.168.0.254 tfoerste test delete from foo where name like ‘%oo%’ 0 452 1216281025 1216281072
192.168.0.254 192.168.0.254 tfoerste test delete from foo where id = 1 0 263 1216281025 1216281079
192.168.0.254 192.168.0.254 tfoerste test select count(*) from foo 0 311 1216281025 1216281087
192.168.0.254 192.168.0.254 tfoerste test select * from foo 0 467 1216281025 1216281109
192.168.0.254 192.168.0.254 tfoerste test delete from foo 0 413 1216281025 1216281116
192.168.0.254 192.168.0.254 tfoerste test drop table foo 0 249 1216281025 1216281122


MySQL Logging

#
# When|DNS_Client|AS|ClientCountry|ClientCity|DNS_Server|Query|NumRetCode|RetCode|NumAnswer|NumQueryType|QueryType|TransactionId|Answers|AuthNSs|Cli2SrvTTL|Srv2CliTTL|NumQueryPkts|NumReplyPkts|ServerResponseTime(ms)
#
1337504414.408|193.13.117.34|13036|CZ||12.12.192.5|www.xxxxxx.it|0|NOERROR|0|1|A|58846||ns01.xxxxxx.org;ns03.xxxxx.net;ns02.xxxxx.com|50|64|1|1|0.294
1337504414.405|164.40.112.55|14280|CA||12.12.192.5|dns2.xxxxx.it|0|NOERROR|0|28|AAAA|16705||dns2.xxxxx.it;dns1.xxxxxx.it|47|64|1|1|0.341


DNS Logging