Friday Happy Hour: PostgreSQL & mac addresses

by gorthx

Postgres has a datatype just for storing mac addresses. Let’s check it out!


mactest=# CREATE TABLE hosts
(host_id serial,
switch_name varchar(32),
switch_port varchar(10),
mac macaddr);


mactest=# INSERT INTO hosts
(switch_name, switch_port, mac)
VALUES
('sweeyotch01', 'Gi0/1', '0018.b967.3cd0'),
('sweeyotch01', 'Gi0/2', '00:1C:B0:5A:53:80'),
('sweeyotch01', 'Gi0/3', '00:1C:B0:5D:89:89'),
('sweeyotch02', 'Fa0/1', '00-09-7c-be-21-00'),
('sweeyotch02', 'Fa0/2', '00-09-7c-be-11-16')
;

Postgres converts them all to the same format:

mactest=# mactest=# SELECT switch_name, switch_port, mac
FROM hosts
switch_name | switch_port | mac
-------------+-------------+-------------------
sweeyotch01 | Gi0/1 | 00:18:b9:67:3c:d0
sweeyotch01 | Gi0/2 | 00:1c:b0:5a:53:80
sweeyotch01 | Gi0/3 | 00:1c:b0:5d:89:89
sweeyotch02 | Fa0/1 | 00:09:7c:be:21:00
sweeyotch02 | Fa0/2 | 00:09:7c:be:11:16
(5 rows)

But you don’t have to convert to that format to do a SELECT … WHERE mac=[whatever] :


mactest=# SELECT switch_name, switch_port, mac
FROM hosts
WHERE mac='00-18-b9-67-3c-d0';
switch_name | switch_port | mac
-------------+-------------+-------------------
sweeyotch01 | Gi0/1 | 00:18:b9:67:3c:d0
(1 row)

To find macs from the same manufacturer*, we use the trunc() operator, like so:

mactest=# SELECT switch_name, switch_port, mac
FROM hosts
WHERE trunc(mac) = trunc(macaddr '00:1c:b0:5d:89:89');
switch_name | switch_port | mac
-------------+-------------+-------------------
sweeyotch01 | Gi0/2 | 00:1c:b0:5a:53:80
sweeyotch01 | Gi0/3 | 00:1c:b0:5d:89:89
(2 rows)

Expanding upon that slightly:

mactest=# SELECT switch_name, switch_port, mac
FROM hosts
WHERE trunc(mac) =
(SELECT trunc(mac)
FROM hosts
WHERE switch_name='sweeyotch02' AND switch_port='Fa0/1')
;
switch_name | switch_port | mac
-------------+-------------+-------------------
sweeyotch02 | Fa0/1 | 00:09:7c:be:21:00
sweeyotch02 | Fa0/2 | 00:09:7c:be:11:16
(2 rows)

MAC-Manufacturer is a useful set of scripts if you have to match up MACs with their manufacturer.


* the first 3 bytes of the address are usually the manufacturer.

Advertisements
%d bloggers like this: