I needed to access 75 mailboxes across multiple mailbox databases and didn't want to grant myself full access permissions to them one at a time so i went searching for a solution. This nifty command granted me access to every mailbox in the company:
Get-MailboxDatabase | Add-ADPermission -user <myusername> -AccessRights GenericAll -InheritanceType All
I found a variation of this command in a technet forum post available here and modified it to give myself access to all mailboxes, and set inheritance.
HTH
Tuesday, January 17, 2012
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:
SELECT
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:
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:
SELECT
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.
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.
Notes:
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
<!DOCTYPE service-group SYSTEM "avahi-service.dtd">
<service-group>
<name replace-wildcards="yes">Aastra self-configuration on %h</name>
<service>
<type>_aastra-cfg._tcp</type>
<txt-record>protocol=tftp</txt-record>
</service>
</service-group>
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
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.
Notes:
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
/etc/avahi/services/tftp.service
This is the config file that tells avahi-daemon to listen for aastra phones and tell the phone to use tftp to boot./etc/avahi/services/tftp.service:
<?xml version="1.0" standalone="no"?><!DOCTYPE service-group SYSTEM "avahi-service.dtd">
<service-group>
<name replace-wildcards="yes">Aastra self-configuration on %h</name>
<service>
<type>_aastra-cfg._tcp</type>
<txt-record>protocol=tftp</txt-record>
</service>
</service-group>
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.
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:
HKCU\Software\Microsoft\ExchangeServer\v14\AdminTools\NodeStructureSettings
"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."
HKCU\Software\Microsoft\ExchangeServer\v14\AdminTools\NodeStructureSettings
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:
$m->setReturnPath('bounceXXXXXXXX@bounces.saasbouncer.com')
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:
<?PHP
$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
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.
<?PHP
$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'");
}
?>
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:
$m->setReturnPath('bounceXXXXXXXX@bounces.saasbouncer.com')
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:
<?PHP
$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.
http://www.saasbouncer.com/reports/csv.php?acctid=XXXXXXXX&apikey=XXXXXXXX&reportdate=2011-10-05
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.
<?PHP
$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'");
}
?>
Subscribe to:
Posts (Atom)