Local admin group report based on domain role

[Noobmode]

/** 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

I have put notes in to help anyone that may be just starting out with SQL. Once you paste the query into Lansweeper's SQL editor, it will remove the comments.

USE CASE: If you require a report that is more customizable than the canned unauthorized users report, this may be useful.

Instructions: Read the notes to provide context for the different fields needed.

Notes: t3.DomainRole = 1 is your workstations, t3.DomainRole > 1 is your servers.

I hope this helps someone out there!

[FNB_Cliff]

Great Information. Using the default "Computer: Unauthorized administrators" report, can someone explain how to remove certain UserNames from the report? For instance, there's one account on all machines that's a local admin and I'd like to hide it from the report.

Thanks,
BC



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 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

[JacobH]

Thanks Noob:

Here's an additional report that helps with auditing local administrator group adds/removes - past 1 day

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