SQL function to check the Data Collection status for specific cluster in VRA 7.X

Often there are situation where data collection will get stuck and I felt very hard to check different tables in Database to check what is the current value and how can we compare for working cluster.

To check the data collection basically we need to check in 4 Tables
Host, DataCollectionStatus,FilterSpec and FilterSpecGroup
As per the result we would need to pass the different values to see what is set, which i found very tedious task and I have come up with SQL function which can be utilize to view result by just providing the clustername

CREATE FUNCTION dbo.ClusterDataCollection (@ClusterName CHAR(255))  
RETURNS TABLE  
AS  
RETURN   
(  select dbo.Host.ProvisioningAgentID,dbo.host.HostDNSName,dbo.Host.HostName ,.DataCollectionStatus.LastCollectedStatus,dbo.DataCollectionStatus.LastCollectedTime,dbo.DataCollectionStatus.CollectionStartTime,dbo.DataCollectionStatus.IsDisabled,dbo.FilterSpec.FilterSpecGroupID,dbo.FilterSpecGroup.FilterSpecGroupName as DataCollectionCategory
from dbo.Host join dbo.DataCollectionStatus on dbo.Host.HostID=dbo.DataCollectionStatus.EntityID 
join dbo.FilterSpec on dbo.DataCollectionStatus.FilterSpecID=dbo.FilterSpec.FilterSpecID
join dbo.FilterSpecGroup on dbo.FilterSpec.FilterSpecGroupID=dbo.FilterSpecGroup.FilterSpecGroupID
where dbo.Host.HostName=@ClusterName
);  

Next is how to save this function for future use.
1. Connect to database of the VRA
2. Right click on the database and click New Query
3. Copy paste the function and click on Execute or F5
4. You should see the result like below

Now time to call the function and View the Result.

1. Right click on database and click on new Query.
2. Paste the below mention query(Note: Resource Cluster is name of cluster , for you it will different)

select * from dbo.clusterdatacollection('Resource Cluster');

You should see the similar result like below.

If you would want to view the code or edit in future ,You can do that by going to below path.

1. Open the SQL Server Management Studio
2. Expand the Programmability=>Functions=>Table Value function