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 Hyper-V guests dissapeared and reappeared
by  Esben.D   Go to last post Go to first unread
Last post: Yesterday at 4:30:47 PM(UTC)
Lansweeper DB cleanup script
by  William382  
Go to last post Go to first unread
Last post: Yesterday at 4:23:43 PM(UTC)
Lansweeper Installing MS KB with Deploy
by  Esben.D   Go to last post Go to first unread
Last post: Yesterday at 4:01:45 PM(UTC)
Lansweeper Ticket Info Meter incorrect
by  pfalls  
Go to last post Go to first unread
Last post: Yesterday at 3:27:44 PM(UTC)
Lansweeper Asset Checkboxes in reports
by  ufficioced   Go to last post Go to first unread
Last post: Yesterday at 1:22:17 PM(UTC)
Lansweeper Silent "Run as logged in user" option
by  CyberCitizen  
Go to last post Go to first unread
Last post: Yesterday at 3:45:25 AM(UTC)
Lansweeper New ticket creation not emailing the user
by  MVMIC IT LANSWEEPER   Go to last post Go to first unread
Last post: 11/15/2019 5:36:23 PM(UTC)