Friday, 21 September 2012

Email/OWA Only Users in Windows

A customer of ours wanted to provide a person of theirs (a subcontractor, i'd assume) with an email box, but no logon rights to any computers on their network. After putting on my thinking hat, this is what came out.

  • Create a Security Group named "Email Only Users Group". If you're on Small Business Server, use the SBS Console (which does a bunch of other magic behind the scenes); if you're on Windows Server Proper, use Active Directory Users and Groups.
  • If you're on Small Business Server, create a User Role from the SBS Console. Call it "Email Only User Role". Make sure that the only Security group assigned to this User Role is "Email Only Users Group".
  • In Group Policy Manager, create a new GPO Object. If you're on SBS, put it in <domain>/MyBusiness/Users/SBSUsers. If you're on Windows Server Proper, put it where-ever you want your Email Only Users to be. Call it "Email Only Users Policy"
  • Edit the Policy thusly
    • From the Scope tab's Security Filtering, remove whatever groups there are there and add Email Only Users Group
    • Edit the policy itself (right-click the policy name and choose Edit...) and enable Computer Configuration > Policies > Windows Settings > Local Policies > User Rights Assignment > Deny log on locally. To avoid typos, use the Browse button in the dialog box to add the security group <domain>Email Only Users Group into the list of groups this policy applies to. This may be an extra step, but i don't want to end up locking out everybody by mistake.
  • Create a new user. If you're on SBS, give this user the role Email Only Users Role. Otherwise, just create the user (i tend to create users on an Exchage server since i then get users created along with their mailboxes) and change the group memberships to that the new user gets (only) the Email Only Users Group. If you have Distribution Groups, you can add those to the user too.

And that's about the size of it. Normal warnings and disclaimers apply :)

Tuesday, 14 August 2012

Check current time over HTTP

Here's a way to check the current time using HTTP:

curl -I | grep "Date: "

This will return a human readable line of text telling what the time is in GMT, in HTTP-Date format. Works on Linux, Cygwin and probably OSX. With a bit of work, this should be portable to Arduino :) [0]

The reason it works is because the web server you're polling (here,, but you can use any server you like) will return the current time and date in its reply.

Thanks to Eddy Ververst for the idea.

[0] The fact that the HTTP-Date format is very permissible on what's allowed and what's not will make this a bit tricky to port to Arduino, but if you stick to a known server and check your results once in a while, you should be okay.

Friday, 27 April 2012

Spring cleaning at the Exchange server

One of our clients had a problem this morning -- mail just wouldn't arrive. The mail server was up, but unfortunately the c:-disk had become so filled that Exchange hit the parking break. I managed to squeeze some air from temporary files and the mail flow continued.

The root problem was that many users have huge mailboxes. As a collective excercise with the customer, i decided to try and hit them with some stats, so that only the people with actually huge mailboxes need be bothered with "clean up your mess"-nags from the administration.

So here's what i did. It's quick and dirty and it's built on mostly manual labour.

Step one - get the numbers 

Connect to the Exchange server. Fire up Exchange PowerShell. Enter the following chant:

Get-MailboxStatistics |ft displayname,totalitemsize,itemcount

Since our customer only has as many mailboxes that can fit one screen, i then selected all the text lines, copied them and pasted them into Excel twenty-ten (or whatever your poison).

Massage the data 

After pasting the lines, click the Data tab and the Text to columns button. The defaults will guess what you want, which is fixed-width data.

After getting your data into three columns, manually remove the "B" symbol at the end of each line. I'm sure you can do that with some nifty scripting, but with only sixty or seventy boxes, a succession of F2-Del-Enter pressings was the fastest. If you have a proper number of mailboxes, it's worth thinking of the automation bits.


Give your columns some nifty headings (Mailbox, size, items). Add a new column. Call it Size in MB and give it the value of the left-neighbour cell divided by (1024*1024). Mark all of your data and choose Home -> Format as a table. Hide the column with size in bytes.

Now select the values in the column Size in MB. Select Conditional formatting -> Data bars -> Gradient fill (go for red ones for effect).

Send to the nice person in the admin at the customer and smile.


Wednesday, 7 March 2012

Disk space alarms for Exchange 2007/2010 and MS SQL Server 2008 using PowerShell

Disks fill up, and when they do, bad things happen. If the data disks for the mail or database servers fill up, mail stops and the production halts. Not good.

Fortunately, it's possible to get an early warning for Exchange 2007/2010 and SQL Server 2008 R2, using PowerShell. Both server products come with their customized PowerShell modules, which you'll need to use to make the magic happen.

Obligatory disclaimer: I am not a coder and i'm a PowerShell neophyte. I'm most likely making stoopid noobie errors here, but at least i get the job done with this.

The first question to have answered when monitoring disk space is which disk volumes should i be watching? The system drive and the drive(s) with the databases or mailboxes, of course. But which disks are they on?

There are two ways to answer that question. Either you go to the Exchange or SQL Server management interface and browse, or you create a script which asks for the information and works from there. As the second option creates a portable solution, that's the path for me.


Let's begin with the Exchange server. The following will list of the names and database files for the mailbox databases on the server:

Get-MailboxDatabase | select Name,EdbFilePath

If you have a slightly bigger organization you'll probably have more than one database, otherwise you'll end up with a single line answer.

Then you iterate through the list to get the drive the database file is on and ask WMI how much space there is free and in total on the disk:

Get-MailboxDatabase | select Name,EdbFilePath | foreach {   $drive = $_.EdbFilePath.DriveName  $disk = Get-WmiObject Win32_LogicalDisk -filter "DeviceID='$drive'"  $free = 100 * $disk.FreeSpace / $disk.Size}

We now need to check whether "free" is below an alarm limit, so add this to the top of the script

$alarm = 10

...and this before the last bracket...

if ($free -lt $alarm) {  $msg = $_.Name + " @ $drive {0:n1}% free" -f $free  $msg}

This would output a warning for each volume containing an Exchange mail store with less than ten percent free space (with one decimal, that's what the {0:n1} stuff does).

If you'd rather have the message go to the Event log, then change the last $msg line to

Write-EventLog -logname Application -source Application -eventID 42000 -entrytype Warning -message $msg

Change the values for -source and -eventID to something which makes sense for your system. I'm no Event log guru, so i do not know what values are sensible or not. Since we use Kaseya VSA on the systems we monitor, my source is KaseyaCollector.

SQL Server

The story is pretty much the same on SQL server, except that you need to run it on the SQL PowerShell interface, sqlps.exe

First, you connect to the database server's management bits and from there on it's much like with the Exchange server script. There is one ugly hack that i'm particularly ashamed of, and that's when i typecast the Root property into a string to strip off the trailing slash from it so that i can use it in the WMI query. I'm sure it's possible to get the information without throwing out the semantics, but i haven't found it yet. Suggestions welcome.

$alarm = 10$srv = New-Object Microsoft.SqlServer.Management.SMO.Server("(local)")$srv.Databases | select Name,PrimaryFilePath | foreach {  $vol = Get-ItemProperty -path $_.PrimaryFilePath | select Root  $did = $vol.Root.Name.substring(0,2)  $disk = Get-WmiOhject Win32_LogicalDisk -filter "DeviceID='$did'"  $free = 100 * $disk.FreeSpace / $disk.Size  if ($free -lt $alarm) {    $msg = $_.Name + " @ $did {0:n1}% free" -f $free    Write-Eventlog -logname Application -source Application -eventID 42002 -entrytype Warning -message $msg  }}

Up next: Automating it all

In a future posting, i'll write how to automate this process. If you're impatient, google how to schedule Exchange 2007/2010 PowerShell scripts. It's not hard but it's tricky.

Friday, 24 February 2012

Sunset-sensitive garden lights with awk, bash and the intertubez

Update: Added code to illuminate the garden in the morning as well.

As the darkness moves later by the day, it was time to change the garden light schedule. As a reminder, my garden lights are controlled by a relay card, which is controlled by a Nanode (an Arduino with an Ethernet) which is controlled by a small Linux plug computer. The Linux box runs cron, which sends http requests over curl to the Nanode, which in its turn switches the lights on or off.

Now the easy way to change the schedule would be to edit the crontab. But that would be uncool, since darkness falls at a different time each day. The nifty way would be to have the lights on only between sunset and sleepy-time. So that's how i wanted it.

Step One: when does the sun set.

There are a few Python scripts out there which return the times of sunrise and sunset. As my tiny Linux box didn't have the proper libraries and it's running an ancient Linux (so it can't be easily updated using apt), i couldn't use that. And after all, why duplicate the effort if the effort's been made elsewhere? And when there's nothing to learn in the process?

The answer can be found at Earthtools, using an URL not quite unlike this:$LAT/$LONG/$DAY/$MON/$TZ/$ISDST

To make this work, you'll need enter the values for LATitude, LONGitude, DAY, MONth, TimeZone and whether it IS DST. 

So we need to add a Step Zero, or actually a multiude of them:

  • Where are we?
  • What date is it?
  • Is it Daylight Savings Time?

The last two questions can be answered with some bash scripting, the date command and a line of awk. Note that as i am quite the awk and bash neophyte, things are probably a bit less elegant here as they would be from a seasoned shell scripter.

MON=$(date +%m)DAY=$(date +%d)TZ=$(date +%:::z)DST=$(date +%Z | grep S)ISDST=0if [ $DST ]; then  ISDST=1fi

This script fails miserably if the character S appears in your time zone, so you'll have to localize a bit before deploying, alright?

To make an educated guess as to where we are, we can either consult an on line mapping service, your friendly GPS or a web service. The web service seed like the most lazy option, and is to be found here:

This will return a HTML file with some funky headers containing your assumed whereabouts. It probably won't be exact enough to deploy missiles to, but good enough for a sunset value.

Now to the parsing bits, which is where the awk knowledge of the Internets way outweighs mine.

First the location. The file name of the location output above is in the variable LOCFN.

LAT=$( awk -F'"' '/name="latitude"/ {print $4}' $LOCFN)LONG=$( awk -F'"' '/name="longitude"/ {print $4}' $LOCFN)

Then the stellar bits.

sunrise=$(awk -F'[<|>]' '/sunrise/ {print $3}' $SUNFN)sunset=$(awk -F'[<|>]' '/sunset/ {print $3}' $SUNFN)ssunrise=$(date -d $sunrise +%s)ssunset=$(date -d $sunset +%s)smorning=$(date -d 06:30 +%s)snight=$(date -d 22:30 +%s)

The first two lines extract the sunrise and sunset from the file, whose name is stored in the variable SUNFN. The next two lines parse that value into seconds since The Epoch, which makes calculations doable. The last two linea are to allow calculation when the garden lights will illuminate in the morning or go out in the evening (this bit was fixed from yesterday's edition).

Like this. No special calculations are done for weekends, which is kind of lazy, but we take care of that later.

if [[ ( ( "$ssunset" -lt "$snow" ) && ( "$snow" -lt "$snight" ) )    || ( ( "$smorning" -lt "$snow" ) && ( "$snow" -lt "$ssunrise" ) ) ]] ;then        /usr/bin/curl http://relaybox/[1-4]/HIGHelse        /usr/bin/curl http://relaybox/[1-4]/LOWfi

My Nanode-connected relay box lives at the address relaybox. Or that's what i'd like you to think when you try to hack my LAN. The relays one through four are set to high if the window of darkness is upon my garden (wooh), otherwise they're drawn low.

Finally, and this is the bit i'm not entirely proud of, an edit of the crontab:

*/2 6-10 * * 1-5 /path/to/sunset/script*/2 16-22 * * 1-5 /path/to/sunset/script

This will hit the script every two minutes in the morning and evening. If the sun rises after ten-oh-oh, the lights will stay on until sixteen-hundred. It's a bug, not a feature.

Soure available upon request.

Sunday, 5 February 2012

Android phones practically illegal for pre-teens?

I came across the Google Account Age Requirements page (which i had no idea even existed). Google says one needs to be over 13 to create a Google account (14 in Spain and South Korea, 16 in the Netherlands). I haven't checked whether these age requirements hold for Google app for domains accounts, but if they do like i suppose they do, we're in trouble. Or more so, i am.

My not-yet-teenage daughter (but surely acting like one at times) has an Android phone and when her brother will get a phone, it'll probably be an Android too. But ack. As a Google account is practically required for an Android phone, how can somebody pre-teen use an Android phone?

I also haven't checked if there are age reqs for .mac and Live! accounts (and if they're vital for iPhone or Windows phone use) but i wouldn't be surprised if they have similar demands.

What options are there for communication and sharing stuff in a secure, child-compatible way, without using a Google account? Set up your own mail server and Diaspora pod? Last time i looked, there wasn't even a Diaspora client for Android, and how the heck am i supposed to get anyone else on Diaspora when i can't even get them on Google+?