Privileged AD Users in Local Groups Report

[Mister_Nobody]

I decided to analyze AD users for administrative rights on local machines and wrote this query with 3 levels membership:

Select Top 1000000 tblassets.AssetID,
  tblassets.Domain,
  tblassets.AssetName,
  tblUsersInGroup.Groupname As local_adm_group_name,
  tblUsersInGroup.Domainname As local_membership_domain,
  tblUsersInGroup.Username As local_membership_object_name,
  tblADObjects1.domain As Down_L1_Domain,
  tblADObjects1.sAMAccountName As Down_L1_Object_name,
  tblADObjects2.domain As Down_L2_Domain,
  tblADObjects2.sAMAccountName As Down_L2_Object_name,
  tblADObjects3.domain As Down_L3_Domain,
  tblADObjects3.sAMAccountName As Down_L3_Object_name
From tblassets
  Inner Join tblUsersInGroup On tblassets.AssetID = tblUsersInGroup.AssetID And
      tblUsersInGroup.admingroup = 1 And tblUsersInGroup.Username Not In
      ('Domain Admins', 'Administrator')
  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
Order By Down_L3_Domain Desc,
  Down_L2_Domain Desc,
  Down_L1_Domain Desc,
  local_membership_domain Desc,
  tblassets.assetname Desc

 *You can  filter your standard accounts and groups by edit list

Not In ('Domain Admins', 'Administrator')

[Mister_Nobody]

Active Directory: Privileged AD users logged on servers V2

Select Top 1000000 tblassets.AssetID,
  tblassets.Domain,
  tblassets.AssetName,
  tblassets.oscode,
  tblUsersInGroup.admingroup,
  tblUsersInGroup.Groupname As local_adm_group_name,
  tblUsersInGroup.Domainname As local_membership_domain,
  tblUsersInGroup.Username As local_membership_object_name,
  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
    When tblADObjects.sAMAccountName Is Not Null Then 'domain_local_assigned'
    When tblADObjects.sAMAccountName Is Null Then 'local_user'
    Else 'Unknown'
  End object_path,
  cpli.min_logontime,
  cpli.max_logontime
From tblassets
  Inner Join tblUsersInGroup On tblassets.AssetID = tblUsersInGroup.AssetID And
      (Not tblUsersInGroup.Username In ('Domain Admins', 'Administrator'
) Or Not tblUsersInGroup.admingroup = 1)
      And (Not tblUsersInGroup.Username In ('Domain Users', 'Authenticated Users',
        'INTERACTIVE') Or Not tblUsersInGroup.Groupname In ('Users'))
  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'

[mwrobo09]

Hi Mister_Nobody,

Great report. this is close to what I am looking for, but I am wondering if you may be able to help me with something that I am looking for that may be able to use what you have here.

I am trying to build a list of PCs showing the account that is currently logged in and also if the user has admin rights to the PC and I can get that information if I reference the tblUsersInGroup table, but that will only tell me if someone is specifically added to the administrators group.  What it does not show me is if the user gets admin rights via a Active Directory group, because it may be nested in a few universal security groups.  I want to be able to pull both 

Something I was looking for was

ComputerName   userdomain    username     Admin Rights (True/False)

Thanks

[Mister_Nobody]

My reports.
Active Directory: Privileged user logged on servers:

Select Top 1000000 tblassets.AssetID,
  tblassets.AssetName,
  tblassets.IPAddress,
  tblUsersInGroup.admingroup As Local_admin_status,
  tblUsersInGroup.Groupname,
  etblCPlogoninfo.first_logontime,
  etblCPlogoninfo.last_logontime,
  tblADusers.Userdomain,
  tblADusers.Username,
  Case
    When tblADusers.Displayname Is Null Or
      tblADusers.Displayname = '' Then tblADusers.Userdomain + '\' +
      tblADusers.Username
    Else tblADusers.Displayname
  End As Displayname,
  tblADusers.OU As hyperlink_name_OU,
  'https://ls_server_fqdn/Report/report.aspx?det=web50findusersbyOU&@OU=' +
  tblADusers.OU As hyperlink_OU,
  tblADusers.IsEnabled,
  tblADusers.ExpirationDate,
  tblADusers.PasswordNeverExpires pwdNoExpire,
  tblADusers.PasswordLastSet pwdLSet,
  tblADusers.UserCannotChangePassword pwdCantChng,
  tblADusers.PasswordExpirationDate pwdExpDate
From tblassets
  Inner Join (Select i.domain,
      i.username,
      i.assetid,
      Min(i.logontime) first_logontime,
      Max(i.logontime) last_logontime
    From tblCPlogoninfo i
    Group By i.domain,
      i.username,
      i.assetid) etblCPlogoninfo On tblassets.AssetID = etblCPlogoninfo.AssetID
  Inner Join tblADusers On etblCPlogoninfo.Domain = tblADusers.Userdomain And
      etblCPlogoninfo.Username = tblADusers.Username
  Left Join tblUsersInGroup On tblassets.AssetID = tblUsersInGroup.AssetID And
      tblUsersInGroup.Username = tblADusers.Username
Where tblassets.oscode Like '%s%'
Order By tblassets.AssetName