→ The Lansweeper Customer Excellence Awards 2024 - Submit Your Project Now! Learn More & Enter Here
03-19-2023 12:06 AM - edited 03-19-2023 09:51 PM
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...
03-21-2023 12:27 AM
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
03-21-2023 01:05 AM - edited 03-21-2023 01:41 AM
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.
03-22-2023 07:49 PM
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
03-21-2023 10:49 PM
Thanks for the clarification. Let me see what I can come up with.
03-22-2023 10:35 AM
Thanks for helping with this @rader pinning this to the top to share with the rest of the community
03-20-2023 11:50 PM
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.
03-20-2023 11:56 PM - edited 03-21-2023 12:01 AM
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)
03-20-2023 11:44 PM
I don't think this is recursively querying down from the local group membership. Domain group members are not present in the output.
03-20-2023 11:31 PM
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
Experience Lansweeper with your own data. Sign up now for a 14-day free trial.
Try Now