Posts Tagged ‘unix’
Today, I had the problem of converting a timestamp to a readable time format inside excel. Not quite as easy as I would have hoped for, but the solution is fairly painless.
In the excel column you wish to display the date, you need to place the following formula.
=(((COLUMN_ID_HERE/60)/60)/24)+DATE(1970,1,1)
You would then need to replace COLUMN_ID_HERE with the cell that holds the timestamp. e.g. A1
The above formula can be simplified, but I think the above explains a little more what is actually being done to the data 
You would then need to format the cell to be dd/mm/yyyy hh:mm and you are done
It has now reached the stage where I am logging into 20 – 40 different servers a day, which itself is quite time consuming when you know the server logins stored in your head. However, all our servers have different access passwords and finding each server password takes a little too long (there is no way I could remember all our server logins).
There is a great feature on UNIX machines which will allow you to simply ssh into a server without the password as long as you have configured each machine.
The setup of this is pretty quick, there are only 3 main things you need to remember.
1. Generate the Key on the client box
2. Copy the Key into your clipboard from the client box
3. Enter the Key into the server box from your clipboard
Stage 1: Generating the Key
ssh-keygen -t dsa
By running the above command, you will generate your client key. You will be faced with a few questions, you should just be able to press enter with no answer on each question.
Generating public/private dsa key pair.
Enter file in which to save the key (/root/.ssh/id_dsa):
Enter passphrase (empty for no passphrase):
Enter same passphrase again:
Your identification has been saved in /root/.ssh/id_dsa.
Your public key has been saved in /root/.ssh/id_dsa.pub.
The key fingerprint is:
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
Stage 2: Access the Key
You will need to access the key that was just generated to place on your server so you can quickly access it. First things first, you need to get the key onto the screen or into your email. I would recommend using the screen output to avoid any formatting errors by your mail client.
Outputting onto the screen:
cat ~/.ssh/id_dsa.pub
Sending the output to your email:
cat ~/.ssh/id_dsa.pub | mail email@domain.com
Stage 3: Storing the Key on the Server
Now you have the key, you just need to add it onto the server you are going to be SSH’ing into.
Open up the file ‘~/.ssh/authorized_keys’ using your preferred editor. I usually use nano, so:
nano ~/.ssh/authorized_keys
Then copy the key into the end of the file. You need to ensure that the key stays on one line and you have one key per line.
Once you have saved the authorized_keys file, you should be able to hop back onto the client box and just type
ssh root@hostname
and you will be logged straight into shell on the ‘hostname’ box.
And thats all you need to do
This has saved me soo much time on a day to day basis and probably given me an extra 30 mins > 1 hour a day to get some real work done.
So, its only a few hours away from the timestamp 1234567890 and we just went past 1234543210. As always, sad enough to take a photo (well, Jordan (www.oxcreative.com) took the photo) on a monitor in our office which is displaying www.coolepochcountdown.com. Its only 6 or so hours till all hell breaks out and the time starts going backwards (or we just see a nice timestamp come and go).
