12-30-2014 07:24 AM
Hello,
Recently, we realized that some of our users have 'indexing level' incorrectly set to 'Full' (We use 'Brief' by default). So, we want to know how many of them have indexing level set to 'Full'.
As I couldn't find any powershell cmdlets or any other way, thought of querying the DB directly.
Found 'indexsettings' table in 'EnterpriseVaultDirectory' DB but it doesn't have the archive name column.
Below is the snippet of the output from 'IndexSettings' Table.
The problem is that I need to map the values to the corresponding 'ArchiveName' to find out the names of the users.
'ContainerId' is the primary key of 'IndexSettings' table but I couldn't find this column in any other table (except IndexingDisclaimerExclusions table which is empty). OTOH, 'ArchiveName' is present in 'Archive' Table but I am not sure how can I join these two tables without a common column.
Any thoughts on how can I get the corresponding 'ArchiveName' for the 'ContainerID'?
-Ram
Solved! Go to Solution.
12-30-2014 08:43 AM
Here you go, its a large query but will do exactly what you want.
Also the ContainerID can either be the Site Indexing level, a Mailbox Policy indexing level or an individual users archive indexing level
So for an archive it goes from
IndexSettings.ContainerID -> Root.VaultEntryID and Root.RootIdentity -> Archive.RootIdentity
For the Mailbox Policy it goes from
ExchangeMailboxEntry.PolicyEntryId -> IndexSettings.ContainerID
For the Site its a little more trickier
Archive.RootIdentity -> IndexVolume.RootIdentity
IndexVolume.IndexRootPathEntryId -> IndexRootPathEntry.IndexRootPathEntryId
IndexRootPathEntry.IndexServiceEntryId -> IndexServiceEntry.ServiceEntryId
IndexServiceEntry.ComputerEntryId -> ComputerEntry.ComputerEntryId
ComputerEntry.ComputerSiteEntryId -> IndexSettings.ContainerID
Anyway, here is the full query
SELECT A.ArchiveName "Archive Name", (IRP.IndexRootPath + '\' + IV.FolderName) "Index Folder", IV.IndexedItems "Item Count", CASE IV.CurrentIndexingLevel WHEN 0 THEN 'Brief' WHEN 1 THEN 'Medium (32-Bit Only)' WHEN 2 THEN 'Full' ELSE 'Not Set' END "Index Volume Indexing Level", CASE IV.IndexVolumeType WHEN 0 THEN '32-Bit' WHEN 1 THEN '64-Bit' END "Bitness", PTG.DisplayName "Provisioning Group", PE.poName "Archiving Policy", CASE IS1.IndexingLevel WHEN 0 THEN 'Brief' WHEN 1 THEN 'Medium (32-Bit Only)' WHEN 2 THEN 'Full' END "Archive Indexing Level", CASE IS2.IndexingLevel WHEN 0 THEN 'Brief' WHEN 1 THEN 'Medium (32-Bit Only)' WHEN 2 THEN 'Full' END "Policy Indexing Level", CASE IS3.IndexingLevel WHEN 0 THEN 'Brief' WHEN 1 THEN 'Medium (32-Bit Only)' WHEN 2 THEN 'Full' END "Site Indexing Level" FROM SiteEntry SE, ComputerEntry CE, Archive A, Root R, ExchangeMailboxEntry EME, PolicyTargetGroup PTG, PolicyEntry PE, IndexingSettings IS1, IndexingSettings IS2, IndexingSettings IS3, IndexVolume IV, IndexRootPathEntry IRP, IndexingServiceEntry ISE WHERE A.RootIdentity = R.RootIdentity AND R.VaultEntryId = EME.DefaultVaultId AND EME.PolicyTargetGroupEntryId = PTG.PolicyTargetGroupEntryId AND PTG.PolicyEntryId = PE.poPolicyEntryId AND R.VaultEntryId = IS1.ContainerId AND PE.poPolicyEntryId = IS2.ContainerId AND SE.SiteEntryId = IS3.ContainerId AND R.RootIdentity = IV.RootIdentity AND IV.IndexRootPathEntryId = IRP.IndexRootPathEntryId AND IRP.IndexServiceEntryId = ISE.ServiceEntryId AND ISE.ComputerEntryId = CE.ComputerEntryId AND CE.ComputerSiteEntryId = SE.SiteEntryId ORDER BY A.ArchiveName, IV.FolderName
12-30-2014 08:43 AM
Here you go, its a large query but will do exactly what you want.
Also the ContainerID can either be the Site Indexing level, a Mailbox Policy indexing level or an individual users archive indexing level
So for an archive it goes from
IndexSettings.ContainerID -> Root.VaultEntryID and Root.RootIdentity -> Archive.RootIdentity
For the Mailbox Policy it goes from
ExchangeMailboxEntry.PolicyEntryId -> IndexSettings.ContainerID
For the Site its a little more trickier
Archive.RootIdentity -> IndexVolume.RootIdentity
IndexVolume.IndexRootPathEntryId -> IndexRootPathEntry.IndexRootPathEntryId
IndexRootPathEntry.IndexServiceEntryId -> IndexServiceEntry.ServiceEntryId
IndexServiceEntry.ComputerEntryId -> ComputerEntry.ComputerEntryId
ComputerEntry.ComputerSiteEntryId -> IndexSettings.ContainerID
Anyway, here is the full query
SELECT A.ArchiveName "Archive Name", (IRP.IndexRootPath + '\' + IV.FolderName) "Index Folder", IV.IndexedItems "Item Count", CASE IV.CurrentIndexingLevel WHEN 0 THEN 'Brief' WHEN 1 THEN 'Medium (32-Bit Only)' WHEN 2 THEN 'Full' ELSE 'Not Set' END "Index Volume Indexing Level", CASE IV.IndexVolumeType WHEN 0 THEN '32-Bit' WHEN 1 THEN '64-Bit' END "Bitness", PTG.DisplayName "Provisioning Group", PE.poName "Archiving Policy", CASE IS1.IndexingLevel WHEN 0 THEN 'Brief' WHEN 1 THEN 'Medium (32-Bit Only)' WHEN 2 THEN 'Full' END "Archive Indexing Level", CASE IS2.IndexingLevel WHEN 0 THEN 'Brief' WHEN 1 THEN 'Medium (32-Bit Only)' WHEN 2 THEN 'Full' END "Policy Indexing Level", CASE IS3.IndexingLevel WHEN 0 THEN 'Brief' WHEN 1 THEN 'Medium (32-Bit Only)' WHEN 2 THEN 'Full' END "Site Indexing Level" FROM SiteEntry SE, ComputerEntry CE, Archive A, Root R, ExchangeMailboxEntry EME, PolicyTargetGroup PTG, PolicyEntry PE, IndexingSettings IS1, IndexingSettings IS2, IndexingSettings IS3, IndexVolume IV, IndexRootPathEntry IRP, IndexingServiceEntry ISE WHERE A.RootIdentity = R.RootIdentity AND R.VaultEntryId = EME.DefaultVaultId AND EME.PolicyTargetGroupEntryId = PTG.PolicyTargetGroupEntryId AND PTG.PolicyEntryId = PE.poPolicyEntryId AND R.VaultEntryId = IS1.ContainerId AND PE.poPolicyEntryId = IS2.ContainerId AND SE.SiteEntryId = IS3.ContainerId AND R.RootIdentity = IV.RootIdentity AND IV.IndexRootPathEntryId = IRP.IndexRootPathEntryId AND IRP.IndexServiceEntryId = ISE.ServiceEntryId AND ISE.ComputerEntryId = CE.ComputerEntryId AND CE.ComputerSiteEntryId = SE.SiteEntryId ORDER BY A.ArchiveName, IV.FolderName
12-30-2014 11:11 PM
Thanks for the solution Jesus.
Found that it was actually 'DefaultVaultId' Column in ExchangeMailboxEntry table.
ExchangeMailboxEntry.DefaultVaultId -> IndexSettings.ContainerID
Yes, we set the indexing level at the mailbox policy level.
I am pasting the SQL query that I used for your reference.
use EnterpriseVaultDirectory
Select a.mbxalias as Alias ,a.mbxdisplayname as DisplayName, b.indexinglevel, a.MbxArchivingState as ArchivingState, a.LastModified, a.defaultvaultid as VaultID, b.containerid as ContainerID from ExchangeMailboxEntry a, IndexingSettings b
where a.DefaultVaultId = b.ContainerId and
b.IndexingLevel = 2