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.
