Report Local Account + AD Group member

[Nearuppp]

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;

[Mister_Nobody · Accepted solution]

https://community.lansweeper.com/t5/reports-analytics/privileged-ad-users-in-local-groups-report/td-...

[Nearuppp]

Thanks that will works for me