Report of all users with a mailbox, mail server name & store name

By taking my lost post we can easily modify that to give us a basic report of all users along with with the mail server their mailbox resides on and which mail store their mailbox is in. Although it's possible to clip the CN= out of the results using vbscript, it's much easier to just import the text file that gets created into excel, select the entire column and do a replace on CN= and simply put nothing in the replace line. This nicely and neatly removes the CN= value allowing you to better sort. You can also clip any uneeded columns in there as well as there will be several that will likely be of no use. When importing the file into excel select both the comma and the semicolon as delimeters. Enjoy!

'***************************************************
'Kris Waters, http://krisdev.blogspot.com
Const ForWriting = 2
Const OpenAsASCII = 0
strOutputFile = "c:\Mail_report.txt"

Set objRootDSE = GetObject("LDAP://rootDSE")
strDomain = "LDAP://"& objRootDSE.Get("defaultNamingContext")

Set objFSO = CreateObject("scripting.filesystemobject")
Set objOutputFile = objFSO.CreateTextFile _
(strOutputFile, ForWriting, OpenAsASCII)

Set objConnection = CreateObject("ADODB.Connection")
objConnection.Open "Provider=ADsDSOObject;"

Set objCommand = CreateObject("ADODB.Command")
objCommand.ActiveConnection = objConnection

objCommand.CommandText = "<"& strDomain &">;(objectCategory=person)" & _
";distinguishedName,cn;subtree"
objCommand.Properties("Page Size") = 6000

Set objRecordSet = objCommand.Execute

While Not objRecordSet.EOF

strUserDN = objRecordSet.Fields("DistinguishedName")
set objUser = GetObject("LDAP://"& strUserDN)

If instr(objUser.homemdb,"CN=") <> 0 then
objOutputfile.writeline objUser.sn & ";" & _
objUser.givenName &";"& objUser.homemdb
End If

objRecordSet.Movenext

Wend

Wscript.Echo "All Done"

objOutputFile.close

For all users that have an Exchange mailbox, write ALL their email addresses to a file

Although this can be done with ldifde, or possibly with csvde, I've never been terribly skillful with either of those utilities. So, this little vbscript will scrub your AD looking for anyone that has a value in the homemdb attribute. This generally means they have a mailbox on an exchange server somewhere, and thus should have an smtp addres in the proxyAddresses value in AD. This script does user accounts only, not contacts, groups, or anything else that might have an smtp address. If you're trying to search your domain for a particular smtp address to see where it's being used I have a script that does that as well, located here.

This is a pretty standard script, it uses a sql type query to gather all user accounts, then does some standard looping to sort out the ones that actually have mailboxes. Since the proxyAddresses attribute is 'multivalued', meaning that it can hold more than one entry, we have to sort through all the possible entries for that value. I also incorporated some string searches to drop the X400 address and any holdover MBX values so we get a nice list of JUST smtp addresses.
'***************************************************

On Error Resume Next
Const ForWriting = 2
Const OpenAsASCII = 0
strOutputFile = "c:\SmtpAdds.txt"

Set objRootDSE = GetObject("LDAP://rootDSE")
strDomain = "LDAP://"& objRootDSE.Get("defaultNamingContext")

Set objFSO = CreateObject("scripting.filesystemobject")
Set objOutputFile = objFSO.CreateTextFile _
(strOutputFile, ForWriting, OpenAsASCII)

Set objConnection = CreateObject("ADODB.Connection")
objConnection.Open "Provider=ADsDSOObject;"

Set objCommand = CreateObject("ADODB.Command")
objCommand.ActiveConnection = objConnection

objCommand.CommandText = "<"& strDomain &">;(objectCategory=person)" & _
";distinguishedName,cn;subtree"
objCommand.Properties("Page Size") = 6000

Set objRecordSet = objCommand.Execute

While Not objRecordSet.EOF
strUserDN = objRecordSet.Fields("DistinguishedName")
set objUser = GetObject("LDAP://"& strUserDN)

If instr(objUser.homemdb,"CN=") <> 0 then
objOutputfile.writeline objUser.displayName

For Each entry in objUser.GetEx("proxyAddresses")

If instr(entry,"X400") = 0 Then
proxy1 = entry

If instr(proxy1, "MBX:") = 0 Then
objOutputfile.writeline proxy1
End If
End If

Next
objOutputfile.writeline
End If

objRecordSet.MoveNext

Wend

objConnection.Close

Set objRootDSE = Nothing
set objConnection = Nothing
set objCommand = Nothing
set objRecordSet = Nothing
set objUser = Nothing
set objOutputFile = Nothing
set objFSO = Nothing

wscript.Echo "All Done"

AD Exchange Mailbox Quota Report

The following vbscript will generate a report of all mailbox enabled users in Active Directory who have the 'Use mailbox store defaults' tickbox UN checked. It will also give you the value, if any, that is set in the 'Issue warning at (KB)' field and the 'Prohibit send at (KB)' fields. Enjoy!

'******************************************************
Set objRootDSE = GetObject("LDAP://rootDSE")
strDomain = "LDAP://"& objRootDSE.Get("defaultNamingContext")

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objOutputFile=objFSO.CreateTextFile("c:\MBQuotas.txt",True)


Set objConnection = CreateObject("ADODB.Connection")
objConnection.Open "Provider=ADsDSOObject;"

Set objCommand = CreateObject("ADODB.Command")
objCommand.ActiveConnection = objConnection

objCommand.CommandText = "<"& strDomain &">;"& _
"(objectCategory=user)" & _
";distinguishedName,displayName,mDBUseDefaults,homemdb,"& _
"mDBOverQuotaLimit,mDBStorageQuota;subtree"

objCommand.Properties("Page Size") = 6000

Set objRecordSet = objCommand.Execute

While Not objRecordSet.EOF

'Make sure there is NO linewrap in the following line
if objRecordSet.Fields("mDBUseDefaults") = FALSE AND objRecordSet.Fields("homemdb") <> "" then

strOutput = objRecordSet.Fields("displayName") & ";" & _
objRecordSet.Fields("mDBOverQuotaLimit") & _
";" & objRecordSet.Fields("mDBStorageQuota")
objOutputFile.WriteLine strOutput
end if
objRecordSet.MoveNext
Wend

wscript.Echo "All Done"

Breckenridge, CO - Christmas 2005

Change AD info for users from File - vbscript, csv

The following script was written for someone who had an interesting situation. He had a list of numerous users within his domain (not contained within a single OU) whose phone numbers and fax numbers needed to be changed. So, what we started with here was a csv file with a samAccountname (NT style login name), the new phone number and the new fax number in a csv file, in the following example format:
ssmith,999-9999,888-8888

The goal was then to have a script read this file and change the phone number (telephoneNumber) and fax number (facsimileTelephoneNumber) accordingly. In case you don't know, having a vbscript read a file isn't a terribly big deal, but it really doesn't do the best job of processing information on a single line. For example, when we have ssmith,999-9999,888-8888 it isn't the easiest thing to say 'hey, there's actually 3 pieces of data on this line'. What we wind up having to do is use the comma (rather painfully) to extrapolate each piece of information out of the single one line string, assign it to a variable and commit the change to AD.

We begin by reading the file into an array, extracting the information we need for each change by manipulating the string, then using NameTranslate to convert the samAccountName (that we got from the file) into a distinguishedName, bind to the object, insert the new information and commit the changes.

Enjoy!


'**************************************************
On error resume next
'written by Kristina L. Waters
'1/24/2006
'**************************************************
Const ADS_NAME_INITTYPE_GC = 3
Const ADS_NAME_TYPE_USER_PRINCIPAL_NAME = 9
Const ADS_NAME_TYPE_NT4 = 3
Const ADS_NAME_TYPE_1779 = 1 'Distinguished Name

strFile = "c:\users.csv"

'**************************************************
'Determine DNS Domain Name
Set objRootDSE = GetObject("LDAP://RootDSE")
strDNSDomain = objRootDSE.Get("defaultNamingContext")

'**************************************************
' Use the NameTranslate object to find the NetBIOS
' domain name from the DNS domain name.
Set objTrans = CreateObject("NameTranslate")
objTrans.Init ADS_NAME_INITTYPE_GC, strDNSDomain
objTrans.Set ADS_NAME_TYPE_1779, strDNSDomain
strNetBIOSDomain = objTrans.Get(ADS_NAME_TYPE_NT4)

'The following removes the \ as in DOMAIN\ '
strNetBIOSDomain=Left(strNetBIOSDomain,Len(strNetBIOSDomain)-1)
'wscript.Echo strNetBiosDomain


Set fso = CreateObject("Scripting.FileSystemObject")
Set usrFile = Fso.OpenTextFile(strFile)

'make File into an Array
aData = Split(usrFile.Readall,vbcrlf)

For X=0 To UBound(aData)
strData=aData(x) 'Read in each line of array
'wscript.Echo x

'**************************************************
strUser = Left(strData,InStr(strData,",")-1)
'wscript.Echo strUser

objTrans.Init ADS_NAME_TYPE_1779, strNetBIOSDomain
objTrans.Set ADS_NAME_TYPE_NT4, strNetBIOSDomain & "\" & strUser
strUserDN = objTrans.Get(ADS_NAME_TYPE_1779)

'**************************************************
set objUser = GetObject("LDAP://"& strUserDN)
'wscript.Echo objUser.displayName

If InStr(LCase(strData),LCase(objUser.samAccountname)) Then

'Remove the username from the string
strData=Mid(strData,InStr(strData,",")+1)

'Extract the phone number
strPhone=Left(strData,InStr(strData,",")-1)

'Extract the fax
strFax=Mid(strData,InStr(strData,",")+1)

objUser.telephoneNumber = strPhone
objUser.facsimileTelephoneNumber = strFax

'uncomment the line (remove the tick at beginning) to commit changes!
'objUser.setinfo info!

End If

'****************************
Next

set objRootDSE = nothing
set objTrans = nothing
set objOu = nothing
set fso = nothing
set usrFile = nothing

wscript.Echo "All Done"
'****************************

Add users to a group from a CSV file

Here's another that I'm posting per request. This one comes in handy for me quite often. It can be modified to read in (from the file) samAccountnames, the smtp address, just about anything that can be converted using the name translate method. Here's a link that talks about some of the things you can do with IADsNameTranslate. Read through the script to see what attributes you need to modify to get it to work for you. Enjoy!

'------------------------------------------------------
'Use any and all scripts from this site at your own risk
'Test test test!
'This script will take a csv file of samAccountnames (ntname)
'and add all the users to the specified group.

'By KLW, 08/26/05

On Error Resume Next

Const ADS_PROPERTY_CLEAR = 1
Const ADS_PROPERTY_UPDATE = 2
Const ADS_PROPERTY_APPEND = 3
Const ADS_PROPERTY_DELETE = 4

Const ADS_NAME_INITTYPE_GC = 3

Const ADS_NAME_TYPE_USER_PRINCIPAL_NAME = 9
Const ADS_NAME_TYPE_NT4 = 3
Const ADS_NAME_TYPE_1779 = 1 'Distinguished Name

'----------------------------------------------------------
'Replace the below item with the security group
'or dist list you want modified
'Modify the dc=microsoft,dc=com to match your domain
Const strGroup = "LDAP://CN=IT Dept,OU=Distribution " _
& "Lists,OU=Exchange,DC=microsoft,DC=com"
Set objGroup = GetObject(strGroup)

'Replace this item with the list of users
Const strFile = "c:\users.csv"

'---------------------------------------------------------
set objReadFSO = CreateObject ("Scripting.FileSystemObject")
set objUsersFile = objReadFSO.OpenTextFile(strFile)

'---------------------------------------------------------
'Determine DNS Domain Name
Set objRootDSE = GetObject("LDAP://RootDSE")
strDNSDomain = objRootDSE.Get("defaultNamingContext")

'--------------------------------------------------------
' Use the NameTranslate object to find the NetBIOS
' domain name from the DNS domain name.
Set objTrans = CreateObject("NameTranslate")
objTrans.Init ADS_NAME_INITTYPE_GC, strDNSDomain
objTrans.Set ADS_NAME_TYPE_1779, strDNSDomain
strNetBIOSDomain = objTrans.Get(ADS_NAME_TYPE_NT4)

'The following removes the \ as in DOMAIN\
strNetBIOSDomain = Left(strNetBIOSDomain, Len(strNetBIOSDomain) - 1)
'wscript.Echo strNetBiosDomain

'------------------------------------------------------
do while objUsersFile.AtEndOfStream = False
strUser = rtrim(Ucase(objUsersFile.readLine))

objTrans.Init ADS_NAME_TYPE_1779, strNetBIOSDomain
objTrans.Set ADS_NAME_TYPE_NT4, strNetBIOSDomain & "\" & strUser
strUserDN = objTrans.Get(ADS_NAME_TYPE_1779)

'wscript.Echo strUserDN
set objUser = GetObject("LDAP://"& strUserDN)
strDNPath = objUser.distinguishedName

objGroup.PutEx ADS_PROPERTY_APPEND, "member", Array(strDNPath)
objGroup.SetInfo
Loop

'----------------------------------------------------
wscript.echo "All Done"

set objUser = Nothing
set objGroup = Nothing
set objUsersFile = Nothing
set objReadFSO = Nothing
set objRootDSE = Nothing
set objTrans = Nothing

Can I allow more than one person to manage a Windows distribution list? Yes!

Short answer, yes. In a Windows 2000 domain (not sure if this restriction is exactly the same in a 2003 domain), let's say you have a distribution list called ALLIT, which you manually add people to when needed. You have several administrative assistants. Normally, admin1 updates the list, as he has been set as the manager of the list from within Active Directory Users & Computers (ADUC), using the Managed By tab in the properties of the group.

What happens when admin1 is out sick, on vacation, etc? Your boss would like admin1, admin2, AND admin3 to be able to update the list. But, the problem is that you can only select a single user in the Managed By tab. So what do you do? There's a pretty easy way to get around this. First off, you need to be familiar with ADSI edit which you should be if you're an admin for a 2000/2003 domain. There are lots of links and information that will tell you that ADSI edit is terribly dangerous. Well, just about any admin tool can be terribly dangerous if used the wrong way. Personally, I find ADSI edit invaluable, just be careful until you become familiar with it. Here's a quick tutorial: ADSI_EDIT.

What you need to do first is find (or create) a group that contains all the users who should be able to update the given list. In our case, we're going to create a security group called 'ALLIT Update' and add admin1, admin2 and admin3 to the group.

Next, head into ADSI edit, right click and select 'Connect To well known naming context' and make sure 'Domain' is in the drop down list. Expand things out and you should see a structure that looks like your OU structure when you're in the ADUC. Find the 'ALLIT Update' group, right click and select properties, scroll down til you find the distinguishedName attribute, double click it and copy the value.

Next, find the ALLIT group in ADSI edit and find the managedBy value. Paste the distinguishedName value from 'ALLIT Update' into this field, exit, head back into the ADUC, find ALLIT and you should now see the 'ALLIT Update' listed in there as the group manager. All you need to do now is tick the 'manager can update group membership' checkbox. Now admin1, admin2 and admin3 should be able to manager the ALLIT group. (if they can't do it right away, wait just a bit - have them log off/back on then try again). You also have the added convenience of merely adding people to and from 'ALLIT Update' whenever you want to add/remove group managers.

Enjoy!
Kris