Remote Desktop Services Servers Overview

Select Top 1000000 tsysOS.Image As icon,
tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tblAssets.Username,
tblAssets.Userdomain,
tblAssets.IPAddress,
tsysIPLocations.IPLocation,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tsysOS.OSname As OS,
Case
When subquery1.featureCaption Is Not Null Then 'Yes'
Else 'No'
End As [RD Connection Broker],
Case
When subquery2.featureCaption Is Not Null Then 'Yes'
Else 'No'
End As [RD Web Access],
Case
When subquery3.featureCaption Is Not Null Then 'Yes'
Else 'No'
End As [RD Virtualization Host],
Concat(subquery4.Average, ' ', subquery4.Unit) As [Avg CPU Usage],
Concat(subquery5.Average, ' ', subquery5.Unit) As [Avg RAM Usage],
Concat(subquery6.Average, ' ', subquery6.Unit) As [Total Disk Space Usage],
Case
When TsysLastscan.Lasttime < GetDate() - 1 Then
'Last registry scan more than 24 hours ago! Scanned registry information may not be up-to-date. Try rescanning this machine.'
End As Comment,
tblAssets.Lastseen,
tblAssets.Lasttried,
TsysLastscan.Lasttime As LastFeatureScan
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysOS On tsysOS.OScode = tblAssets.OScode
Inner Join TsysLastscan On tblAssets.AssetID = TsysLastscan.AssetID
Inner Join TsysWaittime On TsysWaittime.CFGCode = TsysLastscan.CFGcode
Inner Join tsysIPLocations On tsysIPLocations.LocationID =
tblAssets.LocationID
Left Join (Select tblFeature.AssetId,
tblFeatureUni.featureName,
tblFeatureUni.featureCaption,
tblFeatureUni.addedDate
From tblFeature
Inner Join tblFeatureUni On tblFeatureUni.featUniID = tblFeature.featUniId
Where tblFeatureUni.featureName = 'SBMgr-UI') As subquery1 On
subquery1.AssetId = tblAssets.AssetID
Left Join (Select tblFeature.AssetId,
tblFeatureUni.featureName,
tblFeatureUni.featureCaption,
tblFeatureUni.addedDate
From tblFeature
Inner Join tblFeatureUni On tblFeatureUni.featUniID = tblFeature.featUniId
Where tblFeatureUni.featureName = 'WebAccess') As subquery2 On
subquery2.AssetId = tblAssets.AssetID
Left Join (Select tblFeature.AssetId,
tblFeatureUni.featureName,
tblFeatureUni.featureCaption,
tblFeatureUni.addedDate
From tblFeature
Inner Join tblFeatureUni On tblFeatureUni.featUniID = tblFeature.featUniId
Where tblFeatureUni.featureName = 'VmHostAgent') As subquery3 On
subquery3.AssetId = tblAssets.AssetID
Left Join (Select Top 1000000 a.AssetID,
Min(a.AssetName) As AssetName,
Min(pcm.Name) As MetricName,
Min(pcm.Unit) As Unit,
Round(Cast(Avg(pcsm.Value) As float), 0) As Average,
Min(pcsmi.Name) As Identifier
From tblAssets a
Inner Join tblAssetCustom ac On a.AssetID = ac.AssetID
Inner Join tsysAssetTypes at On at.AssetType = a.Assettype
Inner Join tblState On tblState.State = ac.State
Inner Join tblPerformanceCountersScan pcs On pcs.AssetId = a.AssetID
Inner Join tblPerformanceCountersScanMetric pcsm On
pcsm.PerformanceCountersScanId = pcs.Id
Inner Join tsysPerformanceCounterMetric pcm On pcm.Id = pcsm.Metric
Left Join tblPerformanceCountersScanMetricIdentifier pcsmi On
pcsmi.Id = pcsm.PerformanceCountersScanMetricIdentifierId
Where tblState.Statename = 'Active' And pcs.ScanDateTime > GetDate() - 7 And
a.Assettype = -1 And pcm.Name Like '%CPU%'
Group By a.AssetID,
pcm.Id,
pcsmi.Id
Order By AssetName,
MetricName) As subquery4 On subquery4.AssetID = tblAssets.AssetID
Left Join (Select Top 1000000 a.AssetID,
Min(a.AssetName) As AssetName,
Min(pcm.Name) As MetricName,
Min(pcm.Unit) As Unit,
Round(Cast(Avg(pcsm.Value) As float), 0) As Average,
Min(pcsmi.Name) As Identifier
From tblAssets a
Inner Join tblAssetCustom ac On a.AssetID = ac.AssetID
Inner Join tsysAssetTypes at On at.AssetType = a.Assettype
Inner Join tblState On tblState.State = ac.State
Inner Join tblPerformanceCountersScan pcs On pcs.AssetId = a.AssetID
Inner Join tblPerformanceCountersScanMetric pcsm On
pcsm.PerformanceCountersScanId = pcs.Id
Inner Join tsysPerformanceCounterMetric pcm On pcm.Id = pcsm.Metric
Left Join tblPerformanceCountersScanMetricIdentifier pcsmi On
pcsmi.Id = pcsm.PerformanceCountersScanMetricIdentifierId
Where tblState.Statename = 'Active' And pcs.ScanDateTime > GetDate() - 7 And
a.Assettype = -1 And pcm.Name = 'Free physical memory'
Group By a.AssetID,
pcm.Id,
pcsmi.Id
Order By AssetName,
MetricName) As subquery5 On subquery5.AssetID = tblAssets.AssetID
Left Join (Select Top 1000000 a.AssetID,
Min(a.AssetName) As AssetName,
Min(pcm.Name) As MetricName,
Min(pcm.Unit) As Unit,
Round(Cast(Avg(pcsm.Value) As float), 0) As Average,
Round(Cast(Sum(pcsm.Value) As float), 0) As Summ,
Min(pcsmi.Name) As Identifier
From tblAssets a
Inner Join tblAssetCustom ac On a.AssetID = ac.AssetID
Inner Join tsysAssetTypes at On at.AssetType = a.Assettype
Inner Join tblState On tblState.State = ac.State
Inner Join tblPerformanceCountersScan pcs On pcs.AssetId = a.AssetID
Inner Join tblPerformanceCountersScanMetric pcsm On
pcsm.PerformanceCountersScanId = pcs.Id
Inner Join tsysPerformanceCounterMetric pcm On pcm.Id = pcsm.Metric
Left Join tblPerformanceCountersScanMetricIdentifier pcsmi On
pcsmi.Id = pcsm.PerformanceCountersScanMetricIdentifierId
Where tblState.Statename = 'Active' And pcs.ScanDateTime > GetDate() - 7 And
a.Assettype = -1 And pcm.Name = 'Percentage disk used space' And
pcsmi.Name = 'Total'
Group By a.AssetID,
pcm.Id,
pcsmi.Id
Order By AssetName,
MetricName) As subquery6 On subquery6.AssetID = tblAssets.AssetID
Where tblAssetCustom.State = 1 And TsysWaittime.CFGname = 'FEATURES' And
(subquery1.AssetId Is Not Null Or subquery2.AssetId Is Not Null Or
subquery3.AssetId Is Not Null)
Order By tblAssets.Domain,
tblAssets.AssetName