RE: [mssms] RE: Query assistance

[Enley, Carl]

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_SMS1, change the SMS1 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_SMS1)
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]&

[mssms] RE: Query assistance

[Enley, Carl]

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: listsad...@lists.myitforum.com [mailto:listsad...@lists.myitforum.com] On 
Behalf Of Garth Jones
Sent: Thursday, May 25, 2017 8:32 AM
To: mssms@lists.myitforum.com
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: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 Garth Jones
Sent: Wednesday, May 24, 2017 3:51 PM
To: mssms@lists.myitforum.com<mailto:mssms@lists.myitforum.com>
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.

 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=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!




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 3:31 PM
To: mssms@lists.myitforum.com<mailto:mssms@lists.myitforum.com>
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 c

RE: [mssms] RE: Query assistance

[Beardsley, James]

Try this. Down in the WHERE clause, you’ll see the Select Name from 
_RES_COLL_SMS1, change the SMS1 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_SMS1)
ORDER BY
   sys.Name0, UniqueUserName




From: 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
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

[mssms] RE: Query assistance

[Ed Aldrich]

Be sure to post the link to the video!


[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] On 
Behalf Of Garth Jones
Sent: Wednesday, May 24, 2017 3:51 PM
To: mssms@lists.myitforum.com
Subject: [mssms] RE: Query assistance

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.

 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=1136210<http://devconnections.com/dc17/Public/SpeakerDetails.aspx?FromPage=Speakers.aspx=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!




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 3:31 PM
To: mssms@lists.myitforum.com<mailto:mssms@lists.myitforum.com>
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: listsad...@lists.myitforum.com<mailto: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<mailto: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: De

[mssms] RE: Query assistance

[Garth Jones]

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.

 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=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!




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] On 
Behalf Of Enley, Carl
Sent: Wednesday, May 24, 2017 3:31 PM
To: mssms@lists.myitforum.com
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: listsad...@lists.myitforum.com<mailto: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<mailto: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.myitfo

[mssms] RE: Query assistance

[Enley, Carl]

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.F1EF]


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: D

[mssms] RE: Query assistance

[Garth Jones]

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] On 
Behalf Of Enley, Carl
Sent: Wednesday, May 24, 2017 8:37 AM
To: 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.F1EF]


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 

RE: [mssms] RE: Query assistance

[Enley, Carl]

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 c

RE: [mssms] RE: Query assistance

[Ed Aldrich]

…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] On 
Behalf Of Erik Wold
Sent: Wednesday, May 24, 2017 11:29 AM
To: mssms@lists.myitforum.com; 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 st

Re: [mssms] RE: Query assistance

[Erik Wold]

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

[mssms] RE: Query assistance

[Collin Murphy]

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] 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

[mssms] RE: Query assistance

[Enley, Carl]

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

[mssms] RE: Query assistance

[Garth Jones]

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
[Description: Description: 
cid:image001.jpg@01CC63FB.974F4EC0]
Enhancing Your Business
[Description: Description: Description: Description: Description: Description: 
Description: 
cid:image004.png@01CD5E19.F3C3A110][Description: 
Description: Description: Description: Description: Description: Description: 
cid:image005.png@01CD5E19.F3C3A110][Description: 
Description: Description: Description: Description: Description: Description: 
cid:image006.jpg@01CD5E19.F3C3A110][Description:
 Description: Description: Description: Description: Description: Description: 
Description: Description: Description: Description: Description: Description: 
Description: Description: Enhansoft's YouTube 
Page][Description: Description: 
Description: Description: Description: Description: Description: 
cid:image007.png@01CD5E19.F3C3A110]

Subscribe to Enhansoft's Newsletter!

From: 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
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

Re: [mssms] RE: Query Assistance

[Sherry Kissinger]

In order to know what you "mean" by new clients... what does a new client
mean to you?
- Has been a windows 10 image for days/weeks/years, but just lately had the
CM client installed?  in that case, you can look at v_agentdiscoveries
where agentname='mp_clientregistration' ... and use AgentTime there.
- your windows 10 boxes only become CM clients because you use OSD, so the
hinv result of v_gs_operating_system .Installdate0 is a good indicator of
"new client" -- because for some strange reason your environment seems to
constantly uninstall/reinstall the CM client, so a box might have been a CM
client for years and years; but people uninstall/reinstall the client all
the time and mp_clientregistration happens over and over again for multiple
boxes, so it's not as useful?  so the installdate0 of the OS is a better
indication of "new client" vs mp_clientregistration.
- some other indication of "new client" that is special to your
environment, that I haven't thought of.

On Wed, Aug 31, 2016 at 3:43 PM, Smith, Robert F (ES & CSO) <
robert.f.sm...@ngc.com> wrote:

> This should work may need some tweaking
>
>
>
> SELECT
>
>
>
> v_R_System.Name0 AS[Server Name],
>
>
>
> v_GS_OPERATING_SYSTEM.Caption0 AS[Operating System],
>
>
>
> v_GS_OPERATING_SYSTEM.LastBootUpTime0 AS[Last Machine Boot Up Time],
>
>
>
> v_GS_WORKSTATION_STATUS.LastHWScan AS[Last HW Scan Date],
>
>
>
> V_GS_LASTSOFTWARESCAN.LASTSCANDATE AS[LAST SOFTWARE SCAN DATE],
> v_GS_OPERATING_SYSTEM.Installdate0 AS[Create Date]
>
>
>
> FROM v_R_System INNER JOIN  v_GS_OPERATING_SYSTEM on v_GS_OPERATING_SYSTEM
> .ResourceID=v_R_System.ResourceID INNER JOIN
>
>
>
> v_GS_LastSoftwareScan ON 
> v_R_System.ResourceID=v_GS_LastSoftwareScan.ResourceID
> INNER JOIN
>
>
>
> v_GS_WORKSTATION_STATUS ON v_R_System.ResourceID=v_GS_WORKSTATION_STATUS.
> ResourceID
>
>
>
> where v_GS_OPERATING_SYSTEM.Caption0 like '%Windows 10%'
>
>
>
>
>
>
>
>
>
> Robert F. Smith
> Enterprise Operations
> Systems Management Infrastructure
> Phone 703-949-2368
> Cell 203-524-2172
>
>
>
>
>
>
>
> *From:* listsad...@lists.myitforum.com [mailto:listsadmin@lists.
> myitforum.com] *On Behalf Of *Boseman, Marcia H - Raleigh, NC
> *Sent:* Wednesday, August 31, 2016 4:15 PM
> *To:* mssms@lists.myitforum.com
> *Subject:* EXT :[mssms] Query Assistance
>
>
>
>
>
> Does anyone have a SQL query or SCCM 2012 report that would show all
> windows 10 machines that became new clients on a daily basis?
>
>
>
>


-- 
Thank you,

Sherry Kissinger

My Parameters:  Standardize. Simplify. Automate
Blogs: http://www.mofmaster.com, http://mnscug.org/blogs/sherry-kissinger,
http://www.smguru.org

[mssms] RE: Query Assistance

[Smith, Robert F (ES & CSO)]

This should work may need some tweaking

SELECT

v_R_System.Name0 AS[Server Name],

v_GS_OPERATING_SYSTEM.Caption0 AS[Operating System],

v_GS_OPERATING_SYSTEM.LastBootUpTime0 AS[Last Machine Boot Up Time],

v_GS_WORKSTATION_STATUS.LastHWScan AS[Last HW Scan Date],

V_GS_LASTSOFTWARESCAN.LASTSCANDATE AS[LAST SOFTWARE SCAN DATE], 
v_GS_OPERATING_SYSTEM.Installdate0 AS[Create Date]

FROM v_R_System INNER JOIN  v_GS_OPERATING_SYSTEM on 
v_GS_OPERATING_SYSTEM.ResourceID=v_R_System.ResourceID INNER JOIN

v_GS_LastSoftwareScan ON v_R_System.ResourceID=v_GS_LastSoftwareScan.ResourceID 
INNER JOIN

v_GS_WORKSTATION_STATUS ON 
v_R_System.ResourceID=v_GS_WORKSTATION_STATUS.ResourceID

where v_GS_OPERATING_SYSTEM.Caption0 like '%Windows 10%'




Robert F. Smith
Enterprise Operations
Systems Management Infrastructure
Phone 703-949-2368
Cell 203-524-2172



From: listsad...@lists.myitforum.com [mailto:listsad...@lists.myitforum.com] On 
Behalf Of Boseman, Marcia H - Raleigh, NC
Sent: Wednesday, August 31, 2016 4:15 PM
To: mssms@lists.myitforum.com
Subject: EXT :[mssms] Query Assistance


Does anyone have a SQL query or SCCM 2012 report that would show all windows 10 
machines that became new clients on a daily basis?