ACT Group and Assett report

[DarrenC]

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

[Susan_A · Accepted solution]

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