Editing built-in report: "Unauthorized administrators" to show if user is Enabled in AD

[metador]

I see there is a table: "tblADusers.IsEnabled", but can't succesfully integrate with given query.
I want to include in the same report to show if the user (shown in report - UserName) to check if it's enabled in AD.

Select Top (1000000) tsysOS.Image As icon,
  tblAssets.AssetID,
  tblAssets.AssetName,
  tblUsersInGroup.Domainname,
  tblUsersInGroup.Username,
  tblUsersInGroup.Lastchanged,
  tblAssets.Domain,
  tblAssets.IPAddress,
  tblAssets.Description,
  tblAssetCustom.Manufacturer,
  tblAssetCustom.Model,
  tblAssetCustom.Location,
  tsysIPLocations.IPLocation,
  tsysOS.OSname As OS,
  tblAssets.SP,
  tblAssets.Firstseen As [Created at],
  tblAssets.Lastseen As [Last successful scan]
From tblUsersInGroup
  Inner Join tblAssets On tblUsersInGroup.AssetID = tblAssets.AssetID
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
  Left Outer Join tsysIPLocations On tsysIPLocations.StartIP <=
      tblAssets.IPNumeric And tsysIPLocations.EndIP >= tblAssets.IPNumeric
Where Not Exists(Select tblAssets.AssetName As Domain,
      tblUsers.Name As Username
    From tblAssets Inner Join tblUsers On tblAssets.AssetID = tblUsers.AssetID
    Where tblUsers.BuildInAdmin = 1 And tblUsersInGroup.Domainname =
      tblAssets.AssetName And tblUsersInGroup.Username = tblUsers.Name) And
  Not Exists(Select tsysadmins.Domain,
      tsysadmins.AdminName As username From tsysadmins
    Where tblUsersInGroup.Domainname Like tsysadmins.Domain And
      tblUsersInGroup.Username Like tsysadmins.AdminName) And
  tblUsersInGroup.Admingroup = 1 And tblAssetCustom.State = 1
Order By tblAssets.AssetName

Or at least to hide "Disbled in AD" users from this report.

[Andy_Sismey]

Hi,

So here is how I would link the User Status with True False or "User Enabled" / "Disabled"

Select Top (1000000) tsysOS.Image As icon,
  tblAssets.AssetID,
  tblAssets.AssetName,
  tblUsersInGroup.Domainname,
  tblUsersInGroup.Username,
  tblUsersInGroup.Lastchanged,
  tblAssets.Domain,
  tblAssets.IPAddress,
  tblAssets.Description,
  tblAssetCustom.Manufacturer,
  tblAssetCustom.Model,
  tblAssetCustom.Location,
  tsysIPLocations.IPLocation,
  tsysOS.OSname As OS,
  tblAssets.SP,
  tblAssets.Firstseen As [Created at],
  tblAssets.Lastseen As [Last successful scan],
  tblADusers.IsEnabled,
  Case
    When tblADusers.IsEnabled = 'True' Then 'User Is Enabled'
    Else 'User Disabled'
  End As [Account Status]
From tblUsersInGroup
  Inner Join tblAssets On tblUsersInGroup.AssetID = tblAssets.AssetID
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
  Left Outer Join tsysIPLocations On tsysIPLocations.StartIP <=
      tblAssets.IPNumeric And tsysIPLocations.EndIP >= tblAssets.IPNumeric
  Inner Join lansweeperdb.dbo.tblADusers On tblUsersInGroup.Username =
      tblADusers.Username
Where Not Exists(Select tblAssets.AssetName As Domain,
      tblUsers.Name As Username
    From tblAssets Inner Join tblUsers On tblAssets.AssetID = tblUsers.AssetID
    Where tblUsers.BuildInAdmin = 1 And tblUsersInGroup.Domainname =
      tblAssets.AssetName And tblUsersInGroup.Username = tblUsers.Name) And
  Not Exists(Select tsysadmins.Domain,
      tsysadmins.AdminName As username From tsysadmins
    Where tblUsersInGroup.Domainname Like tsysadmins.Domain And
      tblUsersInGroup.Username Like tsysadmins.AdminName) And
  tblUsersInGroup.Admingroup = 1 And tblAssetCustom.State = 1
Order By tblAssets.AssetName

[SweeperTest25]

Hi, I really like your report. I would like to show only the unauthorized administrators, considering up to 3 or 4 levels of group nesting.
For example, I have GroupA, which is a member of Administrators, GroupB, which is a member of GroupA, and User1, who is a member of GroupB. 

I know Lansweeper allows adding authorized users and groups manually, but I would prefer not to add all of them by hand.
Could you please help me with this? Thank you!

[Mister_Nobody]

https://community.lansweeper.com/t5/reports-analytics/unauthorized-administrators-with-3-levels/m-p/...