I created an SQL report and published to the SCCM web reports portal and now I would like to create a prompt for it, but I am LOST.

After I implemented Sherry Kissinger's local group members configuration baseline audit (http://mnscug.org/blogs/sherry-kissinger/244-all-members-of-all-local-groups-configmgr-2012), I created an SQL report that retrieves members of local groups, but now I would like to be able to create a prompt that allows a user to be able to choose which OU name to search within. I have looked online and I have read some blog posts, but they all seem to go about this differently and none seem to address my exact need. 

Here is my original "Local Group Members Audit" SQL code that has no prompt:

SELECT        

	v_GS_SYSTEM.Name0 as [Computer Name], 
		v_GS_LocalGroupMembers0.Account0 as [User Name], 
		v_GS_LocalGroupMembers0.Name0 as [Group Name],
		v_GS_LocalGroupMembers0.Category0 as [Category], 
		v_GS_LocalGroupMembers0.Domain0 as [Domain], 
		v_GS_LocalGroupMembers0.Type0 as [Type]

FROM            
	
	v_GS_SYSTEM
		Inner Join v_GS_LocalGroupMembers0 on v_GS_LocalGroupMembers0.ResourceID = v_GS_SYSTEM.ResourceID
		Inner Join v_RA_System_SystemOUName on v_RA_System_SystemOUName.ResourceID = v_GS_SYSTEM.ResourceID

Order By

	[Computer Name],
	[Group Name]

How can I get an OU prompt tossed in here? 

I am assuming I would just throw in a:

Where

	v_RA_System_SystemOUName = @OUName

And then create a dataset in report builder for it, but this where I am LOST!!

Thanks everyone.

You're all AWESOME for always offering your time and expertise!!


August 19th, 2015 6:21pm

You have to create another dataset that defines the @OUName in a query so you could choose it dynamically. You can take a look at the built-in reports that have this functionality to help you out with it and these links might help you out also:

http://blogs.technet.com/b/gary_simmons_mcs/archive/2013/12/10/creating-a-custom-report-for-system-center-2012-r2-configuration-manager-part-3.aspx

https://asithadesilva.wordpress.com/2013/04/01/how-to-prompt-collection-to-a-report-in-sccm-2012/

https://technet.microsoft.com/en-us/library/cc678429.aspx?f=255&MSPPError=-2147217396


Free Windows Admin Tool Kit Click here and download it now
August 19th, 2015 11:18pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics