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] On Behalf Of Garth Jones Sent: Wednesday, May 24, 2017 8:19 AM To: mssms@lists.myitforum.com Subject: [mssms] RE: Query assistance The query will get you stated. Select U.Full_User_Name0, Case When isnull(U.Mail0, 'n/a') = 'n/a' then 'n/a' when U.Mail0 = '' then 'n/a' else U.Mail0 End as '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