→ The Lansweeper Customer Excellence Awards 2024 - Submit Your Project Now! Learn More & Enter Here

cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
Nearuppp
Engaged Sweeper

Hello,

I've been assigned to make reports on our different assets and to make sure they don't have local accounts, but I got an issue while doing so is that there was groups inside the assets and therefore i need to make a recursive query to do so. I've tried multiple query without success... I've done a schema because i was struggling to understand the structure maybe it can help you help me... 

 

And here's the query i've tried for the first stage : 

SELECT
tblAssets.AssetName,
tblUsersInGroup.Groupname AS 'Access',
tblADObjects.sAMAccountName AS 'User',
tblADMembership.ChildAdObjectID,
tblUsersInGroup.Username,
tblADMembership.ParentAdObjectID,
tblADGroups.Name AS 'Group Member',
tblUsersInGroup.Domainname,
tblAssets.Lastseen,
tblAssetCustom.Contact
FROM tblUsersInGroup
JOIN tblAssets ON tblUsersInGroup.AssetID = tblAssets.AssetID
JOIN tblAssetCustom ON tblAssets.AssetID = tblAssetCustom.AssetID
JOIN tblADGroups ON tblUsersInGroup.Username = tblADGroups.Name
JOIN tblADMembership ON tblADGroups.ADObjectID = tblADMembership.ParentAdObjectID
JOIN tblADObjects ON tblADMembership.ChildAdObjectID = tblADObjects.ADObjectID
WHERE tblUsersInGroup.Username NOT IN ('Domain Users', 'Admin_Limited', 'Domain Admins')
ORDER BY tblAssets.AssetName ASC;

 

 

1 ACCEPTED SOLUTION
2 REPLIES 2