Faster querying of AD for null fields (July 28, 2016)
For a while now I’ve been running a task that required me to query AD for all accounts that have any value in the ipPhone field.
My first idea was to run the following command.
Get-Aduser -Properties ipPhone -filter 'ipPhone -ne $null'This unfortunately will not work as $null is not recognized. So on I went and just piped the unfiltered output into the where command to filter out the results.
Get-Aduser -Properties ipPhone -Filter * | where {$_.ipPhone -ne $null}Which worked and would have continued to work if I didn’t get annoyed with how slow it was running. On I went to figure out how to speed this up when I realized that I can flip it around. Instead of looking for not null, I could look for containing anything. Which resulted in this query.
Get-Aduser -Properties ipPhone -filter 'ipPhone -like "*"'But why bother? Because like I said, speed. I tested both versions with the Measure-Command cmdlet.
Before:
Days : 0
Hours : 0
Minutes : 1
Seconds : 11
Milliseconds : 620
Ticks : 716205867
TotalDays : 0.000828941975694444
TotalHours : 0.0198946074166667
TotalMinutes : 1.193676445
TotalSeconds : 71.6205867
TotalMilliseconds : 71620.5867
After:
Days : 0
Hours : 0
Minutes : 0
Seconds : 2
Milliseconds : 395
Ticks : 23950138
TotalDays : 2.77200671296296E-05
TotalHours : 0.000665281611111111
TotalMinutes : 0.0399168966666667
TotalSeconds : 2.3950138
TotalMilliseconds : 2395.0138That tiny change shaved 70 seconds off a script. A script that took almost a minute and a half now takes less than 20 seconds.