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

accurate-results1.png

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

accurate-results2.png

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

accurate-results3.png

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

accurate-results4.png

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

accurate-results5.png

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:

accurate-results7.png

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

accurate-results6.png

As you can see, those results are accurate based on the value of the PasswordExpirationEnabled property.

µ