Custom Report - Admin Users & Groups by Server
I found a report from last year here in the forums and modified slightly. I want to show both users & groups within the administrators group. Right now this report only shows users, but not the sub-groups. How do I add those as we manage many of our servers by ad domain groups. This would help with security and identify if any servers are misconfigured for admin access.
select resource.name as computer_name,domain_netbios_name domain_name,invgroups.name as group_name,invsystemusers.caption as user_name,
invsystemusers.full_name as users_real_name,BRANCH_OFFICE_NAME as remote_office,software_name as operating_system
From invcomputer
inner join resource on resource.resource_id=invcomputer.computer_id
inner join invcomponent on invcomponent.computer_id=resource.resource_id
inner join invgroups on invgroups.component_id=invcomponent.component_id
inner join invgroupmembers on invgroupmembers.group_component_Id=invgroups.component_id
inner join invsystemusers on invsystemusers.component_id=invgroupmembers.user_component_Id
inner join branchmemberresourcerel on branchmemberresourcerel.resource_id=resource.resource_id
inner join branchofficedetails on branchofficedetails.branch_office_id=branchmemberresourcerel.branch_office_id
inner join invcomputerosrel on invcomputerosrel.computer_id=resource.resource_id
inner join invsw on invsw.softwarE_id=invcomputerosrel.software_id
where invgroups.name like '%admin%' and SOFTWARE_NAME like '%server%'
order by computer_name, user_name
select resource.name as computer_name,domain_netbios_name domain_name,invgroups.name as group_name,invsystemusers.caption as user_name,
invsystemusers.full_name as users_real_name,BRANCH_OFFICE_NAME as remote_office,software_name as operating_system
From invcomputer
inner join resource on resource.resource_id=invcomputer.computer_id
inner join invcomponent on invcomponent.computer_id=resource.resource_id
inner join invgroups on invgroups.component_id=invcomponent.component_id
inner join invgroupmembers on invgroupmembers.group_component_Id=invgroups.component_id
inner join invsystemusers on invsystemusers.component_id=invgroupmembers.user_component_Id
inner join branchmemberresourcerel on branchmemberresourcerel.resource_id=resource.resource_id
inner join branchofficedetails on branchofficedetails.branch_office_id=branchmemberresourcerel.branch_office_id
inner join invcomputerosrel on invcomputerosrel.computer_id=resource.resource_id
inner join invsw on invsw.softwarE_id=invcomputerosrel.software_id
where invgroups.name like '%admin%' and SOFTWARE_NAME like '%server%'
order by computer_name, user_name