SQL query for items per index location

[ianG]

Im sure it can be done, but Ive not been able to cobble it together :\

Looking for a table with 2 columns, 'IndexRootPath', and 'Count'.

IndexRootPath is directly from the table IndexRootPathEntry

And Count would be the number of times that corresponding path's IndexRootPathEntryId occurs in the table IndexVolume

So sort of:

 use EnterpriseVaultDirectory

select IRPE.IndexRootPath "Path",
  (select count(*) from IndexVolume where *SCENE MISSING*) as Count

from IndexRootPathEntry IRPE, IndexVolume IV

order by IndexRootPath 

Any pointers?

[R_Meeker]

Use the indexvolumesetview

Indexeditems field and the type 9 being MBX archiving, or 17 for journal archiving.

Select archivename, indexeditems, indexrootpath
from indexvolumesetview

where type = 9

[ianG]

Very good, hadnt seen views before, so good to see - many thanks :)

I realise my question above wasnt terribly clear though. Im just looking for the number of indexes (not indexed items), per index location. So with our 32 index locations:

index1 - 140

index2 - 150

index3 - 140

...

index32 145

etc, sort of result

[JesusWept3 · Accepted solution]

Try this query

SELECT CE.ComputerName "EV Server",
       IRP.IndexRootPath "Index Path", 
       COUNT(IV.IndexVolumeIdentity) "Number of Volumes",
       SUM(IV.IndexedItems) "Number of Indexed Items"
FROM IndexVolume IV, IndexRootPathEntry IRP, IndexingServiceEntry ISE, ComputerEntry CE
WHERE IV.IndexRootPathEntryId = IRP.IndexRootPathEntryId
  AND IRP.IndexServiceEntryId = ISE.ServiceEntryId
  AND ISE.ComputerEntryId = CE.ComputerEntryId
GROUP BY CE.ComputerName, IRP.IndexRootPath
ORDER BY CE.ComputerName, IRP.IndexRootPath 

[ianG]

wow, thats excellent!

perfect :) if I could thank you twice, I would