PowerShell: When Best Practices and Accurate Results Collide
I'm a big believer in trying to write my PowerShell code to what the industry considers to be the best practices as most are common sense anyway, although as one person once told me: "Common sense isn't all that common anymore".
I would hope that even the most diehard best practices person would realize that if you run into a scenario where following best practices causes the results to be skewed, that at least in that scenario it's worth taking a step back so you can see the bigger picture. I recently encountered a couple of these scenarios which I'll demonstrate in this blog article.
Scenario 1: You want to find all users in the NorthWind Active Directory OU (Organizational Unit)
whose department property does not have a value equal to Sales
.
1Get-ADUser -Filter {Department -ne 'Sales'} -SearchBase 'OU=Northwind Users,OU=Users,OU=Test,DC=mikefrobbins,DC=com' -Properties Department |
2Select-Object -Property Name, Department
In the previous example, I've followed the best practices by filtering left and while everything looks correct, unfortunately there is some sort of filtering going on behind the scenes that the filter parameter is performing which skews the results.
This time, I'll grab all of the users in the NorthWind OU and pipe them to the Where-Object
cmdlet to perform the filtering:
1Get-ADUser -Filter * -SearchBase 'OU=Northwind Users,OU=Users,OU=Test,DC=mikefrobbins,DC=com' -Properties Department |
2Where-Object Department -ne 'Sales' |
3Select-Object -Property Name, Department
Notice that although you would think that both of these commands in the previous two examples would
return the same results, the ones without a Department are somehow filtered out by the first command
even though their department is not equal to Sales
.
Scenario 2: You want to return a list of SQL Server users from the default instance on a server named SQL01 where the password expiration policy is not enabled:
1$SQL = New-Object –TypeName Microsoft.SqlServer.Management.Smo.Server -ArgumentList 'sql01'
2$SQL.Logins |
3Where-Object {-not $_.PasswordExpirationEnabled} |
4Select-Object -Property Name, LoginType
Those results look plausible, but unfortunately they're inaccurate. As we can see in the following
results, the PasswordExpirationEnabled
property is a Boolean:
1$SQL.Logins |
2Get-Member -Name PasswordExpirationEnabled
Conventional wisdom would lead you to believe that the valid values for a Boolean are true or false but it's that assumption that if something isn't true, it must be false that leads to inaccurate results in this scenario.
By adding the PasswordExpirationEnabled property to our results, we can see where the logic problem occurs:
1$SQL.Logins |
2Where-Object {-not $_.PasswordExpirationEnabled} |
3Select-Object -Property Name, LoginType, PasswordExpirationEnabled
Based on the previous results, you can see that the value can also be null. The problem can also be
seen in the GUI since it's not possible to set the PasswordExpirationEnabled
option in SQL for a
Windows user:
To get accurate results, we'll need to see if the value is false because not true could be false or null:
1$SQL.Logins |
2Where-Object {$_.PasswordExpirationEnabled -eq $false} |
3Select-Object -Property Name, LoginType, PasswordExpirationEnabled
As you can see, those results are accurate based on the value of the PasswordExpirationEnabled
property.
µ