Count of items per folder in Archive Shuttle (4301367)

Overview

It might be necessary to obtain the per-folder items count from a source archive stored in Archive Shuttle Item database.

Solution

The following SQL query, run over Archive Shuttle Item database, can be used in order to obtain the per-folder items count:

SELECT sp.Path, COUNT(i.ItemId) AS ItemCount
FROM Item i
INNER JOIN StoragePath sp ON sp.StoragePathId = i.StoragePathId
INNER JOIN ItemRouting ir ON ir.ItemId = i.ItemId
GROUP BY sp.Path

By adding a where condition with particulat ContainerMappingId(s), e.g. 1044, the per-folder & per-mapping items count can be obtained:

SELECT sp.Path, COUNT(i.ItemId) AS ItemCount
FROM Item i
INNER JOIN StoragePath sp ON sp.StoragePathId = i.StoragePathId
INNER JOIN ItemRouting ir ON ir.ItemId = i.ItemId
WHERE ir.ContainerMappingId = 1044
GROUP BY sp.Path

Container mapping ID is stored e.g. on the Stage 1, or Existing Mappings screen and can be seen after Container Mapping ID column is added to the grid.