It appears to be the tail end of the replace function:
https://msdn.microsoft.com/en-us/library/ms186862.aspx
REPLACE ( string_expression , string_pattern , string_replacement )
Replace(
string_expression = (Select c.name + ', ' from v_collection c join
v_fullcollectionmembership fcm on fcm.collectionid=c.collectionid where
fcm.resourceid=sys1.resourceid and c.name like '%Patch%' FOR XML PATH('')
)+'..',
string_pattern = ', ..',
string_replacement = ''
) AS Collections
From: [email protected]
To: [email protected]
Subject: RE: [mssms] Reporting on collection membership
Date: Tue, 17 Mar 2015 18:42:36 +0000
That did the trick. I did have to further modify available values in the report
in SSRS to reflect the domain name scheme changes since you used a different
view
for that.
Any chance you can explain what this portion was for? I can see the reason for
everything except this:
)+'..',', ..','')
From: [email protected] [mailto:[email protected]]
On Behalf Of Sherry Kissinger
Sent: Tuesday, March 17, 2015 10:47 AM
To: [email protected]
Subject: Re: [mssms] Reporting on collection membership
oh. take out all the ' surrounding your @prompts. I added those in. for
testing, I had real values in there that made sense in my lab.
On Tuesday, March 17, 2015 12:24 PM, "Atkinson, Matt T"
<[email protected]>
wrote:
It throws an error:
Error in WHERE clause near '('.
Unable to parse query text.
I’ll try to keep poking at it and see if I can figure it out.
From:
[email protected] [mailto:[email protected]]
On Behalf Of Sherry Kissinger
Sent: Tuesday, March 17, 2015 6:20 AM
To: [email protected]
Subject: Re: [mssms] Reporting on collection membership
This should be pretty close:
select
sys1.resourceid,
sys1.Netbios_Name0 as 'Name',
sys1.Resource_Domain_OR_Workgr0 as 'Domain',
sys2.SystemRole0 as 'SystemRole',
os.Caption0 as 'Caption',
cs.lastpolicyrequest as 'Last Policy Request',
Replace((Select c.name + ', ' from v_collection c
join v_fullcollectionmembership fcm on
fcm.collectionid=c.collectionid
where fcm.resourceid=sys1.resourceid
and c.name like '%Patch%'
FOR XML PATH('') )+'..',', ..','') AS Collections
from dbo.v_r_system sys1
inner join dbo.v_gs_system sys2 on sys1.resourceid=sys2.resourceid
inner join dbo.v_GS_OPERATING_SYSTEM os on os.resourceid=sys1.resourceid
inner join dbo.v_ch_clientSummary cs on cs.resourceid=sys1.resourceid
where
sys1.Resource_Domain_OR_Workgr0 like
'@domain'
and os.caption0 like '@OperatingSystem'
and sys1.Netbios_Name0 like '@hostname'
and sys2.systemrole0 like '@Role'
order by Collections desc
On Monday, March 16, 2015 5:28 PM, "Atkinson, Matt T"
<[email protected]>
wrote:
Hi All,
Another reporting problem I’ve been banging my head against today. I’ve built a
fairly simple report to list machine names, Operating Systems, domain, and last
policy request time with
some parameters so the person running the report can filter to their liking.
Now I’m being asked to add a column displaying any patch collections the
machine is a part of. I’ve been struggling to figure this out, when I add the
collections to the query, I
don’t get the same number of results and lose about 800 servers from the
report. I need to figure out how to get the report to display a null if the
machine isn’t in a collection that is specified in the where clause. Right now
it appears they are just being
omitted. I’d also like machines with membership in multiple collections to be
listed on a single row, instead of a row for each matching collection.
Here is the current query if I run this I get approximately 4700 results, if I
remove the collection info it is ~5500 results. Gut instinct makes me think
this is a join issue, but I’ve
tried other joins as well with the same results:
SELECT dbo.v_R_System.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, dbo.v_Collection.Name AS Collection
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 INNER JOIN
dbo.v_FullCollectionMembership ON
dbo.v_R_System.ResourceID = dbo.v_FullCollectionMembership.ResourceID INNER JOIN
dbo.v_Collection ON
dbo.v_FullCollectionMembership.CollectionID = dbo.v_Collection.CollectionID
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) AND (dbo.v_Collection.Name LIKE
'%Patch%Reboot%')
Matt Atkinson
Client Systems Engineer
3601 Murray Blvd Ste. 175 Beaverton, OR 97005
W: 971-282-0342 C: 503-851-4620
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.
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.
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.
