Thank you Garth, sorry I could not make it to Ontario. :) Won't be at IT/DEV either but I did find out they are sending us to Ignite...presenting there? :)
From: [email protected] [mailto:[email protected]] On Behalf Of Garth Jones Sent: Thursday, May 25, 2017 8:32 AM To: [email protected] Subject: [mssms] RE: Query assistance Here is the query that get you the details you are looking for. SELECT distinct RV.Netbios_Name0 as 'Computer', SCUM.TopConsoleUser0 as 'UserId', USR.Full_User_Name0 as 'User Name', Case When isnull(USR.Mail0, 'n/a') = 'n/a' then 'n/a' when USR.Mail0 = '' then 'n/a' else USR.Mail0 End as 'Email' FROM dbo.v_R_System_Valid RV join dbo.v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP SCUM on RV.ResourceID = SCUM.ResourceID join dbo.v_R_User USR on SCUM.TopConsoleUser0 = USR.Unique_User_Name0 Order by RV.Netbios_Name0, SCUM.TopConsoleUser0 Garth Jones Chief Architect www.Enhansoft.com<http://www.enhansoft.com/> [Description: Description: cid:[email protected]]<http://www.enhansoft.com/> Enhancing Your Business [Description: Description: Description: Description: Description: Description: Description: cid:[email protected]]<http://www.enhansoft.com/blog>[Description: Description: Description: Description: Description: Description: Description: cid:[email protected]]<https://twitter.com/enhansoft>[Description: Description: Description: Description: Description: Description: Description: cid:[email protected]]<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:[email protected]]<http://myitforum.com/myitforumwp/community/groups/enhansoft/> Subscribe to Enhansoft's Newsletter!<http://www.enhansoft.com/register> From: [email protected]<mailto:[email protected]> [mailto:[email protected]] On Behalf Of Garth Jones Sent: Wednesday, May 24, 2017 3:51 PM To: [email protected]<mailto:[email protected]> Subject: [mssms] RE: Query assistance This sender failed our fraud detection checks and may not be who they appear to be. Learn about spoofing<http://aka.ms/LearnAboutSpoofing> Feedback<http://aka.ms/SafetyTipsFeedback> If you are in Ottawa, Ontario, I'm presenting on this topic starting at 6pm (aka 130 minutes from now) at the Microsoft office. :) Otherwise, I will try to write this up tomorrow when I get back into the office tomorrow. <shameless self-promotion > Or/and you can see me at IT/Dev Connections in October, I'm doing two session there on ConfigMgr, SQL and Reporting. http://devconnections.com/dc17/Public/SpeakerDetails.aspx?FromPage=Speakers.aspx&ContactID=1136210 If you come to IT/Dev, let me know and I will get you a signed napkin from the famous but elusive Rod Trent! </shameless self-promotion on> Garth Jones Chief Architect www.Enhansoft.com<http://www.enhansoft.com/> [Description: Description: cid:[email protected]]<http://www.enhansoft.com/> Enhancing Your Business [Description: Description: Description: Description: Description: Description: Description: cid:[email protected]]<http://www.enhansoft.com/blog>[Description: Description: Description: Description: Description: Description: Description: cid:[email protected]]<https://twitter.com/enhansoft>[Description: Description: Description: Description: Description: Description: Description: cid:[email protected]]<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:[email protected]]<http://myitforum.com/myitforumwp/community/groups/enhansoft/> Subscribe to Enhansoft's Newsletter!<http://www.enhansoft.com/register> From: [email protected]<mailto:[email protected]> [mailto:[email protected]] On Behalf Of Enley, Carl Sent: Wednesday, May 24, 2017 3:31 PM To: [email protected]<mailto:[email protected]> Subject: [mssms] RE: Query assistance 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: [email protected]<mailto:[email protected]> [mailto:[email protected]] On Behalf Of Garth Jones Sent: Wednesday, May 24, 2017 10:10 AM To: [email protected]<mailto:[email protected]> 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:[email protected]]<http://www.enhansoft.com/> Enhancing Your Business [Description: Description: Description: Description: Description: Description: Description: cid:[email protected]]<http://www.enhansoft.com/blog>[Description: Description: Description: Description: Description: Description: Description: cid:[email protected]]<https://twitter.com/enhansoft>[Description: Description: Description: Description: Description: Description: Description: cid:[email protected]]<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:[email protected]]<http://myitforum.com/myitforumwp/community/groups/enhansoft/> Subscribe to Enhansoft's Newsletter!<http://www.enhansoft.com/register> From: [email protected]<mailto:[email protected]> [mailto:[email protected]] On Behalf Of Enley, Carl Sent: Wednesday, May 24, 2017 8:37 AM To: [email protected]<mailto:[email protected]> 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:[email protected]] From: [email protected]<mailto:[email protected]> [mailto:[email protected]] On Behalf Of Garth Jones Sent: Wednesday, May 24, 2017 8:19 AM To: [email protected]<mailto:[email protected]> 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:[email protected]]<http://www.enhansoft.com/> Enhancing Your Business [Description: Description: Description: Description: Description: Description: Description: cid:[email protected]]<http://www.enhansoft.com/blog>[Description: Description: Description: Description: Description: Description: Description: cid:[email protected]]<https://twitter.com/enhansoft>[Description: Description: Description: Description: Description: Description: Description: cid:[email protected]]<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:[email protected]]<http://myitforum.com/myitforumwp/community/groups/enhansoft/> Subscribe to Enhansoft's Newsletter!<http://www.enhansoft.com/register> From: [email protected]<mailto:[email protected]> [mailto:[email protected]] On Behalf Of Enley, Carl Sent: Wednesday, May 24, 2017 8:00 AM To: [email protected]<mailto:[email protected]> 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
