Export Exchange query-based distribution group members to a CSV file

Thursday, December 4, 2008

Export Exchange query-based distribution group members to a CSV file


This is a script I run internally to generate reports of query-based distribution group members as CSV files (viewable in Excel).

You'll need the free ADFIND tool from joeware for this script to run.
http://www.joeware.net/freetools/tools/adfind/index.htm

You'll also need to change the following line to reflect the proper LDAP base DN for your domain.
SET BASEDN="dc=domain,dc=local"

Script: Run-QBDG-Export.bat
@ECHO OFF
SETLOCAL
IF "%1" EQU "" (
ECHO.
ECHO ERROR!
ECHO Parameter required ^
ECHO.
ECHO Usage: Run-QBDG-Export.bat ^
ECHO.
GOTO :EOF
)

REM CHANGE THIS LINE TO MATCH YOUR DOMAIN
SET BASEDN="dc=domain,dc=local"
SET CMDLINE=ADFIND -b %BASEDN% -f "cn=%1*" -nodn cn
%CMDLINE% >%1-GETGROUPS.txt 2>NUL
IF EXIST %1-GETGROUPS.txt (
FIND /I "0 Objects returned" %1-GETGROUPS.txt
IF ERRORLEVEL 1 (
GOTO START_SEARCH
) ELSE (
ECHO ERROR: Could not query Active Directory for groups with %1*
GOTO END
)
) ELSE (
ECHO ERROR: Could not query Active Directory for groups with %1*
GOTO END
)

:START_SEARCH
FOR /F "usebackq tokens=1*" %%A IN (`type %1-GETGROUPS.txt ^| FIND /I ">cn:"`) DO (
SET GRP_OBJ=%%B
CALL :GET_MEMBERS %%B
)

FOR /F "tokens=1,2 delims=:" %%A in ("%TIME%") DO (
SET MYTIME=%%A:%%B
)
GOTO END

:GET_MEMBERS
SET FLT_QRY_OBJ="msExchDynamicDLFilter:"
SET FLT_DN_OBJ="msExchDynamicDLBaseDN:"

SET FLT_QRY_CMD=ADFIND -b %BASEDN% -f "cn=%GRP_OBJ%"
REM GET QUERY STRING
%FLT_QRY_CMD% > %1-ADINFO.txt 2>NUL

FOR /F "usebackq tokens=1*" %%A IN (`type %1-ADINFO.txt ^| FIND /I %FLT_QRY_OBJ%`) DO (
SET QRY_STR="%%B"
)
REM GET QUERY BASE DN
FOR /F "usebackq tokens=1*" %%A IN (`type %1-ADINFO.txt ^| FIND /I %FLT_DN_OBJ%`) DO (
SET QRY_DN="%%B"
)

ECHO Running the following query:
ECHO ------------------------------------------------
ECHO CN: %GRP_OBJ%
ECHO DN: %QRY_DN%
ECHO QS: %QRY_STR%
ECHO.
IF EXIST "%GRP_OBJ%.csv" (
DEL /Q "%GRP_OBJ%.csv"
)
ECHO Creating export file...
ADFIND -csv -b %QRY_DN% -f %QRY_STR% sn givenName mail title physicalDeliveryOfficeName employeeID -nodn >"%GRP_OBJ%.csv" 2>NUL
ECHO Done.
ECHO.
ECHO.
IF NOT EXIST "%GRP_OBJ%.csv" (
ECHO Could not create "%GRP_OBJ%.csv"
ECHO.
ECHO Press any key to continue or Ctrl-C to quit...
PAUSE >NUL 2>NUL
)
REM PAUSE
GOTO :EOF

:END
DEL /Q %1-GETGROUPS.txt >NUL 2>NUL
DEL /Q %1-BODY.TXT >NUL 2>NUL
DEL /Q %1-ADINFO.txt >NUL 2>NUL

ENDLOCAL

9 comments :

Kiwipedro said...

Hi - nice script, saved me a lot of time.

Just a note, we use "#" in front of our DGs, which gets escaped with a "\". Something (perhaps the For /F) doesn't like that in your batch file.

So I changed "CN=%1*" to "CN=*%1*" and took the \# off the front of the parsed argument and all was well :-)

Thanks again!
KP

MarcJ said...

Hi Erik,

Very helpful post indeed. Thanks dude. By the way, I run a blog on Free Active Directory Reporting Tools, and think tha Adfind's still one of the best one's out there.

Quest's PowerShell additions do seem to be diminishing its value a bit, but I hope it can be continually improved so admins out there continue to find value in it!

Thanks anyways for your post. BTW, if you've some time, feel free to stop by and say Hello.

Ciao,
Marc

DZeD said...

Can someone post an example on how to use this script with ADFind please

DZeD said...

Cam someone post an example on how to use this scrpit with ADFind please

erikpt said...

DZeD, it's fairly simple. Place the script along with ADFIND in the same directory and run the script.

DZeD said...

Erik,
I am missing something, as I change the "SET BASEDN" line, put ADFind and the bat file in the same directory, then run the bat from the commamd prompt and get an error as below:


C:\QueryBasedTools>Run-QBDG-Export.bat

ERROR!
Parameter required ECHO.
Usage: Run-QBDG-Export.bat ECHO.

C:\QueryBasedTools>

ScottL said...

Hi Erik,

I've run into a problem when using this script, and wondered if you or anyone else might lend a hand.

My environment:
Native server 2003 AD forest, Exchange 2003, with my QBDGs residing in an OU in the root domain of the forest. There are three child domains where the majority of the user accounts reside.

Problem:
The query runs and generates the expected csv file. However, only accounts residing in the root domain are listed. Is there some way to also pull the QBDG "member" account names which do not reside within the root domain?

Thanks,
Scott

erikpt said...

ScottL, the fix is simple, edit the batch file and add "-gc" to the line(s) where ADFIND runs Place it between ADFIND and -csv, so the command looks like this


ADFIND -gc -csv -b %QRY_DN% -f %QRY_STR% sn givenName mail title physicalDeliveryOfficeName employeeID -nodn >"%GRP_OBJ%.csv" 2>NUL


HTH

Manaz said...

DZed: Replace "ECHO" with a search term for the group you're after. For "All Staff", I used "All" (without the inverted commas).

That solved the issue.