2

I'm the "script guy" at my work, which is a blessing and a curse... I've been tasked with generating a script of:

Can you create a script to pull all users that have a proxy address that is not an xyz.org email address out of Active Directory.

What would be the best approach? I'm not sure where the best place to look for this is. I'm researching it now, but since I don't know the best terms to use in my searches... It's taking me longer than I hoped to find something useful.

Utilizing this:

%SystemRoot%\SYSTEM32\rundll32.exe dsquery,OpenQueryWindow  

gives me a lightweight search tool that shows the basics of what I want... the only problem is it doesn't have a "like" or "contains" search function. enter image description here

I need the same kinda search... but with the ability to filter out the proxy addresses that don't contain "xyz.org".

1
  • 1
    +1 for blessing and a curse - I know that feeling all too well
    – Joe Taylor
    Sep 28 '11 at 10:46
2

Your Question sounds like a job for the PowerShell.

If you don't have PowerShell installed, grab it here. Windows 7 and Server 2008 R2 have PowerShell already installed.

Get the Quest PowerShell Commands for Active Directory. This extension provides handy commands for scripting all kinds of AD operations.

Now for your query:

Note: you can keep this query much simpler, but I'll stretch it out a bit for better understanding.

# Get all Users and save it to a variable. 
#-SizeLimit 0 returns ALL users, otherwise only 1000 are returned.
# -IncludedProperties proxyaddresses makes sure the proxyaddresses are included

$allusers = Get-QADUser -SizeLimit 0 -IncludedProperties proxyaddresses

# Filter Users without a xyz.org email address and save them to a variable

$filteredusers = $allusers | where {!($_.proxyaddresses -like "*@xyz.org") -and $_.proxyaddresses}

# Sort and output the list

$filteredusers | sort Displayname | format-table Displayname, proxyaddresses -auto


# Other interesting fieldnames you could use with format-table
# GivenName, sn, mail, SamAccountName and many more
# 
# To get a list of all possible field names use: 
# $filteredusers[0] | format-list *
# This will return all fields of the first user saved in $filteredusers
2
  • Spot on. +10 if I could. Now if I could figure out how to filter out null proxyaddresses as well... where {$_.proxyaddresses -neq ""} doesn't seem to filter.
    – WernerCD
    Sep 28 '11 at 14:44
  • i've updated my post. where{$_.proxyaddresses} does the trick
    – mich732
    Sep 28 '11 at 17:35
1

This free AD reporting tool might help as you can generate a list to import into a spreadsheet app for more refining - I use it at work for managing our systems:

AD Info - Active Directory Reporting Tool

A flexible Active Directory reporting tool with over 140 built in reports as well as the option to create your own. With more flexability than other Active Directory reporting tools and a modern user friendly interface, AD Info lets you easily query your Active Directory domain for the information you need. Use one of the 140+ queries that come with the application or use the custom query designer to create your own query based on any attributes you choose.

http://www.cjwdev.co.uk/Software/ADReportingTool/Info.html

2
  • Tool looks nice... I'd hate to have to pay $60 for a one-off query (need a "custom" query, since Proxy Address' aren't provided by that tool)
    – WernerCD
    Sep 27 '11 at 14:54
  • Darn. Sorry about that.
    – Linker3000
    Sep 27 '11 at 16:32
0

using vbscript :

    Set objRoot = GetObject("LDAP://RootDSE") 
    strDNC = objRoot.Get("DefaultNamingContext") 

    Const ADS_SCOPE_SUBTREE = 2

    Set objConnection = CreateObject("ADODB.Connection")
    Set objCommand = CreateObject("ADODB.Command")
    objConnection.Provider = "ADsDSOObject"
    objConnection.Open "Active Directory Provider"
    Set objCommand.ActiveConnection = objConnection

    objCommand.Properties("Page Size") = 1000
    objCommand.Properties("Searchscope") = ADS_SCOPE_SUBTREE 

    objCommand.CommandText = "SELECT givenNames,sn,telephoneNunmber FROM '" & strDNC & "' WHERE objectCategory='user'" & § " And mail != '*@xyz.org'"
    Set objRecordSet = objCommand.Execute
    strResults = ""
    While Not objRecordSet.EOF
        If strResults = "" Then
            strResults = objRecordSet("givenNames") & "," & objRecordSet("sn") & "," & objRecordSet("telephoneNumber")
        Else
            strResults = strResults & VbCrLf & objRecordSet("givenNames") & "," & objRecordSet("sn") & "," & objRecordSet("telephoneNumber")
        End If
    Wend
    objRecordSet.Close

    Set objFSO = CreateObject("Scripting.FileSystemObject")
    Set objFile = objFSO.CreateTextFile("Results.csv", True)
    objFile.Write strResults
    objFile.Close

    MsgBox "Finished. See Results.csv"
0

I had good results using the "AdFind" tool. At least in my organization's AD setup, "proxyAddresses" are returned as one of the standard Attributes, and can then be easily stuffed into a CSV file for later processing, or just grepped to find out anything that does not match xyz.org....

adfind -nodn -default -f "(&(name=ljwobker))" name proxyaddresses

Returns all records with my name (you can wildcard this or remove it for "everyone" and then outputs the "name" and "proxyaddresses" Attributes for that user. This query returns:

[ljwobker:/home/ljwobker]$ adfind -nodn -default -f "(&(name=ljwobker))" name proxyaddresses

AdFind V01.49.00.00cpp Joe Richards (joe@joeware.net) February 2015

Using server: server.xyz.org:389
Directory: Windows Server 2008 R2
Base DN: DC=xyz,DC=org

>name: ljwobker
>name: ljwobker
>proxyAddresses: x400:C=us;A= ;P=XYZ Org;O=Exchange;S=Wobker;G=Lawrence;
>proxyAddresses: X400:C=us;A= ;P=XYZ Org;O=Exchange;S=Wobker;G=LJ;
>proxyAddresses: SIP:ljwobker@xyz.org
>proxyAddresses: smtp:ljwobker@exch.xyz.org
>proxyAddresses: SMTP:ljwobker@xyz.org

So if I grep -v out the xyz.org addresses, I'm left only with the X400 stuff... which strictly speaking meets your criteria for a "non xyz.org" proxyAddress, but in all likelihood you'd also want to filter out and only leave yourself with the stuff that is actually a proxy address from a different org. (I'm assuming that's your actual goal, but the filtering part should be simple enough...)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.