CM SQL Query for is LedBat Enabled on the Site Role Servers

I had the occasion to want to check the DP and SUP configurations for whether or not LEDBat was enabled for those roles, and yes... I absolutely could go look in the console, go to every server with a dp or sup role, right-click, and look.  But... being me, I knew that information had to be in SQL somewhere, so I spent time looking for it.  Could I have just done this manually, in less time?  Likely.  But... since I've spent the time creating the sql to look this up, this blog gets to have the results of my research.  The one element I'm not sure of is that "Flags" apparently has "something to do with whether or not a DP role has this enabled".  For me, if that value was a 4, that meant "yes", but since it IS a 'Flag' value, often that means that different values could have different meanings... but I couldn't find out what those meanings are.  So... if you find in your environment that 'Flags' == 4 vs. not == 4 doesn't necessarily mean "yes, DP LedBat is enabled", I know that's certainly a possibility, and you may need to adjust the sql query to match your reality.

Anyway... for a starting point, here's the SQL I have so far.

select
LEFT(RIGHT(SUBSTRING(sru.NalPath,0,CHARINDEX(']',sru.NALPath)),LEN(SUBSTRING(sru.NalPath,0,CHARINDEX(']',sru.NALPath)))-12)   ,LEN(RIGHT(SUBSTRING(sru.NalPath,0,CHARINDEX(']',sru.NALPath)),LEN(SUBSTRING(sru.NalPath,0,CHARINDEX(']',sru.NALPath)))-12))-2) as 'ServerName'
,case when srup.Name = 'Flags' then 'DP LedBat'
else srup.Name end as 'Name',
Case
when srup.Name = 'Flags' and srup.Value3 = 4 then '1'
when srup.Name = 'Flags' and srup.Value3 <> 4 then '0'
when srup.Name = 'SUP LEDBAT' then cast(Value3 as varchar)
when srup.Name = 'LocalDriveDOINC' then cast(Value1 as varchar)
when srup.name = 'DiskSpaceDOINC' then Cast(value3 as varchar) + ' ' + Value1
when srup.Name = 'RetainDOINCCache' then cast(value3 as varchar)
End as 'Value'
,case when RoleTypeID = 3 then 'DPSetting'
   when RoleTypeID = 12 then 'SUPSetting' end as 'RoleType'
,roletypeid
from SC_SysResUse_Property srup with (nolock)
join sc_sysresuse sru with (nolock) on sru.id=srup.SysResUseID
where srup.name in ('SUP LEDBAT','Flags','DiskSpaceDOINC','LocalDriveDOINC','RetainDOINCCache')
and roletypeID in (3,12)
order by 'Servername',sru.roletypeid, srup.Name

and in my lab (with the one and only one server), this is the result.  A SupRole only has a single result of yes/no (1/0).
For a DP, there is the 'enabled at all', then what might have been chosen for 'Local drive to be used' (LocalDriveDOINC), DiskSpace (DiskSpaceDOINC), and whether or not to retain cache when disabling the Connected Cache server (RetainDOINCCache).

ServerName     Name              Value      RoleType     roletypeid
HS5.b.b        DiskSpaceDOINC    70 GB      DPSetting    3
HS5.b.b        DP LedBat         1          DPSetting    3
HS5.b.b        LocalDriveDOINC   Automatic  DPSetting    3
HS5.b.b        RetainDOINCCache  1          DPSetting    3
HS5.b.b        SUP LEDBAT        1          SUPSetting   12

Another tip:

While interactively logged into the server, Get-Nettcpsetting -settingName InternetCustom | Select CongestionProvider should result in LEDBAT.  Get-NetTransportFilter -SettingName InternetCustom   should list the TCP Ports.