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: 14
0
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: 175  
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

Active Discussions

Lansweeper Creat report ALL office (ms officce,libreoffice,openoffice)
by  RC62N   Go to last post Go to first unread
Last post: 11/15/2019 9:58:33 PM(UTC)
Lansweeper PC Build Report Missing Software
by  RC62N  
Go to last post Go to first unread
Last post: 11/15/2019 8:57:21 PM(UTC)
Lansweeper How to Report on Dynamic Asset Groups
by  Rob-CD   Go to last post Go to first unread
Last post: 11/15/2019 11:49:22 AM(UTC)
Lansweeper Custom All Assets Report Help
by  Matt Fuhrman  
Go to last post Go to first unread
Last post: 11/14/2019 3:55:25 PM(UTC)
Report Center Calculate your network's computing power
by  GBInnovation   Go to last post Go to first unread
Last post: 11/13/2019 12:11:18 PM(UTC)
Lansweeper Patch Tuesday report, last 3 months
by  JacobH  
Go to last post Go to first unread
Last post: 11/12/2019 5:28:37 PM(UTC)
Lansweeper Assets Missing Quickfix Data - Last 60 Days
by  Mike6436   Go to last post Go to first unread
Last post: 11/11/2019 6:11:30 PM(UTC)
Lansweeper BlueKeep Vulnerability
by  jwood.mls  
Go to last post Go to first unread
Last post: 11/11/2019 4:58:59 PM(UTC)