‎07-09-2015 10:29 PM
Solved! Go to Solution.
‎07-13-2015 02:34 PM
Select Top 100000 tblADusers.Displayname,
tblADusers.Firstname,
tblADusers.Lastname,
tblAssets.AssetName,
tblAssets.AssetID,
tLastLogon.[last logon]
From tblADusers
Left Join (Select tblCPlogoninfo.AssetID,
Max(tblCPlogoninfo.logontime) As [last logon],
tblCPlogoninfo.Username,
tblCPlogoninfo.Domain
From tblCPlogoninfo
Group By tblCPlogoninfo.AssetID,
tblCPlogoninfo.Username,
tblCPlogoninfo.Domain) tLastLogon On tLastLogon.Username =
tblADusers.Username And tLastLogon.Domain = tblADusers.Userdomain
Left Join tblAssets On tLastLogon.AssetID = tblAssets.AssetID
Inner Join tblADMembership On tblADusers.ADObjectID =
tblADMembership.ChildAdObjectID
Inner Join tblADGroups On tblADMembership.ParentAdObjectID =
tblADGroups.ADObjectID
Where tblADGroups.Name Like 'Example AD group '
Order By tblADusers.Displayname,
tLastLogon.[last logon] Desc
‎07-14-2015 02:37 PM
‎07-14-2015 11:09 AM
‎07-13-2015 05:53 PM
‎07-13-2015 02:34 PM
Select Top 100000 tblADusers.Displayname,
tblADusers.Firstname,
tblADusers.Lastname,
tblAssets.AssetName,
tblAssets.AssetID,
tLastLogon.[last logon]
From tblADusers
Left Join (Select tblCPlogoninfo.AssetID,
Max(tblCPlogoninfo.logontime) As [last logon],
tblCPlogoninfo.Username,
tblCPlogoninfo.Domain
From tblCPlogoninfo
Group By tblCPlogoninfo.AssetID,
tblCPlogoninfo.Username,
tblCPlogoninfo.Domain) tLastLogon On tLastLogon.Username =
tblADusers.Username And tLastLogon.Domain = tblADusers.Userdomain
Left Join tblAssets On tLastLogon.AssetID = tblAssets.AssetID
Inner Join tblADMembership On tblADusers.ADObjectID =
tblADMembership.ChildAdObjectID
Inner Join tblADGroups On tblADMembership.ParentAdObjectID =
tblADGroups.ADObjectID
Where tblADGroups.Name Like 'Example AD group '
Order By tblADusers.Displayname,
tLastLogon.[last logon] Desc
Experience Lansweeper with your own data. Sign up now for a 14-day free trial.
Try Now