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.
