→ 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: 
DarrenC
Engaged Sweeper
Hi,

New to Lansweeper after taking over from somebody who has retired, I need to be able to list out the directory group and the username/asset name(computer).

I can get the asset name but no matter what I try I cannot link a table.

Can you help?




Select Top 1000000 tblADusers.Username,
tblADusers.Firstname,
tblADusers.Lastname,
tblADusers.Displayname,
tblADusers.email,
tblADGroups.Name As ADGroupName,
tblADGroups.Description As ADGroupDescription,
tblADusers.Userdomain
From tblADMembership
Left Join tblADGroups On tblADMembership.ParentAdObjectID =
tblADGroups.ADObjectID
Right Join tblADusers On tblADMembership.ChildAdObjectID =
tblADusers.ADObjectID
Order By tblADusers.Userdomain,
tblADusers.Username,
ADGroupName
1 ACCEPTED SOLUTION
Susan_A
Lansweeper Alumni
It's not entirely clear to me, but I assume that what you are after is a list of your AD users, their groups and their last logged on computer. If so, please add the report below to your Lansweeper installation by following these instructions.
Select Top 1000000 SubQuery.Username,
SubQuery.Domain As Userdomain,
tblADusers.Firstname,
tblADusers.Lastname,
tblADusers.Name,
tblADusers.Displayname,
tblADusers.email,
tblADGroups.Name As ADGroupName,
tblADGroups.Description As ADGroupDescription,
SubQuery.LastLogon,
tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain
From tblCPlogoninfo
Inner Join (Select Top 1000000 tblCPlogoninfo.Username,
tblCPlogoninfo.Domain,
Max(tblCPlogoninfo.logontime) As LastLogon
From tblCPlogoninfo
Group By tblCPlogoninfo.Username,
tblCPlogoninfo.Domain) SubQuery On tblCPlogoninfo.Username =
SubQuery.Username And tblCPlogoninfo.Domain = SubQuery.Domain And
tblCPlogoninfo.logontime = SubQuery.LastLogon
Inner Join tblAssets On tblAssets.AssetID = tblCPlogoninfo.AssetID
Inner Join tblADusers On tblADusers.Username = tblCPlogoninfo.Username And
tblADusers.Userdomain = tblCPlogoninfo.Domain
Left Join tblADMembership On tblADMembership.ChildAdObjectID =
tblADusers.ADObjectID
Left Join tblADGroups On tblADMembership.ParentAdObjectID =
tblADGroups.ADObjectID
Order By Userdomain,
SubQuery.Username,
ADGroupName

View solution in original post

1 REPLY 1
Susan_A
Lansweeper Alumni
It's not entirely clear to me, but I assume that what you are after is a list of your AD users, their groups and their last logged on computer. If so, please add the report below to your Lansweeper installation by following these instructions.
Select Top 1000000 SubQuery.Username,
SubQuery.Domain As Userdomain,
tblADusers.Firstname,
tblADusers.Lastname,
tblADusers.Name,
tblADusers.Displayname,
tblADusers.email,
tblADGroups.Name As ADGroupName,
tblADGroups.Description As ADGroupDescription,
SubQuery.LastLogon,
tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain
From tblCPlogoninfo
Inner Join (Select Top 1000000 tblCPlogoninfo.Username,
tblCPlogoninfo.Domain,
Max(tblCPlogoninfo.logontime) As LastLogon
From tblCPlogoninfo
Group By tblCPlogoninfo.Username,
tblCPlogoninfo.Domain) SubQuery On tblCPlogoninfo.Username =
SubQuery.Username And tblCPlogoninfo.Domain = SubQuery.Domain And
tblCPlogoninfo.logontime = SubQuery.LastLogon
Inner Join tblAssets On tblAssets.AssetID = tblCPlogoninfo.AssetID
Inner Join tblADusers On tblADusers.Username = tblCPlogoninfo.Username And
tblADusers.Userdomain = tblCPlogoninfo.Domain
Left Join tblADMembership On tblADMembership.ChildAdObjectID =
tblADusers.ADObjectID
Left Join tblADGroups On tblADMembership.ParentAdObjectID =
tblADGroups.ADObjectID
Order By Userdomain,
SubQuery.Username,
ADGroupName