Tuesday, November 22, 2011

How to Dedupe a SQL Database Table

We had some log files get parsed twice this morning and accidentally fed the same data in to our table twice due to a race condition.  Removing the extra records was fairly simple, I started out simply trying to find the duplicate rows and expanded from there to remove the extra records.

calllogid is the primary key in the table
uicallid is a row that _should_ be unique but is not because of data from a legacy system being imported in to the table

The logs that were imported twice were identical in every way except for the calllogid, and I chose to match on uicallid because that is a unique value that should never be duplicated.

I started out trying to get the calllogid of every record that was duplicated and I used a subquery to do this:
count(uicallid) as count,
( SELECT TOP 1 CallLogID from dbo.tablename as sub where sub.uicallid = main.uicallid) as id 
FROM dbo.tablename as main 
group by main.uicallid
having count(main.uicallid) > 1 

Manually checking the output I was able to see how many times each record was duplicated and each record from this morning showed up twice, just as i expected.  Once I was confident I had the correct data selected I asked a coworker for help and I was taught you could do a delete where fieldname IN (<a subquery>).  

Now the final query looks like this:

DELETE FROM dbo.tablename
WHERE CallLogID IN ( select
( SELECT TOP 1 CallLogID from dbo.tablename as sub where sub.uicallid = main.uicallid) as id 
FROM dbo.tablename as main 
group by main.uicallid
having count(main.uicallid) > 1 )

The query to delete the duplicate records ran on the 5.5 million row 1GB table in 15 seconds, and it took 3 seconds to do the select to find them.  Not tooo bad.  If there was more than one duplicate entry this query would only delete one of them, so the query would need to be run multiple times to delete multiple entries.

Wednesday, November 9, 2011

Autoprovision Aastra SIP Phones using mDNS

If you are unable to change the DHCP options on your DHCP server to allow for Option 66 there is a relatively undocumented alternative, mDNS (bounjour).  There is a blurb about it in the manual which tells you this exists, but it doesn't give you any direction, and there isn't much about it on the internet.  I managed to get it to work and am simply posting my solution, hopefully it works for you.

This is the snippet about mDNS in the Aastra Admin Guide:
The IP phones can perform an auto-discovery of all servers on a network using
mDNS. When the IP phone discovers a TFTP server, it is automatically
configured by that TFTP server.
An unconfigured phone (phone right out of the box) added to a network, attempts
to auto-discover a configuration server on the network without any end-user
intervention. When it receives DHCP option 66 (TFTP server), it automatically
gets configured by the TFTP server.
An already configured phone (either previously configured by auto-discovery or
manually configured) added to a network, uses its predefined configuration to
boot up.
1. Configuration parameters received via DHCP do not constitute
configuration information, with the exception of a TFTP server.
Therefore, you can plug a phone into a DHCP environment, still use the
auto-discovery process, and still allow the use of the TFTP server
parameter to set the configuration server.
2. DHCP option 66 (TFTP server details) overrides the mDNS phase of
the auto-discovery. Therefore, the DHCP option takes priority and the
remaining process of auto-discovery continues.
3. As the phone performs auto-discovery, all servers in the network
(including the TFTP server), display in the phone window. However, only
the server configured for TFTP automatically configures the phone.

There isn't any other information to be found, and I spent a lot of time trying to get this feature to work.  My final solution was to use avahi-daemon to do be the mDNS responder.  One thing to note is if there is a DHCP server sending out option 66 that will override this and the phone will listen to the DHCP option 66 instead.

The installation and configuration is incredibly simple.  On Debian I installed the daemon and the utilities.  I did this directly on my phone system in order to create an all in one type of appliance where I didn't need access to the DHCP server or be at the mercy of the configuration options available in that device.

apt-get install avahi-daemon avahi-discover avahi-utils 

Next, we need to configure avahi for the Aastra phones.  We need to create
This is the config file that tells avahi-daemon to listen for aastra phones and tell the phone to use tftp to boot.


<?xml version="1.0" standalone="no"?>
<!DOCTYPE service-group SYSTEM "avahi-service.dtd">
<name replace-wildcards="yes">Aastra self-configuration on %h</name>

And that's it.  Reboot your phone and it should auto discover the tftp server and grab the aastra.cfg and mac.cfg files from there.

I've tested this with the following phones:
Aastra 6757i
Aastra 6739i
Aastra 6731i

Thursday, November 3, 2011

Infocus 3114 Projector Review

Scroll to the bottom for an update
Our old projector died when somebody tuned it off without letting it go through the cool down cycle so we decided it was time to find a new projector.  We wanted something bright that would allow us to throw a large image in our training / conference room that would be visible with the lights on, which our old projector could not do.  We found the Infocus 3114 which is quite bright at 3,500 Lumens and seemed to fit the bill so we ordered it.

We ceiling mount our projector and the process was fairly straightforward.  The bracket that held the old one up had the right layout to also hold the new one so it was simple.  Upon turning it on it was of course upside down, and finding the menu to configure the projector for upside down mode became a chore.  Unfortunately it's not an easy to find option, you have to open settings, and then scroll through several pages worth of options before getting to the Ceiling Mount: On/Off option.  For ease of use I would like to see this option on the first page, or be a question on first boot up along with language.  The ultimate cool factor would be for the projector to be able to figure out if it were upside down or right side up, but that would add additional cost for added hardware, so simplifying the process in software seems acceptable to me.

The projector has a ton of features, if you plug a laptop in to it via USB it can emulate a second display by installing drivers and allowing you to use it without needing the right HDMI/DVI/VGA cable, etc.  This is a feature we have yet to try but are planning to in the near future.  It seems intriguing and if it works well it could be fairly handy.

The projector throws a great image in our training room with all of the lights on, so we're quite happy with it.  Unfortunately a week or so after we got it we noticed it would flicker every once in a while, and then come back.  We figured it was a cabling issue and reseated all the cables but unfortunately it kept doing it, but it wasn't a huge annoyance.  After 45 days or so it did this one day, and then simply shut off.  Upon turning it back on it made an interesting noise and didn't boot up but displayed a couple warning lights on the button array.  A minute later I tried again and it did boot, and worked great for another 10 or 15 minutes before shutting off.  This cycle has been repeated several times now, so I called CDW where we bought it and unfortunately it's been too long so we can't exchange it through them.  I was given the support number for Infocus and sent their way.  I called Infocus and was told 10 to 15 days for them to process and return it, excluding shipping to and from.  No advanced replacement option.  Ugh.

It's 2011, it's a $1,300 business projector, and they don't offer advanced replacement?  What am I supposed to do in my training room for the next month?  Fortunately our CDW sales rep suggested we purchase a new unit from them, RMA the old one, and return the new unit back to CDW once we got the broken unit back (within 30 days to avoid restocking fees).   Thankfully CDW is accomdating and understanding that Infocus is terrible.

I went ahead and tried to submit an RMA, but the form they emailed me me kept saying my email address was invalid and I couldn't submit my RMA.  Turns out the propulated address they used (my gmail, which was entered properly) couldn't be used for some inexplicable reason. "You have entered an Invalid Field Value XXXXXX@gmail.com for the following field: email."  I used a different address and their system decided that address was valid and let me submit my ticket.  I submitted this bug to them, hopefully they fix it.

Finally I got the packing instructions,  but I find it interesting they don't service their projectors in house, they use projector doctors!

Even more interesting they don't insure the projector when they ship it back to you, if you want that you have to contact them for pricing.

**Update 1/17/2012**
It took 4 weeks to get our projector back.  It worked for about a week, and then died again.  We ended up sending our old one back to Infocus directly and they sent us a replacement, although it was delayed a couple weeks because they were on back order.  It was nice of them to replace it directly, but would have been nice had they told me how long it would take.  This may be the last Infocus projector I purchase.  We've owned it somewhere around 12 weeks and has been in our possession and worked for about 1/4 of that.

Infocus apparently changed their replacement policy to send refurbished units rather than repairing your old projector in order to expedite the process but it wasn't done in time to save us from having quite the ordeal.

Saturday, October 29, 2011

Friday, October 14, 2011

Exchange Server 2010 Management Console Can't Connect

After replacing our Exchange Server 2010's Client Access Server (CAS) the Exchange Management Console on our Terminal Server continued to try and connect to the old CAS even though it was cleanly removed from the domain.  Because we used a different name for the new CAS server the tools that had cached the old name could never connect and apparently weren't smart enough to go find the new server.  This is the error I got:
"The attempt to connect to http://server.domain.com/PowerShell using "Kerberos" authentication failed: connecting to remote server failed with the following error message : The WinRM client acnnot complete the operation within the time specified.  Check if the machine name is valid and is reachable over the network and firewall exception for Windows Remote Management service is enabled.  For more information, see the about_Remote_Troubleshooting Help topic."

I never found a key or setting file I could remove to fix it and posted on the technet forums, eventually getting a working solution.  Simply remove the following registry key and your EMC will automatically detect a new CAS and reconnect:

Wednesday, October 5, 2011

Email Bounce Processing with SaaSbouncer

One of my projects was sending a newsletter to its 270,000 member database for the first time, and we decided to use Amazon's Simple Email Service (the basics of that are in this blog post here).  Due to the age of some of the addresses in our membership database we were generating a large number of bounce backs and non delivery reports and we needed a tool to filter our bounces and remove them from our database.  We did some research and found bouncely which seemed like it might work, but I figured I could write a similar tool on my own. And SaaSbouncer was born.

SaaSbouncer was designed to accept all of your bounces and NDR's, process them, and give you access to the data via the API's

Step 1
First, you need to sign up  for the service which is quite simple, simply go to www.saasbouncer.com and register.  You will receive a 30 day free trial (or 500 bounces) by default.  Once you are signed in you will be provided with an email address along the lines of bounceXXXXXXXX@bounces.saasbouncer.com which will be specific to your account.

Step 2
Next, if you are using Amazon's Simple Email Service (SES) you need to verify your saasbouncer bounce address or you will not be able to send emails with this return-path; if you are not using SES you do not need to worry about this.  As per the saasbouncer instructions you can do this with the following command, replacing the address with your custom address:
ses-verify-address.pl -k aws-credentials -v bounceXXXXXXXX@bounces.saasbouncer.com

The verification email will show up in your non bounced messages where you can open it and approve it, or alternatively saasbouncer will generally approve it within a couple hours.

Step 3
The next step is to send your email.  If you are sending via Amazon's SDK for PHP you would do the following to set the returnpath to your saasbouncer account:

If you are using Amazon's SES you have one more advantage: the messageId.  The result of the sendEmail command returns an array, one of which is the messageId.  If you store this messageId in your database along with the email address you sent the message you will get near 100% accuracy with SaaSbouncer.  When saasbouncer receives a message we do our best to try and find the original email address the message was sent to but sometimes this is not possible due to email forwarding or masquerading.  However, the messageId will be available so in the API along with the email address the bounce was received for.  If you match based on messageId you will have near perfect accuracy.

If you are sending directly from PHP your message send function could look something like this, the important part is you set the return-path header:

$headers = "From: " . $from_name . "<" . $from_address . ">\n";
$headers .= "Reply-To: <" . $from_address . ">\n";
$headers .= "X-Sender: " . $from_name . "<" . $from_address . ">\n";
$headers .= "X-Mailer: PHP/". phpversion() . "\n"; //mailer
$headers .= "Return-Path: <bounceXXXXXXXX@bounces.saasbouncer.com>\n";
mail($email, $subject, $message, $headers);

Unfortunately we do not have a unique messageId available to us when an email is sent in this format so we will not be able to track bounces based on messageId, only based on email address.

Step 4
Once you have sent your email you will want to retrieve the information about bounces.  It can take several days for all of the bounces and complaints to come in, but 90% of the bounces will come in within minutes of sending your outbound emails.  There are a couple ways you can get access to the bounce data. 

The most interesting option is the push notification API which sends an HTTP GET request to the URL of your choice.  You can configure and enable this option under account settings, and then the GET request would look something like this: http://yourdomain.com/saasbouncerendpoint.php?email=user@domain.com&messageid=0912512-12512-sadf-124as-12&maileremail=daemon@domain.com&datereceived=2011-12-15%2012:15:00&smtpstatuscode=5.0.0&emailstatus=failed&subject=delivery%20failure

Alternatively from the website you can generate a report using an interface that looks something like this.  You can select what data you would like in your CSV and do whatever you want with it.  

Finally, there is an API which allows you to download a CSV programatically, you use your account id and your apikey in the following URL and specify the day you want data for.  

A good way to use this is to have a scheduled task that grabs this URL once a day (just after midnight), parses the CSV, and then marks the members as having invalid email addresses. 

$file = fopen("http://www.saasbouncer.com/reports/csv.php?acctid=XXXXXXXX&apikey=XXXXXXXX&reportdate=2011-10-05");
while($csvvalues = fgetcsv($file)){
//$csvvalues[0] = email
//$csvvalues[1] = messageid
//$csvvalues[2] = email subject
//$csvvalues[3] = email the bounce was received from
//$csvvalues[4] = date/time the bounce was received and processed
$email = mysql_escape_string($csvvalues[0]);
mysql_query("update member set invalidEmail = 1 where email = '$email'");

Friday, September 30, 2011

Sending email with Amazon SES Email Service from PHP

We recently started using Amazon's Simple Email Service, a feature of Amazon Web Services to deliver messaging to our CIMLS Commercial Real Estate user database of 270,000 members.  We haven't done any form of newsletter or update and now that we've spent the last 8 months redesigning the site and giving it a major face lift we decided it would be a good opportunity for us to say hey, we're back, we're working on the site and provide examples of the many new features we have added.

We decided to use the AWS SES platform to send emails to our users in bulk because it provides statistics on bounce rates, complaints, abuse reports, and a trusted stable bulk mail platform.  Some of our members have been gone for 3 or 4 years and we've found some of them simply file a complaint about the email even though they signed up for us and we include the unsubscribe link in the message.  As we've received each complaint we simply go through and unsubscribe them from future communication from us, no harm no foul.

I downloaded the AWS SDK for PHP and looked at the implementation.  It includes classes for many of the AWS services but I'm only interested in the Simple Email Service, so I went and found the ses.php class and chose to use just that.

The ses class provides many functions and digging through the class helped unravel what all could be done with it, this is a simplified version of the function I wrote to deliver email.

I made one modification to the ses class, if there were an error I wanted it to be returned to me so I could handle it.  On line 335 of ses.php I changed the line from "return false" to "return $rest->error"

Our true code takes a CSV of emailaddress,fullname and will send the email to every user in the CSV.  This is a stripped down example of that so you can use it however you please, but that's why we do the %%USERNAME%% replace and handle errors the way we do.


$ses = new SimpleEmailService("ACCESSKEY","SECRETKEY");
$m = new SimpleEmailServiceMessage();
$m->setReturnPath('bounces@domain.com'); //the return path defines where any complaints or abuse messages will go -- this is important to have configured so you can clean out your mailing list of members that do not want to receive messages
$usermsg = str_replace("%%USERNAME%%",$username,$msg); //we use a token to replace with the users name to make the message more personalized

//setMessageFromString takes the first parameter for a TEXT message, and an 
//optional second for an HTML message.  If you would like to provide both
// formats at the same time that is possible, however it isn't something we have
// been doing so I wrote this little code to handle our use case.
if($_REQUEST['content'] == "HTML")
$result = $ses->sendEmail($m);

//check and see if there is an error, print it to the console.  In most cases
// you probably want to reattempt delivery to any address that errors out, 
//but the throttling error is the only one i've encountered so far.
print("Message not sent to {$name[0]}, {$result['Error']['Code']} - {$result['Error']['Message']}<br />\r\n");
if($result['Error']['Code'] == "Throttling")
$failedemails .= "$to,$username<br>\r\n";
else print("Email sent to {$name[0]}<br>\r\n");
print $failedemails; //i do all of this in a for loop, and spit out a list of emails,names that need to be re-sent due to quota limitations

What I've Learned:
After five days of sending 1,000 emails a day our Max24HourSend quota is now 1360, and our MaxSendRate is no 5. The documentation suggests that it should take 3 days to get to 10,000 but our rate may be increasing more slowly due to higher bounce rates (10 to 17%).  Last night I sent out a large batch of emails to members that haven't been around from 2006-2008 so our bounce rates are pretty high, as are the complaint rates.  I expect that to tail off as we start emailing members that are more current.  

Here's the example output of the ses-get-stats.pl command:
Timestamp               DeliveryAttempts        Rejects Bounces Complaints
2011-09-30T05:33:00Z    414                     0       97      2
2011-09-30T06:33:00Z    67                      0       8       0
2011-09-30T06:48:00Z    631                     0       110     3
2011-09-30T07:18:00Z    93                      0       14      0

Going Forward
I would like to build a daemon to monitor the Amazon SES send quota and then as quota becomes available automatically deliver the appropriate number of emails.  This could help efficiently distribute the email load and make sure that we keep running up the Amazon SES quota.  In order to effectively implement this I want to create an email table that stores all of the outbound emails that are queued up and then let the daemon work through the list automatically, simplifying delivery of email newsletters. 

The documentation I've found has said that it takes several days of maxing out your 1,000 message per day quota before your quota will be raised.  We've been hammering ours for 2 days and haven't seen an increase, even though we've been lightly using our account for the last several months (under 50 per day).

Complaints and Abuse Messages
We have received a few complaints and a few abuse messages for which we have removed those users from our email campaigns.  Even though there is an unsubscribe link in the email people don't use it, probably because they've been trained to never use an unsubscribe link in an email you think is spam.  My guess is they forgot they registered for us and are now treating email from us as unsolicited spam.  It is important to set the returnpath in the email to an account that can receive the complaints and abuse messages, although the email address used must by verified by Amazon SES before it can be used.  Using a tool like saasbouncer to filter your bounces and spam complaints can definitely simplify your life if you are sending out thousands of emails or more.

Thursday, September 29, 2011

MozyPro Isn't So CozyTho (Feature Review)

I help small businesses with their IT needs and this last June I found myself replacing a very costly backup solution for a small law firm. At work I use Microsoft Data Protection Manager and sometimes Symantec Backup Exec, but both of those are overkill and not natively offsite, which was a requirement.  They have a Microsoft Small Business Server 2008 Premium server, and we needed a backup solution to handle the following things:
  1. SQL Server Database
  2. Exchange Server Database
  3. Files
  4. Offsite Backup

After some research and testing I ended up selecting MozyPro to replace the old backup solution because it's very cost effective at $7 per server and 50 cents per gigabyte per month, and it filled all of our requirements.  With approximately 85GB of data the total cost comes out to under $50/mo which is very manageable for the security of an offsite backup.  We started out backing up the majority of our data with it, using VSS for SQL and Exchange and file backups for the rest.

This is where we hit our first bump in the road.  If you select the VSS backup for exchange (which is the way to back it up), and you accidentally select the EDB itself for backup, then the backup will inexplicably fail over and over.  After a few hundred GB of failed attempts at uploading the database I called Mozy and they pointed this out.  Problem solved.  One would think if this were a problem they knew about they would fix  the software to prevent this from happening.  Apparently that would be too easy.

Fast forward to today where I'm finally getting around to testing the ability to restore all of that data to a different server in the event of disaster recovery.  Mozy provides 3 seperate methods to restore your data, direct download, archive, and by media restore.  The final recovery method is in the Mozy client on the protected server, but that's not the focus of this review.

"Direct Download" as they call it allows you to select your files, click direct download, and you install the mozy restore manager which will do the downloading starting within a couple minutes of clicking restore.  We restored 85GB in about 48 hours, although the restore manager client has a bug in it and will stop transferring files saying: "Stopped: need private key"
Mozy support provided me with a beta version of  the restore manager that picked up where the previous version had left off.  The restore speed is quite abysmal, the test was performed in a datacenter with a 100megabit connection.  Watching the progress bar it seemed to move around 200-300K/s much of the time.

Here is the graph of the connection, the restore started sunday night and finished tuesday night:

Archive is where it compresses all of your files and provides links to download them.  When restoring 85gb it took 22.5 hours for it to archive everything, and provided 89 seperate links to download the files.  There is no hierarchy so you need to know where each file goes.  All 128,000 of them.  Good luck!  Archive is better suited for downloading a single file, or a directory.  Archiving 1GB of data in 3 files takes around 30 minutes, although according to the timestamps it took 71 minutes, it only took about 30.

Media Restore is the final option.  In my case restoring 85GB of data it would cost $112.45, and apparently take 2-5 days for processing and then they overnight it to you on DVD's.  If you're in a hurry this isn't a very good option unless you have dialup.  Otherwise you should hopefully be able to download 85GB quicker than that.

The Good
The restore process using the restore manager put most of the files back where they were on the original file system.  The exchange backup worked as I expected it would.

The Bad (Why I Wouldn't Recommend Mozy)
When I went to test the restore of our data I started out restoring 85GB using the Direct Download / Restore Manager. I went to bed and the next morning the download had stopped saying I needed to enter my private key (what private key?).  I did a search and found others had this problem as well,  http://community.mozy.com/t5/Mozy-for-Mac/Private-encryption-key/td-p/13683, so I contacted support and they gave me a beta version of the client which solved the problem.  

Fast forward 36 hours and the restore is complete.  I went to restore the exchange database and it's in a dirty shutdown state which is fairly typical for backup software.  After I used eseutil to repair the database everything there worked fine, I was able to mount the database in a Recovery Storage group and use the powershell to restore mailboxes to a test account.  

Next up I needed to restore the SQL database.  Unfortunately the transaction log was in the restore folder, but the main database file, the MDF, isn't there.  I  found it in the error list in Mozy so I went and selected just the SQL database files using the web interface and redownloaded them.  After several attempts the restore manager gave up saying 3 of 3 files complete, but 2 failed:

Okay, no problem.  I went back and chose the archive option.  Thirty minutes later the web interface showed 1 of 3 files... the other 2 missing, and it says it had recovered 1 of 1 files... I started another chat session (I'm good at this by now), and the guy said odd, did something and half an hour later the archive download works.  I downloaded the files (at 300 to 600K/s), extracted them, went in to SQL Server Management Studio and attempted to attach the database.
For some reason the MDF and LDF files do not match, so the database can't be attached. 

Having failed so far I decided to do one last thing.  Attempt to restore the database on the production server using the VSS restore feature of the full Mozy client from the source server. Yep.  You guessed it.  That failed too.  Why wouldn't it?
I contacted Mozy again and the chat agent escalated this to Tier2 and informed me I should get a response in 24 to 48 hours.  When pressed about how long a response typically takes the answer was "it depends how busy they are", no kidding, thanks genius, that's very insightful.  It's a good thing I'm simply attempting to test the restore process because at this point I'm not certain that our SQL data is protected by Mozy.  If this were a real outage where we needed to restore from backups I would now be 72 hours in to the process, have encountered many errors in the Mozy system, and be expecting a 24 to 48 hour expected response time from a Mozy Tier2 technician.

The next day I bugged Mozy and said I was going to cancel service if I didn't get a response ASAP, 30 minutes later I had a new archive download to try, which actually worked!

As it turns out, the Mozy software does not always take a snapshot of the LDF (Transaction Log) and the MDF (Core Database) at the same time, and if the files aren't a matching set they are useless.

Here is a screenshot they provided showing the timestamps for each file.  I've highlighted each of the rows that resulted in a successful backup by taking a snapshot of both files at the same time.  The rest of the backups are useless.  Good job MozyPro!

Awesome.  I guess I won't be planning to use the Mozy backup software for SQL server anymore as it only works half of the time.  From now on I plan to use the built in SQL Backup, which will create a .bak file on the server, and then let Mozy upload the 1GB file every night.  It's not very efficient because it can't be a differential backup, but it's better safe than lose your data.

Mozy works well for file backups, and exchange backups, but I definitely wouldn't recommend them if you need to do any SQL backups.  As always, you should test your backup system, but given the questionable reliability of the Mozy backups I would recommend testing it several times over the course of a couple days or weeks.  Additionally, Mozy support is very slow to respond taking several hours to respond to any emails even after the Tier 2 tech has finally contacted you.

Test your backups.  DO NOT use Mozy if quick and easy access to your data is important to you.

Update 2/21/2013

I decided it was time to do another test restore to make sure everything was still going okay.  Not so surprisingly the website restore functionality still doesn't work.  It constantly says it does not detect the restore manager software even though it is installed.  Eventually I simply launched the restore manager by hand and logged in, and it started to restore the data.  *phew*.  

Performance seems to be different, it now manages 0-25megabit (on a 35megabit connection), and uses 3 simultaneous streams.  In 3 hours it downloaded 8,714 files for a total of 4.5GB, approximately 4megabit.  The tool gets stuck downloading files sometimes, so the download rate is jumpy.  The tool says it restored 170gb of 118gb (175,000 files), and then crashed.  Checking the size of the restored files reads 78GB, and the Exchange databases were not restored.  Ugh.  What the heck.

I figured after a year and a half my review would be outdated, but it appears as though Mozy still hasn't worked out all of the kinks.  Maybe in mid 2015 they will have gotten all of their stuff together. :)

Wednesday, August 24, 2011

I Was Called an Asshole by a TeleMarketer From Rapid7.

I work in IT (obviously) and we get LOTS of calls from people trying to sell us stuff.  We have told the gatekeeper (our receptionist) that she can tell any and all of them we do not need their services and please stop calling, and that if we do need their services we will seek them out.  Frequently a sales person will call multiple times before getting the hint that our gatekeeper is not going to open the door to the IT department.  She's very good at that.

Sales calls are a part of life, it's how a lot of business gets done.  Good sales people will try a couple times and eventually get the hint that the person they are calling truly isn't interested and will move on to the next record.  It's not uncommon to have to deal with rude receptionists (ours isn't) or irritated people on the other end of the line, it's also part of the job, so it's not necessarily a fun job to have.  However, it is not part of the job to call people names when they don't give you the opportunity to give your sales pitch.  It's your job to move on. 

Unfortunately one pesky agent from Rapid7 apparently doesn't know this, calling for both myself and my boss  several times over the course of a week, and would not take no for an answer.  Lauren was certain that because our  receptionist doesn't know what they do she can't possibly make the decision that we don't need her services.  Our very polite receptionist asked that next time she called I PLEASE take the call so I can tell them we don't need their services.  I did.  This is the story of that call.

8/24/2011, 1:05 PM:
I take the call and Lauren is very chipper and polite, attempting to sound like I'm her best friend (a decent sales tactic, it's much more interesting than monotone).  I immediately go in to my spiel about how we don't need whatever it is that they are selling, and that I would like her to please stop calling as she is annoying our receptionist.  She quickly became defensive and astounded that I would "blow her off" without even knowing who she was or why she was calling and that I would be sorry when in a month or two I needed to do a risk assessment (I doubt I will) and I had blown her off.  I informed her that was exactly what I was doing, and if we needed their services in the future I would happily call her (I won't now), and she would remember me and be happy I called.  She told me that she wouldn't be happy because she doesn't like working with assholes or poor people.  Seriously.  That's exactly what she said.

I said I can definitely be an a-hole sometimes but I have been polite and simply asked you to stop calling, if I was an a-hole I would have simply hung up on you.  At that point she hung up on me.  How rude.

I can't believe a telemarketer who was trying to sell me something called me an a-hole while I was being polite.  

NOW this blog post is me being what she called me. As was the email I sent to every email address listed on their website detailing this call.

Here is the reply I received from Rapid7

Hello Andrew,

Thank you very much for taking the time to get in touch with us so that we can investigate this issue. I can assure you it is certainly NOT our practice to call prospects names nor insult them. 

I’ll speak with the head of our Sales team right away to look into this. 

I can certainly understand the frustration that you’re feeling now but do hope that you’ll give us another chance in the future should you need Vulnerability Management solutions. 


Thursday, August 4, 2011

CloudTC Android Based Executive SIP Phone Unveiling/Review

Update: Panasonic has released their KX-UT670 which is a much better option than the CloudTC phone.  You can read about it here: http://www.andrewparisio.com/2012/05/panasonic-ut670-review.html

In October 2010 CloudTC was showing off their fancy new Glass 1000 Phone at Astricon.  It's a beautiful device with huge potential in the SIP/Asterisk world.  We signed up to get a pair of development units which ended up shipping a little later than they had hoped for but it wasn't the end of the world.  Check out the picture of this thing, it looks quite impressive.

Phone Construction:
The phone is well built, it has a heavy base so it doesn't tip over when you press on it, however in order to press the home button I've found myself holding the back side of the phone with my fingers while pressing the button with my thumb as it requires quite a bit of pressure and sometimes can cause the phone to slide across my desk.  The handset is very light, which some people consider cheap feeling but that doesn't both me.  The handset that shipped with the device tends to fall off of the phone because the notch isn't deep enough, CloudTC said they will be shipping new handsets out that solve this problem as well as some call quality problems (which I honestly haven't noticed).  For an Executive SIP Phone I think this device is fairly well designed, although a case that is more "sexy" might add more appeal for picky executives.   

Phone Configuration:
Configuration of the Glass phone is very simple, the IP address is displayed in the top right hand corner so you simply navigate to that IP in your web browser and configure the SIP settings from there, the default username and password are both admin.  I had mine up and running in a couple minutes.

Phone Features:
Anyway, the device is fairly impressive on paper.  Android 2.1, a 1024x600 9?"  touch screen (not capacitive), PoE (Power Over Ethernet), Bluetooth connectivity, a quality speakerphone and a fairly well designed case.  I quickly plugged it in to my PoE switch and it didn't work.  As it turns out, the PoE feature was removed because  of issues sourcing parts, I guess they should update their documentation page saying the device has PoE.  Oh well, I plugged in the AC adapter and fired the thing up.  The first thing I did was install angry birds on it, which as you can imagine is pretty fun on a giant screen like this.  Without multitouch support you can't zoom in or out, but that doesn't really matter as this isn't an angry birds phone, it's an executive SIP phone.

I grabbed my bluetooth headset and went to connect it to the phone, but there is no way to pair a bluetooth device unless you've used android before and know how to create a shortcut.  I simply tapped and held on the homepage, added a shortcut to bluetooth and managed to pair my device.  The unfortunate part is while it may have bluetooth connectivity apparently it doesn't use it for anything.

Using the Phone as a Phone:
As a SIP phone the device needs to work well as an actual phone, not just be a pretty toy.  I've had a few more dropped calls than I normally experience, but the speakerphone on the device is pretty good on my end although I haven't been on the other end of one of these I haven't had any complaints.  Dialing on the keypad is simple although the dialer tries to format the phone number with parenthesis which is a little bit awkward.  If I dial my extension which is 1593 the phone will display "1 (593" expecting me to dial the next 7 digits of an 11 digit US based phone number.  Currently there is no way to tell the phone what the allowed dial patterns are, if that were added they would probably be able to better format phone numbers.  Hopefully this gets added in the future.

Bug List
  • CallerID does not display the name of the caller, only the number
  • If you hold down the back button for more than 2 seconds you get an overlay of the touchpad driver
    • Fixed in build 1768 (changed to 5 seconds)
  • The phone does not support auto-answer so you can't do intercom calls or callcenter work
  • Google Maps runs out of memory if you use it, i only opened it to test but it quickly crashed
    • As of build 2009 it works for a minute but with the traffic layer on after scrolling around a couple times it says low on memory and turns off the traffic layer, then the entire phone hard locks
  • If you pull down the android notification window you are unable to slide it back up
    • Fixed in build 1768
  • Inbound calls will be rejected if they come from the same number as the phone.  If i call 1593 from another device with the callerid of 1593 it will reject the call
  • The phone dialer is clunky, and the formatting of numbers is very awkward
    • Formatting is fixed, redial added in build 2009
  • The volume controls don't provide a test-tone so you have no idea how loud 10 is until you make a test call
  • The PC Connector software which is supposed to sync your contacts from outlook is broken and won't connect -- they said they don't support this, but it's the only way to get contacts in to your phone from exchange
  • The phone lacks a DND mode
    • Fixed in build 1768
  • The phone has dropped several calls or hung up during a call :(
  • Bluetooth doesn't work
  • There is very little documentation

Final Thoughts:
CloudTC has built a beautiful phone but has left a few things to be desired in the execution.  As a new small company I'm hoping they work out the kinks and get this thing moving but they don't seem all that quick to help.  I've been an early adopter before and in most cases companies are generally very appreciative of the support they get and provide early adopters with direct access to developers, generally taking feedback and incorporating it while providing users what they need to accomplish their goals in a give and take relationship.  In fact I've gotten free devices, free software, and features I needed from companies before while helping them introduce new products to market.  I'm not sure who else is developing for this device but I'm not going to continue investing my time or energy until the kinks are worked out.  

For a $600 executive SIP phone this device comes up short.  For an Executive that just wants to make phone calls the device is sufficient, but much more advanced use and I think the device is not yet ready for prime time.

Monday, July 11, 2011

Our (Brief) Foray into a Brother HL-2280DW All In One Laser Printer

Update at bottom (7/12/2011)
The Bad:
Can't scan multiple pages in to a single document
Wireless is not so easy to configure (I'm in IT).
Drivers won't be automatically detected (It's 2011 guys)

The Good:
It prints quickly
It scans (one page) quickly, with decent image quality.


We have been printing and scanning quite a bit so decided we would get an AIO.  We wanted a laser printer as an ink toner cartridge that lasts 500 pages won't go far with 30-40 page documents.  That said we spent quite a bit of time looking around and we found the Brother HL-2280 Printer was a small and inexpensive device which takes a Brother TN-450 toner cartridge that provides a decent cost per page.  My girlfriend went to Staples to play with and purchase the said printer and the cashier, the clerk informed her that the starter toner in the printer would only print 20 (twenty) pages before running out, and therefore she should purchase a new toner to go with it; he offered to sell her a TN-420.  Really?  Lying Thieves, thankfully she wasn't suckered in by this gimmick.  For reference, the printer comes with a TN-420 toner cartridge which according to staples.com prints 1,200 pages.  I can only assume that the pimply geek with huge gauge holes in his ears was flat out lying in order to get her to purchase a toner cartridge and improve his sales attach rate.  Also, the two year warranty is only 29.99.  Thanks, we'll pass. I hope it dies before two years is up so it can fill a dumpster somewhere.

Step 1) Plug in, configure wifi (Fail #1)
With the printer at home I plug it in, and turn it on.  Open the menu, go to wifi and use the wifi protected setup menu, which i've admittedly never used.  I choose the option where it gives me a code that i type in to the router, and then the devices pair.  60 seconds later my belkin router says device connected successfully, and the printer spits out a page saying failed.  I tried a couple variations of this and eventually gave up.  Ultimately I connected to the printer via an ethernet cable, connected to the web interface with the default username of admin and the default password of password and managed to configure the wireless from there.

Step 2) Connect to computer (Fail #2)
I used the add a printer wizard in Windows 7 and to my delight it was quickly detected.  Except the printer driver wasn't found.  I went to the brother website and download the printer driver, installed it via computer management and I'm off to the races!

Step 3) Print a document (The only successful step)
It prints. Oh, and it can print on both sides / duplex perfectly.

Step 4) Scan a document (Fail #3) (Update below)
I press the scan button expecting the scan to email function similar to other brother AIO's I've used.  It says check connection.  This device can only perform the scanning functions through the brother software.  No problem I say! I download the 130MB software, install it (reboot required... ugh it's 2011), and scan a document.  All of the defaults scan to JPG, including scan to email or file.  I change it to default to PDF and go to scan my document.  As it turns out, this brother All In One HL-2280DW IS NOT CAPABLE of scanning multiple pages in to a single document.  Say you want to scan and email a 2 page document, you will need to scan it in to two separate files and email them separately.  The scanner we had some 12 or 15 years ago was capable of this simple functionality.  Press scan, it scans the document, asks you if there are any more pages, and either scans the next page or finishes with the file.

Step 5) Return for full refund.
Reason stated on return claim: defective.

My Backstory:
I have installed and used multiple brother all in one laser devices and figured I'd give them a shot since at the budget end their cost per page is far lower than the competing HP model (1.7c/page vs 5c/page).  I have no previous hatred of brother until this new device was purchased (and about to become returned).

It turns out it is possible to scan multiple pages. The preferences menu doesn't show the option, but if you right click on the email or file button for example, and go to settings you get almost the exact same menu you find under preferences, but it has a checkbox for manual scan. It does work. The downside is you must return to your computer to press the next page button, and the software on the computer effectively does a new scan job and stitches the two together. This is clearly a piece of equipment meant for scanning one page things, and the software isn't well designed (why does the checkbox only show up on right click -> settings, instead of the main preferences tab you get?

Thursday, July 7, 2011

Embedding a User Editable Google Maps and Street View Control in a Website

We recently added a feature allowing our users to show the Google Street View of their property on our website and there wasn't a lot of documentation about how to do it.  Ideally we wanted to be able to display the street view of a real estate listing so prospective buyers could get a better feel for the property and the surrounding area, and it seemed like a solveable problem.

The final solution ended up looking like this:

In order to accomplish this we needed to do several things.  First, we already know the address of the property that is being entered because as a real estate listing site that is something users enter with their listing info.  Therefore we simply need to use the google maps and google street view API to display the map and street view version to our users so they can confirm the location and angle of the view.  Once they have done this we store the POV information and the location of the property (in case the google approximation is incorrect) in our database to ensure we display the correct info.

Step 1) Display the approximate location for the street view and map controls and allow the user to adjust the view to their liking

var map1;
var panoramaOptions;
var myPano;
var point;
var point1;
var marker1;

//Use the validate form code to set hidden textboxes to the values
//from the streetview Pano, so when the form gets submitted the
//values get passed to the server to be saved
function validateForm(){
this.document.getElementById('pitch').value = myPano.getPov().pitch;
this.document.getElementById('heading').value = myPano.getPov().heading;
this.document.getElementById('svzoom').value = myPano.getPov().zoom;
this.document.getElementById('sv_latitude').value = myPano.getPosition().lat();
this.document.getElementById('sv_longitude').value = myPano.getPosition().lng();
this.document.getElementById('latitude').value = map.getPosition().lat();
this.document.getElementById('longitude').value = map.getPosition().lng();
return true;

function load() {
//set the point for the panoramic
//at this point we don't know the POV info so we set them all
//to 0, the user will have to adjust the POV and we will save
//that information
point = new google.maps.LatLng(66.6666, 66.6666);
panoramaOptions =
pov: {
heading: 0,
//use a div with elementid pano to display the streetview panorama
myPano = new google.maps.StreetViewPanorama(document.getElementById("pano"), panoramaOptions);

var sv = new google.maps.StreetViewService();
var availability_cb = function(data, status) {
//if the streetview service can't display teh location (due to lack of data)
//then we use a bunch of hidden fields to display an error message
//apologizing for the inability to use streetview
if (status !== 'OK')
document.getElementById('sv_enabled').checked = true;
//show the panoramic


//this code displays the normal streetview map and allows
//the user to drag the pushpin to set the proper location
//in the event the google provided position isn't quite accurate
//this map gets put in a div with the id map
map1 = new google.maps.Map(document.getElementById("map"),
center: new google.maps.LatLng(66.6666, 66.6666),
zoom: 15,
mapTypeId: 'roadmap'

point1 = new google.maps.LatLng(

marker1 = new google.maps.Marker({
map: map1,
position: point1,
draggable: true

All of the preceding code creates the following layout in the website:

Step 2) We store the information from the streetview and normal map in our database, that way when we display the listing to users they get the previously-set location.

Step 3) We display the street view control and the normal roadmap view on our site.
var map1;
var panoramaOptions;
var myPano;
var point;

function load() {
//Set up the street view position
point = new google.maps.LatLng(66.6666, 66.6666);
panoramaOptions =
pov: {
heading: 61.4137, //This is the custom POV that was set
pitch:9.02999, //by the user

//use the same divs as before, pano for streetview and map
//for the roadmap.
myPano = new google.maps.StreetViewPanorama(document.getElementById("pano"), panoramaOptions);

//set up the roadmap
map1 = new google.maps.Map(document.getElementById("map"), {
center: new google.maps.LatLng(66.6666, 66.6666),
zoom: 15,
mapTypeId: 'roadmap'
var point1 = new google.maps.LatLng(

var marker1 = new google.maps.Marker({
map: map1,
position: point1



Wrap Up
And once again this creates the final picture:
I've left out some things like storing the POV info in my database as well as how to convert an address in to a Lat/Lng via the google maps API so you'll have to read the API's to figure out how to do that.  This should help you a bit with the street view functionality :)
If you'd like to see it in action this code is what currently drives the street view and property map functionality of all the Commercial Real Estate Listings at www.cimls.com 

Wednesday, June 29, 2011

Asterisk Directory Application Crash With Asterisk Realtime in 1.6.2

I recently discovered that asterisk-addons- has a bug in res_config_mysql that causes asterisk to segfault if you send a user to the Directory() app while using realtime asterisk for voicemail.  This bug is fixed in the asterisk-addons branch of 1.6.2 so be sure to use the latest res_config_mysql.c if you are using asterisk-addons-  The SVN is available here: http://svn.digium.com/svn/asterisk-addons/branches/1.6.2/

I spent a fair amount of time tracing down this crash before attempting to use the latest version out of SVN and discovered that this crash had been patched about a month ago.  I guess the moral of the story is to always test the latest code out of SVN if you are experiencing segfaults.  Not just the latest release, but test against the latest code in SVN as well, it could save you a lot of time debugging.

Asterisk,, Crash With Microsoft Exchange Unified Messaging

We use Unified Messaging to provide mailboxes to our users in Asterisk along with the added features UM provides over the built in Voicemail app, and we recently upgraded from to and found a new bug.  The bug was introduced in, and is fixed in the asterisk 1.6.2 branch in SVN as of 6/29/2011.  The tag has the fix included as well, so be sure to use that if you are planning to use Exchange Unified Messaging with Asterisk.


    -- Called 1593@Exchange2010LCYEX2
Segmentation fault (core dumped)

I didn't bother to do much investigation considering the fix is included in

Wednesday, May 25, 2011

Dynamically Restrict Access to Asterisk SIP 5060 using iptables

We have an asterisk deployment where we have users who work from home so their phones need to be able to connect to asterisk remotely.  In order to limit the number of security threats to our system we lock it down by using the permit and deny settings of SIP Peers to make sure that only those users can get in.

In order to do this for external users we originally allowed every IP to connect, simply relying on using very strong random passwords (40+ character random passwords, unique per extension), and fail2ban to block repeated attempts.  This has been working for us for the last year and a half but I decided to take our security one step further and lock it down to the exact IP of the phone.

We use Aastra 6731i and 6757i devices which have the ability to grab a URL when they boot up, the startup event.  In the aastra.cfg you would have this line:
action uri startup: http://asteriskpbx/aastraphone.php?action=register&ext=$$SIPUSERNAME$$

This tells the phone to grab aastraphone.php at startup.  We use this to keep track of phones, so I decided I would use it to add an addtional layer of IP security to our system.

First, you need to have access to iptables from your web server (or you can pass commands another way, this is a quick and dirty method).

Install & Configure Sudo
apt-get install sudo
add the following lines to /etc/sudoers
#give access to iptables
Cmnd_Alias IPT=/sbin/iptables

#give access to iptables to the account apache is running under, for me www-data
# User privilege specification

Build aastraphone.php
This will insert an allow rule for the given IP at the top of the input chain.  It will not be removed automatically, so you should probably use some mechanism for going through and cleaning up these entries.  A quick and dirty method would be to put the allow rules in a chain and then flush the chain nightly.  The next time the phone polls the web page it would then be re-authorized for access.  Not a great solution, but for 9-5 shops it would work great at midnight.  A more elegant solution would be to track them in a database and check the database every once in a while for IP's that need to be removed.  

Additionally, the realtime version of this is able to check and see if an IP is supposed to be allowed to connect in from the outside world, this solution does not do that it assumes that if the phone knows how to get to the aastraphone.php file that it is allowed to connect externally.  Because of this it is important to have configured the permit and deny options in sip.conf to prevent peers from being used externally if they shouldn't.

$ext = $_REQUEST['ext'];
if($ext > 0 && is_numeric($ext)){
system("sudo /sbin/iptables -I INPUT 1 -s {$_SERVER['REMOTE_ADDR']} -p udp --dport 5060 -j ACCEPT");

Configure IPTables Default Rules
Your system will need to be configured to allow internal hosts to connect, and reject external hosts to connect to your SIP port by default.  Using the following commands should handle this for you.  Using the related/established rule is important so that your communication with your SIP providers stays fully functional.
#by default block all access from the outside work to your asterisk system
iptables -A INPUT -s -d -m state --state INVALID -j DROP
iptables -A INPUT -m state --state ESTABLISHED,RELATED -j ACCEPT
iptables -I INPUT -p udp --dport 5060 -s -j ACCEPT #allow all 192.168.x.x hosts to connect
iptables -I INPUT -p udp --dport 5060 -j DROP #block all others that don't match a rule.  Alternatively you could use the default policy for input drop.
iptables -I INPUT -p tcp --dport 80 -j ACCEPT #allow web traffic for the aastraphone.php file to function

This solution assumes you leave the web server open to the world, as the phones will need to be able to get to that to gain access to SIP/5060.