‎09-11-2019 03:38 PM
/** Select required fields from tables **/
Select Top 1000000 t1.AssetID,
t1.AssetName,
t2.Username,
t2.Domainname,
t2.Groupname,
t3.Domainrole,
/** Sets naming convention based on domain role **/
Case t3.Domainrole
When 1 Then 'WORKSTATION'
Else 'SERVER'
End As DomainRoleDesc
/** Sets table alias and provides joins required for report **/
From tblAssets As t1
Inner Join tblUsersInGroup As t2 On t1.AssetID = t2.AssetID
Inner Join tblComputersystem As t3 On t1.AssetID = t3.AssetID And
/** Set domain role to select server or workstation, 1 is workstation, greater than 1 is server **/
t3.Domainrole > 1
/** If you have local users for management you dont care about, exclude them here with a does not equal, you will need an exclusion for each group or user **/
Where t2.Username != 'LOCAL USERS OR GROUPS YOU DONT WANT TO SEE' And t2.Groupname = 'administrators'
/** Orders by system name **/
Order By t1.AssetName
‎03-05-2020 04:38 PM
‎09-18-2019 06:24 PM
Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tblUsersInGroupHist.Username,
Case tblUsersInGroupHist.Action
When 1 Then 'added to'
When 2 Then 'removed from'
End As action,
tblUsersInGroupHist.Groupname,
tblUsersInGroupHist.Lastchanged
From tblUsersInGroupHist
Inner Join tblAssets On tblAssets.AssetID = tblUsersInGroupHist.AssetID
Inner Join tblComputersystem On tblComputersystem.AssetID =
tblUsersInGroupHist.AssetID
Where tblUsersInGroupHist.Groupname = 'Administrators' And
tblUsersInGroupHist.Lastchanged > GetDate() - 1
Experience Lansweeper with your own data. Sign up now for a 14-day free trial.
Try Now