‎07-25-2013 12:52 PM
‎07-26-2013 04:58 PM
Select Top 1000000 tblUsersInGroup.AssetID,
tblAssets.AssetUnique,
tblAssets.Domain,
tblUsersInGroup.Username,
tblUsersInGroup.Domainname As Userdomain,
tsysOS.Image As icon,
Max(tblCPlogoninfo.logontime) As LastLogon
From tblUsersInGroup
Inner Join tblAssets On tblUsersInGroup.AssetID = tblAssets.AssetID
Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Left Join tblCPlogoninfo On tblCPlogoninfo.AssetID = tblUsersInGroup.AssetID
And tblCPlogoninfo.Username = tblUsersInGroup.Username And
tblCPlogoninfo.Domain = tblUsersInGroup.Domainname
Where Not Exists(Select Distinct * From (Select tblAssets.AssetName As Domain,
tblUsers.Name As Username
From tblAssets
Inner Join tblUsers On tblAssets.AssetID = tblUsers.AssetID
Where tblUsers.BuildInAdmin = 1 And tblUsersInGroup.Domainname Like
tblAssets.AssetName And tblUsersInGroup.Username Like tblUsers.Name
Union
Select tsysadmins.Domain,
tsysadmins.AdminName As username
From tsysadmins
Where tblUsersInGroup.Domainname Like tsysadmins.Domain And
tblUsersInGroup.Username Like tsysadmins.AdminName) DERIVEDTBL) And
tblUsersInGroup.Admingroup = 1
Group By tblUsersInGroup.AssetID,
tblAssets.AssetUnique,
tblAssets.Domain,
tblUsersInGroup.Username,
tblUsersInGroup.Domainname,
tsysOS.Image
Order By tblAssets.Domain,
tblAssets.AssetUnique,
Userdomain,
tblUsersInGroup.Username
Experience Lansweeper with your own data. Sign up now for a 14-day free trial.
Try Now