cancel
Showing results for 
Search instead for 
Did you mean: 

Query the list of users who has the indexing level set to Full

Ram_EUHR
Level 4

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.

Indexsettings.PNG

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

1 ACCEPTED SOLUTION

Accepted Solutions

JesusWept3
Level 6
Partner Accredited Certified

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
https://www.linkedin.com/in/alex-allen-turl-07370146

View solution in original post

2 REPLIES 2

JesusWept3
Level 6
Partner Accredited Certified

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
https://www.linkedin.com/in/alex-allen-turl-07370146

Ram_EUHR
Level 4

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