This post is to answer one of the many interesting questions that seem to pop up doing Exchange work! This is for one of my favourite customers in Winnipeg. Next time, let’s arrange the work for summertime so we can go golfing Tyler, eh? For some added nostalgia, let’s use Exchange 2007 as the example here as this will likely be one of the last blog posts to feature Exchange 2007 as it goes of extended support in a couple of years from now….
Back to the question at hand. The Exchange team had planned out their storage design and arranged mailbox sizes into different tiers according to the internal chargeback that a department was going to pay for the storage. They stored this in CustomAttribute7 and used Basic, Premium and Ultimate to differentiate the sizes.
All good so far. Until someone started to have some issues spelling Ultimate……
So we wanted to locate all mailboxes that did not have CustomAttribute7 set to either Basic, Premium or Ultimate. Only mailboxes with an empty CustomAttribute7 field or one that was not spelt correctly should be returned so that they could be proactively remediated.
Update 13-4-2015: Title did not publish correctly - edited to update this.
Test Lab Configuration
The below shows the lab configuration. There are multiple mailboxes, some with the correct spelling and some were spelt incorrectly. Additionally, there are mailboxes that have an empty CustomAttribute7 field.
Get-Mailbox | Where-object {$_.CustomAttribute7 –ne””} | Select-Object Name, CustomAttribute7
Get-Mailbox | Where-object {$_.CustomAttribute7 –ne $Null } | Select-Object Name, CustomAttribute7
Queries That Do Not Work
Some of the queries that they tried are shown below. You will note that they return back everything, not just the ones that are blank or not spelt correctly.
Get-Mailbox -ResultSize Unlimited -WarningAction SilentlyContinue | where {($_.CustomAttribute7 -notlike "Ultimate" -or $_.CustomAttribute7 -notlike "Premium" -or $_.CustomAttribute7 -notlike "Basic" -or $_.CustomAttribute7 -notlike $null)} | ft Name,CustomAttribute7
Get-Mailbox -ResultSize Unlimited -WarningAction SilentlyContinue | where { @("Ultimate","Premium","Basic","$null") -ne $_.CustomAttribute7 } | ft Name,Alias,CustomAttribute7
Rather than using the –Like or –NotLike operators, let’s use something slightly different.
Hello, Operator?
PowerShell has multiple comparison operators available to us. To see them take a look at the built in help content. You can see all of them by running Get-Help About_*, and specifically here we want to look at Get-Help about_comparison_Operators. Typically the following operators are considered for tasks such as this post covers, but they each have their own behaviours:
- -Like – looks for a match within a string. Supports wildcards
- -Contains – Intended for arrays, not strings
- -Match – Looks for a match within a string. Supports regular expressions.
By default, all comparison operators are case-insensitive. To make a comparison operator case-sensitive, precede the operator name with a "c". For example, the case-sensitive version of "-eq" is "-ceq". To make the case-insensitivity explicit, precede the operator with an "i". For example, the explicitly case-insensitive version of "-eq" is "ieq".
The –Match operator can be changed to –CMatch to make the operator case sensitive.
The –NotMatch operator can be changed to –CNotMatch to make the operator case sensitive.
Alighty! Since we have the operators at our disposal, let's make use of them to construct our query.
Using Regular Expressions
We want to find all mailboxes that do not have CustomAttribute7 set to the values of Basic, Premium or Ultimate.
We could use something like the below to return the data:
Get-Mailbox | Where-Object {$_.CustomAttribute7 –NotMatch “Ultimate” –And $_.CustomAttribute7 –NotMatch “Premium” -and $_.CustomAttribute7 –NotMatch “Basic” } | Select-Object Name, CustomAttribute7
Note though that it does not show instances such as “basic”, as the search is not case sensitive. “Basic” and “basic” are treated as the same.
That’s pretty good, but how to make it case sensitive? The easiest way is to use the native PowerShell operator. In this case we want the case sensitive version of –NotMatch which is –CNotmatch.
Get-Mailbox | Where-Object {$_.CustomAttribute7 –CNotMatch “Ultimate” –And $_.CustomAttribute7 –CNotMatch “Premium” -and $_.CustomAttribute7 –CNotMatch “Basic” } | Select-Object Name, CustomAttribute7
PowerShell has native regular expression (regex) capabilities, and can also pull in the full .NET range of regex features. This allows us to use standard regex syntax to parse the data.
If we revert to the initial –NotMatch syntax, let’s then add the case sensitivity to the regex query. Remember that the query is case insensitive by default. To make the –NotMatch or –Match operators case sensitive we can add (?-i) to the query, so it looks like this: –NotMatch “(?-I)Ultimate”. The opposite of (?-i) is (?i) which is case insensitive. Adding (?-i) to the start of the regex makes the entire query case sensitive.
The full example would look like:
Get-Mailbox | Where-Object {$_.CustomAttribute7 –NotMatch “(?-I)Ultimate” –And $_.CustomAttribute7 –NotMatch “(?-I)Premium” -and $_.CustomAttribute7 –NotMatch “(?-I)Basic” } | Select-Object Name, CustomAttribute7
It is also possible to make only certain portions of the regex query case sensitive. We can achieve this by inserting multiple instances of the (?-i) and (?i) options.
The below example shows how the matching changes with the different examples. Note the last two examples have different results based on the last character in the string. The last example initially does case sensitive check, which is then negated after checking the first character.
Would love to see what others are doing to get details.
Cheers,
Rhoderick
