Cleaning up Magento 1.9 Disk Session Storage

When you choose to store Magento user sessions via file rather than DB, you may run into disk space consumption issues after a while. One resolution is to clean out older session files periodically.

The following commands have proven helpful.

Get the count of session files older than 5 days.

find /var/www/html/var/session/ -mtime +5 | wc -l

Delete session files older then 5 days.
find /var/www/html/var/session/ -mtime +5 -exec rm {} \;

Two Ways to Clean Email from ERP Database

I recently worked with some data from an ERP which stored customer’s email addresses like so:
Goofy right. Anyway, here are a couple of methods of cleaning up the data and returning only the first part of such a string.

STUFF(b.Email, PATINDEX('%#%', b.Email), ( LEN(b.Email) - PATINDEX('%#%', b.Email) + 1 ), '') 'Email2'
SUBSTRING( b.Email, 1, PATINDEX('%#%', b.Email) - 1 ) 'Email3'

Cleaning SQL Columns for Export

Several times a year I’m asked to run some queries and export the results to Excel, CSV or Tab delimited files.

If you’ve ever done this you may have run into bad exports due to line ending or tab characters in the DB columns. I’m always forgetting how to clean these up so here’s a reminder to me.

REPLACE( REPLACE( REPLACE( CAST( <column> AS VARCHAR(MAX) ), CHAR(13), ' ' ), CHAR(10), ' ' ), CHAR(9), '' ) AS '<column>'

Manual Install / Upgrade OpenSSH on Ubuntu

Recently I updated OpenSSH due to a PCI scan failure. We had OpenSSH 6.6 running on an Ubuntu 14.4.3 server. As of this writing OpenSSH is at 7.0 but has not been packaged for Ubuntu Aptitude yet.

UPDATE 08/23/2016: I just ran through this process again to install OpenSSH 7.3p on an Ubuntu 14.04 LTS box. Same commands worked without issue. One thing I did do different this time was uninstall the OpenSSH included with Ubuntu by running apt-get remove openssh-server. This guaranteed there was no conflict when installing from source.

This means I need to do a manual install of OpenSSH portable. It was pretty simple for me since I could use the default configuration. Your mileage may vary.

$ apt-get remove openssh-server
$ wget http://.../OpenSSH/portable/openssh-7.0p1.tar.gz
$ tar -zxvf openssh-7.3p1.tar.gz
$ cd openssh-7.3p1
$ ./configure
$ make
$ make install

To test this you can try connecting to local host to see the ssh version returned.
$ ssh -v localhost