How to find a needle in the Array stack

By Jesse Hamrick • June 16th, 2009

I finally had a reason to use Ying Li’s script that compares two arrays and I must tell you it worked beautifully… What a time saver!!! The big boss comes to me (as I’ve become his go-to-guy when he needs something done quick) and asks if I could create an email distribution list which contains 1000 users. I said “no problem” do you have the list of users? He presented me the list and told me it is of the utmost importance that everyone on this list receives an email that will be distributed at the end of the week. Again we say, “NO Problem!!!”

This list was in excel format and contained columns for Last name, First name, and email address. Since I required a property that would be unique to each user, I extracted the email addresses to a text file calledDistEmails.txt. My first step was to write a script that would locate each user in Active Directory and add them as a member to the Distribution list.

# Search AD using email addresses to get Disgtinguished Name (DN)
$ColEmail = Get-Content “C:\MyScripts\DistEmails.txt”
$Search = New-Object DirectoryServices.DirectorySearcher([ADSI]“”)

foreach ($objEmail in $ColEmail){

$Search.Filter=”(&(objectClass=User)(proxyAddresses=smtp:$objEmail))”
$objUsers = $Search.FindAll()
foreach ($objUser in $objUsers){$ADPath = $objUser.Path.Remove(0,7)}
foreach($Item in $ADPath)
{
$Mgroup=[ADSI]“LDAP://CN=DistGroup,DC=Domain,DC=Com”
$Mgroup.Member.add($Item)
$Mgroup.setinfo()
}
}

The code above takes my list of email addresses, uses an LDAP filter to get the DN of each user, then adds each user to the Distribution Group. So here is when the fun began, remember what the boss said? Of utmostimportance that each person receives the email. First, I verified that the list did have 1000 user entries in it (counted excel spread sheet rows). Second, I needed to verify that there are 1000 members in the Distribution Group. How did I do this???

Wrote a script of course…

$Search = New-Object DirectoryServices.DirectorySearcher([ADSI]“”)
$Search.Filter=”(memberOf=CN=DistGroup,DC=Domain,DC=com)”
$objUsers = $Search.FindAll()
$ObjUsers.Count

Code uses an LDAP filter to search for members of the Distribution Group, saves the results to the $objUsers array, then I just use the count property for the number of entries in the array. Note: the array count starts with 0 not 1.

If my count property would have came out to 999 then I would have been home free, nothing is easy as my count was 989. Oh boy! How to I find Ten needles in a hay stack of 1000 users? Any thought of doing this manually by referencing the excel file and ‘Active Directory Users and Computers’ is out-of-the-question.

I remembered posting one of Ying Li scripts in the script library that should do the trick. All I needed where two files to do a comparison on. One file I already had which was the DistEmails.txt file I used in the script to add group membership. The second file had to be a list of the email addresses of the current 990 members of the Distribution list. Time for some more code…

$Search = New-Object DirectoryServices.DirectorySearcher([ADSI]“”)
$Search.Filter=”(memberOf=CN=DistGroup,DC=Domain,DC=com)”
$objUsers = $Search.FindAll()
foreach ($objUser in $objUsers){$colProps = $objUser.Properties
foreach($objProp in $colProps){$Items = $objProp.proxyaddresses}
$Items >> C:\MyScipts\GroupEmail.txt
}

Now I have a text file Called GroupEmail.txt. The next step I had to do with the file was open it in WordPad and use “replace” to remove the “SMTP:” portion of the email address so that the entries match the addresses in the DistEmail.txt file.

Time to compare the files to see which email addresses are missing.

$arrFirst = Get-Content “C:\MyScripts\DistEmails.txt”
$arrSecond = Get-Content “C:\MyScripts\GroupEmail.txt”
New-Item “C:\MyScripts\FirstInSecond.txt” -Type file
New-Item “C:\MyScripts\FirstNOTinSecond.txt” -type file
foreach($First in $arrFirst){
if($arrSecond -contains $First)
{Add-Content “C:\MyScripts\FirstInSecond.txt” $First}
else
{Add-Content “C:\MyScripts\FirstNOTinSecond.txt” $First}
}

Each file is placed in an array. We then create two new text files. We compare the two arrays and depending on the result we write the array entry in either the FirstInSecond.txt or the FirstNOTinSecond.txt file. Opening the FirstNOTinSecond.txt yields the results of our ten missing email addresses. Investigating the issue I found that the user accounts were still valid, just their email address had changed format. I added the missing ten users to the distribution group (yes, I did it manually via ADUC) and provided the boss with a list of users that are members of the group, all 1000 of them…

Email This Post To A Friend Email This Post To A Friend

Comments

I have to verify group sync against control files on a scheduled basis for certain process here and this is what i do:

get the current email addresses from the members of my DL and out put the results to a file: Current.txt

Get-DistributionGroupMember dl-xxxxx | get-mailbox | select primarysmtpaddress >> c:\work\current.txt

Then compare that output file to my Control file: Address.txt

diff $(get-content C:\work\Address.txt) $(get-content C:\work\current.txt)

The format of the address must match, it will detect spaces as a difference if there are trailing spaces after the address.

diff – as you can see does compare the array contents.
I know you can compare the output of the first command to the input file.. I just haven’t spent the time trying to isolate the key from the value of the array of hashes with a one liner.

I am wondering if you have a script that that takes a list of usernames and determines if those usernames are still in AD. and returns a list of just the names that are still in AD?

any help would be appreciated.

By Jesse Hamrick on July 16th, 2009 at 4:09 pm

Dean,
I am assuming your list of usernames are the SAM account name of the user and that the list is a text file with each username entered on their own line, separated by a carriage return.
CODE:
# ==================================================================
$Users = Get-Content “C:\NameOfFile.txt”

$Search = New-Object DirectoryServices.DirectorySearcher([ADSI]“”)

foreach($User in $users){
$Search.Filter=”(&(objectClass=User)(sAMAccountName=$User))”
$objUsers = $Search.FindAll()
$objUsers
}
# ===================================================================

That will return the users who are in AD.

Hope that helps,

By Automation System on July 19th, 2011 at 12:39 pm

I have a file with ItemNumber and Count
I have another file with range of ItemNumbers with corresponding Itemtype
Example: input.csv file looks like
ItemNumber,Count
1221,10
1543,14
4321,21
5411,15
5123,20
2412,19

helper.csv file looks like
start,end,type
0,1000,TypeA
1001,2000,TypeB
2001,3000,TypeC
3001,4000,TypeD
4001,5000,TypeE
5001,6000,TypeF

Output.csv is expected to be
Type,Total
TypeF,35
TypeB,24
TypeE,21
TypeC,19

How can I do this effeciently if helper file has about 111800 rows and the input file has about 5000 rows.
I tried using do while loops looping inside helper file for each row in input file and that took more than 6 hours to complete. While in the loop, the script checks if(($itemnumber -ge $start) -and ($itemnumber -le $end))
it’s almost like finding needle in a haystack, except for I have 5000 needles and my haystack is more than 100,000 rows. Any help would be appreciated. Thanks.

I know this was posted a long time ago, but it seems like a more efficient method of removing the “SMTP:” portion in the GroupEmail list be to run the following:

foreach($Item in $Items){$Item = $Item -replace “$SMTP:”, $null}

prior to writing to the file. This would eliminate the need to manually edit information, and while trivial in this case, could mean the difference between being able to schedule a task to run completely on its own or requiring additional user input to complete.

Also, your guides and information have been extremely helpful in learning how to operate efficiently with Powershell! It’s certainly helping me to bring a skillset to the table that my team did not already have in force.

Sorry, that was supposed to be “^SMTP:” (^ being the character for a “begins with” search), not “$SMTP:”

It is far easier to merge the two lists, sort, put in a text file, and then use the GNU Win32 uniq command.

C:\> uniq -c AddressList.txt > Completed.txt

Use column mode on the text editor of your choice and remove the count of duplicates. This can be done in under 30 seconds if uniq is already installed.

For the Automation System problem with two files, it is best to go with a true scripting language(e.g. Python) rather than a scripting language designed specifically for Windows system administration.

Bottom line is to use the correct tool for the job, PowerShell is a great language for system administration tasks such as the script that I’m currently working on for the on-call staff to find and restart stopped services. I would not dream of using either Perl or Python for that. As IT professionals, we’re called on for doing our job quickly and efficiently; and not to get fixated on one solution.

Regards,

 

Leave a Comment

« | Home | »