Name associated with ASN in label?

[David Edelman]

Let¹s try that again:

 select saddr,sas,asname from argusoutput,asns where ((sas=asnum) and (sas
in (2911, 2912, 2913));


From:  David Edelman <dedelman at iname.com>
Date:  Thursday, July 3, 2014 at 1:01 PM
To:  branchbunch <branchbunch at gmail.com>, Jesse Bowling
<jessebowling at gmail.com>
Cc:  Argus <argus-info at lists.andrew.cmu.edu>
Subject:  Re: [ARGUS] Name associated with ASN in label?

May I suggest that you really want the asns table to be:

 (
 asnum INT UNSIGNED NOT NULL PRIMARY KEY,
 asname text);

I would probably use VARCHAR(some number) for the AS name, but the unsigned
is pretty important for the AS number.

I would also create a VIEW that does the join and gives you some additional
flexibility:

  CREATE VIEW addrwithasn AS SELECT a.saddr as ¹saddr', a.sas as ¹sas',
b.asname as Œasname¹ FROM argusoutput a LEFT JOIN asns b ON a.sas = b.asnum;

This gives you the ability to retrieve the Argus data even where the AS
number is missing from the GeoIP data and you can do selects for specific AS
number or source address:

 SELECT * FROM addrwithasn WHERE sas = in (2911, 2912, 2913);

Which IMHO is better than

 select saddr,sas,asname from argusoutput,asns where (sas=asnum) and (sas
in (2911, 2912, 2913));

‹Dave

From:  branchbunch <branchbunch at gmail.com>
Date:  Wednesday, July 2, 2014 at 9:26 PM
To:  Jesse Bowling <jessebowling at gmail.com>
Cc:  Argus <argus-info at lists.andrew.cmu.edu>
Subject:  Re: [ARGUS] Name associated with ASN in label?

I was just dealing with that exact issue a couple of days ago, and I didn't
see any native support for ralabel grabbing the AS name from MaxMind's
GeoIPASNum.dat file in the same way that it grabs the AS number.  That would
be a really nice feature.

For the time being, what I did to incorporate AS names was to create a MySQL
table of AS numbers and names, push my ralabel results to another MySQL
table and then do a MySQL join query to get what I'm after.  Here are the
details

Fetch the latest ASN data from MaxMind and create/update an ASN name lookup
table in MySQL.  


# Create argus database and asns table if they don't already exist
mysql -e 'create database if not exists argus;'
mysql argus -e 'CREATE TABLE IF NOT EXISTS asns (asnum INT NOT NULL PRIMARY
KEY, asname TEXT);'
cd /tmp
rm -f GeoIPASNum2.zip GeoIPASNum2.csv
wget 
http://download.maxmind.com/download/geoip/database/asnum/GeoIPASNum2.zip
unzip GeoIPASNum2.zip
grep -v "[0-9],AS[0-9]" GeoIPASNum2.csv | cut -d\" -f2 | sort | uniq | cut
-c3- | sed 's/ /,"/;s/$/"/' | sort -n > /var/lib/mysql/argus/import.csv
mysql -e 'delete from asns;'
mysql argus -e "load data infile 'import.csv' into table asns FIELDS
TERMINATED BY ',' ENCLOSED BY '\"';"
rm -f /var/lib/mysql/argus/import.csv

Label some argus record(s), feed them into MySQL, and then join them with
the asname.

ralabel -r test.arg -f /etc/ralabel.conf -w labelled.arg
rasqlinsert -r labelled.arg -w mysql://root@localhost/argus/argusoutput -m
none -M drop -s saddr,sas,bytes -s -record
mysql argus -e 'select saddr,sas,asname from argusoutput,asns where
sas=asnum;'


+---------------+--------+------------------------------------+
| saddr         | sas    | asname                             |
+---------------+--------+------------------------------------+
| 110.77.193.19 | 131090 | CAT TELECOM Public Company Ltd,CAT |
+---------------+--------+------------------------------------+


I also use the same basic approach for adding full country names alongside
the country codes that ralabel pulls from the delegated-ipv4-latest file,
which I'd be happy to share about if there is interest.  If you have ralabel
use the GeoLiteCity.dat file, you could also get a full country name put in
the label field, but I was wanting to get that name into a structured field
by itself.


On Wed, Jul 2, 2014 at 4:41 PM, Jesse Bowling <jessebowling at gmail.com>
wrote:
> Before I start reaching around my elbow to solve this on my own, I thought I
> ask if it¹s possible to have the name associated with an ASN included in the
> label field using the argus clients...Currently I¹m using ralabel to include
> the source and destination AS in records for aggregation purposes, but when
> viewing the output it would be nice to include the name associated with the
> ASN, potentially in the label field. For instance, if I have an source ASN of
> 15169, I¹d like to see: sas="GOOGLE - Google Inc.,US² in the label field.
> 
> Is this currently possible and I¹m not thinking of the way to get this in
> there?
> 
> Cheers,
> 
> Jesse



-------------- next part --------------
An HTML attachment was scrubbed...
URL: <https://pairlist1.pair.net/pipermail/argus/attachments/20140703/3587fe21/attachment.html>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: smime.p7s
Type: application/pkcs7-signature
Size: 5811 bytes
Desc: not available
URL: <https://pairlist1.pair.net/pipermail/argus/attachments/20140703/3587fe21/attachment.bin>