‎04-08-2022 12:05 PM
‎04-11-2022 11:56 AM
Select Top 1000000 tsysOS.Image As icon,
tblAssets.AssetID,
tblAssets.AssetName,
tblUsersInGroup.Domainname,
tblUsersInGroup.Username,
tblUsersInGroup.Lastchanged,
tblAssets.Domain,
tblAssets.IPAddress,
tblAssets.Description,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tblAssetCustom.Location,
tsysIPLocations.IPLocation,
tsysOS.OSname As OS,
tblAssets.SP As SP,
tblAssets.Firstseen,
tblAssets.Lastseen
From tblUsersInGroup
Inner Join tblAssets On tblUsersInGroup.AssetID = tblAssets.AssetID
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Left Join tsysIPLocations On tsysIPLocations.StartIP <= tblAssets.IPNumeric
And tsysIPLocations.EndIP >= tblAssets.IPNumeric
Where (tblUsersInGroup.Username Not Like '%Administrator1%' Or
tblUsersInGroup.Username Not Like '%Administartor2%') And
Not Exists(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 =
tblAssets.AssetName And tblUsersInGroup.Username = tblUsers.Name) And
Not Exists(Select tsysadmins.Domain,
tsysadmins.AdminName As username From tsysadmins
Where tblUsersInGroup.Domainname Like tsysadmins.Domain And
tblUsersInGroup.Username Like tsysadmins.AdminName) And
tblUsersInGroup.Admingroup = 1 And tblAssetCustom.State = 1
Order By tblAssets.AssetName
Experience Lansweeper with your own data. Sign up now for a 14-day free trial.
Try Now