→ The Lansweeper Customer Excellence Awards 2024 - Submit Your Project Now! Learn More & Enter Here
‎06-19-2024 03:32 PM
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;
Solved! Go to Solution.
‎06-19-2024 05:37 PM
‎06-19-2024 05:37 PM
‎06-20-2024 11:56 PM
Thanks that will works for me
Experience Lansweeper with your own data. Sign up now for a 14-day free trial.
Try Now