vRealize Automation DataCollection schedules
Data Collection Status information is stored under dbo.DataCollectionStatus table of IaaS Database
select * from DataCollectionStatus
This table contains AgentID , LastCollectedTime , LastConnectedStatus,EntitiyID,DataCollectionStatusID,FilterSpecID and so on ....
FilterSpec refers to the type of endpoint we are collecting data from. dbo.DataCollectionStatus has this FilterSpecID which is coming from dbo.FilterSpec table
dbo.FilterSpec table has FilterSpecName,FilterSpecGroupID,AgentCapabilityName
Let's take the only vSphereEndpoint into consideration and then filter dbo.FilterSpec w.r.t to this endpoint only
Since we selected vSphere the AgentCapabilityName will only be vSphereHypervisor
select * from dbo.FilterSpec where FilterSpecName = 'vSphere';
Each FilterSpecGroupID belongs to a certain type of data collection task for a specific endpoint.
This information is stored under dbo.FilterSpecGroupID table
Now let's identify what these FilterSpecGroupID from dbo.FilterSpec table and check what it refers to from dbo.FilterSpecGroup table
select * from FilterSpecGroup where FilterSpecGroupID in ( select FilterSpecGroupID from dbo.FilterSpec where FilterSpecName = 'vSphere')
As one can see in the above screenshot each FilterSpecGroupID belongs to a FilterSpecGroupName which is eventually a task under DataCollection
What's this ScheduleID then inside dbo.FilterSpecGroup table ?
ScheduleID comes from dbo.CollectionSchedule where default collection schedules are defined and is associated with an ID.
This is the ID that is present under dbo.FilterSpecGroup
So here's the flow
dbo.CollectionSchedule --> dbo.FilterGroupSpec --> dbo.FilterSpec --> dbo.DataCollectionStatus
If one wants to find out the LastCollectedTime and LastCollectedStatus of data collection from the database for a specific cluster, they can use below query
select LastCollectedTime,LastCollectedStatus,HostName from DataCollectionStatus dc, host h where dc.EntityID=h.hostID and h.HostName='ClusterName' order by h.HostName
Note: Replace ClusterName with your specific Compute Resource Name appropriately.