cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
jasonwch
Engaged Sweeper
Hi,

Please advise for SQL to report logged user match users in administrator user group

Thanks
1 REPLY 1
Hemoco
Lansweeper Alumni
You can use the report below to list unauthorized admins and the last logon event for each admin. Admins can be authorized in the Configuration/Website Management/User Pages section of the Lansweeper web console.
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