IP Address Type in 1 Minute

SCRIP

Machbase provides IPV4 type for IPv4 address, IPV6 for IPv6 address and custom operator to support IP address.

Step 1 : Group with scrip and check the list of top 10 scrip. Check the count by the equal condition search.

[mach@localhost ~]$ machsql -s localhost -u sys -p manager
=================================================================
     Machbase Client Query Utility
     Release Version 3.5.0.826b8f2.official
     Copyright 2014 Machbase Inc or its subsidiaries.
     All Rights Reserved.
=================================================================
MACHBASE_CONNECT_MODE=INET, PORT=5656
Type 'help' to display a list of available commands.


Mach> SELECT SRCIP, COUNT(*) FROM SAMPLE_TABLE GROUP BY SRCIP ORDER BY 2 DESC LIMIT 10;
SRCIP           COUNT(*)
----------------------------------------
96.128.212.177  13594
173.149.119.202 13546
219.229.142.218 13537
69.99.246.62    13511
239.81.105.222  13501
86.45.186.17    13487
231.146.69.51   13483
248.168.229.34  13472
105.9.103.49    13472
115.18.128.171  13468
[10] row(s) selected.
Elapsed time: 0.322


Mach> SELECT COUNT(*) FROM SAMPLE_TABLE WHERE SRCIP = '96.128.212.177';
COUNT(*)
-----------------------
13594
[1] row(s) selected.
Elapsed time: 0.192

Step 2 : User SRCIP to obtain the number of ranges less than a specific IP address.

Mach> SELECT SRCIP, COUNT(*) FROM SAMPLE_TABLE WHERE SRCIP < '50.0.0.0' GROUP BY SRCIP ORDER BY SRCIP;
SRCIP           COUNT(*)
----------------------------------------
5.114.66.53     13315
5.238.208.30    13184
6.32.167.53     13375
7.234.88.67     13314
16.0.123.104    13357
16.194.51.72    13424
20.137.26.161   13393
28.197.127.42   13068
31.224.72.52    13290
32.98.52.241    13206
46.201.48.18    13384
[11] row(s) selected.
Elapsed time: 0.260
Mach>

Step 3: Search for network mask using CONTAINED and CONTAINS operators.

mach> SELECT COUNT(*) FROM SAMPLE_TABLE WHERE SRCIP CONTAINED '100.195.159.0/24';
COUNT(*)
-----------------------
13097
[1] row(s) selected.
Elapsed time: 0.184

mach> SELECT COUNT(*) FROM SAMPLE_TABLE WHERE '100.195.159.0/24' NOT CONTAINS SRCIP;
COUNT(*)
-----------------------
986903
[1] row(s) selected.
Elapsed time: 0.269

Create, Insert and Retrieve

Machbase provides IPV4, IPV6 data types and custom operator to support IP address.

Step 1 : create table with using IPV4 and IPV6 data types.

CREATE TABLE IP_TUTORIAL

(
    addr4   IPV4,
    addr6   IPV6
);

Step 2 : insert data into a table.

INSERT INTO IP_TUTORIAL VALUES('127.0.0.1', '::127.0.0.1');
INSERT INTO IP_TUTORIAL VALUES('127.0.0.2', '::127.0.0.2');
INSERT INTO IP_TUTORIAL VALUES('127.0.0.3', '::127.0.0.3');
INSERT INTO IP_TUTORIAL VALUES('127.0.0.4', '::127.0.0.4');
INSERT INTO IP_TUTORIAL VALUES('127.0.0.5', '::127.0.0.5');
INSERT INTO IP_TUTORIAL VALUES('192.0.0.1', 'FFFF::192.0.0.1');
INSERT INTO IP_TUTORIAL VALUES('192.0.10.1', 'FFFF::192.0.10.1');
INSERT INTO IP_TUTORIAL VALUES('192.128.0.1', 'FFFF::192.128.0.1');
INSERT INTO IP_TUTORIAL VALUES('192.128.99.128', 'FFFF::192.128.99.128');
INSERT INTO IP_TUTORIAL VALUES('192.128.99.64', 'FFFF::192.128.99.64');
INSERT INTO IP_TUTORIAL VALUES('192.128.99.32', 'FFFF::192.128.99.32');
INSERT INTO IP_TUTORIAL VALUES('192.128.99.16', 'FFFF::192.128.99.16');
INSERT INTO IP_TUTORIAL VALUES('192.128.99.8', 'FFFF::192.128.99.8');
INSERT INTO IP_TUTORIAL VALUES('192.128.99.4', 'FFFF::192.128.99.4');
INSERT INTO IP_TUTORIAL VALUES('192.128.99.2', 'FFFF::192.128.99.2');
INSERT INTO IP_TUTORIAL VALUES('192.128.99.1', 'FFFF::192.128.99.1');
INSERT INTO IP_TUTORIAL VALUES('255.255.255.255', 'FFFF::255.255.255.255');

Step 3 : retrieve data from machbase

$ machsql
=================================================================
     Machbase Client Query Utility
     Release Version 3.5.0.826b8f2.offcial
     Copyright 2014 Machbase Inc. or its subsidiaries.
     All Rights Reserved.
=================================================================
Machbase server address (Default:127.0.0.1) :
Machbase user ID  (Default:SYS)
Machbase User Password :
MACH_CONNECT_MODE=INET, PORT=5656
Type 'help' to display a list of available commands.
Mach> DESC IP_TUTORIAL;

Mach> SELECT * FROM IP_TUTORIAL;

Mach> SELECT * FROM IP_TUTORIAL WHERE ADDR4 = '127.0.0.3' OR ADDR6 = '::127.0.0.2';

Mach> SELECT * FROM IP_TUTORIAL WHERE ADDR4 < '127.0.0.3';

Mach> SELECT * FROM IP_TUTORIAL WHERE ADDR4 CONTAINED '192.0.0.0/16';

Mach> SELECT * FROM IP_TUTORIAL WHERE '192.128.99.0/24' CONTAINS ADDR4;

Mach> SELECT * FROM IP_TUTORIAL WHERE '192.128.99.0/24' NOT CONTAINS ADDR4;

Mach> SELECT * FROM IP_TUTORIAL WHERE ADDR6 CONTAINED 'FFFF::192.0.10.0/120';

results matching ""

    No results matching ""