Users with direct access to servers report

[fumarola]

Hi,

I would like to create a report about all domain servers with all the user accounts (group members should be extracted) who can access the server directly.

So mainly all servers in the domain with the members of Administrator and Remote Desktop Users Group.

I have created a report based on the below SQL but i don't know how to extract the group members and add them to this report too:

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tblUsersInGroup.Username,
tblUsersInGroup.Domainname,
tblUsersInGroup.Groupname
From tblAssets
Inner Join tblUsersInGroup On tblAssets.AssetID = tblUsersInGroup.AssetID
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Where (tblUsersInGroup.Groupname = 'administrators' Or
tblUsersInGroup.Groupname = 'remote desktop users') And
tblAssetCustom.State = 1
Order By tblAssets.AssetName

There should be an option to add this info since when i click on an Asset -> Config -> User Info -> "Users in Groups" lists the members of the local computer is visible.

Any advise or guidance would be appreciated. 🙂

BR,

Roland

[AZHockeyNut]

Much closer....

1) Create a report and save it with this code

• Select Distinct G.ADGroupID,
  G.Name,
  U.Userdomain,
  U.Username,
  G.ADObjectID,
  U.ADUserID
  From tblADGroups As G
  Inner Join tblADMembership As M On M.ParentAdObjectID = G.ADObjectID Or
  M.ChildAdObjectID = G.ADObjectID
  Inner Join tblADusers As U On U.ADObjectID = M.ChildAdObjectID

It will create a SQL view with a random name, you can find the name when you run the report, it will be at the end of your URL but before the "title" part Example (http://......report.aspx?det=web50repfec49c298d2a49e3a80f08adbd7347bc&title=ADGroups%3a+Members)

Note write it down.
Create a NEW report that you want and put this code in it.

• Select A.AssetID,
  A.AssetName,
  G.Username as 'User or Group Name',
  G.Domainname as 'PC or Domain Name',
  V.Username as 'View User Name',
  (CASE s.ADObjectID WHEN (select adobjectid from tbladusers where adobjectid = s.ADObjectID ) THEN 'USER'
  WHEN (select adobjectid from tblADGroups where adobjectid = s.ADObjectID) THEN 'GROUP'
  ELSE 'LOCAL' END) AS 'Permissions From',
  G.Groupname as 'Local Group Name on Asset'
  From tblAssets A
  Inner Join tblUsersInGroup G On A.AssetID = G.AssetID
  Inner Join tblAssetCustom C On A.AssetID = C.AssetID
  left outer join tbladobjects S on (s.sAMAccountName = G.Username and s.domain = G.Domainname)
  --left outer join tbladobjects S on (s.sAMAccountName = G.Username and s.domain = G.Domainname and ['Permissions From'] <> 'LOCAL' )
  
  left join [dbo].[web50repfec49c298d2a49e3a80f08adbd7347bc] V on V.ADGroupID = s.ADObjectID
  Where (G.Groupname like '%Administrators%' Or
  G.Groupname like '%Remote Desktop Users%') And
  C.State = 1
  Order By A.AssetName

REPLACE my web50repfec49c298d2a49e3a80f08adbd7347bc with the value you got.

You can alter the column order etc to meet your needs but I THINK this is what you wanted.

Let me know, this was kinda fun to do and I needed this report anyhow.

[AZHockeyNut]

OK I have it working in SQL, sort of....Problem is I Used a SQL Cursor and if I paste the code into the Lansweeper Reporting Designer it fails with Object not set to an instance of an object. Code runs in SQL....SLOW!!!
My DBA warned me against doing this.... Said you were much better off creating the report to identify permissions as coming from User or Group permissions, then a link from the group to a report of it's members.

So, with all that being said.... HERE is what I have So FAR, if I figure the rest out of some SQL genius can clean up this mess (My DBA won't), have at it.

DECLARE @ADObjectID varchar(50), @AccountType varchar(50);
DECLARE @AssetID varchar(50), @AssetName varchar(50);
DECLARE @UserName varchar(50), @DomainName varchar(50);
DECLARE @UserName1 varchar(50), @DomainName1 varchar(50);
DECLARE @Name varchar(50), @SAMName varchar(50), @GroupName varchar(50);
declare EnumerateGroups Cursor
for


Select distinct tblAssets.AssetID,
tblAssets.AssetName,
G.Username,
G.Domainname,
G.Groupname,
s.sAMAccountName,
u.Username,
u.Userdomain,
AG.Name,

s.ADObjectID,

(CASE s.ADObjectID WHEN (select adobjectid from tbladusers where adobjectid = s.ADObjectID ) THEN 'USER' ELSE 'GROUP' END) AS AccountType

From tblAssets
Inner Join tblUsersInGroup G
On tblAssets.AssetID = G.AssetID
Inner Join tblAssetCustom
On tblAssets.AssetID = tblAssetCustom.AssetID

inner join tbladobjects S on
s.sAMAccountName = G.Username
and
s.domain = G.Domainname
left outer join tblADusers U on
U.ADObjectID = S.ADObjectID
left outer join tblADgroups AG on
AG.ADObjectID = S.ADObjectID
left outer join tblADMembership M on
AG.ADGroupID = m.ParentAdObjectID
left outer join tblADusers on
u.ADObjectID = m.ChildAdObjectID
Where (G.Groupname = 'administrators' Or
G.Groupname = 'remote desktop users') And
tblAssetCustom.State = 1
-- tblassets.assetid = 96






Order By tblAssets.AssetName ;

open EnumerateGroups;

Fetch Next from EnumerateGroups
Into @AssetID, @AssetName, @UserName, @DomainName, @GroupName,@SAMName, @UserName1, @DomainName1, @Name, @ADObjectID, @AccountType ;

While @@FETCH_STATUS = 0
Begin
if @AccountType = 'GROUP'
select @AssetID as 'Asset ID', tbladusers.Userdomain, tbladusers.Username, @AccountType as 'Permission From', tbladgroups.Name
from tblADMembership
inner join tbladusers on childadobjectid = tbladusers.adobjectid
inner join tbladGroups on parentadobjectid = tblADGroups.ADObjectID
inner join tblAssets on tblAssets.AssetID = @AssetID
where tblADMembership.ParentAdObjectID = @ADObjectID
order by tblassets.AssetID
else
select @AssetID as 'Asset ID', tbladusers.Userdomain, tbladusers.Username, @AccountType as 'Permission From'
from tblADusers

inner join tblAssets on tblAssets.AssetID = @AssetID
where tblADusers.ADObjectID = @ADObjectID
order by tblassets.AssetID

Fetch next from EnumerateGroups
Into @AssetID, @AssetName, @UserName, @DomainName, @GroupName,@SAMName, @UserName1, @DomainName1, @Name, @ADObjectID, @AccountType ;

End

Close EnumerateGroups;
DeAllocate EnumerateGroups;
Go

[AZHockeyNut]

sorry to keep adding to this, but;

something like this is what I was thinking


Select tblAssets.AssetID,
tblAssets.AssetName,
G.Username,
G.Domainname,
G.Groupname,
s.sAMAccountName,
--s.ADObjectID

(CASE s.ADObjectID WHEN (select adobjectid from tbladusers where adobjectid = s.ADObjectID ) THEN 'USER' ELSE 'GROUP' END) AS [Type]

From tblAssets
Inner Join tblUsersInGroup G
On tblAssets.AssetID = G.AssetID
Inner Join tblAssetCustom
On tblAssets.AssetID = tblAssetCustom.AssetID

inner join tbladobjects S on
s.sAMAccountName = G.Username
and
s.domain = G.Domainname

Where (G.Groupname = 'administrators' Or
G.Groupname = 'remote desktop users') And
tblAssetCustom.State = 1
Order By tblAssets.AssetName

[AZHockeyNut]

This sounds like a fun one. So, I am approaching it like this;
-Your query returns local users, local groups, domain users, and domain groups
-You want the output to be only local users and domain users (no groups)
---When a group is there, you want its members instead

If I am reading that correctly, that is good I want the same report.
Problem I see is that some users will be there multiple times thru membership in several groups. Do you want uniqueness?

I would think you could take your current report, add a column for isGroup (Boolean) T/F depending on whether it is a group or a user. then you could have a different report that gives you the members of that group? Basically you are trying to determine how someone has access and they could have it multiple ways. It's a great idea, let's see what we can do.
you may have to look at tblusersingroup and the .admingroup since there could be a local group that has admin permissions that you are missing?

somehow you have to link in tbladusers and tbladgroups, tbladobjects, and tbladmembership where you are matching the domain and samaccountname in tbladobjects then using the adobjectid in the tblgroups and then enumerating the group from there I believe.
I am going to keep hacking at it myself will post if I succeed

[fumarola]

Hi,

Sorry, if i was not so clear.
In Administrator group there can be groups also. So i would like to show the members of the groups instead listing only the AD group name.

But maybe this is not possible.

Thx,

Roland

[Esben_D]

If we understand you correctly you want a report with all the servers, their domain and all the user accounts which have the "Administrator" and "Remote Desktop Users" group.

In your SQL code you are already extracting the group members of the "Administrator" and "Remote Desktop Users" groups.
If you want to show all the group members you can delete the highlighted where clause in your SQL code.

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tblUsersInGroup.Username,
tblUsersInGroup.Domainname,
tblUsersInGroup.Groupname
From tblAssets
Inner Join tblUsersInGroup On tblAssets.AssetID = tblUsersInGroup.AssetID
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Where (tblUsersInGroup.Groupname = 'administrators' Or
tblUsersInGroup.Groupname = 'remote desktop users') And
tblAssetCustom.State = 1
Order By tblAssets.AssetName