Looking for a SQL query to determine new archives (users) per month

[EV_NEwbie]

Hi,

I'm looking for a SQL query that will help me understand the number of active archives per month.  I'm hoping to extrapolate the data to determine how many new EV users per month are being created on the system.

I have a moderately large EV installation spanning multiple EV servers, so I'm going to need to run the query against each SQL database.

Thanks in advance

[MichelZ]

Hi there

Unfortunately, there is no Field that holds the creation time of an archive.
How would you like to do this?

Cheers
Michel

[EV_NEwbie]

For the purposes of what I'm doing, I'm happy to assume that all archives are active archives.  I was hoping to run a query for each month of the year to get the maximum number of archives.  I would then compare the results with the results from the month prior to determine how many new archives were added from month to month.

[R_Meeker]

If you use different vault stores for Exchange and any other archiving you are doing, you best bet is to run a 'count' against the 'archive' table in the directory database, at the beginning of each month, with a 'where' variable on the 'vaultstoreentryid' that relates to that vault store id that contains the exchange mailbox archives.

that way you get a count for all archives in the vault store, subtract form previous month and hey presto alakazam, hence why you need to have a different vault store for exchange and all the others.

[MichelZ]

Hi

Yes, I'd do the same.

SELECT COUNT(*) FROM Archive INNER JOIN [Root] ON Archive.RootIdentity = [Root].RootIdentity INNER JOIN ExchangeMailboxEntry ON [Root].VaultEntryId = ExchangeMailboxEntry.DefaultVaultId WHERE ExchangeMailboxEntry.MbxArchivingState = 1

Cheers
Michel