Remote Desktop Services Servers Overview

Find Servers Facilitating Remote Desktop Services

Remote Desktop Services (RDS) is a Microsoft platform for virtualization, allowing secure, remote desktop access or for running virtualized applications. With remote work being the norm, these services are critical to a company’s operation. If you use RDS it is important to be able to keep an eye on the servers maintaining it.

To help you get an overview of the RDS servers status, you can use the report below to get a list of all machines in your environment that have the one of the server roles installed that are part of an RDS environment. Additionally, the report provides info on the system performance so you know when a server might be close to hitting its limit. You can read more about the report’s use case in the Pro Tips blog post.

Remote Desktop Services Servers Overview Query

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 On tsysAssetTypes.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 On tsysAssetTypes.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 On tsysAssetTypes.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

Show

Hide