Notification

Icon
Error

Local admin group report based on domain role - This report was designed to provide a more customized way of auditing local admins based on role

Posted: Wednesday, September 11, 2019 2:38:58 PM(UTC)
Noobmode

Noobmode

Member Original PosterPosts: 16
1
Like
Quote:

/** 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!
JacobH
#1JacobH Member Posts: 176  
posted: 9/18/2019 5:24:13 PM(UTC)
Thanks Noob:

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



Code:

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
FNB_Cliff
#2FNB_Cliff Member Posts: 2  
posted: 3/5/2020 4:38:44 PM(UTC)
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

Active Discussions

Lansweeper Windows 10 Upgrade to 2004
by  Alex Beaumier   Go to last post Go to first unread
Last post: Today at 1:18:01 PM(UTC)
Lansweeper snmp trap HP 1910 switch
by  info   Go to last post Go to first unread
Last post: Today at 11:52:17 AM(UTC)
Lansweeper Lsagent cloud relay changes the scanserver value
by  ghelpdesk  
Go to last post Go to first unread
Last post: Today at 2:45:11 AM(UTC)
Lansweeper Is there a chance to get the firewall off via Lansweeper?
by  RedWood   Go to last post Go to first unread
Last post: Yesterday at 11:23:31 PM(UTC)
Lansweeper Not working Wake on Lan
by  RedWood  
Go to last post Go to first unread
Last post: Yesterday at 11:17:33 PM(UTC)
Lansweeper Wake on Lan Issues
by  RedWood   Go to last post Go to first unread
Last post: Yesterday at 11:06:12 PM(UTC)
Lansweeper New Web Interface
by  anpatterson03  
Go to last post Go to first unread
Last post: Yesterday at 10:05:19 PM(UTC)