Thanks James, this works as expected. From: listsad...@lists.myitforum.com [mailto:listsad...@lists.myitforum.com] On Behalf Of Beardsley, James Sent: Wednesday, May 24, 2017 4:30 PM To: mssms@lists.myitforum.com Subject: RE: [mssms] RE: Query assistance
Try this. Down in the WHERE clause, you’ll see the Select Name from _RES_COLL_SMS00001, change the SMS00001 to the collection ID of the machine collection you’re trying to limit to. SELECT distinct sys.Name0, UniqueUserName, Case When isnull(USR.Mail0, 'n/a') = 'n/a' then 'n/a' when USR.Mail0 = '' then 'n/a' else USR.Mail0 End as 'Email', OS.Caption0, OS.BuildNumber0 FROM v_R_System sys INNER JOIN dbo.UserMachineRelation pc ON pc.MachineResourceID = sys.ResourceID Join v_R_User USR on SUBSTRING(pc.UniqueUserName, CHARINDEX('\', pc.UniqueUserName) + 1, LEN(pc.UniqueUserName)) = USR.User_Name0 JOIN v_GS_OPERATING_SYSTEM OS on sys.ResourceID = OS.ResourceID WHERE OS.Caption0 like 'Microsoft Windows % Enterprise' and sys.Name0 in (select Name from _RES_COLL_SMS00001) ORDER BY sys.Name0, UniqueUserName 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 12:42 PM To: mssms@lists.myitforum.com<mailto:mssms@lists.myitforum.com> Subject: RE: [mssms] RE: Query assistance 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> [mailto:listsad...@lists.myitforum.com] On Behalf Of Ed Aldrich Sent: Wednesday, May 24, 2017 12:11 PM To: mssms@lists.myitforum.com<mailto: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. ________________________________ Confidentiality Notice: This e-mail is intended only for the addressee named above. It contains information that is privileged, confidential or otherwise protected from use and disclosure. If you are not the intended recipient, you are hereby notified that any review, disclosure, copying, or dissemination of this transmission, or taking of any action in reliance on its contents, or other use is strictly prohibited. If you have received this transmission in error, please reply to the sender listed above immediately and permanently delete this message from your inbox. Thank you for your cooperation.