show the users not authorized with local admin privileges for workstations only

[tsgoncalves]

Hello All,

I've a demand that I need to have a report showing the local admin accounts with the fields below:

- Computer name, Serial number, Workstations type(not server), local group name domain, local group name name and Users in Groups.

The aim is to show the users not authorized with local admin privileges for workstations only.

I can find this information on Lansweeper device page > Config > User info > Users in group

Thank you very much for your support.

Att.,

TG

[tsgoncalves]

Hello! It is very close to what I was looking for. Now I can see all groups and the users listed inside it. The idea is to see users with local admin power only, I don't know if it is possible. What would be the parameter to change?

Thank you very much for your time supporting me.

Att.,

TG

[Cognitnaut]

TG wrote:
Hello! It is very close to what I was looking for. Now I can see all groups and the users listed inside it. The idea is to see users with local admin power only, I don't know if it is possible. What would be the parameter to change?

Thank you very much for your time supporting me.

Att.,

TG

If you would like a column that shows admin yes/no, then the addition is "admingroup". Updated code:

Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName As [Computer Name],
  tblAssetCustom.Serialnumber As [Serial Number],
  tblUsersInGroup.Username,
  tblUsersInGroup.Domainname,
  tblUsersInGroup.Groupname,
  tblUsersInGroup.Admingroup
From tblAssets
  Inner Join tblUsersInGroup On tblAssets.AssetID = tblUsersInGroup.AssetID
  Inner Join lansweeperdb.dbo.tblAssetCustom On tblAssets.AssetID =
      tblAssetCustom.AssetID
  Inner Join lansweeperdb.dbo.tblComputersystem On tblAssets.AssetID =
      tblComputersystem.AssetID
Where Case
    When tblComputersystem.Domainrole > 1 Then 'Server'
    Else 'Workstation'
  End Like 'workstation'
Order By [Computer Name]

[moakyz03]

TG wrote:
Hello! It is very close to what I was looking for. Now I can see all groups and the users listed inside it. The idea is to see users with local admin power only, I don't know if it is possible. What would be the parameter to change?

Thank you very much for your time supporting me.

Att.,

TG

Hi, you can try this and see if it does what you need:

Select Distinct Top 1000000 tblAssets.AssetName,
  tblUsersInGroup.AssetID,
  tblAssets.Domain,
  tblAssetCustom.Serialnumber,
  Case
    When (TsysChassisTypes.ChassisName = 'Desktop' Or
      TsysChassisTypes.ChassisName = 'Mini Tower') Then 'Desktop'
    When (TsysChassisTypes.ChassisName = 'Notebook' Or
      TsysChassisTypes.ChassisName = 'Laptop' Or
      TsysChassisTypes.ChassisName = 'Portable') Then 'Laptop'
    When (TsysChassisTypes.ChassisName = 'Rack Mount' Or
      TsysChassisTypes.ChassisName = 'Other') Then 'Server'
    Else 'Other'
  End As [Asset Type],
  tblUsersInGroup.Domainname,
  tblUsersInGroup.Username,
  tblUsersInGroup.Groupname,
  tsysOS.OSname,
  tblUsersInGroup.Lastchanged,
  tsysOS.Image As icon,
  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 tblSystemEnclosure On tblAssets.AssetID = tblSystemEnclosure.AssetID
  Left Join TsysChassisTypes On tblSystemEnclosure.ChassisTypes =
      TsysChassisTypes.Chassistype
  Left Join tblComputersystem On tblComputersystem.AssetID = tblAssets.AssetID
Where tblUsersInGroup.Groupname = 'administrators' And
  tblComputersystem.Domainrole <= 1
Order By tblAssets.Domain,
  tblAssets.AssetName,
  tblUsersInGroup.Username

[Cognitnaut]

Give the following a try and see if it suits you:

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName As [Computer Name],
tblAssetCustom.Serialnumber As [Serial Number],
tblUsersInGroup.Username,
tblUsersInGroup.Domainname,
tblUsersInGroup.Groupname
From tblAssets
Inner Join tblUsersInGroup On tblAssets.AssetID = tblUsersInGroup.AssetID
Inner Join lansweeperdb.dbo.tblAssetCustom On tblAssets.AssetID =
tblAssetCustom.AssetID
Inner Join lansweeperdb.dbo.tblComputersystem On tblAssets.AssetID =
tblComputersystem.AssetID
Where Case
When tblComputersystem.Domainrole > 1 Then 'Server'
Else 'Workstation'
End Like 'workstation'
Order By [Computer Name]