How to query Exchange server

How to query Exchange server

Good afternoon.  I need to query my Exchange server for the date that each 
Exchange mailbox was created.  I found a nice article at 
http://msexchangetips.blogspot.com/2006/08/exchange-query-mailbox-creation.html 
which shows (and provides scripts) on how to query Exchange server.  I keep 
getting errors with the script and cannot figure them out (probably a simple 
type-o somewhere but the things I keep changing make it worse).  Below is the 
script itself:

'<--------------------------------------------------------->
servername = "exchangeserver.test.com"
PR_NTSDModificationTime = &H3FD60040
Set fso = CreateObject("Scripting.FileSystemObject")
set wfile = fso.opentextfile("c:\admin\mbCreationTime.csv",2,true)
wfile.writeline("Mailbox,CreationTime")
set conn = createobject("ADODB.Connection")
set com = createobject("ADODB.Command")
Set iAdRootDSE = GetObject("LDAP://RootDSE")
strNameingContext = iAdRootDSE.Get("configurationNamingContext")
strDefaultNamingContext = iAdRootDSE.Get("defaultNamingContext")
Conn.Provider = "ADsDSOObject"
Conn.Open "ADs Provider"
svcQuery = "Com.ActiveConnection = Conn
Com.CommandText = svcQuery
Set Rs = Com.Execute
while not rs.eof
GALQueryFilter = "(&(&(&(& 
(mailnickname=*)(!msExchHideFromAddressLists=TRUE)( 
(&(objectCategory=person)(objectClass=user)(msExchHomeServerName=" & 
rs.fields("legacyExchangeDN") & ")) )))))"
strQuery = " com.Properties("Page Size") = 100
Com.CommandText = strQuery
Set Rs1 = Com.Execute
while not Rs1.eof
call procmailboxes(servername,rs1.fields("mail"))
wscript.echo rs1.fields("mail")
rs1.movenext
wend
rs.movenext
wend
rs.close
wfile.close
set fso = nothing
set conn = nothing
set com = nothing
wscript.echo "Done"




sub procmailboxes(servername,MailboxAlias)

Set msMapiSession = CreateObject("MAPI.Session")
on error Resume next
msMapiSession.Logon "","",False,True,True,True,Servername & vbLF & 
MailboxAlias
if err.number = 0 then
on error goto 0
Set objInbox = msMapiSession.Inbox
Set objInfostore = msMapiSession.GetInfoStore(objInbox.StoreID)
Set objRootFolder = objInfostore.Rootfolder
Set Non_IPM_rootFolder = 
msMapiSession.GetFolder(objRootfolder.fields.item(&h0E090102),objInfoStore.ID)

Wscript.echo Non_IPM_rootFolder.fields.item(PR_NTSDModificationTime)
wfile.writeline(mailboxAlias & "," & 
Non_IPM_rootFolder.fields.item(PR_NTSDModificationTime))


else
wscript.echo = "Error Opening Mailbox"
wfile.writeline(mailboxAlias & "," & "Error Opening Mailbox")
end if
Set msMapiSession = Nothing
Set mrMailboxRules = Nothing

End Sub
'<--------------------------------------------------------->

Or if anyone can assist with another method to retrieve the mailbox creation 
date from an Exchange server, please help...


Thanks in advance for any assistance,
UCG

date: Wed, 5 Mar 2008 09:36:00 -0800 author: UnderCoverGuy

Re: How to query Exchange server

What are the exact errors your getting ? this will generally guide in 
telling you want you need to change to get the script to work

Cheers
Glen

"UnderCoverGuy"  wrote in message 
news:9EA046A5-958F-448E-8C87-88F09F1CD4D1@microsoft.com...
> Good afternoon.  I need to query my Exchange server for the date that each
> Exchange mailbox was created.  I found a nice article at
> http://msexchangetips.blogspot.com/2006/08/exchange-query-mailbox-creation.html
> which shows (and provides scripts) on how to query Exchange server.  I 
> keep
> getting errors with the script and cannot figure them out (probably a 
> simple
> type-o somewhere but the things I keep changing make it worse).  Below is 
> the
> script itself:
>
> '<--------------------------------------------------------->
> servername = "exchangeserver.test.com"
> PR_NTSDModificationTime = &H3FD60040
> Set fso = CreateObject("Scripting.FileSystemObject")
> set wfile = fso.opentextfile("c:\admin\mbCreationTime.csv",2,true)
> wfile.writeline("Mailbox,CreationTime")
> set conn = createobject("ADODB.Connection")
> set com = createobject("ADODB.Command")
> Set iAdRootDSE = GetObject("LDAP://RootDSE")
> strNameingContext = iAdRootDSE.Get("configurationNamingContext")
> strDefaultNamingContext = iAdRootDSE.Get("defaultNamingContext")
> Conn.Provider = "ADsDSOObject"
> Conn.Open "ADs Provider"
> svcQuery = "Com.ActiveConnection = Conn
> Com.CommandText = svcQuery
> Set Rs = Com.Execute
> while not rs.eof
> GALQueryFilter = "(&(&(&(&
> (mailnickname=*)(!msExchHideFromAddressLists=TRUE)(
> (&(objectCategory=person)(objectClass=user)(msExchHomeServerName=" &
> rs.fields("legacyExchangeDN") & ")) )))))"
> strQuery = " com.Properties("Page Size") = 100
> Com.CommandText = strQuery
> Set Rs1 = Com.Execute
> while not Rs1.eof
> call procmailboxes(servername,rs1.fields("mail"))
> wscript.echo rs1.fields("mail")
> rs1.movenext
> wend
> rs.movenext
> wend
> rs.close
> wfile.close
> set fso = nothing
> set conn = nothing
> set com = nothing
> wscript.echo "Done"
>
>
>
>
> sub procmailboxes(servername,MailboxAlias)
>
> Set msMapiSession = CreateObject("MAPI.Session")
> on error Resume next
> msMapiSession.Logon "","",False,True,True,True,Servername & vbLF &
> MailboxAlias
> if err.number = 0 then
> on error goto 0
> Set objInbox = msMapiSession.Inbox
> Set objInfostore = msMapiSession.GetInfoStore(objInbox.StoreID)
> Set objRootFolder = objInfostore.Rootfolder
> Set Non_IPM_rootFolder =
> msMapiSession.GetFolder(objRootfolder.fields.item(&h0E090102),objInfoStore.ID)
>
> Wscript.echo Non_IPM_rootFolder.fields.item(PR_NTSDModificationTime)
> wfile.writeline(mailboxAlias & "," &
> Non_IPM_rootFolder.fields.item(PR_NTSDModificationTime))
>
>
> else
> wscript.echo = "Error Opening Mailbox"
> wfile.writeline(mailboxAlias & "," & "Error Opening Mailbox")
> end if
> Set msMapiSession = Nothing
> Set mrMailboxRules = Nothing
>
> End Sub
> '<--------------------------------------------------------->
>
> Or if anyone can assist with another method to retrieve the mailbox 
> creation
> date from an Exchange server, please help...
>
>
> Thanks in advance for any assistance,
> UCG
>

date: Fri, 7 Mar 2008 08:53:59 +1100 author: Glen Scales [MVP]

Re: How to query Exchange server

Previously, what I posted was this original script.  I've cleaned it up a 
little (by adding quotes where they seemed to be missing).  What I have now 
is:

'<--------------------------------------------------------------------->

servername = wscript.arguments(0)
PR_NTSDModificationTime = &H3FD60040
Set fso = CreateObject("Scripting.FileSystemObject")
set wfile = fso.opentextfile("mbCreationTime.csv",2,true)
wfile.writeline("Mailbox,CreationTime")
set conn = createobject("ADODB.Connection")
set com = createobject("ADODB.Command")
Set iAdRootDSE = GetObject("LDAP://RootDSE")
strNameingContext = iAdRootDSE.Get("configurationNamingContext")
strDefaultNamingContext = iAdRootDSE.Get("defaultNamingContext")
Conn.Provider = "ADsDSOObject"
Conn.Open "ADs Provider"
svcQuery = "Com.ActiveConnection = Conn"
Com.CommandText = svcQuery
Set Rs = Com.Execute
while not rs.eof
GALQueryFilter = "(&(&(&(& 
(mailnickname=*)(!msExchHideFromAddressLists=TRUE)( 
(&(objectCategory=person)(objectClass=user)(msExchHomeServerName=" & 
rs.fields("legacyExchangeDN") & ")) )))))"
strQuery = " com.Properties(" & Chr(34) & "Page Size"  & Chr(34) & ") = 100"
Com.CommandText = strQuery
Set Rs1 = Com.Execute
while not Rs1.eof
call procmailboxes(servername,rs1.fields("mail"))
wscript.echo rs1.fields("mail")
rs1.movenext
wend
rs.movenext
wend
rs.close
wfile.close
set fso = nothing
set conn = nothing
set com = nothing
wscript.echo "Done"




sub procmailboxes(servername,MailboxAlias)

Set msMapiSession = CreateObject("MAPI.Session")
on error Resume next
msMapiSession.Logon "","",False,True,True,True,Servername & vbLF & 
MailboxAlias
if err.number = 0 then
on error goto 0
Set objInbox = msMapiSession.Inbox
Set objInfostore = msMapiSession.GetInfoStore(objInbox.StoreID)
Set objRootFolder = objInfostore.Rootfolder
Set Non_IPM_rootFolder = 
msMapiSession.GetFolder(objRootfolder.fields.item(&h0E090102),objInfoStore.ID)

Wscript.echo Non_IPM_rootFolder.fields.item(PR_NTSDModificationTime)
wfile.writeline(mailboxAlias & "," & 
Non_IPM_rootFolder.fields.item(PR_NTSDModificationTime))


else
wscript.echo = "Error Opening Mailbox"
wfile.writeline(mailboxAlias & "," & "Error Opening Mailbox")
end if
Set msMapiSession = Nothing
Set mrMailboxRules = Nothing

End Sub

'<--------------------------------------------------------------------->

I get the following error:

Line: 15
Char: 1
Error: The connection cannot be used to perform this operation.  It is 
either closed or invalid in this context.
Code: 800A0E7D
Source: ADODB.Command


Any thoughts?


Thanks

date: Thu, 6 Mar 2008 15:36:00 -0800 author: UnderCoverGuy

Re: How to query Exchange server

No that wont help looks like you have major cut and paste issues actually 
its not your fault its a problem with the way the author has put it on his 
blog isn't correct (or more to the point the way blogger will interpret 
anything you cut and paste into it) . Try this instead

servername = wscript.arguments(0)
PR_NTSDModificationTime = &H3FD60040
Set fso = CreateObject("Scripting.FileSystemObject")
set wfile = fso.opentextfile("mbCreationTime.csv",2,true)
wfile.writeline("Mailbox,CreationTime")
set conn = createobject("ADODB.Connection")
set com = createobject("ADODB.Command")
Set iAdRootDSE = GetObject("LDAP://RootDSE")
strNameingContext = iAdRootDSE.Get("configurationNamingContext")
strDefaultNamingContext = iAdRootDSE.Get("defaultNamingContext")
Conn.Provider = "ADsDSOObject"
Conn.Open "ADs Provider"
svcQuery = "<LDAP://" & strNameingContext & 
">;(&(objectCategory=msExchExchangeServer)(cn=" & Servername & 
"));cn,name,legacyExchangeDN;subtree"
Com.ActiveConnection = Conn
Com.CommandText = svcQuery
Set Rs = Com.Execute
while not rs.eof
 GALQueryFilter =  "(&(&(&(& 
(mailnickname=*)(!msExchHideFromAddressLists=TRUE)(| 
(&(objectCategory=person)(objectClass=user)(msExchHomeServerName=" & 
rs.fields("legacyExchangeDN") & ")) )))))"
 strQuery = "<LDAP://"  & strDefaultNamingContext & ">;" & GALQueryFilter & 
";mail,distinguishedName,mailnickname;subtree"
 com.Properties("Page Size") = 100
 Com.CommandText = strQuery
 Set Rs1 = Com.Execute
 while not Rs1.eof
  call procmailboxes(servername,rs1.fields("mail"))
  wscript.echo rs1.fields("mail")
  rs1.movenext
 wend
 rs.movenext
wend
rs.close
wfile.close
set fso = nothing
set conn = nothing
set com = nothing
wscript.echo "Done"




sub procmailboxes(servername,MailboxAlias)

Set msMapiSession = CreateObject("MAPI.Session")
on error Resume next
msMapiSession.Logon "","",False,True,True,True,Servername & vbLF & 
MailboxAlias
if err.number = 0 then
on error goto 0
Set objInbox = msMapiSession.Inbox
Set objInfostore = msMapiSession.GetInfoStore(objInbox.StoreID)
Set objRootFolder = objInfostore.Rootfolder
Set Non_IPM_rootFolder = 
msMapiSession.GetFolder(objRootfolder.fields.item(&h0E090102),objInfoStore.ID)

Wscript.echo Non_IPM_rootFolder.fields.item(PR_NTSDModificationTime)
wfile.writeline(mailboxAlias & "," & 
Non_IPM_rootFolder.fields.item(PR_NTSDModificationTime))

else
wscript.echo = "Error Opening Mailbox"
wfile.writeline(mailboxAlias & "," & "Error Opening Mailbox")
end if
Set msMapiSession = Nothing
Set mrMailboxRules = Nothing

End Sub
"UnderCoverGuy"  wrote in message 
news:E6CB564D-C047-4B83-AE57-3E02A033EC71@microsoft.com...
> Previously, what I posted was this original script.  I've cleaned it up a
> little (by adding quotes where they seemed to be missing).  What I have 
> now
> is:
>
> '<--------------------------------------------------------------------->
>
> servername = wscript.arguments(0)
> PR_NTSDModificationTime = &H3FD60040
> Set fso = CreateObject("Scripting.FileSystemObject")
> set wfile = fso.opentextfile("mbCreationTime.csv",2,true)
> wfile.writeline("Mailbox,CreationTime")
> set conn = createobject("ADODB.Connection")
> set com = createobject("ADODB.Command")
> Set iAdRootDSE = GetObject("LDAP://RootDSE")
> strNameingContext = iAdRootDSE.Get("configurationNamingContext")
> strDefaultNamingContext = iAdRootDSE.Get("defaultNamingContext")
> Conn.Provider = "ADsDSOObject"
> Conn.Open "ADs Provider"
> svcQuery = "Com.ActiveConnection = Conn"
> Com.CommandText = svcQuery
> Set Rs = Com.Execute
> while not rs.eof
> GALQueryFilter = "(&(&(&(&
> (mailnickname=*)(!msExchHideFromAddressLists=TRUE)(
> (&(objectCategory=person)(objectClass=user)(msExchHomeServerName=" &
> rs.fields("legacyExchangeDN") & ")) )))))"
> strQuery = " com.Properties(" & Chr(34) & "Page Size"  & Chr(34) & ") = 
> 100"
> Com.CommandText = strQuery
> Set Rs1 = Com.Execute
> while not Rs1.eof
> call procmailboxes(servername,rs1.fields("mail"))
> wscript.echo rs1.fields("mail")
> rs1.movenext
> wend
> rs.movenext
> wend
> rs.close
> wfile.close
> set fso = nothing
> set conn = nothing
> set com = nothing
> wscript.echo "Done"
>
>
>
>
> sub procmailboxes(servername,MailboxAlias)
>
> Set msMapiSession = CreateObject("MAPI.Session")
> on error Resume next
> msMapiSession.Logon "","",False,True,True,True,Servername & vbLF &
> MailboxAlias
> if err.number = 0 then
> on error goto 0
> Set objInbox = msMapiSession.Inbox
> Set objInfostore = msMapiSession.GetInfoStore(objInbox.StoreID)
> Set objRootFolder = objInfostore.Rootfolder
> Set Non_IPM_rootFolder =
> msMapiSession.GetFolder(objRootfolder.fields.item(&h0E090102),objInfoStore.ID)
>
> Wscript.echo Non_IPM_rootFolder.fields.item(PR_NTSDModificationTime)
> wfile.writeline(mailboxAlias & "," &
> Non_IPM_rootFolder.fields.item(PR_NTSDModificationTime))
>
>
> else
> wscript.echo = "Error Opening Mailbox"
> wfile.writeline(mailboxAlias & "," & "Error Opening Mailbox")
> end if
> Set msMapiSession = Nothing
> Set mrMailboxRules = Nothing
>
> End Sub
>
> '<--------------------------------------------------------------------->
>
> I get the following error:
>
> Line: 15
> Char: 1
> Error: The connection cannot be used to perform this operation.  It is
> either closed or invalid in this context.
> Code: 800A0E7D
> Source: ADODB.Command
>
>
> Any thoughts?
>
>
> Thanks
>

date: Fri, 7 Mar 2008 11:49:37 +1100 author: Glen Scales [MVP]

Re: How to query Exchange server

Thanks for the updated script.  Once I adjusted a few lines (due to line 
length limits here), it runs without error, which is a huge plus.

However, I receive the final "Done" when it completed, then I check the CSV 
file and it only has a header line in it (no info).  But no errors though so 
that is a huge plus.

I am running the script as a domain admin with standard domain admin rights 
(no send as / receive as).  Am I missing something?

Thanks so much for your help,
UCG

date: Thu, 6 Mar 2008 19:23:00 -0800 author: UnderCoverGuy

Re: How to query Exchange server

By default administrative accounts are denied access to every mailbox other 
then their own. Because this script is basically using Mapi to login to 
every mailbox to retrieve the desired information you need to have an 
account that has been assigned permissions such as 
http://www.petri.co.il/grant_full_mailbox_rights_on_exchange_2000_2003.htm

It should echo out the names of the mailboxes its trying to access as it 
goes along are you seeing this ? Make sure you use the NetBIOS name of the 
server and not the FQDN which will cause the ADSI query to fail (or not 
return any users)

Cheers
Glen

"UnderCoverGuy"  wrote in message 
news:7ECB6B69-DBE0-45FD-A00B-B43D6E0B94DA@microsoft.com...
> Thanks for the updated script.  Once I adjusted a few lines (due to line
> length limits here), it runs without error, which is a huge plus.
>
> However, I receive the final "Done" when it completed, then I check the 
> CSV
> file and it only has a header line in it (no info).  But no errors though 
> so
> that is a huge plus.
>
> I am running the script as a domain admin with standard domain admin 
> rights
> (no send as / receive as).  Am I missing something?
>
> Thanks so much for your help,
> UCG
>

date: Fri, 7 Mar 2008 17:29:06 +1100 author: Glen Scales [MVP]

Re: How to query Exchange server

Glen,

Thank you very much for your help.  You were right on the money there.  I 
was using the FQDN and not the Exchange Server NetBIOS name.  Once I used the 
NetBIOS name, BINGO - it worked GREAT!!!

Thank you again,
Much props out to you,
UCG

date: Fri, 7 Mar 2008 02:55:03 -0800 author: UnderCoverGuy

Re: How to query Exchange server

I wanted to follow up for anyone who may need something like this in the 
future (I've included the vbScript at the end of this post).

Many thanks to James @ 
http://msexchangetips.blogspot.com/2006/08/exchange-query-mailbox-creation.html 
for supplying the initial code and direction.  KUDOS also goes out to Glen 
for his help in getting this to finally work â especially with providing such 
a quick response.

I've made a few adjustments to accommodate my domain structure (displayName, 
etc.).  One of the changes was to format the user name and should be 
straightforward if you need to update the section or remove it completely.

A few things to note:
- As a domain admin, I was able to run the script from an XP SP2 workstation 
as well as directly on my primary Exchange 2003 server.
- There is a section of the script (look for GALQueryFilter) in which you 
may need to combine the lines if formatting doesnât copy the code as it 
should be.  The entire GALQueryFilter section needs to be on one line only.  
Just look between the commented separators in that area.
- The script will run and display a message box when completed.  The output 
is sent to a file in the same directory from which you run the script.  It is 
a CSV file so you can open it directly in Excel, format it, etc.
- Copy the script over and run using the following syntax:
     vbScriptName <ExchangeServer>
     Replace vbScriptName with the name of the script that you save it to.
     Replace <ExchangeServer> with the name of your Exchange Server.  Do not 
include the brackets and also use your Exchange Servers NetBIOS name.  If you 
use a FQDN, the script will run but will not produce an output other then the 
header in the CSV file.

Enjoy,
UCG

---------------------------------------------------------------------------------------

â<--------------Start of Script-------------->

servername = wscript.arguments(0)
vbsCRLF = Chr(13) & Chr(10)
PR_NTSDModificationTime = &H3FD60040

Set fso = CreateObject("Scripting.FileSystemObject")
set wfile = fso.opentextfile(".\mbCreationTime.csv",2,true)
'<-------Adjust based upon naming syntax for your org------->
wfile.writeline("DisplayName,MailboxAlias,CreationTime")
'<---------------------------------------------------------->
set conn = createobject("ADODB.Connection")
set com = createobject("ADODB.Command")
Set iAdRootDSE = GetObject("LDAP://RootDSE")
strNameingContext = iAdRootDSE.Get("configurationNamingContext")
strDefaultNamingContext = iAdRootDSE.Get("defaultNamingContext")
Conn.Provider = "ADsDSOObject"
Conn.Open "ADs Provider"
svcQuery = "<LDAP://" & strNameingContext & _
">;(&(objectCategory=msExchExchangeServer)(cn=" & Servername & _
"));cn,name,legacyExchangeDN;subtree"
Com.ActiveConnection = Conn
Com.CommandText = svcQuery
Set Rs = Com.Execute
while not rs.eof
'<------------Adjust GALQueryFilter lines below------------>
 GALQueryFilter = 
"(&(&(&(&(mailnickname=*)(!msExchHideFromAddressLists=TRUE)(|(&(objectCategory=person)(objectClass=user)(msExchHomeServerName=" 
& rs.fields("legacyExchangeDN") & ")) )))))"
'<-----------------End of adjustment needs----------------->
 strQuery = "<LDAP://"  & strDefaultNamingContext & ">;" _
 & GALQueryFilter & ";mail,distinguishedName,mailnickname;subtree" _
 & ";mail,displayname,mailnickname;subtree"
 com.Properties("Page Size") = 100
 Com.CommandText = strQuery
 Set Rs1 = Com.Execute
 while not Rs1.eof
  call procmailboxes(servername,rs1.fields("mail"), _
  rs1.fields("displayname"))
  rs1.movenext
 wend
 rs.movenext
wend
rs.close
wfile.close
set fso = nothing
set conn = nothing
set com = nothing
wscript.echo "Done"




sub procmailboxes(servername,MailboxAlias,Display_Name)

Set msMapiSession = CreateObject("MAPI.Session")
on error Resume next
msMapiSession.Logon "","",False,True,True,True,Servername & _
  vbLF & MailboxAlias
if err.number = 0 then
on error goto 0
Set objInbox = msMapiSession.Inbox
Set objInfostore = msMapiSession.GetInfoStore(objInbox.StoreID)
Set objRootFolder = objInfostore.Rootfolder
Set Non_IPM_rootFolder = msMapiSession.GetFolder _
  (objRootfolder.fields.item(&h0E090102),objInfoStore.ID)

CommaCount = ubound(split(lcase(Display_Name), ","))
If CommaCount = 1 Then
  Pos = InStr(1,Display_Name,",",1)
  FirstName = Right(Display_Name, (Len(Display_Name)-(Pos+1)))
  LastName = Left(Display_Name,(Pos-1))
  Display_Name = FirstName & " " & LastName
ElseIf CommaCount = 2 Then
  Pos1 = InStr(1,Display_Name,",",1)
  Pos2 = InStr((Pos1+1),Display_Name,",",1)
  FirstName = Mid(Display_Name,(Pos1+2),((Pos2-Pos1)-2))
  LastName = Left(Display_Name,(Pos1-1))
  SuffixName = Right(Display_Name,(Len(Display_Name)-(Pos2+1)))
  Display_Name = FirstName & " " & LastName & " " & SuffixName
End If

'<---------------Uncomment lines to display info--------------->
'wscript.echo "Server name: " & servername & vbsCRLF & _
'"User display name: " & Display_Name & vbsCRLF & _
'"Mailbox Alias: " & MailboxAlias & vbsCRLF & _
'"Creation time: " & Non_IPM_rootFolder.fields.item(PR_NTSDModificationTime)
'<------------------------------------------------------------->

wfile.writeline(Display_Name & "," & mailboxAlias & "," & _
  Non_IPM_rootFolder.fields.item(PR_NTSDModificationTime))

else
wscript.echo = "Error Opening Mailbox"
wfile.writeline(mailboxAlias & "," & "Error Opening Mailbox")
end if
Set msMapiSession = Nothing
Set mrMailboxRules = Nothing

End Sub

â<---------------End of Script--------------->

date: Fri, 7 Mar 2008 08:09:00 -0800 author: UnderCoverGuy