Monthly Archives: February 2014

Pfsense behind a router

Ok so here’s my setup: - current adsl / cable router - router subnet

1. Install pfsense

2. Assign interfaces
1 for lan
1 for wan

Plug the WAN port into you ADSL / cable router
Plug the LAN port into eg. an un-managed switch.

3. Assign IPs
Subnet -
Gateway -
Enable DHCP
IPRange - to

WAN IP - (static)
Gateway -

4. Configure web front end
Go to (pfsense box) in your browser
Login using : admin pfsense
DNS1 :
DNS1 :
Allow router to override DNS
Allow 192/172/10 addresses (b/c pfsense is running on your lan)
(Click next, next, next)
Set the web gui password

5. Configure your router (cable/adsl modem)

Set a rule to route:

Go to the routers firewall rules and add this …
Ports : 1-65534
Protocol : ALL
Destination :

Or alternatively add the server to your DMZ

6. Load up your web browser and check you can get

7. Enjoy … take a break and come back and install some ubercool packages.

Trouble shooting:
Can pfsense ping router – NO WAN config error
Can pfsense ping pfsense client – NO – LAN config error / Client firewall
Can pfsense client ping pfsense – NO – LAN config error / Client firewall
Can pfsense ping – NO – ASDL/CABLE router config error
Can pfsense client ping router – NO – NAT error
Can pfsense client ping – NO – NAT error / ADSL / CABLE config error
Can pfsense client ping – YES – All good
Can pfsense client load a website – NO – DNS Error – Check everything above is OK
Can pfsense client load a website – YES – Everything is working

If you are having issue make sure you disable any firewalls you have running on the pfsense client boxes … once you have your network configured you can enable them later.


SQL Tip : Maximum date via left join

Selecting records by their maximum date via a left join.

select * from maintable m

left join (

select id
, max(somedate) as mydate
from sometable
group by id
, mydate

) s on =

Whats going on?

1.) select * from maintable m
Select all the records from your base table.

2.) left join
Bring back all matching records

3.) select id, max(somedate) from sometable group by id, mydate
Select the id and date field you wish query the group by is needed to only bring back one record per id and because we are using an aggregate function the sql would error without it

4.) s on =
Here’s where the magic happens: select * from customersales left join (select id, max(lastsaledate) as dtmDate from customersales group by id, dtmDate) sales on = voila you have all the most recent customer sales and a join away from all the customer information. You could also use an inner join to only bring back customers with sales and only their most recent sale.

select * from customersales
inner join (select id, max(lastsaledate) as dtmDate from customersales group by id, dtmDate) sales on =
inner join customer c on = customersales.customerid

Connecting to a serial device over usb in debian / linux

First of all you’ll need a USB to serial cable, mine came with a driver disk but I didn’t use it. I’m running debian jessie and it detected it without any issues. If you want to check to see the device is working and detected just run lsusb and look for your device in the list.

Look for something like this
Bus 003 Device 003: ID 063b:2837 Prolific Technology, Inc. PL2303 Serial Port

Next check the device is accessible run the following:
sudo ls /dev | grep USB

Look for something like this …

Next check you have screen installed by running
sudo apt-get install screen

Connect to the device over serial using :
sudo screen /dev/ttyUSB0

You should now be connected to your device.
This is what I used to configure a HP2626 Pro-Curve switch over serial.
When I first connected I had to hit enter a few times to establish a connection / shell.
Have fun hacking

New members to the blog

Good evening Hakology,

Its with great pleasure I can now introduce you to Jokke, AdvancedNewbie, Hexon and Wh1p are good friends of mine. Over the few years hakology has been running, these guys have helped me out and made a few really cool suggestions for the show. I asked them if they’d be interested posting for the blog, as we’re always discussing stuff on mumble or IRC. Ideas about coding concepts software / hardware and all the usual stuff. They all agreed and will hopefully be making a few submissions as and when they can.

AdvancedNewbie : Electrical boffin, inventor and always coding cool stuff for the web / automation / tools.
Jokke : Works full time developing opensource software and is a networking grandmaster 😀
Hexon : Full time linux admin, networking jedi & D20 qualified.
Wh1p : Cryptography enthusiast, network protocol / communication specialist.

Thanks you guys and welcome aboard.


More surprises soon hehehe