I've built a custom report to display some server information, this gets
consumed by another group and they run additional scripts against the data.
I've been asked to add a column with the collections that the server is a
member of. Is it possible to present that information in a comma separated list
within a single column? I've seen this article on TechNet:
http://blogs.technet.com/b/configurationmgr/archive/2009/08/24/how-to-find-the-collection-membership-information-of-a-specific-client-machine.aspx,
but if I use that I get a row for each collection, and I don't think that is a
very good way to present the data.
This is what my query for the report looks like as of now, without adding in
the collection data:
SELECT dbo.v_R_System.ResourceID AS ResourceID, dbo.v_GS_SYSTEM.Name0 as
Name, dbo.v_GS_SYSTEM.Domain0 AS Domain, dbo.v_GS_SYSTEM.SystemRole0 As
SystemRole,
dbo.v_GS_OPERATING_SYSTEM.Caption0 AS OperatingSystem,
dbo.v_CH_ClientSummary.LastPolicyRequest AS LastPolicyRequest
FROM dbo.v_R_System INNER JOIN
dbo.v_GS_SYSTEM ON dbo.v_R_System.ResourceID =
dbo.v_GS_SYSTEM.ResourceID INNER JOIN
dbo.v_GS_OPERATING_SYSTEM ON dbo.v_R_System.ResourceID
= dbo.v_GS_OPERATING_SYSTEM.ResourceID INNER JOIN
dbo.v_CH_ClientSummary ON dbo.v_R_System.ResourceID =
dbo.v_CH_ClientSummary.ResourceID
where dbo.v_GS_SYSTEM.Domain0 LIKE @domain AND
dbo.v_GS_OPERATING_SYSTEM.Caption0 LIKE @OperatingSystem
AND dbo.v_GS_SYSTEM.Name0 LIKE @hostname AND dbo.v_GS_SYSTEM.SystemRole0 LIKE
@Role
Second,
Matt Atkinson
Client Systems Engineer
________________________________
This message is intended for the sole use of the addressee, and may contain
information that is privileged, confidential and exempt from disclosure under
applicable law. If you are not the addressee you are hereby notified that you
may not use, copy, disclose, or distribute to anyone the message or any
information contained in the message. If you have received this message in
error, please immediately advise the sender by reply email and delete this
message.
