Blog of Andrew Krone

Sorting two columns in a single table using MySQL

2010-03-23T15:31:00.000-07:00

So, someone might say that to sort two columns in a single table using MySQL the statement would look like this:

SELECT * FROM table ORDER BY column1,column2 ASC

This would ONLY sort by "column1" first and then "column2". This is hardly what we're after.

Say you had a column called "time" (that is also built using "time") and a column called "date" (that is built using "date"). If we wanted to truly sort the rows in this table by date AND time we need to do a join like this:

SELECT * FROM (

( SELECT * FROM events ORDER BY date ASC)
UNION
( SELECT * FROM events ORDER BY time ASC)

) AS WOWZER WHERE some_column='some_criteria'";

Let's break it down. First, we start a typical select statement (first line). Then we have a VERY simple join that says I want the date column and time columns joined as a column called "WOWZER". Finally we only want certain criteria for this select.

This is how to truly sort using two columns, unfortunately ORDER BY will not work correctly with just a comma between the two sorts because the items being sorted would be independent of one another.

-Andy

Outlook + Thunderbird + OpenLDAP = Fail

2009-10-22T09:26:00.000-07:00

So, I've tried to build an OpenLDAP directory that can be viewed by both Outlook and Thunderbird clients.

The idea is simple enough, but Microsoft's insistence on re-writing standardized protocols makes the task VERY difficult. This makes sense, why would Microsoft's Active Directory want to be compatible with an Open Source system that's free?

Here's the breakdown, Outlook 2007 searches for the following fields when accessing an LDAP, diregard the first line, that's the connection:

conn=521 op=1 SRCH attr=
cn
commonName
mail
roleOccupant
display-name
displayname
sn
surname
co
organizationName
o
givenName
legacyExchangeDN
objectClass
uid
mailNickname
title
company
physicalDeliveryOfficeName
telephoneNumber
homephone
Telephone-Office2
facsimileTelephoneNumber
mobile
Telephone-Assistant
pager
info

And here's Thunderbird's query:
conn=0 op=1 SRCH attr=
o
company
mail
mozillaUseHtmlMail
xmozillausehtmlmail
mozillaCustom2
custom2
mozillaHomeCountryName
ou
department
departmentnumber
orgunit
mobile
cellphone
carphone
telephoneNumber
title
mozillaCustom1
custom1
mozillaNickname
xmozillanickname
mozillaWorkUrl
workurl
fax
facsimiletelephonenumber
mozillaSecondEmail
xmozillasecondemail
mozillaCustom4
custom4
nsAIMid
nscpaimscreenname
street
streetaddress
postOfficeBox
givenName
l
locality
homePhone
mozillaHomeUrl
homeurl
mozillaHomeStreet
st
region
mozillaHomePostalCode
mozillaHomeLocalityName
mozillaCustom3
custom3
birthyear
mozillaWorkStreet2
mozillaHomeStreet2
postalCode
zip
c
countryname
pager
pagerphone
sn
surname
mozillaHomeState
description
notes
modifytimestamp
cn
commonname

The funny part is the free and open source search by Thunderbird is much more detailed and thorough. Outlook's search on the other hand defies standards. The "Company" field for Outlook is "company" in the LDAP. However this is INCORRECT. The field Outlook should put in as the company is "organizationName" or "o". A newbie reading this might think Microsoft is just being intuitive. Nope, their encouraging their proprietary systems so they cannot interact with open source systems.

As far as I know LDAP has been around a very long time and Microsoft should not ignore the basic schema and standards currently in place. At the very top (the core.schema) the organizationName (or 'o') has been there for a very long time.

Anyway, a work around is to define the 'o' for open source as the same as 'company' for the mega evil giant, Microsoft.

-Andy

Running

2009-07-10T18:36:00.001-07:00

Housing....

2009-07-08T10:07:00.001-07:00

The Great American Bubble Machine:

2009-07-06T14:15:00.001-07:00

The Great American Bubble Machine:

http://www.rollingstone.com/politics/story/28816321/the_great_american_bubble_machine

Peter Schiff

2009-06-10T12:53:00.000-07:00

http://www.ritholtz.com/blog/2009/06/peter-schiff-on-tds/

One Command: "sudo apt-get install ndisgtk"

2009-05-01T18:30:00.000-07:00

Ugh, the online forums are such a waist of time!!! IF you want to install a wireless PCI card on Ubuntu run the above command and download the "inf" driver from the vendor's webpage.

The above command can also help if your Ubuntu system freezes after installing a PCI card. The key is to use the intended driver for any card AND install the driver using NDIS wrappers before installing the PCI card. The generic Linux drivers are just too generic, Windows can't even keep a list of reliable drivers and the hardware manufacturers builf their equipment for Linux!!!!

-Andy

Edicts to Live By

2009-04-20T20:56:00.000-07:00

Ten principles for a Black Swan-proof world

1. What is fragile should break early while it is still small. Nothing should ever become too big to fail.

2. No socialisation of losses and privatisation of gains. Whatever may need to be bailed out should be nationalised; whatever does not need a bail-out should be free, small and risk-bearing. We have managed to combine the worst of capitalism and socialism.

3. People who were driving a school bus blindfolded (and crashed it) should never be given a new bus. The economics establishment (universities, regulators, central bankers, government officials, various organisations staffed with economists) lost its legitimacy with the failure of the system.

4. Do not let someone making an “incentive” bonus manage a nuclear plant – or your financial risks. Odds are he would cut every corner on safety to show “profits” while claiming to be “conservative”.

5. Counter-balance complexity with simplicity. The complex economy is already a form of leverage: the leverage of efficiency.

6. Do not give children sticks of dynamite, even if they come with a warning.

7. Only Ponzi schemes should depend on confidence. Governments should never need to “restore confidence”. Be robust in the face of them.

8. Do not give an addict more drugs if he has withdrawal pains. Using leverage to cure the problems of too much leverage is denial.

9. Economic life should be definancialised. Citizens should not depend on financial assets or fallible “expert” advice for their retirement.

10. Make an omelette with the broken eggs. We need to rebuild the hull with new (stronger) materials; we will have to remake the system before it does so itself.

Bank REO Websites

2009-04-15T11:55:00.001-07:00

IndyMac Bank REO:
http://apps.indymacbank.com/individuals/realestate/search.asp

Ocwen Financial REO:
http://www.ocwen.com/reo/home.cfm

JP Morgan Chase Bank REO:
http://mortgage.chase.com/pages/other/co_properties_landing.jsp

M&T Bank REO:
http://services.mandtbank.com/personal/bank_owned_prop.cfm

Wells Fargo REO:
https://www.pasreo.com/reo/

Washington Mutual REO (WAMU):
http://www.wamuproperties.com

American Home Mortgage REO:
They aren't taking mortgage applications anymore but they are selling real estate, go figure. ;)
http://www.ahmhomes.com

Wachovia REO:
http://reo.wachovia.com/

Washington Mutual REO:
I could not locate any information about their REO.

Deutsche Bank REO:
No information is available online.

SunTrust REO:
SunTrust Mortgage, Inc website claims that all REO is listed with local real estate agents. Below is a link to their REO practices.
http://www.suntrustmortgage.com/reo.asp

First Niagra Bank REO:
The First Niagra website states that REO listings are not currently available but they do list contact information.
http://www.fnfg.com/personal_banking/mortgages/real_estate_owned_sale.asp

Key Bank REO:
No Key Bank REO information is availabe at this time.

Compass Bank REO:
https://www.compassbank.com/appforms/properties/index.jsp

Fannie Mae REO:
http://www.mortgagecontent.net/reoSearchApplication/fanniemae/

Freddie Mac REO:
http://www.homesteps.com/hm01_1featuresearch.htm

HUD REO:
http://www.hud.gov/homes/index.cfm

HomeSales.gov also offers properties offered for sale by the government.

Coldwell Banker REO Division
Coldwell Banker REO Division

Regions Bank Properties
http://realestate.regions.com/servlet/Ore/ForeclosedPropertySearch.jsp

Citibank REO
http://www.citimortgage.com/Mortgage/Oreo/SearchListing.do

SBA Properties
http://app1.sba.gov/pfsales/dsp_search.html

FDIC Real Estate Owned
http://www4.fdic.gov/DRRORE/

Sallie Mae Financial Services Properties
http://www.grpcapital.com/properties/index.html

BB&T REO (Branch Bank and Trust)
http://www.bbt.com/applications/specialassets/search.asp

Beal Bank Commercial REO
http://www.bealbank.com/Content.aspx?ID=13

GRP Financial Services Properties
http://www.grpcapital.com/properties/index.php

Fifth Third Bank REO:
No information is available online.

First Charter REO:
No information is available online.

People's Bank REO
http://www.peoples.com/im/cda/multi_elements/0,,1355,00.html

State Street REO:
No information is available online.

National City Mortgage REO
http://www.ncmcreo.com/

Taylor Bean REO
http://www.taylorbeanhomes.com/

Downey Savings & Loan
http://www.downeysavings.com/bank-owned-properties

Pure Genius

2009-03-20T13:41:00.001-07:00

The Crisis of Credit Visualized from Jonathan Jarvis on Vimeo.

IMG00115.jpg

2008-12-11T13:16:00.000-08:00

Sent from my Verizon Wireless BlackBerry

OpenVPN

2008-12-11T13:04:00.000-08:00

A few more great things about OpenVPN:

1). The whole setup below can be duplicated on a separate server in less than an hour should a server crash

2). The second server does not require additional licensing

3). The UDP protocol can easily be switched to TCP 443 to get through pesky firewalls

4). Vista 64-bit works with the latest OpenVPN GUI

5). Log Files easily show all access by username

Two Factor Authentication for Free

2008-12-11T12:32:00.000-08:00

RSA is a ripoff, they're following in the footsteps of Microsoft and unfortunately many companies believe RSA is the only way to secure their network.

They're wrong, and here's why:

OpenVPN + Server Cert/Key + Pam Authentication Module = FREE two factor authentication.

First, let's define two factor authentication: It's nothing more than two requirements when you logon, tunnel or gain access to a network. For example you have a "pincode" or "password" that never changes. In addition you might have a key chain "token" that is constantly changing. Access requires both, and because the key chain token is always changing it becomes very difficult for the wrong person to gain access.

Little background on industry: The Citrix Access Gateway is probably the industry standard at providing a single factor authentication gateway for a small company. I'm guessing most companies concerned with security have something very similar. The gateway or "CAG" sits behind the companies firewall and accepts authentication requests. That's the first factor, the second is generally a token like system requiring users to carry around a silly key chain. Why? If a user has a private certificate of authority AND a username/password access is still two factor.

What absolutely amazes me is that there are open source applications that can provide 2 factor authentication for FREE.

Here's what a working OpenVPN server config looks like using PAM:

proto udp
;proto tcp
port 1194
dev tap
tls-server
dh /etc/openvpn/easy-rsa/keys/dh1024.pem
ca /etc/openvpn/easy-rsa/keys/ca.crt
cert /etc/openvpn/easy-rsa/keys/server.crt
key /etc/openvpn/easy-rsa/keys/server.key
duplicate-cn
#Define the ip address for the tap0 virtual device
server 10.8.0.0 255.255.255.0
ifconfig-pool-persist /etc/openvpn/ipp.txt
#route to be established on the server
route-up "route delete -net 10.8.0.0/24"
route-up "route add -net 10.8.0.0/24 tap0"

#Allow Clients to talk to one another
client-to-client

#Push the same ping to the server....
push "ping 10"
push "ping-restart 60"
push "route 172.16.4.0 255.255.255.0" #route to another subnet
push "route 172.16.5.0 255.255.255.0" #route to another
push "route 172.16.51.0 255.255.255.0" #route to one more
push "route 172.16.8.0 255.255.255.0" #route to and another
push "route 172.16.81.0 255.255.255.0" #route to some place
push "route 172.16.70.0 255.255.255.0" #route to northern US
push "route 172.16.33.0 255.255.255.0" #route to more north
push "dhcp-option DOMAIN companyname.com" #push the DNS domain suffix

comp-lzo
status-version 2
status /var/log/openvpn-status.log
verb 5
# Keep tunnel open with ping every 10 Seconds, restart ever 120 Seconds
keepalive 10 120

plugin /usr/share/openvpn/plugin/lib/openvpn-auth-pam.so login
client-cert-not-required

I'm going to skip a whole bunch of steps to get this working (see openvpn.org) but if the server is running with the above configuration (with no errors) you'll have a two factor authentication: 1). Using the signed Certificate of Authority and 2). The username/password on the Linux box access by the openvpn-auth-pam.so module above.

Win XP/Vista/32bit/64bit Client Setup:

1). Install OpenVPN GUI v1.0.3
2). Load the ca.crt and client.opvn file.

Here's an example of the client.ovpn file:

client
dev tap

;proto tcp
proto udp
remote 1.2.3.4 1194 (1.2.3.4 should be the public ip of the server)
resolv-retry infinite
nobind
persist-key
persist-tun
ca ca.crt
comp-lzo
verb 3
mute 20
auth-user-pass

Save yourself $10k or $20k, learn OpenVPN.

-Andy

The Economy....

2008-09-30T11:42:00.001-07:00

The Big Picture:
bigpicture.typepad.com

Chris Martenson:
http://www.chrismartenson.com/

Jesse's Cafe Americain:
http://jessescrossroadscafe.blogspot.com/

Clusterfuck Nation:
http://jameshowardkunstler.typepad.com/

Create a command prompt loop Linux

2008-07-08T12:47:00.000-07:00

Example:

[root@servername~ ]# while [ 1 ]
> do
> date;
> df –h |grep backup;
> sleep 600;
> done

This creates a loop that checks the disks mounted to the directories with the name “backup” and displays the date every ten minutes. This is useful to see the progress of a very large sync command on a ten minute basis. Obviously it can be changed for virtually any command.

Note: the “while [ 1 ]” basically means the while is always true, thus the “sleep 600” is required. Otherwise you would have an infinite loop just going crazy.

You can kill the output with ctrl-c

-Andy

Backuppc: Moving the pool

2008-06-25T09:35:00.001-07:00

Here are the instructions to create a dual disk system using backuppc. This approach creates a "copy" of the pool data without RAID. I have two external USB drives, both 500 Gig here. The advantage (I think) of this method is that if a mistake is made or a disk crashes no rebuilding is needed, just a copy. Also, the second disk can be removed for safe keeping, all we need is a replacement disk (no rebuilding)

1). Stop backuppc:
/etc/init.d/backuppc stop

2). Sync the current default location to your new mount point excluding "pc" (USB device = /data1):
rsync -avPH --exclude=pc/ --delete /var/lib/backuppc/ /data1/backuppc/
(This creates all the data for backuppc in /data1/backuppc, the "H" preserves hard links)

3). Move the old location to a new file, just in case:
mv /var/lib/backuppc /var/lib/backuppc_old

4). Change the /data1 mount point in /etc/fstab to mount /var/lib/backuppc, here's the line:
before: /dev/sdc /data1/backuppc ext3 defaults 0 0
after: /dev/sdc /var/lib/backuppc ext3 defaults 0 0

5). If there's a second disk, setup a crontab with an rsync:
rsync -aPH --exclude=pc/ --delete /var/lib/backuppc/ /data2/backuppc/ >/dev/null 2>&1

6). Reboot and verify the disk(s) are mounted correctlty

7). Start backuppc, /etc/init.d/backuppc start

8). After a few days if all is working remove /var/lib/backuppc_old

Note: Rather than creating a symbolic link from /var/lib/backuppc to /data1/backuppc I opted to create a static mount point in /etc/fstab. This is more straightforward. When someone runs "df -k" they'll see the giant mount point at /var/lib/backuppc and hopefully notice that it's a USB device.

Formatting USB: You may also have to format the USB drive with "mkfs.ext3 /dev/sdc". The device can be determined pretty easily with the Logical Volume Management Tool available in the GNOME GUI.

Further Note: Changing /etc/BackupPC/conf.pl and some of the files like "/usr/lib/BackupPC/Lib.pm" so the top directory is hard changed is NOT recommended. This is because someone else may update the backuppc package and the topDir will break.

-Andy

Coinfiguring a Sylog Server on Linux

2008-05-13T11:28:00.000-07:00

Simple stuff, just no good resource out there:

1). Modify the line in /etc/syslog.conf from this:
*.info;mail.none;authpriv.none;cron.none /var/log/messages

To this
*.info;mail.none;authpriv.none;cron.none;local0.none /var/log/messages

2). Add the following two lines to the bottom of /etc/syslog.conf, the file “specialfilename” is the name of the new log:
# Firebox logs
local0.* /var/log/specialfilename

3). Modify /etc/syconfig/syslog so the”SYSLOGD_OPTIONS” look like this:
SYSLOGD_OPTIONS="-r -m 0"

4). Restart the syslog server:
[root@yourserver etc]# /etc/init.d/syslog restart
Shutting down kernel logger: [ OK ]
Shutting down system logger: [ OK ]
Starting system logger: [ OK ]
Starting kernel logger: [ OK ]

2008-04-23T08:50:00.000-07:00

This message was sent using the Picture and Video Messaging service from Verizon Wireless!

To learn how you can snap pictures and capture videos with your wireless phone visit www.verizonwireless.com/picture.

To play video messages sent to email, QuickTime® 6.5 or higher is required. Visit www.apple.com/quicktime/download to download the free player or upgrade your existing QuickTime® Player. Note: During the download process when asked to choose an installation type (Minimum, Recommended or Custom), select Minimum for faster download.

2008-04-21T09:23:00.000-07:00

This message was sent using the Picture and Video Messaging service from Verizon Wireless!

To learn how you can snap pictures and capture videos with your wireless phone visit www.verizonwireless.com/picture.

2008-04-16T08:36:00.000-07:00

This message was sent using the Picture and Video Messaging service from Verizon Wireless!

To learn how you can snap pictures and capture videos with your wireless phone visit www.verizonwireless.com/picture.

2008-04-11T13:18:00.001-07:00

This message was sent using the Picture and Video Messaging service from Verizon Wireless!

To learn how you can snap pictures and capture videos with your wireless phone visit www.verizonwireless.com/picture.

Removing Duplicate Lines from a text or Excel file

2008-03-25T10:56:00.000-07:00

Removing duplicate lines on a file is easy. Although, not sure how to do it with vi or notepad++ or crimson editor.

Here's how to do it in Excel:

1). Open the text file in Excel
2). Create a new macro
3). Cut and paste the procedure below into the macro
4). Run the macro
5). Relish how easy things are with Google

Public Sub DeleteDuplicateRows()
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' DeleteDuplicateRows
' This will delete duplicate records, based on the Active Column. That is,
' if the same value is found more than once in the Active Column, all but
' the first (lowest row number) will be deleted.
'
' To run the macro, select the entire column you wish to scan for
' duplicates, and run this procedure.
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

Dim R As Long
Dim N As Long
Dim V As Variant
Dim Rng As Range

On Error GoTo EndMacro
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

Set Rng = Application.Intersect(ActiveSheet.UsedRange, _
ActiveSheet.Columns(ActiveCell.Column))

Application.StatusBar = "Processing Row: " & Format(Rng.Row, "#,##0")

N = 0
For R = Rng.Rows.Count To 2 Step -1
If R Mod 500 = 0 Then
Application.StatusBar = "Processing Row: " & Format(R, "#,##0")
End If

V = Rng.Cells(R, 1).Value
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Note that COUNTIF works oddly with a Variant that is equal to vbNullString.
' Rather than pass in the variant, you need to pass in vbNullString explicitly.
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
If V = vbNullString Then
If Application.WorksheetFunction.CountIf(Rng.Columns(1), vbNullString) > 1 Then
Rng.Rows(R).EntireRow.Delete
N = N + 1
End If
Else
If Application.WorksheetFunction.CountIf(Rng.Columns(1), V) > 1 Then
Rng.Rows(R).EntireRow.Delete
N = N + 1
End If
End If
Next R

EndMacro:

Application.StatusBar = False
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
MsgBox "Duplicate Rows Deleted: " & CStr(N)

End Sub

2008-03-25T09:39:00.001-07:00

This message was sent using PIX-FLIX Messaging service from Verizon Wireless!
To learn how you can snap pictures with your wireless phone visit

www.verizonwireless.com/getitnow/getpix.

To learn how you can record videos with your wireless phone visit www.verizonwireless.com/getitnow/getflix.

To play video messages sent to email, QuickTime® 6.5 or higher is required. Visit www.apple.com/quicktime/download to download the free player or upgrade your existing QuickTime® Player. Note: During the download
process when asked to choose an installation type (Minimum, Recommended or Custom), select Minimum for faster download.

2008-03-24T07:55:00.001-07:00

This message was sent using PIX-FLIX Messaging service from Verizon Wireless!
To learn how you can snap pictures with your wireless phone visit

www.verizonwireless.com/getitnow/getpix.

To learn how you can record videos with your wireless phone visit www.verizonwireless.com/getitnow/getflix.

To play video messages sent to email, QuickTime® 6.5 or higher is required. Visit www.apple.com/quicktime/download to download the free player or upgrade your existing QuickTime® Player. Note: During the download
process when asked to choose an installation type (Minimum, Recommended or Custom), select Minimum for faster download.

Yum not working

2008-03-20T08:23:00.000-07:00

Yum should work out of the box: but it doesn't. On CentOS (Linux) you may see the following:

[root@yourservername etc]# yum update apcupsd
Loading "installonlyn" plugin
Setting up Update Process
Setting up repositories
Reading repository metadata in from local files
Could not find update match for apcupsd
No Packages marked for Update/Obsoletion

Add these to "/etc/yum.repos.d/CentOS-Base.repo":

[extras2]
name=Fedora Extras - $releasever - $basearch
baseurl=http://download.fedora.redhat.com/pub/fedora/linux/extras/6/$basearch/
gpgcheck=0
gpgkey=http://download.fedora.redhat.com/pub/fedora/linux/extras/RPM-GPG-KEY-Fedora-Extras

NOW, try it again:

[root@servername etc]# yum install apcupsd
Loading "installonlyn" plugin
Setting up Install Process
Setting up repositories
extras2 100% |=========================| 1.1 kB 00:00
Reading repository metadata in from local files
primary.xml.gz 100% |=========================| 1.7 MB 00:11
################################################## 5211/5211
Parsing package install arguments
Resolving Dependencies
--> Populating transaction set with selected packages. Please wait.
---> Downloading header for apcupsd to pack into transaction set.
apcupsd-3.12.4-5.fc6.i386 100% |=========================| 15 kB 00:00
---> Package apcupsd.i386 0:3.12.4-5.fc6 set to be updated
--> Running transaction check

Dependencies Resolved

=============================================================================
Package Arch Version Repository Size
=============================================================================
Installing:
apcupsd i386 3.12.4-5.fc6 extras2 405 k

Transaction Summary
=============================================================================
Install 1 Package(s)
Update 0 Package(s)
Remove 0 Package(s)

Total download size: 405 k
Is this ok [y/N]: y
Downloading Packages:
(1/1): apcupsd-3.12.4-5.f 100% |=========================| 405 kB 00:02
Running Transaction Test
warning: apcupsd-3.12.4-5.fc6: Header V3 DSA signature: NOKEY, key ID 1ac70ce6
Finished Transaction Test
Transaction Test Succeeded
Running Transaction
Installing: apcupsd ######################### [1/1]

Installed: apcupsd.i386 0:3.12.4-5.fc6
Complete!