Okay thanks for the replies. Let me re-phrase my question… Can anyone assist me with writing a Query from within the Config Mgr console that would be able to look at a collection of *machines* and from that output the computer name, primary user and mail0 attribute? I can use the built in prompt for collection from within the query window.
Thanks From: listsad...@lists.myitforum.com [mailto:listsad...@lists.myitforum.com] On Behalf Of Ed Aldrich Sent: Wednesday, May 24, 2017 12:11 PM To: mssms@lists.myitforum.com Subject: RE: [mssms] RE: Query assistance …which means one cannot simply copy a SQL query and paste into collection query properties as that is expecting a WQL query syntax [Ed] Mobile: (401) 924-2293 [Description: Description: cid:image011.png@01CAD56A.EFDE3F90] Ent Cli Mgmt (2003-2016) From: listsad...@lists.myitforum.com<mailto:listsad...@lists.myitforum.com> [mailto:listsad...@lists.myitforum.com] On Behalf Of Erik Wold Sent: Wednesday, May 24, 2017 11:29 AM To: mssms@lists.myitforum.com<mailto:mssms@lists.myitforum.com>; mssms@lists.myitforum.com<mailto:mssms@lists.myitforum.com> Subject: Re: [mssms] RE: Query assistance Also configmgr uses wql, slightly dfifferent than sql. Mvh Erik _____________________________ From: Collin Murphy <collinrmur...@hotmail.com<mailto:collinrmur...@hotmail.com>> Sent: onsdag, mai 24, 2017 4:49 pm Subject: [mssms] RE: Query assistance To: <mssms@lists.myitforum.com<mailto:mssms@lists.myitforum.com>> If you copied and pasted sometimes the apostrophe get messed up. Try deleting and adding them manually. I apologize if you have tried that before. Sent from Mail<https://go.microsoft.com/fwlink/?LinkId=550986> for Windows 10 From: Enley, Carl<mailto:cen...@arifleet.com> Sent: Wednesday, May 24, 2017 8:43 AM To: mssms@lists.myitforum.com<mailto:mssms@lists.myitforum.com> Subject: [mssms] RE: Query assistance Thanks, excuse my ignorance but if I run the query directly in SQL manager I get accurate results. If I try to create a new query from the config mgr console I get a syntax is not correct error, am I missing something? I will be the first to admit that reporting and SQL are not my strong points. Also is it possible to list the users primary device in the results output? [cid:image002.png@01D2D468.E5A24530] From: listsad...@lists.myitforum.com<mailto:listsad...@lists.myitforum.com> [mailto:listsad...@lists.myitforum.com]On Behalf Of Garth Jones Sent: Wednesday, May 24, 2017 8:19 AM To: mssms@lists.myitforum.com<mailto:mssms@lists.myitforum.com> Subject: [mssms] RE: Query assistance The query will get you stated. Select U.Full_User_Name0, Case Whenisnull(U.Mail0,'n/a')='n/a'then'n/a' when U.Mail0=''then'n/a' else U.Mail0 Endas'Email' from dbo.v_R_User U Take this query and build upon it, if you get stuck post what you have here with a short description of the problem you are having. Garth Jones Chief Architect www.Enhansoft.com<http://www.enhansoft.com/> [Description: Description: cid:image001.jpg@01CC63FB.974F4EC0]<http://www.enhansoft.com/> Enhancing Your Business [Description: Description: Description: Description: Description: Description: Description: cid:image004.png@01CD5E19.F3C3A110]<http://www.enhansoft.com/blog>[Description: Description: Description: Description: Description: Description: Description: cid:image005.png@01CD5E19.F3C3A110]<https://twitter.com/enhansoft>[Description: Description: Description: Description: Description: Description: Description: cid:image006.jpg@01CD5E19.F3C3A110]<http://www.facebook.com/EnhansoftInc>[Description: Description: Description: Description: Description: Description: Description: Description: Description: Description: Description: Description: Description: Description: Description: Enhansoft's YouTube Page]<http://www.youtube.com/user/Enhansoft/videos>[Description: Description: Description: Description: Description: Description: Description: cid:image007.png@01CD5E19.F3C3A110]<http://myitforum.com/myitforumwp/community/groups/enhansoft/> Subscribe to Enhansoft’s Newsletter!<http://www.enhansoft.com/register> From: listsad...@lists.myitforum.com<mailto:listsad...@lists.myitforum.com> [mailto:listsad...@lists.myitforum.com]On Behalf Of Enley, Carl Sent: Wednesday, May 24, 2017 8:00 AM To: mssms@lists.myitforum.com<mailto:mssms@lists.myitforum.com> Subject: [mssms] Query assistance I need help writing a query that will allow me to pull the primary user and the mail0 attribute for each machine in a collection. I need to communicate with a group of users about an anticipated software update and it would be great if I could add all of the machines to a collection run a query against it to pull the email address for the primary device user. I could then just export the results and copy the email address into Outlook for the communication. I do have the email address being discovered and UDA turned on and working, CM 1702 if it matters. Thanks ________________________________ Legal Notice: This email is intended only for the person(s) to whom it is addressed. If you are not an intended recipient and have received this message in error, please notify the sender immediately by replying to this email or calling +44(0) 2083269015 (UK) or +1 866 592 4214 (USA). This email and any attachments may be privileged and/or confidential. The unauthorized use, disclosure, copying or printing of any information it contains is strictly prohibited. The opinions expressed in this email are those of the author and do not necessarily represent the views of 1E Ltd. Nothing in this email will operate to bind 1E to any order or other contract.