Find select or view for allocated Datastore Space in vCAC Database

[vMarkusK1985]

Hello,

I need to report the allocated Datastore Space per Datastore and BusinessGroup.

In the attachment is the View in vCAC where the values are visible.

Home >            Infrastructure > Reservations > Reservations

Does anybody know where to find in the Database?

[VirExprt]

Hey!!

You may have to filter and trace out the data which is distributed in different tables..

All Reservation related tables are located as 'dbo.Host........'

Br,

MG

[JamesMW]

Not entirely sure what level of info you want, but digging thru the HostReservation and ProvisioningGroup tables should provide what you are looking for.

select
  g.GroupName,
  r.HostReservationName,
  r.ReservationMemorySizeMB,
  r.ReservationStorageSizeGB
from
  ProvisioningGroup g left join
  HostReservation r on r.GroupID = g.GroupID
where
  MachineType = 0

This should get you started.

[GrantOrchardVMw]

You could pull this out of the IaaS Capacity Usage by Group portlet pretty easily. Are you planning on running it regularly?

Grant

[vMarkusK1985]

Hi,

thats a good starting point but not what i need.

My intention is to Report the Used reservation per Datastore because I need i to report the Usage of my Storage classes (Gold, Silver).

There is no out of the box report avaiable...

[vMarkusK1985]

Sorry, thats not detailed enoth.

I need the different Datastores and Storage Classes.

[kumarankpl]

This is saved in HostReservationStats view(Select * from HostReservationStats). Where you can find the Columns StorageAllocated and other information's.

[vMarkusK1985 · Accepted solution]

SELECT DISTINCT D.Name, SUM(E.MaxCapacity) AS Reserviert, SUM(B.StorageAllocated) AS Verwendet 
FROM [vCAC].[dbo].[HostReservation] A, [vCAC].[dbo].[HostReservationToStorageStats] B, [vCAC].[dbo].[HostToStorage] C, [vCAC].[dbo].[HostStorageReservationPolicy] D,[vCAC].[dbo].[HostReservationToStorage] E 
WHERE A.HostReservationName = 'RES-Default' AND A.HostReservationID = B.HostReservationID AND A.HostReservationID = E.HostReservationID AND B.HostToStorageID = C.HostToStorageID AND C.HostStorageReservationPolicyID = D.HostStorageReservationPolicyID AND C.HostToStorageID = E.HostToStorageID AND D.Name = 'Gold' 
GROUP BY D.Name;