Why you shouldn't take your Birthday off... »Useful Zantaz EAS SQL Queries
About a year ago I rolled out Zantaz EAS (Exchange Archive Solution) to 3000 users. We were the first large production rollout onto NetApp filers for Zantaz, and after a bunch of tweaking we got it up running. (Zantaz says you can run the Indexer and Archiver over CIFS, but don't do it! :-)
Anyway, here are a few useful SQL queries you can use when troubleshooting or performing maintenance on your EAS system.
Note: These queries assume you use MS SQL as the backend for your EAS deployment. Feel free to tweak as required for your version. We are still running 4.1 SP1 here, but my understanding is 4.3 hasn't made great changes to the DB structure at this point.
Some simple queries to get started with...
Find a user with the LegacyExchangeDN similar to SmithJ1
select * from users where objDistName like '%SmithJ1%'
Find all users who are enabled in EAS (enabled is zero, disabled is 1)
select * from users where easStatus = 0
Get a count of active EAS users (change easStatus = 1 for disabled users)
select count(*) from users where easStatus = 0
Find how many messages user 1670 has indexed.
select count(*) from INDEXED_BY where USERID = 1670
Find how many messages user 1670 has archived.
select count(*) from refer where USERID = 1670
Find out which search Indexes user 1670 is assigned to.
select * from user_search_idex where userid = 1670
Some simple update statements:
Change the ObjDistName (AKA LegacyExchangeDN) for user 1670
update users set objDistName = "/O=ORG-NAME/OU=SITE/CN=RECIPIENTS/CN=USER-NAME" where userid = 1670
Disable user 1670 in EAS
update users set easStatus = 1 where userid = 1670
Some more fun queries...
Find all users who have more than one DB entry by username, outputs the Username and count... *1
select username,count(username) as number from USERS group by USERNAME having count(username)>1
Find all users who have more than one DB entry by username, outputs the Username and ObjDistName...
select username,objDistName from users where username in (select username from users groub by username having count(username)>1)
The userstatus field has some great potential for scripting. This field is incremented by the last job number after a successfull archive pass, so you can find users failing to archive, or estimate the last successfull archive date from this field.
Find all users who have not been archived since job pass # 5000
Related to the above, find all users who are not disabled and have not archived passed a userStatus value (<5000)
select * from users where userStatus <5000 and easStatus <> 1
Got an EAS client error message that references a specific message ID? Get the message details (msgid=99999.)
select * from EASSRV.PROFILE P where msgid=99999
Note: This field may be known as PROFILE P, or have a custom header appended to it depending on your installation.
Notes:
1. I usually replace select * with select username,userId, objDistName and limit the return fields to just what I need.
2. The objDistName is a key field so you cannot have 2 identical values.
3. The objDistName field MUST BE in UPPERCASE. If you modify it, don't add lower case chars.
I will post some batch scripts I have for manipulating the EAS DB from the command line using SQLCMD.exe in the near future. Have fun !!
Search
