Report server local group members and enumerate nested domain group members

[swept]

Can anyone offer a report similar to the built in Config >> User Info >> "Users in Groups" listing that's under each Windows computer's page but which also enumerates nested domain group members?   This would be for Lansweeper 8.0 with two AD domains scanned and servers with cross-domain local group members.   Columns which distinguish domain of group members and whether they are a local user, domain user, domain group, or domain user via nested domain group would be a secondary objective.  Attempts so far either have lead to Lansweeper freezing or only incomplete results.  Help is appreciated.

This enumerates domain group members but misses local users and generates inaccurate listings from any groups that have identical naming on each domain:

 Select Distinct Top 1000000 tblAssets.AssetUnique,
  tblUsersInGroup.AssetID,
  tblAssets.Domain As [Server Domain],
  tblUsersInGroup.Domainname As [Member Domain],
  tblUsersInGroup.Username,
  tblUsersInGroup.Accounttype,
  tblAssets.IPAddress,
  tblUsersInGroup.Groupname,
  tblAssets.AssetName,
  tblADusers.Username As Username1,
  tblADGroups.Name,
  tblADMembership.ChildAdObjectID
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
  Inner Join tblADGroups On tblUsersInGroup.Username = tblADGroups.Name
  Inner Join tblADMembership On tblADGroups.ADObjectID =
    tblADMembership.ParentAdObjectID
  Inner Join tblADusers On tblADMembership.ChildAdObjectID =
    tblADusers.ADObjectID
Order By tblAssets.AssetName

This includes local users but doesn't enumerate domain group members:

Select Distinct Top 1000000 tblAssets.AssetUnique,
  tblUsersInGroup.AssetID,
  tblAssets.Domain As [Server Domain],
  tblUsersInGroup.Domainname As [Member Domain],
  tblUsersInGroup.Username,
  tblUsersInGroup.Accounttype,
  tblUsersInGroup.Groupname,
  tblAssets.AssetName,
  tblADusers.Username As Username1,
  tblADGroups.Name,
  tblADMembership.ChildAdObjectID,
  tblADGroups.GroupType
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
  Inner Join tblADGroups On tblUsersInGroup.Groupname = tblADGroups.Name
  Inner Join tblADMembership On tblADGroups.ADObjectID =
    tblADMembership.ParentAdObjectID
  Inner Join tblADusers On tblADMembership.ChildAdObjectID =
    tblADusers.ADObjectID
Order By tblAssets.AssetName

References:

https://community.lansweeper.com/t5/forum/users-with-direct-access-to-servers-report/m-p/60339 (results in sql select error even with report alphanumeric populated)
https://community.lansweeper.com/t5/forum/scan-local-administrators-group-and-expand-any-groups-with...

[rader]

I've added in the Scan Server at the end of the report. That may help with multiple scan servers.

As for the nested domain groups, something like <Main Sec. Group>|<Secondary Sec. Group>|<Final Sec. Group> or <Main Sec. Group>|<Secondary Sec. Group>|<Username>?

I don't have nested security groups so my reports don't show data like that. I can play with some scenarios to see what I can come up with though.

Select Top 1000000 tblUsersInGroup.Groupname As Groupname,
  tblUsersInGroup.Domainname As [Domain or Local Computer],
  tblUsersInGroup.Username As Username,
  tblAssets1.AssetName As [Computer Name],
  tblAssets.Scanserver As [LS Scanning Server]
From tblAssets
  Inner Join tblUsersInGroup On tblAssets.AssetID = tblUsersInGroup.AssetID
  Inner Join htblusers On tblAssets.Username = htblusers.username
  Inner Join tblAssets tblAssets1 On
      tblAssets1.AssetID = tblUsersInGroup.AssetID
Group By tblUsersInGroup.Groupname,
  tblUsersInGroup.Domainname,
  tblUsersInGroup.Username,
  tblAssets1.AssetName,
  tblAssets.Scanserver
Order By Groupname

[swept]

There are multiple (two) AD domains scanned from a single Lansweeper scan server rather than multiple Lansweeper scan servers.  I mentioned this aspect because inaccurate extra results populate in the report output if a member domain group is queried in the sql code by its name alone for membership and the name exists on both domains.

99% of the target membership is Local group >> domain group 1 >> domain user.  Outliers can exist at additional recursion depth Local group >> domain group 1 >> domain group 2 >> domain user. 

[rader]

Not sure if this will answer your query as I don't have a multiple domain scanning setup. I do however have LSAgents reporting in to another site that I monitor, and those agents are in differing domains. So I rewrote the report to bring in those results. I did add the scanserver and an OU column to help identify some domain results, but it's only reporting the local scanning server domain for the OU field. That may help you, or not. From there you may be able to tweak the results.

I also looked into the tblADGroups and tblADObjects tables for answers, but every time I added those to the query, it seemed to limit the results to only my scanning server domain. Therefore it excluded the LSAgent results. You may want to add those tables to test on your domain scanning model. Your results may be better than mine.

Here's the latest code to test out. I'm pretty much at the end of my testing capability, mainly because I don't have the data to extract like you may need. Good luck.

Select Top (1000000) tblUsersInGroup.Groupname,
  tblAssets.Domain As [Local & LSAgent Domains],
  tblUsersInGroup.Username,
  tblAssets.AssetID,
  tblAssets.AssetName,
  tblADComputers.OU,
  tblAssets.Scanserver
From tblAssets
  Left Outer Join tblADComputers On tblAssets.AssetID = tblADComputers.AssetID
  Inner Join tblUsersInGroup On tblAssets.AssetID = tblUsersInGroup.AssetID
Where tblAssets.Assettype <> 66
Order By tblAssets.AssetName

[rader]

Thanks for the clarification. Let me see what I can come up with.

[Mercedes_O]

Thanks for helping with this @rader  pinning this to the top to share with the rest of the community

[rader]

I don't have more than one scan server, so I can't test for that.

The report duplicates exactly the Config >> User Info >> "Users in Groups" format, all that's needed is to filter for the computer name in the last column.

Do you have an example, even if it's on a napkin, of the report style you're looking for? I might be able to fine tune the report from there.

[swept]

A report similar to the built in "Users in Groups" but which also enumerates nested domain group members.  Output would include:

local group >> local user (direct member)
local group >> ad domain user (direct member)
local group >> ad domain group (direct member)
local group >> ad domain group >> ad domain group members (indirect member)

[swept]

I don't think this is recursively querying down from the local group membership. Domain group members are not present in the output.

[rader]

Took me awhile but here's a duplicate report with an added bonus of the ability to sort on the Computer Name for ease of sorting thru the flak.

Select Top 1000000 tblUsersInGroup.Groupname As Groupname,
  tblUsersInGroup.Domainname As [Domain or Local Computer],
  tblUsersInGroup.Username As Username,
  tblAssets1.AssetName As [Computer Name]
From tblAssets
  Inner Join tblUsersInGroup On tblAssets.AssetID = tblUsersInGroup.AssetID
  Inner Join htblusers On tblAssets.Username = htblusers.username
  Inner Join tblAssets tblAssets1 On
      tblAssets1.AssetID = tblUsersInGroup.AssetID
Group By tblUsersInGroup.Groupname,
  tblUsersInGroup.Domainname,
  tblUsersInGroup.Username,
  tblAssets1.AssetName
Order By Groupname