Terminal Servers (role installed, not necessarily configured)

[MikW]

Report will show servers with TS role installed but not necessarily configured (empty 'Value' field).

*Requires configuring scan of RegPaths
- 'HKLM\SYSTEM\CurrentControlSet\services\TermService\Parameters\LicenseServers' and Value 'SpecifiedLicenseServers'
- 'HKLM\SOFTWARE\Microsoft\MSLicensing\Parameters' and Values 'EnterpriseServerMulti' and 'DomainLicenseServerMulti'

In case of 2000/2003 it will show either 'EnterpriseServerMulti' or 'DomainLicenseServerMulti' value depending on which is not empty unless both are empty then it will show empty.

Select Top 1000000 tsysOS.Image As icon,
  tblAssets.AssetID,
  tblAssets.AssetName,
  tblAssets.Domain,
  tblAssets.Username,
  tblAssets.Userdomain,
  Case When (SubQuery1.Valuename Is Not Null And SubQuery1.Valuename <> '') Or
    ((SubQuery2.Valuename Is Not Null And SubQuery2.Valuename <> '') Or
    (SubQuery3.Valuename Is Not Null And SubQuery3.Valuename <> '')) Then 'Yes'
    Else 'No' End As ValuenameFound,
  Case
    When tsysOS.OSname In ('Win 2008', 'Win 2008 R2', 'Win 2012', 'Win 2012 R2',
    'Win 2016') Then SubQuery1.Value
    When tsysOS.OSname In ('Win 2003', 'Win 2003 R2', 'Win 2000 S') And
    SubQuery2.Value Is Not Null Then SubQuery2.Value Else SubQuery3.Value
  End As Value,
  tsysOS.OSname,
  tblAssets.IPAddress,
  tsysIPLocations.IPLocation,
  tblAssets.Firstseen,
  tblAssets.Lastseen,
  tblAssets.Lasttried,
  TsysLastscan.Lasttime As LastRegistryScan,
  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,
  Case
    When tsysOS.OSname In ('Win 2008', 'Win 2008 R2', 'Win 2012', 'Win 2012 R2',
    'Win 2016') Then SubQuery1.Regkey
    When tsysOS.OSname In ('Win 2003', 'Win 2003 R2', 'Win 2000 S') And
    SubQuery2.Value Is Not Null Then SubQuery2.Regkey Else SubQuery3.Regkey
  End As Regkey,
  Case
    When tsysOS.OSname In ('Win 2008', 'Win 2008 R2', 'Win 2012', 'Win 2012 R2',
    'Win 2016') Then SubQuery1.Valuename
    When tsysOS.OSname In ('Win 2003', 'Win 2003 R2', 'Win 2000 S') And
    SubQuery2.Value Is Not Null Then SubQuery2.Valuename
    Else SubQuery3.Valuename End As Valuename,
  Case
    When tsysOS.OSname In ('Win 2008', 'Win 2008 R2', 'Win 2012', 'Win 2012 R2',
    'Win 2016') Then SubQuery1.Lastchanged
    When tsysOS.OSname In ('Win 2003', 'Win 2003 R2', 'Win 2000 S') And
    SubQuery2.Value Is Not Null Then SubQuery2.Lastchanged
    Else SubQuery3.Lastchanged End As Lastchanged,
  TsysChassisTypes.ChassisName
From tblAssets
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysOS On tsysOS.OScode = tblAssets.OScode
  Inner Join tsysIPLocations On tsysIPLocations.LocationID =
    tblAssets.LocationID
  Inner Join tblSystemEnclosure
    On tblAssets.AssetID = tblSystemEnclosure.AssetID
  Inner Join TsysChassisTypes On TsysChassisTypes.Chassistype =
    tblSystemEnclosure.ChassisTypes
  Inner Join TsysLastscan On tblAssets.AssetID = TsysLastscan.AssetID
  Inner Join TsysWaittime On TsysWaittime.CFGCode = TsysLastscan.CFGcode
  Left Join (Select Top 1000000 tblRegistry.AssetID,
    tblRegistry.Regkey,
    tblRegistry.Valuename,
    tblRegistry.Value,
    tblRegistry.Lastchanged
  From tblRegistry
  Where
    tblRegistry.Regkey Like
    '%SYSTEM\CurrentControlSet\services\TermService\Parameters\LicenseServers'
    And tblRegistry.Valuename = 'SpecifiedLicenseServers') SubQuery1
    On SubQuery1.AssetID = tblAssets.AssetID
  Left Join (Select Top 1000000 tblRegistry.AssetID,
    tblRegistry.Regkey,
    tblRegistry.Valuename,
    tblRegistry.Value,
    tblRegistry.Lastchanged
  From tblRegistry
  Where tblRegistry.Regkey Like '%SOFTWARE\Microsoft\MSLicensing\Parameters' And
    tblRegistry.Valuename = 'EnterpriseServerMulti') SubQuery2
    On SubQuery2.AssetID = tblAssets.AssetID
  Left Join (Select Top 1000000 tblRegistry.AssetID,
    tblRegistry.Regkey,
    tblRegistry.Valuename,
    tblRegistry.Value,
    tblRegistry.Lastchanged
  From tblRegistry
  Where tblRegistry.Regkey Like '%SOFTWARE\Microsoft\MSLicensing\Parameters' And
    tblRegistry.Valuename = 'DomainLicenseServerMulti') SubQuery3
    On SubQuery3.AssetID = tblAssets.AssetID
Where tsysOS.OSname In ('Win 2003', 'Win 2003 R2', 'Win 2000 S', 'Win 2008',
  'Win 2008 R2', 'Win 2012', 'Win 2012 R2', 'Win 2016') And
  (SubQuery1.Value Is Not Null Or SubQuery2.Value Is Not Null Or
    SubQuery3.Value Is Not Null) And tblAssetCustom.State = 1 And
  TsysWaittime.CFGname = 'registry'
Order By tblAssets.Domain,
  tblAssets.AssetName