Garth thanks for taking the time to explain this to me. I guess what I really want is a way (Sql or WQL) to select a collection of computers and from there output the computer name, primary user assigned to that device and their email address. I am surprised this is not natively possible from within the console as it seems that it would save a bunch of time. I am okay with either SSMS or creating SSRS report to get this done, I just don't have the expertise to start from scratch.
From: listsad...@lists.myitforum.com [mailto:listsad...@lists.myitforum.com] On Behalf Of Garth Jones Sent: Wednesday, May 24, 2017 10:10 AM To: mssms@lists.myitforum.com Subject: [mssms] RE: Query assistance You can't take a SQL query and use it within the CM console, they are two different (SQL vs WQL) languages. What you are asking to do make a lot more sense to do in SSMS or even a SSRS reporting than in CM console. The CM console will be almost impossible to do what you want. Yes you can get the primary user (PU) or top console user (TCU) for every computer, I was just giving you a starting point. There are ton of SQL examples on the web for both TCU and PU. I always recommend that you start with the basic details you want for you SQL query then add to your query until you are happy with it. As such, What exactly is the most basic details that you want (ignore email and TCP/PU for now), that is the query you should write in SSMS. Once you have that add TCP/PU, then email. 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:37 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:image012.jpg@01D2D475.F1EF0000] 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 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
