This website uses cookies. By clicking Accept, you consent to the use of cookies. Click Here to learn more about how we use cookies.
Accept
Reject
Community FAQ
cancel
Turn on suggestions
Showing results for 
Show  only  | Search instead for 
Did you mean: 
cancel
Turn on suggestions
Showing results for 
Show  only  | Search instead for 
Did you mean: 

Unauthorized administrators with 3 levels

SweeperTest25
Engaged Sweeper II

2 weeks ago

Hi everyone, I have used " Unauthorized administrators" report but It doesnt work with levels.

I  would like to show only the unauthorized administrators, considering up to 3 levels of group nesting.
For example, I have GroupA, which is a member of Administrators, GroupB, which is a member of GroupA, and User1, who is a member of GroupB.

I know Lansweeper allows adding authorized users and groups manually, but I would prefer not to add all of them by hand.
Could you please help me with this? Thank you!

 

11 REPLIES 11
Mister_Nobody
Honored Sweeper III

Monday

Preview Version of Report:

Select Top 1000000 tblassets.AssetID,
  tblassets.Domain,
  tblADcomputers.OU,
  tblassets.AssetName,
  tblADcomputers.Location,
  tblADcomputers.Description,
  ADOCOMP.sAMAccountName As ManagerName,
  ADOCOMP.domain As ManagerDomain,
  tblUsersInGroup.Groupname + '->' + tblUsersInGroup.Domainname + '\' +
  tblUsersInGroup.Username + Case
    When tblADObjects6.sAMAccountName Is Not Null Then
      '->' + tblADObjects1.domain + '\' + tblADObjects1.sAMAccountName + '->' +
      tblADObjects2.domain + '\' + tblADObjects2.sAMAccountName + '->' +
      tblADObjects3.domain + '\' + tblADObjects3.sAMAccountName + '->' +
      tblADObjects4.domain + '\' + tblADObjects4.sAMAccountName + '->' +
      tblADObjects5.domain + '\' + tblADObjects5.sAMAccountName + '->' +
      tblADObjects6.domain + '\' + tblADObjects6.sAMAccountName
    When tblADObjects5.sAMAccountName Is Not Null Then
      '->' + tblADObjects1.domain + '\' + tblADObjects1.sAMAccountName + '->' +
      tblADObjects2.domain + '\' + tblADObjects2.sAMAccountName + '->' +
      tblADObjects3.domain + '\' + tblADObjects3.sAMAccountName + '->' +
      tblADObjects4.domain + '\' + tblADObjects4.sAMAccountName + '->' +
      tblADObjects5.domain + '\' + tblADObjects5.sAMAccountName
    When tblADObjects4.sAMAccountName Is Not Null Then
      '->' + tblADObjects1.domain + '\' + tblADObjects1.sAMAccountName + '->' +
      tblADObjects2.domain + '\' + tblADObjects2.sAMAccountName + '->' +
      tblADObjects3.domain + '\' + tblADObjects3.sAMAccountName + '->' +
      tblADObjects4.domain + '\' + tblADObjects4.sAMAccountName
    When tblADObjects3.sAMAccountName Is Not Null Then
      '->' + tblADObjects1.domain + '\' + tblADObjects1.sAMAccountName + '->' +
      tblADObjects2.domain + '\' + tblADObjects2.sAMAccountName + '->' +
      tblADObjects3.domain + '\' + tblADObjects3.sAMAccountName
    When tblADObjects2.sAMAccountName Is Not Null Then
      '->' + tblADObjects1.domain + '\' + tblADObjects1.sAMAccountName + '->' +
      tblADObjects2.domain + '\' + tblADObjects2.sAMAccountName
    When tblADObjects1.sAMAccountName Is Not Null Then
      '->' + tblADObjects1.domain + '\' + tblADObjects1.sAMAccountName
    Else ''
  End object_path,
  cpli.min_logontime,
  cpli.max_logontime
From tblassets
  Inner Join tblADcomputers On tblassets.AssetID = tblADcomputers.AssetID
  Left Join tblADObjects As ADOCOMP On ADOCOMP.ADObjectID =
      tblADcomputers.ManagerADObjectId
  Inner Join tblUsersInGroup On tblassets.AssetID = tblUsersInGroup.AssetID And
      tblUsersInGroup.admingroup = 1 
	  And 
	  	  Not Exists(Select tsysadmins.Domain,
      tsysadmins.AdminName As username From tsysadmins
    Where tblUsersInGroup.Domainname Like tsysadmins.Domain And
      tblUsersInGroup.Username Like tsysadmins.AdminName)
	  
  Left Join tblADObjects On tblUsersInGroup.Domainname = tblADObjects.domain And
      tblUsersInGroup.Username = tblADObjects.sAMAccountName
  Left Join tblADMembership On tblADObjects.ADObjectID =
      tblADMembership.ParentAdObjectID
  Left Join tblADObjects tblADObjects1 On tblADObjects1.ADObjectID =
      tblADMembership.ChildAdObjectID
  Left Join tblADMembership tblADMembership1 On tblADObjects1.ADObjectID =
      tblADMembership1.ParentAdObjectID
  Left Join tblADObjects tblADObjects2 On tblADMembership1.ChildAdObjectID =
      tblADObjects2.ADObjectID
  Left Join tblADMembership tblADMembership2 On tblADObjects2.ADObjectID =
      tblADMembership2.ParentAdObjectID
  Left Join tblADObjects tblADObjects3 On tblADMembership2.ChildAdObjectID =
      tblADObjects3.ADObjectID
  Left Join tblADMembership tblADMembership3 On tblADObjects3.ADObjectID =
      tblADMembership3.ParentAdObjectID
  Left Join tblADObjects tblADObjects4 On tblADMembership3.ChildAdObjectID =
      tblADObjects4.ADObjectID
  Left Join tblADMembership tblADMembership4 On tblADObjects4.ADObjectID =
      tblADMembership4.ParentAdObjectID
  Left Join tblADObjects tblADObjects5 On tblADMembership4.ChildAdObjectID =
      tblADObjects5.ADObjectID
  Left Join tblADMembership tblADMembership5 On tblADObjects5.ADObjectID =
      tblADMembership5.ParentAdObjectID
  Left Join tblADObjects tblADObjects6 On tblADMembership5.ChildAdObjectID =
      tblADObjects6.ADObjectID
  Left Join (Select i.assetid,
      i.domain,
      i.username,
      Min(i.logontime) min_logontime,
      Max(i.logontime) max_logontime
    From tblcplogoninfo As i
    Group By i.assetid,
      i.domain,
      i.username) cpli On tblassets.assetid = cpli.assetid And
      Coalesce(tblADObjects6.domain, tblADObjects5.domain, tblADObjects4.domain,
      tblADObjects3.domain, tblADObjects2.domain, tblADObjects1.domain,
      tblUsersInGroup.Domainname) = cpli.domain And
      Coalesce(tblADObjects6.sAMAccountName, tblADObjects5.sAMAccountName,
      tblADObjects4.sAMAccountName, tblADObjects3.sAMAccountName,
      tblADObjects2.sAMAccountName, tblADObjects1.sAMAccountName,
      tblUsersInGroup.Username) = cpli.username
Where tblassets.oscode Like '%s'

and
  Not Exists(Select tsysadmins.Domain,
      tsysadmins.AdminName As username From tsysadmins
    Where   Coalesce(tblADObjects6.domain, tblADObjects5.domain, tblADObjects4.domain,
      tblADObjects3.domain, tblADObjects2.domain, tblADObjects1.domain,
      tblUsersInGroup.Domainname) Like tsysadmins.Domain And
      Coalesce(tblADObjects6.sAMAccountName, tblADObjects5.sAMAccountName,
      tblADObjects4.sAMAccountName, tblADObjects3.sAMAccountName,
      tblADObjects2.sAMAccountName, tblADObjects1.sAMAccountName,
      tblUsersInGroup.Username) Like tsysadmins.AdminName)
0 Kudos
Mister_Nobody
Honored Sweeper III
In response to Mister_Nobody

Monday

Add code for filtering any built-in administators:

Select Top 1000000 tblassets.AssetID,
  tblassets.Domain,
  tblADcomputers.OU,
  tblassets.AssetName,
  tblADcomputers.Location,
  tblADcomputers.Description,
  ADOCOMP.sAMAccountName As ManagerName,
  ADOCOMP.domain As ManagerDomain,
  tblUsersInGroup.Groupname + '->' + tblUsersInGroup.Domainname + '\' +
  tblUsersInGroup.Username + Case
    When tblADObjects6.sAMAccountName Is Not Null Then
      '->' + tblADObjects1.domain + '\' + tblADObjects1.sAMAccountName + '->' +
      tblADObjects2.domain + '\' + tblADObjects2.sAMAccountName + '->' +
      tblADObjects3.domain + '\' + tblADObjects3.sAMAccountName + '->' +
      tblADObjects4.domain + '\' + tblADObjects4.sAMAccountName + '->' +
      tblADObjects5.domain + '\' + tblADObjects5.sAMAccountName + '->' +
      tblADObjects6.domain + '\' + tblADObjects6.sAMAccountName
    When tblADObjects5.sAMAccountName Is Not Null Then
      '->' + tblADObjects1.domain + '\' + tblADObjects1.sAMAccountName + '->' +
      tblADObjects2.domain + '\' + tblADObjects2.sAMAccountName + '->' +
      tblADObjects3.domain + '\' + tblADObjects3.sAMAccountName + '->' +
      tblADObjects4.domain + '\' + tblADObjects4.sAMAccountName + '->' +
      tblADObjects5.domain + '\' + tblADObjects5.sAMAccountName
    When tblADObjects4.sAMAccountName Is Not Null Then
      '->' + tblADObjects1.domain + '\' + tblADObjects1.sAMAccountName + '->' +
      tblADObjects2.domain + '\' + tblADObjects2.sAMAccountName + '->' +
      tblADObjects3.domain + '\' + tblADObjects3.sAMAccountName + '->' +
      tblADObjects4.domain + '\' + tblADObjects4.sAMAccountName
    When tblADObjects3.sAMAccountName Is Not Null Then
      '->' + tblADObjects1.domain + '\' + tblADObjects1.sAMAccountName + '->' +
      tblADObjects2.domain + '\' + tblADObjects2.sAMAccountName + '->' +
      tblADObjects3.domain + '\' + tblADObjects3.sAMAccountName
    When tblADObjects2.sAMAccountName Is Not Null Then
      '->' + tblADObjects1.domain + '\' + tblADObjects1.sAMAccountName + '->' +
      tblADObjects2.domain + '\' + tblADObjects2.sAMAccountName
    When tblADObjects1.sAMAccountName Is Not Null Then
      '->' + tblADObjects1.domain + '\' + tblADObjects1.sAMAccountName
    Else ''
  End object_path
  ,
  cpli.min_logontime,
  cpli.max_logontime
From tblassets
  Inner Join tblADcomputers On tblassets.AssetID = tblADcomputers.AssetID
  Left Join tblADObjects As ADOCOMP On ADOCOMP.ADObjectID =
      tblADcomputers.ManagerADObjectId
  Inner Join tblUsersInGroup On tblassets.AssetID = tblUsersInGroup.AssetID And
      tblUsersInGroup.admingroup = 1 And 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)
  Left Join tblADObjects On tblUsersInGroup.Domainname = tblADObjects.domain And
      tblUsersInGroup.Username = tblADObjects.sAMAccountName
  Left Join tblADMembership On tblADObjects.ADObjectID =
      tblADMembership.ParentAdObjectID
  Left Join tblADObjects tblADObjects1 On tblADObjects1.ADObjectID =
      tblADMembership.ChildAdObjectID
  Left Join tblADMembership tblADMembership1 On tblADObjects1.ADObjectID =
      tblADMembership1.ParentAdObjectID
  Left Join tblADObjects tblADObjects2 On tblADMembership1.ChildAdObjectID =
      tblADObjects2.ADObjectID
  Left Join tblADMembership tblADMembership2 On tblADObjects2.ADObjectID =
      tblADMembership2.ParentAdObjectID
  Left Join tblADObjects tblADObjects3 On tblADMembership2.ChildAdObjectID =
      tblADObjects3.ADObjectID
  Left Join tblADMembership tblADMembership3 On tblADObjects3.ADObjectID =
      tblADMembership3.ParentAdObjectID
  Left Join tblADObjects tblADObjects4 On tblADMembership3.ChildAdObjectID =
      tblADObjects4.ADObjectID
  Left Join tblADMembership tblADMembership4 On tblADObjects4.ADObjectID =
      tblADMembership4.ParentAdObjectID
  Left Join tblADObjects tblADObjects5 On tblADMembership4.ChildAdObjectID =
      tblADObjects5.ADObjectID
  Left Join tblADMembership tblADMembership5 On tblADObjects5.ADObjectID =
      tblADMembership5.ParentAdObjectID
  Left Join tblADObjects tblADObjects6 On tblADMembership5.ChildAdObjectID =
      tblADObjects6.ADObjectID
  Left Join (Select i.assetid,
      i.domain,
      i.username,
      Min(i.logontime) min_logontime,
      Max(i.logontime) max_logontime
    From tblcplogoninfo As i
    Group By i.assetid,
      i.domain,
      i.username) cpli On tblassets.assetid = cpli.assetid And
      Coalesce(tblADObjects6.domain, tblADObjects5.domain, tblADObjects4.domain,
      tblADObjects3.domain, tblADObjects2.domain, tblADObjects1.domain,
      tblUsersInGroup.Domainname) = cpli.domain And
      Coalesce(tblADObjects6.sAMAccountName, tblADObjects5.sAMAccountName,
      tblADObjects4.sAMAccountName, tblADObjects3.sAMAccountName,
      tblADObjects2.sAMAccountName, tblADObjects1.sAMAccountName,
      tblUsersInGroup.Username) = cpli.username
Where
-- filter for servers
tblassets.oscode Like '%s' And
--
Not Exists(Select tsysadmins.Domain,
      tsysadmins.AdminName As username From tsysadmins
    Where Coalesce(tblADObjects6.domain, tblADObjects5.domain,
      tblADObjects4.domain, tblADObjects3.domain, tblADObjects2.domain,
      tblADObjects1.domain, tblUsersInGroup.Domainname) Like tsysadmins.Domain
      And Coalesce(tblADObjects6.sAMAccountName, tblADObjects5.sAMAccountName,
      tblADObjects4.sAMAccountName, tblADObjects3.sAMAccountName,
      tblADObjects2.sAMAccountName, tblADObjects1.sAMAccountName,
      tblUsersInGroup.Username) Like tsysadmins.AdminName)
RolandB
Engaged Sweeper III

2 weeks ago

I have been wishing for that for a long time.
The wish is... in the background, the nested groups would have to be resolved and then displayed

SweeperTest25
Engaged Sweeper II
In response to RolandB

a week ago

Hello RolandB, I hope you're having a great day.


This report would be extremely useful for all companies, especially for audit and cybersecurity purposes.
I don’t see the point of allowing 'Authorized Administrators' in large organizations where there are so many groups and users. It’s insane to expect all of them to be added manually.

RolandB
Engaged Sweeper III
In response to SweeperTest25

a week ago

Hi SweeperTest25

you don´t understand me ... I see this also .. I need this also for more security in big company and I self have this implemented without Lansweeper.  I think also is it implemented in Lansweeper it´s a very great feature in Lansweeper and help us.

0 Kudos
SweeperTest25
Engaged Sweeper II
In response to Mister_Nobody

a week ago

Hello Mister_Nobody,
I’ve seen those reports, but I’m not sure how to combine them with the 'Unauthorized Administrators' report to achieve my goal. This would be very useful for all organizations.

 

 

0 Kudos
Mister_Nobody
Honored Sweeper III
In response to SweeperTest25

a week ago

I think there is no problem to combine reports. 

0 Kudos
FrankSc
Lansweeper Tech Support
Lansweeper Tech Support

2 weeks ago

Dear, 

At the moment, there is no hierarchy feature available for this. 

This could be a nice improvement, though. Do you mind contacting our Support team so they can submit a feature request for this?

0 Kudos

Reports & Analytics

Ask about reports you're interested in and share reports you've created. Subscribe to receive daily updates of reports shared in the Community.

New to Lansweeper?

Try Lansweeper For Free

Experience Lansweeper with your own data.
Sign up now for a 14-day free trial.

Try Now
Top