Jeff this is very helpful and the query does create a parameter, but the collection drop down box is not showing the collection names only system names. I found the v_collection table with the collection name but how am I linking this table to the main query. Do I still need to use the v_fullcollectionmembership table?
Thanks Mike Iacaruso Enterprise Desktop Engineer Office of Technology Services Towson University 410-704-3965 [email protected]<mailto:[email protected]> From: [email protected] [mailto:[email protected]] On Behalf Of Krueger, Jeff Sent: Wednesday, February 11, 2015 4:58 PM To: [email protected] Subject: [mssms] RE: Prompt for collection in custom report He needs a join to a table that has the collection ID in it. Here is the revised query. If you paste this into report builder it should automatically pick up @CollID and create a parameter for it. SELECT v_R_System.Name0 as 'Computer Name', v_GS_OPERATING_SYSTEM.Caption0 as 'Operating System', v_R_System.Client0 as 'Client', v_GS_AntimalwareHealthStatus.AntivirusSignatureUpdateDateTime, v_GS_AntimalwareHealthStatus.AntivirusSignatureAge, v_R_System.Last_Logon_Timestamp0, v_R_System.User_Name0 as 'Last User Logon' FROM v_GS_AntimalwareHealthStatus INNER JOIN v_R_System ON v_GS_AntimalwareHealthStatus.ResourceID = v_R_System.ResourceID INNER JOIN v_GS_OPERATING_SYSTEM ON v_R_System.ResourceID = v_GS_OPERATING_SYSTEM.ResourceID Inner Join v_FullCollectionMembership on v_R_System.ResourceID = v_FullCollectionMembership.ResourceID Where v_FullCollectionMembership.CollectionID = @CollID Order By v_GS_AntimalwareHealthStatus.AntivirusSignatureUpdateDateTime From: [email protected]<mailto:[email protected]> [mailto:[email protected]] On Behalf Of John Aubrey Sent: Wednesday, February 11, 2015 4:36 PM To: [email protected]<mailto:[email protected]> Subject: [mssms] RE: Prompt for collection in custom report Add before the Order By WHERE 'your collection item' LIKE @CollectionID From: [email protected]<mailto:[email protected]> [mailto:[email protected]] On Behalf Of Iacaruso, Mike Sent: Wednesday, February 11, 2015 4:27 PM To: [email protected]<mailto:[email protected]> Subject: [mssms] Prompt for collection in custom report Can anyone out there help me with this SQL query? This query works the way I want it but in trying to create a SCCM report based on it, I am having problems trying to figure out how to have a user select a collection for the report to run off of. I found several examples out there but I can't seem to figure out how to add the collection id to this query and from what sql table to use for the collection id. Thanks SELECT v_R_System.Name0 as 'Computer Name', v_GS_OPERATING_SYSTEM.Caption0 as 'Operating System', v_R_System.Client0 as 'Client', v_GS_AntimalwareHealthStatus.AntivirusSignatureUpdateDateTime, v_GS_AntimalwareHealthStatus.AntivirusSignatureAge, v_R_System.Last_Logon_Timestamp0, v_R_System.User_Name0 as 'Last User Logon' FROM v_GS_AntimalwareHealthStatus INNER JOIN v_R_System ON v_GS_AntimalwareHealthStatus.ResourceID = v_R_System.ResourceID INNER JOIN v_GS_OPERATING_SYSTEM ON v_R_System.ResourceID = v_GS_OPERATING_SYSTEM.ResourceID Order By v_GS_AntimalwareHealthStatus.AntivirusSignatureUpdateDateTime Mike Iacaruso Enterprise Desktop Engineer Office of Technology Services Towson University 410-704-3965 [email protected]<mailto:[email protected]> ________________________________ CONFIDENTIALITY NOTICE: This email contains information from the sender that may be CONFIDENTIAL, LEGALLY PRIVILEGED, PROPRIETARY or otherwise protected from disclosure. This email is intended for use only by the person or entity to whom it is addressed. If you are not the intended recipient, any use, disclosure, copying, distribution, printing, or any action taken in reliance on the contents of this email, is strictly prohibited. If you received this email in error, please contact the sending party by reply email, delete the email from your computer system and shred any paper copies. Note to Patients: There are a number of risks you should consider before using e-mail to communicate with us. See our Privacy & Security page on www.henryford.com<http://www.henryford.com> for more detailed information as well as information concerning MyChart, our new patient portal. If you do not believe that our policy gives you the privacy and security protection you need, do not send e-mail or Internet communications to us.
