Report that shows what Managed Server List is assigned to clients

Products

• Endpoint Protection

Issue/Introduction

A report that shows what Managed Server List clients are assigned is not available in the SEPM console. Is it possible to review this data?

Environment

SEPM 14.3.x

Resolution

While no report is available in the console, the following query can be run to create a list of computers and what Managed Server List is assigned to them. Please note the following when using this query:

• This query is provided as-is, and does not provide any warrantees or guarantees. Use at your own risk.
• If you have more than one Domain, the domain ID needs to be added to the query at this line:

  --and bm.ID != 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx'

  Change the number between the ' ' to the domain ID to query. This query will need to be run for each domain in the SEPM. Also remove the -- to add the line to the query, such as:

  and bm.ID != 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx'

• The Default Managed Server List that is created out of box will always be labeled "Default Managed Server List" even if it has been renamed.
• This query will need to be run using Microsoft's SQL Server Management Studio.

declare @t as table (id int identity(1,1), ParentID varchar(40), GroupID varchar(40), GroupName varchar(250), SemAgentPolicyId varchar(40), subgroupxml xml)
DECLARE @x XML  
SET @x= ( select cast(cast(bm.content as varbinary(max)) as xml)
from BASIC_METADATA bm
where bm.TYPE = 'SemClientGroupTree'
--and bm.ID != 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx'
)

insert into @t
SELECT '00000000000000000000000000000000', T.c.value('(@Id)[1]', 'varchar(40)') AS ID,
T.c.value('(@Name)[1]', 'varchar(40)') AS Name,
T.c.value('(ObjReference/@TargetId)[1]', 'varchar(40)') AS SemAgentPolicyId,
T.c.query('./SemClientGroup') AS subgroupxml
FROM   @x.nodes('/SemClientGroupTree/SemClientGroup') T(c)  




declare @i as int = 1

while (select top 1 id from @t where len(cast (subgroupxml as nvarchar(max))) > 1 and GroupID not in (select distinct ParentID from @t) order by id desc) is not null
begin
declare @id as int = (select top 1 id from @t where len(cast (subgroupxml as nvarchar(max))) > 1 and GroupID not in (select distinct ParentID from @t) order by id desc)
set @x = (select subgroupxml from @t where id = @id)

insert into @t
SELECT (select GroupId from @t where id = @id), T.c.value('(@Id)[1]', 'varchar(40)') AS ID,
(select GroupName from @t where id = @id) + '\' + T.c.value('(@Name)[1]', 'varchar(40)') AS Name,
T.c.value('(ObjReference/@TargetId)[1]', 'varchar(40)') AS SemAgentPolicyId,
T.c.query('./SemClientGroup') AS subgroupxml
FROM   @x.nodes('/SemClientGroup') T(c)  
set @i = @i+1
end

select
c.COMPUTER_NAME,
cast (a.MAJOR_VERSION as nvarchar(10)) + '.' + cast (a.MINOR_VERSION as nvarchar (10)) As 'Version',
t.GroupName,
isnull (bm.NAME, 'Default Management Server List') as 'Management Server List'
from SEM_CLIENT c
join SEM_AGENT a on a.COMPUTER_ID = c.COMPUTER_ID
join
(
select t.GroupName, GroupID, MSL
from
(
select bm.ID as SemGroupPolicyID, bm.NAME,
cast(cast(bm.content as varbinary(max)) as xml).value('(/SemGroupPolicy/ObjReference[@Name="SemAgentPolicyLink"]/@TargetId)[1]', 'nvarchar(36)') 'SemAgentPolicyID'
from BASIC_METADATA bm
where bm.TYPE = 'SemGroupPolicy'
) SemGroupPolicy
join
(
SELECT SemAgentPolicy.SemAgentPolicyID, SemAgentPolicy.MSL
FROM
(
select bm.ID as SemAgentPolicyID, cast(cast(bm.content as varbinary(max)) as xml) SemAgentPolicyXML
,cast(cast(bm.content as varbinary(max)) as xml).value('(/SemAgentPolicy/Communication/ObjReference[@Name="ServerListLink"]/@TargetId)[1]', 'nvarchar(50)') as MSL
from BASIC_METADATA bm
where bm.TYPE = 'SemAgentPolicy'
) SemAgentPolicy
) SemAgentPolicy on SemGroupPolicy.SemAgentPolicyID = SemAgentPolicy.SemAgentPolicyID
join @t t on t.SemAgentPolicyId = SemGroupPolicy.SemGroupPolicyID
) t on t.GroupID = c.GROUP_ID
left join BASIC_METADATA bm on bm.ID = t.MSL