Matt, I think you are under the mistaken impression that I know what I'm doing. :)
I stumble through; and only know that trick above because I remembered "hey, John Nelson did this with IP addresses... let me search for that..." which led me to http://www.myitforum.com/forums/IP-Address-Query-m197459.aspx and then I 100% stole that syntax. What it actually *does* I couldn't say... but I see Michael found the 'why' of why that's there. We all learn from each other; that's what Myitforum is all about, imo. On Tuesday, March 17, 2015 2:59 PM, Michael Kimbley II <[email protected]> wrote: 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 mailto:'@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.
