Monday, October 24, 2016

Setting up rocketchat on a UBUNTU 16.04 server

Steps to install on UBUNTU 16.04

1.     Perform the basic install of Unbutu 16.04 server.
Using the default drive sizing settings etc are probably fine, especially if you are testing this.

         Set an appropriate username (ex.  Administrator)
         Set the appropriate country
         Set the appropriate timezone
         Set Hostname (ex Rocketchat)

2.     When installation is completed, server will reboot.

3.     Log into the server and reboot the box again (Weird Ubuntu thing I’ve encountered in the past)
Reboot [enter]

4.     Box restarts, log in and do the following:

5.     sudo apt-get update
6.     sudo apt-get dist-upgrade
(this will take 30 minutes probably)

7.     sudo snap install rocketchat-server
8.    reboot

9.     If you want to hardcode an IP,  eetup IP address by editing the file:  /etc/network/interfaces/
iface eth0 inet static
address x.x.x.x
netmask x.x.x.x
gateway x.x.x.x

10.  Save your changes
11.  Reboot

By default RocketChat uses port 3000 so, if you want to forward standard :80 port, you can do it with this in UBUNTU with this following commands

sudo iptables -t nat -I OUTPUT -p tcp -d --dport 80 -j REDIRECT --to-ports 3000
sudo iptables -t nat -I PREROUTING -p tcp --dport 80 -j REDIRECT --to-ports 3000

Done!  You should now be, by default, http://IPofYourServer:3000 and be prompted by a rocketchat webpage.
Register as a new user, this user will automatically become the Administrator user.
You can go in an resign this permission to other users, so its not permanent.

Should you wish to update it.  These commands will help:

"snap find rocketchat-server" will pull the SNAP repository for the latest version.

"sudo snap refresh rocketchat-server" will update your version to the latest.  Takes a minute or so usually to download, the upgrade is under a minute and users probably will just see some "reconnecting" indications if they are logged in, but usually only takes 10-20 seconds for the service to restart.  Everyone will automatically log back in.

If running the above sudo command gives you an error similar to the following:
bla bla bla assumes unsupported features: snapd2.23 

Try the following command:

sudo apt update sudo apt install snapd

The re-run the snap find/snap refresh commands again. You should be good to go.

Friday, October 21, 2016

Aligning multiple rows of data in excel

Here is how to align two sets of data in excel each set containing multiple rows, but only one common element between the two to use as a 'key'.

In this example the 4 digit ID number is common between the two lists, but the data needs to be transformed from Example 1 to Example 2.

Example 1

What the data needs to look like!
Example 2

The data in column D,E & F in example 1 is now aligned with column A & B in example 2, with all the rows maintaining their relevant position with each other in their rows.

Here's some Excel magic.

First selected the the data that you want to align.  
In this case, Columns A & B are fine, but C,D & E need to be 'merged'.

Select the data in the 2nd group and move it to a new tab in Excel
(second group is circled in Red in example 1)

In the new location, and with all the data still selected, name the range, in the data window circled in red.  This example it is called "MyData"

Back in the original location, put in the following code in column C, D and E in their first row.
Cell C1

Cell D1

Cell E1

Like this:

The data in $b1 will be compared against the table "MyData" in column 1, 2 or 3.

Depending on the matches, you may see data appear or see #N/A, which indicates no match.

Selected the 3 boxes, then auto filled the boxes below with the formula.

All the data is aligned now.  The boxes that have no matches show "#N/A" in excel.

Just note that for this code to work, the first row of data that is in your table needs to be the data you want to match.  

Monday, October 3, 2016

Tether an iPhone to router for Internet access

The ISP providing internet to your office/home network (wired and wireless) is unavailable and you need internet to work again.  The method described here will allow you to have multiple connections through your iPhone's data plan using your existing SOHO router.

To use your iPhone with your router, you will need to utilize a computer that will host the connection between your iPhone and your router.  You can still use this machine for internet access, however it will have limited access to your LAN resources since it is 'outside' the network.

(Mobile Network Data) <-> iPhone <-> Host Computer <-> Router <-> (wired/wireless LAN)
  1. On the host computer, (tested on windows 7 and 10), plug your iPhone into it with the USB cable.
  2. Enable Personal Hotspot
  3. When prompted with this message, select USB ONLY
  4. After 30 secs or so, windows SHOULD recognize this as a device in your computer network settings.
  5. Now select the "Apple Mobile Device Ethernet", hold down the CTRL key and click on the Local Area Connection that coincides with the Ethernet port of your host computer.

  6. Right click on this and then select BRIDGE CONNECTIONS.  You'll see this message appear.
  7. Then you'll see a new network device show up....
  8. Give it a minute or so, but eventually you'll see this:
  9. You'll see the new Ethernet device show up as "iPhone"
  10. (On the host computer, you'll see that the iPhone has provided the new "Ethernet 2" connection an IP of, and the iPhone itself has the ip of  )

  11. Now plug in the host computer into your router's "WAN" port
  12. Depending on the router model/design, you may have some due diligence to allow it to accept this new connection.  The router has to obtain a proper WAN side ip, in our example, it would be an IP of 172.20.10.X

Once your router has obtained the IP from the host computer/iPhone bridge, then your machines that are connected to this router through a wired and/or wireless connection will now have internet access.

Your router LAN IP structure will stay exactly the same, so all your local users should still have access to the same local resources they are used to.  The router will automatically make the internet available to these users.

Remember, all these machines will be using your data plan, so be cautious of usage!