No Country Data in Squert - ip2c.tcl Fails

154 views
Skip to first unread message

Patrick Schilling

unread,
Jan 10, 2017, 6:01:00 PM1/10/17
to security-onion
I have two separate brand new Security Onion builds using the latest version and both are failing to show country data in Squert. After looking through a bunch of previous posts this usually seems to be caused by ip2c.tcl issues.

When I run that command manually, I get the following...

admin@securityonion:/var/www/so/squert/.scripts$ sudo ./ip2c.tcl
Fetching AFRINIC Checksum..
Bookmark found, looking for changes.. Fetching new data from AFRINIC

ERROR: 7
Verifying transfer.. Checksum Mismatch. Retrying..

Fetching new data from AFRINIC

Verifying transfer.. Looks good, processing..
Processed 3320 IPv4 records and skipped 4878.

Fetching APNIC Checksum..
Bookmark found, looking for changes.. Fetching new data from APNIC


ERROR: 28
Verifying transfer.. Checksum Mismatch. Retrying..

Fetching new data from APNIC


ERROR: 28
Verifying transfer.. Checksum Mismatch. Retrying..

Fetching new data from APNIC

Verifying transfer.. Looks good, processing..
Processed 37408 IPv4 records and skipped 53321.

Fetching ARIN Checksum..
Bookmark not found, Fetching ARIN Data.
Verifying transfer.. Looks good, processing..
Processed 58239 IPv4 records and skipped 70615.

Fetching LACNIC Checksum..
ERROR: 7
Bookmark not found, Fetching LACNIC Data.
Checksum not found. Retrying..

Bookmark not found, Fetching LACNIC Data.
ERROR: 7
Checksum not found. Retrying..

Bookmark not found, Fetching LACNIC Data.
ERROR: 7
Checksum not found. Retrying..

Bookmark not found, Fetching LACNIC Data.
ERROR: 7
Checksum not found. Retrying..

Bookmark not found, Fetching LACNIC Data.
ERROR: 7
Checksum not found. Retrying..

Bookmark not found, Fetching LACNIC Data.
Checksum not found. Retrying..

Bookmark not found, Fetching LACNIC Data.
ERROR: 7
Checksum not found. Retrying..

Bookmark not found, Fetching LACNIC Data.
Checksum not found. Retrying..

Bookmark not found, Fetching LACNIC Data.
Checksum not found. Retrying..

Bookmark not found, Fetching LACNIC Data.
Checksum not found. Retrying..

Bookmark not found, Fetching LACNIC Data.
ERROR: 7
Checksum not found. Retrying..

Bookmark not found, Fetching LACNIC Data.
Checksum not found. Retrying..

Bookmark not found, Fetching LACNIC Data.
ERROR: 7
Checksum not found. Retrying..

Bookmark not found, Fetching LACNIC Data.
ERROR: 7
Checksum not found. Retrying..

Bookmark not found, Fetching LACNIC Data.
ERROR: 7
Checksum not found. Retrying..

Bookmark not found, Fetching LACNIC Data.
ERROR: 7
Checksum not found. Retrying..

Bookmark not found, Fetching LACNIC Data.
ERROR: 7
Checksum not found. Retrying..

Bookmark not found, Fetching LACNIC Data.
ERROR: 7
Checksum not found. Retrying..

Bookmark not found, Fetching LACNIC Data.
Checksum not found. Retrying..

Bookmark not found, Fetching LACNIC Data.
Checksum not found. Retrying..

Bookmark not found, Fetching LACNIC Data.
ERROR: 7
Checksum not found. Retrying..

Bookmark not found, Fetching LACNIC Data.
Checksum not found. Retrying..

Bookmark not found, Fetching LACNIC Data.
ERROR: 7
Checksum not found. Retrying..

Bookmark not found, Fetching LACNIC Data.
Checksum not found. Retrying..

Bookmark not found, Fetching LACNIC Data.
Checksum not found. Retrying..

Bookmark not found, Fetching LACNIC Data.
ERROR: 7
Checksum not found. Retrying..

Bookmark not found, Fetching LACNIC Data.
Checksum not found. Retrying..

Bookmark not found, Fetching LACNIC Data.
ERROR: 7
Checksum not found. Retrying..

Bookmark not found, Fetching LACNIC Data.
ERROR: 7
Checksum not found. Retrying..

Bookmark not found, Fetching LACNIC Data.
Checksum not found. Retrying..

Bookmark not found, Fetching LACNIC Data.
Checksum not found. Retrying..

Bookmark not found, Fetching LACNIC Data.
ERROR: 7
Checksum not found. Retrying..

Bookmark not found, Fetching LACNIC Data.
ERROR: 7
Checksum not found. Retrying..

Bookmark not found, Fetching LACNIC Data.
Checksum not found. Retrying..

Bookmark not found, Fetching LACNIC Data.
Checksum not found. Retrying..

Bookmark not found, Fetching LACNIC Data.
Checksum not found. Retrying..

Bookmark not found, Fetching LACNIC Data.
ERROR: 7
Checksum not found. Retrying..

Bookmark not found, Fetching LACNIC Data.
ERROR: 7
Checksum not found. Retrying..

Bookmark not found, Fetching LACNIC Data.
ERROR: 7
Checksum not found. Retrying..

Bookmark not found, Fetching LACNIC Data.
Checksum not found. Retrying..

Bookmark not found, Fetching LACNIC Data.
ERROR: 7
Checksum not found. Retrying..

Bookmark not found, Fetching LACNIC Data.
ERROR: 7
Checksum not found. Retrying..

Bookmark not found, Fetching LACNIC Data.
ERROR: 7
Checksum not found. Retrying..

Bookmark not found, Fetching LACNIC Data.
ERROR: 7
Checksum not found. Retrying..

Bookmark not found, Fetching LACNIC Data.
ERROR: 7
Checksum not found. Retrying..

Bookmark not found, Fetching LACNIC Data.
Checksum not found. Retrying..

Bookmark not found, Fetching LACNIC Data.
Checksum not found. Retrying..

Bookmark not found, Fetching LACNIC Data.
ERROR: 7
Checksum not found. Retrying..

Bookmark not found, Fetching LACNIC Data.
Checksum not found. Retrying..

Bookmark not found, Fetching LACNIC Data.
Checksum not found. Retrying..

Bookmark not found, Fetching LACNIC Data.
Checksum not found. Retrying..

Bookmark not found, Fetching LACNIC Data.
Checksum not found. Retrying..

Bookmark not found, Fetching LACNIC Data.
ERROR: 7
Checksum not found. Retrying..

Bookmark not found, Fetching LACNIC Data.
Checksum not found. Retrying..

Bookmark not found, Fetching LACNIC Data.
Checksum not found. Retrying..

Bookmark not found, Fetching LACNIC Data.
ERROR: 7
Checksum not found. Retrying..

Bookmark not found, Fetching LACNIC Data.
ERROR: 7
Checksum not found. Retrying..

Bookmark not found, Fetching LACNIC Data.
ERROR: 7
Checksum not found. Retrying..

Bookmark not found, Fetching LACNIC Data.
Checksum not found. Retrying..

Bookmark not found, Fetching LACNIC Data.
Checksum not found. Retrying..

Bookmark not found, Fetching LACNIC Data.
ERROR: 7
Checksum not found. Retrying..

Bookmark not found, Fetching LACNIC Data.
ERROR: 7
Checksum not found. Retrying..

Bookmark not found, Fetching LACNIC Data.
Checksum not found. Retrying..

Bookmark not found, Fetching LACNIC Data.
Checksum not found. Retrying..

Bookmark not found, Fetching LACNIC Data.
Checksum not found. Retrying..

Bookmark not found, Fetching LACNIC Data.
Checksum not found. Retrying..

Bookmark not found, Fetching LACNIC Data.
Checksum not found. Retrying..

Bookmark not found, Fetching LACNIC Data.
ERROR: 7
Checksum not found. Retrying..

Bookmark not found, Fetching LACNIC Data.
Checksum not found. Retrying..

Bookmark not found, Fetching LACNIC Data.
ERROR: 7
Checksum not found. Retrying..

Bookmark not found, Fetching LACNIC Data.
Checksum not found. Retrying..

Bookmark not found, Fetching LACNIC Data.
Checksum not found. Retrying..

Bookmark not found, Fetching LACNIC Data.
ERROR: 7
Checksum not found. Retrying..

Bookmark not found, Fetching LACNIC Data.
ERROR: 7
Checksum not found. Retrying..

Bookmark not found, Fetching LACNIC Data.
ERROR: 7
Checksum not found. Retrying..

Bookmark not found, Fetching LACNIC Data.
ERROR: 7
Checksum not found. Retrying..

Bookmark not found, Fetching LACNIC Data.
Checksum not found. Retrying..

Bookmark not found, Fetching LACNIC Data.
Checksum not found. Retrying..

Bookmark not found, Fetching LACNIC Data.
Checksum not found. Retrying..

Bookmark not found, Fetching LACNIC Data.
ERROR: 7
Checksum not found. Retrying..

Bookmark not found, Fetching LACNIC Data.
Checksum not found. Retrying..

Bookmark not found, Fetching LACNIC Data.
ERROR: 7
Checksum not found. Retrying..

Bookmark not found, Fetching LACNIC Data.
ERROR: 7
Checksum not found. Retrying..

Bookmark not found, Fetching LACNIC Data.
Checksum not found. Retrying..

Bookmark not found, Fetching LACNIC Data.
ERROR: 7
Checksum not found. Retrying..

Bookmark not found, Fetching LACNIC Data.
ERROR: 7
Checksum not found. Retrying..

Bookmark not found, Fetching LACNIC Data.
ERROR: 7
Checksum not found. Retrying..

Bookmark not found, Fetching LACNIC Data.
ERROR: 7
Checksum not found. Retrying..

Bookmark not found, Fetching LACNIC Data.
Checksum not found. Retrying..

Bookmark not found, Fetching LACNIC Data.
ERROR: 7
Checksum not found. Retrying..

Bookmark not found, Fetching LACNIC Data.
Checksum not found. Retrying..

Bookmark not found, Fetching LACNIC Data.
ERROR: 7
Checksum not found. Retrying..

I manually broke this since it appears to be in a loop. I have no outbound firewall or proxy rules preventing this on my network.

Any suggestions?

Thanks,
Pat

Éric Marcoux

unread,
Jan 11, 2017, 10:59:12 AM1/11/17
to security-onion

Hello Patrick,

Are your Security Onion servers behind a proxy? The ip2c script does not support proxy. If this is the case, I can attach a modified version of the script that does support proxy if you need it.

Regards,
Eric

Patrick Schilling

unread,
Jan 11, 2017, 11:40:13 AM1/11/17
to security-onion
Hi Eric,

I have a squid transparent proxy but do not block anything out.

I tested connecting manually to oen of the sites and received the following...

admin@securityonion:/var/www/so/squert/.scripts$ ftp ftp.apnic.net
Connected to ftp.apnic.net.
220 APNIC FTP Repository (ftp.apnic.net)
Name (ftp.apnic.net:aciadmin): anonymous
331 Anonymous login ok, send your complete email address as your password
Password:
230-Welcome to the master FTP site for APNIC Pty. Ltd.
There are currently 12 users out of 60 possible.


**********************************************************************
** This Archive is also accessible via HTTP at http://ftp.apnic.net **
**********************************************************************

The local time is Thu Jan 12 01:43:43 2017.

If you have problems, please try using a dash (-) as the first character
of your password -- this will turn off the continuation messages that may
be confusing your ftp client.

NOTE: All transactions with this server are logged.
If you do not like this, disconnect now!

Please send mail to tech...@apnic.net if you experience any problems.

230 Anonymous access granted, restrictions apply
Remote system type is UNIX.
Using binary mode to transfer files.
ftp> ls
500 Illegal PORT command
ftp: bind: Address already in use
ftp> passive
Passive mode on.
ftp> ls
227 Entering Passive Mode (202,12,29,205,235,217).
150 Opening ASCII mode data connection for file list
lrwxrwxrwx 1 ftp ftpd 9 Jul 21 2014 apnic -> pub/apnic
lrwxrwxrwx 1 ftp ftpd 8 Jul 21 2014 ietf -> pub/ietf
lrwxrwxrwx 1 ftp ftpd 6 Jul 21 2014 pub -> public
drwxr-xr-x 16 root root 4096 May 23 2014 public
drwxrwxrwx 2 ftp ftpd 4096 Apr 2 2008 uploads
-r-xr-xr-x 1 ftp ftpd 703 Nov 29 2002 welcome.msg
226 Transfer complete
ftp> cd pub/stats/apnic
250 CWD command successful
ftp> ls
227 Entering Passive Mode (202,12,29,205,217,55).
150 Opening ASCII mode data connection for file list
drwxr-xr-x 2 autodbm bulkwhois 4096 Sep 29 2014 2001
drwxr-xr-x 2 autodbm bulkwhois 4096 Jan 6 2004 2002
drwxr-xr-x 2 autodbm bulkwhois 24576 Oct 1 2014 2003
drwxrwxr-x 2 autodbm bulkwhois 65536 Oct 1 2014 2004
drwxrwxr-x 2 autodbm bulkwhois 65536 Apr 14 2009 2005
drwxrwxr-x 2 autodbm bulkwhois 77824 Oct 1 2014 2006
drwxrwxr-x 2 autodbm bulkwhois 81920 Apr 14 2009 2007
drwxrwxr-x 2 autodbm bulkwhois 139264 Oct 1 2014 2008
drwxrwxr-x 4 autodbm bulkwhois 163840 Oct 1 2014 2009
drwxr-xr-x 2 autodbm bulkwhois 147456 Sep 29 2014 2010
drwxr-xr-x 2 autodbm bulkwhois 217088 Dec 31 2011 2011
drwxr-xr-x 2 autodbm bulkwhois 229376 Dec 31 2012 2012
drwxr-xr-x 2 autodbm bulkwhois 266240 Dec 31 2013 2013
drwxr-xr-x 2 autodbm bulkwhois 290816 Dec 31 2014 2014
drwxr-xr-x 2 autodbm bulkwhois 299008 Dec 31 2015 2015
drwxr-xr-x 2 autodbm bulkwhois 290816 Dec 31 04:30 2016
drwxr-xr-x 2 autodbm bulkwhois 12288 Jan 11 04:30 2017
-rw-r--r-- 1 autodbm bulkwhois 6610 Jan 11 04:30 README-ASSIGNED.TXT
-rw-r--r-- 1 autodbm bulkwhois 9220 Jan 11 04:30 README-EXTENDED.TXT
-rw-r--r-- 1 autodbm bulkwhois 7278 Jan 11 04:30 README-LEGACY.TXT
-rw-r--r-- 1 autodbm bulkwhois 7533 Jan 11 04:30 README.TXT
-rw-r--r-- 1 autodbm bulkwhois 79125 Jan 11 01:18 assigned-apnic-20170111
-rw-r--r-- 1 autodbm bulkwhois 330 Jan 12 01:15 assigned-apnic-20170112
lrwxrwxrwx 1 autodbm bulkwhois 23 Jan 11 04:30 assigned-apnic-latest -> assigned-apnic-20170111
-rw-r--r-- 1 autodbm bulkwhois 2429787 Jan 11 01:15 delegated-apnic-20170111
-rw-r--r-- 1 autodbm bulkwhois 189 Jan 11 01:15 delegated-apnic-20170111.asc
-rw-r--r-- 1 autodbm bulkwhois 66 Jan 11 01:15 delegated-apnic-20170111.md5
-rw-r--r-- 1 autodbm bulkwhois 2430304 Jan 12 01:15 delegated-apnic-20170112
-rw-r--r-- 1 autodbm bulkwhois 189 Jan 12 01:15 delegated-apnic-20170112.asc
-rw-r--r-- 1 autodbm bulkwhois 66 Jan 12 01:15 delegated-apnic-20170112.md5
-rw-r--r-- 1 autodbm bulkwhois 4493049 Jan 11 01:15 delegated-apnic-extended-20170111
-rw-r--r-- 1 autodbm bulkwhois 189 Jan 11 01:15 delegated-apnic-extended-20170111.asc
-rw-r--r-- 1 autodbm bulkwhois 75 Jan 11 01:15 delegated-apnic-extended-20170111.md5
-rw-r--r-- 1 autodbm bulkwhois 4494359 Jan 12 01:15 delegated-apnic-extended-20170112
-rw-r--r-- 1 autodbm bulkwhois 189 Jan 12 01:15 delegated-apnic-extended-20170112.asc
-rw-r--r-- 1 autodbm bulkwhois 75 Jan 12 01:15 delegated-apnic-extended-20170112.md5
lrwxrwxrwx 1 autodbm bulkwhois 33 Jan 12 01:15 delegated-apnic-extended-latest -> delegated-apnic-extended-20170112
-rw-r--r-- 1 autodbm bulkwhois 189 Jan 12 01:15 delegated-apnic-extended-latest.asc
-rw-r--r-- 1 autodbm bulkwhois 73 Jan 12 01:15 delegated-apnic-extended-latest.md5
-rw-r--r-- 1 autodbm bulkwhois 65154 Jan 11 01:15 delegated-apnic-ipv6-assigned-20170111
-rw-r--r-- 1 autodbm bulkwhois 189 Jan 11 01:15 delegated-apnic-ipv6-assigned-20170111.asc
-rw-r--r-- 1 autodbm bulkwhois 80 Jan 11 01:15 delegated-apnic-ipv6-assigned-20170111.md5
-rw-r--r-- 1 autodbm bulkwhois 65154 Jan 12 01:15 delegated-apnic-ipv6-assigned-20170112
-rw-r--r-- 1 autodbm bulkwhois 189 Jan 12 01:15 delegated-apnic-ipv6-assigned-20170112.asc
-rw-r--r-- 1 autodbm bulkwhois 80 Jan 12 01:15 delegated-apnic-ipv6-assigned-20170112.md5
lrwxrwxrwx 1 autodbm bulkwhois 38 Jan 12 01:15 delegated-apnic-ipv6-assigned-latest -> delegated-apnic-ipv6-assigned-20170112
-rw-r--r-- 1 autodbm bulkwhois 189 Jan 12 01:15 delegated-apnic-ipv6-assigned-latest.asc
-rw-r--r-- 1 autodbm bulkwhois 78 Jan 12 01:15 delegated-apnic-ipv6-assigned-latest.md5
lrwxrwxrwx 1 autodbm bulkwhois 24 Jan 12 01:15 delegated-apnic-latest -> delegated-apnic-20170112
-rw-r--r-- 1 autodbm bulkwhois 189 Jan 12 01:15 delegated-apnic-latest.asc
-rw-r--r-- 1 autodbm bulkwhois 64 Jan 12 01:15 delegated-apnic-latest.md5
-rw-r--r-- 1 autodbm bulkwhois 65967 Jan 11 01:15 legacy-apnic-20170111
-rw-r--r-- 1 autodbm bulkwhois 189 Jan 11 01:15 legacy-apnic-20170111.asc
-rw-r--r-- 1 autodbm bulkwhois 63 Jan 11 01:15 legacy-apnic-20170111.md5
-rw-r--r-- 1 autodbm bulkwhois 65967 Jan 12 01:15 legacy-apnic-20170112
-rw-r--r-- 1 autodbm bulkwhois 189 Jan 12 01:15 legacy-apnic-20170112.asc
-rw-r--r-- 1 autodbm bulkwhois 63 Jan 12 01:15 legacy-apnic-20170112.md5
lrwxrwxrwx 1 autodbm bulkwhois 21 Jan 12 01:15 legacy-apnic-latest -> legacy-apnic-20170112
-rw-r--r-- 1 autodbm bulkwhois 189 Jan 12 01:15 legacy-apnic-latest.asc
-rw-r--r-- 1 autodbm bulkwhois 61 Jan 12 01:15 legacy-apnic-latest.md5
226 Transfer complete
ftp> get delegated-apnic-extended-latest
local: delegated-apnic-extended-latest remote: delegated-apnic-extended-latest
local: delegated-apnic-extended-latest: Permission denied
ftp> quit
221 Goodbye.


Not sure why permission denied so I went via http and can successfully navigate and view that file.


I also ran these other commands taken from posts I have read...


admin@sotest:/var/www/so/squert/.scripts$ sudo rm -f *.md5; ./ip2c.tcl
error deleting "results.txt": permission denied
while executing
"file delete -force $resultsFile"
invoked from within
"if {[file exists $resultsFile]} {
file delete -force $resultsFile
}"
(file "./ip2c.tcl" line 221)

admin@sotest:/var/www/so/squert/.scripts$ ls
clicat.tcl delegated-lacnic-extended-latest.txt results.txt securityonion-squert.cnf securityonion_update.sql stats.sh
cliscript.tcl ip2c.tcl securityonion_create_elsa_link.sh securityonion_update.sh squert.sql

admin@sotest:/var/www/so/squert/.scripts$ sudo ./ip2c.tcl
Fetching AFRINIC Checksum..
Bookmark not found, Fetching AFRINIC Data.
ERROR: 7
Verifying transfer.. Checksum Mismatch. Retrying..

Bookmark not found, Fetching AFRINIC Data.
Verifying transfer.. Looks good, processing..
Processed 3322 IPv4 records and skipped 4878.

Fetching APNIC Checksum..
Bookmark not found, Fetching APNIC Data.
Verifying transfer.. Looks good, processing..
Processed 37408 IPv4 records and skipped 53321.

Fetching ARIN Checksum..
ERROR: 56
Bookmark not found, Fetching ARIN Data.
ERROR: 56
Checksum not found. Retrying..

Bookmark not found, Fetching ARIN Data.
Checksum not found. Retrying..

Bookmark not found, Fetching ARIN Data.

ERROR: 56
Checksum not found. Retrying..

Bookmark not found, Fetching ARIN Data.
Checksum not found. Retrying..

Bookmark not found, Fetching ARIN Data.
ERROR: 56
Checksum not found. Retrying..

now it looks like it is stuck in a loop, but the first couple seemed to complete.


admin@sotest:~$ sudo /usr/bin/php -e /var/www/so/squert/.inc/ip2c.php 0
Performing base queries (this can take a while)..


-> Query Time: 5.614 seconds
-> Source Count: 695
-> Destination Count: 4002
-> Total Mapped: 144



admin@sotest:/var/www/so/squert/.scripts$ mysql -u root -D securityonion_db -e 'SELECT COUNT(DISTINCT start_ip) FROM ip2c'
+--------------------------+
| COUNT(DISTINCT start_ip) |
+--------------------------+
| 3 |
+--------------------------+


admin@sotest:/var/www/so/squert/.scripts$ mysql -u root -D securityonion_db -e 'SELECT DISTINCT COUNT(*) FROM mappings'
+----------+
| COUNT(*) |
+----------+
| 159 |
+----------+


admin@sotest:/var/www/so/squert/.scripts$ mysql -u root -D securityonion_db -e 'SELECT INET_NTOA(ip),age FROM mappings ORDER BY age DESC LIMIT 25'
+---------------+---------------------+
| INET_NTOA(ip) | age |
+---------------+---------------------+
| 10.253.1.174 | 2017-01-11 14:50:10 |
| 10.254.3.165 | 2017-01-11 14:35:11 |
| 192.168.87.1 | 2017-01-11 13:50:08 |
| 10.254.3.159 | 2017-01-11 12:10:06 |
| 10.253.3.170 | 2017-01-11 10:55:05 |
| 10.253.3.163 | 2017-01-11 10:45:06 |
| 10.253.100.8 | 2017-01-11 10:20:06 |
| 10.254.3.161 | 2017-01-11 09:20:06 |
| 10.253.1.178 | 2017-01-11 09:15:06 |
| 10.253.3.150 | 2017-01-11 08:50:06 |
| 10.254.3.168 | 2017-01-11 08:25:04 |
| 10.253.0.78 | 2017-01-11 07:10:05 |
| 10.253.3.162 | 2017-01-11 07:05:05 |
| 10.253.3.168 | 2017-01-11 05:35:09 |
| 10.253.1.91 | 2017-01-11 04:55:04 |
| 10.253.1.85 | 2017-01-11 04:40:07 |
| 192.168.1.50 | 2017-01-11 04:15:07 |
| 10.253.100.6 | 2017-01-11 04:15:06 |
| 10.254.3.150 | 2017-01-11 02:50:05 |
| 10.254.3.155 | 2017-01-11 01:15:04 |
| 10.253.3.164 | 2017-01-11 01:15:04 |
| 10.253.100.4 | 2017-01-11 00:55:05 |
| 10.253.3.23 | 2017-01-11 00:55:05 |
| 10.254.3.164 | 2017-01-11 00:20:06 |
| 10.253.1.182 | 2017-01-11 00:15:07 |
+---------------+---------------------+



admin@sotest:/var/www/so/squert/.scripts$ ip='192.168.66.1' && mysql -u root -D securityonion_db -e "SELECT *, INET_NTOA(start_ip) AS start_ip_addr, INET_NTOA(end_ip) AS end_ip_addr FROM ip2c WHERE start_ip<=INET_ATON(\"$ip\") AND end_ip>=INET_ATON(\"$ip\") \G;"
*************************** 1. row ***************************
registry: RFC1918
cc: LO
c_long: RFC1918
type: ipv4
start_ip: 3232235520
end_ip: 3232301055
date: 1996-02-01 00:00:00
status: allocated
start_ip_addr: 192.168.0.0
end_ip_addr: 192.168.255.255




admin@sotest:/var/www/so/squert/.scripts$ ip='10.253.1.176' && mysql -u root -D securityonion_db -e "SELECT INET_NTOA(ip),age FROM mappings WHERE ip = INET_ATON(\"$ip\")"
+---------------+---------------------+
| INET_NTOA(ip) | age |
+---------------+---------------------+
| 10.253.1.176 | 2017-01-10 18:00:02 |
+---------------+---------------------+


Not sure if any of this helps.

Thanks in advance,
Pat

Patrick Schilling

unread,
Jan 11, 2017, 11:51:33 AM1/11/17
to security-onion
*Update to Previous*

I think the permission denied was due to local write - didn't use sudo, Ubuntu newb...

Changed to home dir and successfully downloaded file via ftp...

admin@securityonion:~$ ftp ftp.afrinic.net
Connected to ftp.afrinic.net.
220 ::::: Welcome to AFRINIC FTP service ::::::
Name (ftp.afrinic.net:aciadmin): anonymous
331 Please specify the password.
Password:
230 Login successful.
Remote system type is UNIX.
Using binary mode to transfer files.
ftp> ls
500 Illegal PORT command.
ftp: bind: Address already in use
ftp> passive
Passive mode on.
ftp> ls
227 Entering Passive Mode (196,216,2,24,122,233).
150 Here comes the directory listing.
drwxr-xr-x 2 0 0 32 Jul 08 2016 AIS16
drwxr-xr-x 2 14 50 46 Oct 01 2008 db
drwxr-xr-x 2 1005 50 54 Sep 01 2015 dbase
lrwxrwxrwx 1 14 50 1 Jun 28 2012 pub -> .
drwxr-xr-x 2 14 50 133 Mar 22 2005 rfc
-rw-r--r-- 1 0 0 26 Nov 04 2014 robots.txt
drwxrwxr-x 3 1004 1004 20 Feb 19 2005 stats
drwxrwxr-x 2 1003 50 12288 Jan 11 16:25 zones
226 Directory send OK.
ftp> cd stats
250 Directory successfully changed.
ftp> ls
227 Entering Passive Mode (196,216,2,24,126,240).
150 Here comes the directory listing.
drwxr-xr-x 15 1004 1004 4096 Jan 11 05:30 afrinic
226 Directory send OK.
ftp> cd afrinic
250 Directory successfully changed.
ftp> ls
227 Entering Passive Mode (196,216,2,24,124,221).
150 Here comes the directory listing.
drwxr-xr-x 2 1004 1004 45056 Oct 01 2012 2005
drwxr-xr-x 2 1004 1004 53248 Oct 01 2012 2006
drwxr-xr-x 2 1004 1004 53248 Oct 01 2012 2007
drwxr-xr-x 2 1004 1004 49152 Oct 01 2012 2008
drwxr-xr-x 2 1004 1004 49152 Oct 01 2012 2009
drwxr-xr-x 2 1004 1004 53248 Oct 01 2012 2010
drwxrwxr-x 2 1004 1004 32768 Oct 02 2012 2011
drwxr-xr-x 2 1004 1004 65536 Dec 31 2012 2012
drwxr-xr-x 2 1004 1004 110592 Jan 01 2014 2013
drwxr-xr-x 2 1004 1004 110592 Dec 31 2014 2014
drwxr-xr-x 2 1004 1004 110592 Dec 31 2015 2015
drwxr-xr-x 2 1004 1004 110592 Jan 01 00:05 2016
drwxr-xr-x 2 1004 1004 4096 Jan 11 00:05 2017
-rw-r--r-- 1 1004 1004 1328 May 13 2011 AFRINICPUBKEY.TXT
-rw-r--r-- 1 1004 1004 9207 Oct 08 2012 README-EXTENDED.txt
-rwxr-xr-x 1 1004 1004 13046 Jun 11 2007 RIR-Statistics-Exchange-Format.txt
-rw-r--r-- 1 1004 1004 211476 Mar 30 2015 delegated-afrinic-20150330
-rw-r--r-- 1 1004 1004 189 Mar 30 2015 delegated-afrinic-20150330.asc
-rw-r--r-- 1 1004 1004 65 Mar 30 2015 delegated-afrinic-20150330.md5
-rw-r--r-- 1 1004 1004 255797 Jan 11 00:05 delegated-afrinic-20170111
-rw-r--r-- 1 1004 1004 189 Jan 11 00:05 delegated-afrinic-20170111.asc
-rw-r--r-- 1 1004 1004 65 Jan 11 00:05 delegated-afrinic-20170111.md5
-rw-r--r-- 1 1004 1004 363225 Mar 30 2015 delegated-afrinic-extended-20150330
-rw-r--r-- 1 1004 1004 189 Mar 30 2015 delegated-afrinic-extended-20150330.asc
-rw-r--r-- 1 1004 1004 74 Mar 30 2015 delegated-afrinic-extended-20150330.md5
-rw-r--r-- 1 1004 1004 425396 Jan 11 00:05 delegated-afrinic-extended-20170111
-rw-r--r-- 1 1004 1004 189 Jan 11 00:05 delegated-afrinic-extended-20170111.asc
-rw-r--r-- 1 1004 1004 74 Jan 11 00:05 delegated-afrinic-extended-20170111.md5
lrwxrwxrwx 1 1004 1004 35 Jan 11 05:30 delegated-afrinic-extended-latest -> delegated-afrinic-extended-20170111
lrwxrwxrwx 1 1004 1004 39 Jan 11 05:30 delegated-afrinic-extended-latest.asc -> delegated-afrinic-extended-20170111.asc
lrwxrwxrwx 1 1004 1004 39 Jan 11 05:30 delegated-afrinic-extended-latest.md5 -> delegated-afrinic-extended-20170111.md5
lrwxrwxrwx 1 1004 1004 26 Jan 11 05:30 delegated-afrinic-latest -> delegated-afrinic-20170111
lrwxrwxrwx 1 1004 1004 30 Jan 11 05:30 delegated-afrinic-latest.asc -> delegated-afrinic-20170111.asc
lrwxrwxrwx 1 1004 1004 30 Jan 11 05:30 delegated-afrinic-latest.md5 -> delegated-afrinic-20170111.md5
226 Directory send OK.
ftp> get delegated-afrinic-extended-latest
local: delegated-afrinic-extended-latest remote: delegated-afrinic-extended-latest
227 Entering Passive Mode (196,216,2,24,123,40).
150 Opening BINARY mode data connection for delegated-afrinic-extended-latest (425396 bytes).
226 Transfer complete.
425396 bytes received in 1.52 secs (273.2 kB/s)
ftp> quit
221 Goodbye.
admin@securityonion:~$ ls
delegated-afrinic-extended-latest Desktop Documents Downloads Music Pictures Public search_ip2c.sh Templates Videos

Thanks,
Pat

Éric Marcoux

unread,
Jan 11, 2017, 11:59:43 AM1/11/17
to security-onion
Hi Patrick

Glad to here it.

Best regards,
Eric

Patrick Schilling

unread,
Jan 11, 2017, 12:17:52 PM1/11/17
to security-onion
Hi Eric,

While I can manually do the ftp download, the ip2c.tcl script still doesn't complete. I have let it run for hours and it just keeps retrying whatever it is stuck on. I've checked all the logs I can find and don't have any idea why it won't work. Right now it is just stuck in a loop showing the following over and over...


Bookmark not found, Fetching ARIN Data.
ERROR: 56
Checksum not found. Retrying..

Bookmark not found, Fetching ARIN Data.
Checksum not found. Retrying..

Bookmark not found, Fetching ARIN Data.
ERROR: 56
Checksum not found. Retrying..


Attached is a capture of the Summary Tab in Squert showing no country data.

Any help is appreciated.

Thanks,
Pat
Squert.png

Éric Marcoux

unread,
Jan 11, 2017, 1:06:02 PM1/11/17
to security-onion
Hello Patrick,

I tried to run the script on my SO Box and it worked. I looked at the code of the script and the error 56 is related to the Curl error code (list here https://curl.haxx.se/libcurl/c/libcurl-errors.html). In your case, the error 56 indicate a failure with receiving network data.

I don't know how your network is set up, but I recommand that you check your settings on the network side.

Hope it helps.

Regards,
Eric

Patrick Schilling

unread,
Jan 11, 2017, 3:45:41 PM1/11/17
to security-onion
Hi Eric,

I does appear all the error codes (7, 28, 56) I've seen are network related. I tried once again while running a capture on the SO machine, as well as looking at both my firewall and squid logs for anything.

admin@securityonion:/var/www/so/squert/.scripts$ sudo ./ip2c.tcl
Fetching AFRINIC Checksum..
Bookmark found, looking for changes.. No revisions, skipping.

Fetching APNIC Checksum..
Bookmark found, looking for changes.. Fetching new data from APNIC

Verifying transfer.. Looks good, processing..
Processed 37415 IPv4 records and skipped 53343.

Fetching ARIN Checksum..
ERROR: 56
Bookmark found, looking for changes.. Fetching new data from ARIN

ERROR: 56
Checksum not found. Retrying..

Fetching new data from ARIN

Checksum not found. Retrying..

Fetching new data from ARIN

ERROR: 56
Checksum not found. Retrying..

Fetching new data from ARIN

ERROR: 56
Checksum not found. Retrying..

Fetching new data from ARIN

Checksum not found. Retrying..

Fetching new data from ARIN

I ctrl-c here and stop the capture. It looks like the 1st two (AFRINIC and APNIC) finish and then the 3rd (ARIN) goes into a loop. Here is what I see when I follow the TCP streams...


220 ProFTPD 1.3.3g Server (ftp.arin.net) [::ffff:199.71.0.151]
USER anonymous
331 Anonymous login ok, send your complete email address as your password
PASS f...@example.com
230 Anonymous access granted, restrictions apply
PWD
257 "/" is the current directory
CWD pub
250 CWD command successful
CWD stats
250 CWD command successful
CWD arin
250 CWD command successful
EPSV
229 Entering Extended Passive Mode (|||56610|)
TYPE I
200 Type set to I
SIZE delegated-arin-extended-latest
213 8503574
RETR delegated-arin-extended-latest
150 Opening BINARY mode data connection for delegated-arin-extended-latest (8503574 bytes)
226 Transfer complete
QUIT
221 Goodbye.


220 APNIC FTP Repository (ftp.apnic.net)
USER anonymous
331 Anonymous login ok, send your complete email address as your password
PASS f...@example.com
230-Welcome to the master FTP site for APNIC Pty. Ltd.
There are currently 10 users out of 60 possible.


**********************************************************************
** This Archive is also accessible via HTTP at http://ftp.apnic.net **
**********************************************************************

The local time is Thu Jan 12 05:46:29 2017.

If you have problems, please try using a dash (-) as the first character
of your password -- this will turn off the continuation messages that may
be confusing your ftp client.

NOTE: All transactions with this server are logged.
If you do not like this, disconnect now!

Please send mail to tech...@apnic.net if you experience any problems.

230 Anonymous access granted, restrictions apply
PWD
257 "/" is the current directory
CWD pub
250 CWD command successful
CWD stats
250 CWD command successful
CWD apnic
250 CWD command successful
EPSV
229 Entering Extended Passive Mode (|||52736|)
TYPE I
200 Type set to I
SIZE delegated-apnic-extended-latest.md5
213 73
RETR delegated-apnic-extended-latest.md5
150 Opening BINARY mode data connection for delegated-apnic-extended-latest.md5 (73 bytes)
226 Transfer complete
QUIT
221 Goodbye.


220 ProFTPD 1.3.3g Server (ftp.arin.net) [::ffff:199.71.0.151]
USER anonymous
331 Anonymous login ok, send your complete email address as your password
PASS f...@example.com
230 Anonymous access granted, restrictions apply
PWD
257 "/" is the current directory
CWD pub
250 CWD command successful
CWD stats
250 CWD command successful
CWD arin
250 CWD command successful
EPSV
229 Entering Extended Passive Mode (|||49661|)
TYPE I
200 Type set to I
SIZE delegated-arin-extended-latest.md5
213 67
RETR delegated-arin-extended-latest.md5


220 ProFTPD 1.3.3g Server (ftp.arin.net) [::ffff:199.212.0.151]
USER anonymous
331 Anonymous login ok, send your complete email address as your password
PASS f...@example.com
230 Anonymous access granted, restrictions apply
PWD
257 "/" is the current directory
CWD pub
250 CWD command successful
CWD stats
250 CWD command successful
CWD arin
250 CWD command successful
EPSV
229 Entering Extended Passive Mode (|||60227|)
TYPE I
200 Type set to I
SIZE delegated-arin-extended-latest
213 8503574
RETR delegated-arin-extended-latest


220 ProFTPD 1.3.3g Server (ftp.arin.net) [::ffff:199.71.0.151]
USER anonymous
331 Anonymous login ok, send your complete email address as your password
PASS f...@example.com
230 Anonymous access granted, restrictions apply
PWD
257 "/" is the current directory
CWD pub
250 CWD command successful
CWD stats
250 CWD command successful
CWD arin
250 CWD command successful
EPSV
229 Entering Extended Passive Mode (|||60186|)
TYPE I
200 Type set to I
SIZE delegated-arin-extended-latest
213 8503574
RETR delegated-arin-extended-latest
150 Opening BINARY mode data connection for delegated-arin-extended-latest (8503574 bytes)
226 Transfer complete
QUIT
221 Goodbye.


220 ProFTPD 1.3.3g Server (ftp.arin.net) [::ffff:199.71.0.151]
USER anonymous
331 Anonymous login ok, send your complete email address as your password
PASS f...@example.com
230 Anonymous access granted, restrictions apply
PWD
257 "/" is the current directory
CWD pub
250 CWD command successful
CWD stats
250 CWD command successful
CWD arin
250 CWD command successful
EPSV
229 Entering Extended Passive Mode (|||62689|)
TYPE I
200 Type set to I
SIZE delegated-arin-extended-latest
213 8503574
RETR delegated-arin-extended-latest


220 ProFTPD 1.3.3g Server (ftp.arin.net) [::ffff:199.212.0.151]
USER anonymous
331 Anonymous login ok, send your complete email address as your password
PASS f...@example.com
230 Anonymous access granted, restrictions apply
PWD
257 "/" is the current directory
CWD pub
250 CWD command successful
CWD stats
250 CWD command successful
CWD arin
250 CWD command successful
EPSV
229 Entering Extended Passive Mode (|||54802|)
TYPE I
200 Type set to I
SIZE delegated-arin-extended-latest
213 8503574
RETR delegated-arin-extended-latest
150 Opening BINARY mode data connection for delegated-arin-extended-latest (8503574 bytes)
226 Transfer complete
QUIT
221 Goodbye.


220 ProFTPD 1.3.3g Server (ftp.arin.net) [::ffff:199.71.0.151]
USER anonymous
331 Anonymous login ok, send your complete email address as your password
PASS f...@example.com
230 Anonymous access granted, restrictions apply
PWD
257 "/" is the current directory
CWD pub
250 CWD command successful
CWD stats
250 CWD command successful
CWD arin
250 CWD command successful
EPSV
229 Entering Extended Passive Mode (|||56610|)
TYPE I
200 Type set to I
SIZE delegated-arin-extended-latest
213 8503574
RETR delegated-arin-extended-latest
150 Opening BINARY mode data connection for delegated-arin-extended-latest (8503574 bytes)
226 Transfer complete
QUIT
221 Goodbye.


So it looks like it is finishing but then doing a loop. I am not a deep packet guy so any light you can shed on this would be great. There was also absolutely nothing in the firewall or squid proxy logs.

Thanks,
Pat

Éric Marcoux

unread,
Jan 12, 2017, 8:53:30 AM1/12/17
to security-onion
On Tuesday, 10 January 2017 18:01:00 UTC-5, Patrick Schilling wrote:

Hello Pat,

Can you start a WireShark capture on your SO box and run the script for some time (1 to 3 failled download attempt)? I will check the network capture and try to figure out what going on. If you want you can send the capture to my email (for privacy). I will do my best to help you. I had a lot of problem with that script myself.

Regards,
Eric

Patrick Schilling

unread,
Jan 12, 2017, 2:52:18 PM1/12/17
to security-onion
Hi Eric,

Thank you so much for all your help.

After looking at the pattern of the capture yesterday, it seemed to me that the connection wasn't maintaining persistence over a single link. Every time I ran the script it would start to loop in a different place. I took another capture on my load balancing device and verified the same. Turns out that I needed to modify the ftp profile to persist based on source address.

Once I did that the script ran through without issue and I now have Country data in Squert!

Again, I really appreciate your follow up, you definitely led me down the correct path with the network timeout errors.

Thanks,
Pat

Éric Marcoux

unread,
Jan 12, 2017, 3:14:45 PM1/12/17
to security-onion
Hello Pat,

I'm happy for you!

Regards,
Eric
Reply all
Reply to author
Forward
0 new messages