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] 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] 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 
>
Sent: onsdag, mai 24, 2017 4:49 pm
Subject: [mssms] RE: Query assistance
To: >



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 for Windows 10

From: Enley, Carl
Sent: Wednesday, May 24, 2017 8:43 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: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
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
[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: 

Re: [mssms] m.2 and win10 OSD

[Mike Dougherty]

m.2 is a drive form factor, not necessarily a drive "communication" type.
is the m.2 drive NVMe? or sata?

is the machine itself configured for UEFI booting? Legacy Boot/CSM support?
SecureBoot enabled?
is there an NVMe setting in the bios/setup?
does the system/BIOS itself recognize a hard drive is attached?

the fact that diskpart doesn't list a drive in winpe when you run it
indicates you're either missing winpe drivers/bios settings or you have bad
hardware.

what version of the ADK are you running?
have you updated the drivers in your boot media recently?


On Wed, May 24, 2017 at 5:34 PM, Michael K Murray 
wrote:

> Hey folks,
>
>
>
> We’re trying to image an OptiPlex 7050 that has a 500GB m.2 card. It gets
> to the partition step and fails, as it doesn’t recognize any disk. Running
> diskpart at a command prompt shows no disks. I’ve Googled like crazy, but
> only seem to find Windows 7 articles. We’re trying to image with Win10. Log
> attached.
>
>
>
>
>
> Best Regards,
>
>
>
> Mike Murray
>
> Desktop Engineer/IT Consultant - IT Support Services
>
> California State University, Chico
>
> 530.898.4357 <(530)%20898-4357>
> mmur...@csuchico.edu
>
>
>
> Remember, Chico State will NEVER ask you for your password via email!
>
> For more information about recognizing phishing scam emails go to:
> http://www.csuchico.edu/isec/basics/spam-and-phishing.shtml
>
>
>
>

[mssms] Dell BIOS Update for Intel AMT Vulnerability

[Morris, Spencer]

Has anyone looked into deploying these to production? 
http://en.community.dell.com/techcenter/extras/m/white_papers/20443914


We have them going out to desktops outside of office hours with a reboot, which 
is no problem.


Our laptops are only on site during office hours and it appears that the bios 
update specifically needs a reboot to finish installing, a shutdown and start 
will not work to install the update.


Does anyone have any ideas on how to make this work with a shutdown and start? 
Since that is what most laptop users will do at the end of the day and the 
beginning of the next day. I know I could use SCCM or PSADT to prompt for a 
reboot, but I'm trying to minimize user interruption with a reboot in the 
middle of the day.


Thanks for your help.

[2016 Design Firm of the Year][WRA 100 Years]


The information supplied in this message may be privileged, confidential and/or 
exempt from disclosure under applicable law. If you are not the intended 
recipient of this message, the sender does not intend delivery to you to waive 
any privilege or right pertaining to this message. You have no right to retain, 
disseminate, copy or disclose the material contained herein. If you have 
received this message in error, please immediately notify the sender by return 
e-mail, and delete the errant message. Thank you.
WRA_Disclaimer_v20070222a

[mssms] Handling Unexpected Reboots in Task Sequences

[Beardsley, James]

I'm working on a task sequence to upgrade Windows 7 to Windows 10 and prior to 
the upgrade, because it was causing issues, we need to remove the DisplayLink 
Driver and Core Software. So I have a script that removes those and that 
Uninstall DisplayLink step is calling it (from package source). I've added the 
/noreboot and REBOOT=REALLYSUPPRESS parameters to the msiexec within the script 
but the uninstall always forces a reboot with no heads up and I can't seem to 
suppress it. I tried putting a step afterwards that runs "shutdown -a" in to 
see if it would catch it before it restarted but no luck. So the question is, 
what would be the best way to uninstall DisplayLink and be able to handle the 
unexpected reboots in the middle of the task sequence so that when the machine 
comes back up, it would continue with the OS upgrade?

I'm in the process of testing using an application to uninstall DisplayLink and 
then checking the box under Options within the TS to retry on unexpected 
restart so that's why you see two Uninstall DisplayLinks below. But figured I'd 
reach out to everyone here as well in case that doesn't work.

[cid:image001.png@01D2D4B1.D1E436E0]


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.

[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

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

[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
[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 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] 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
[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:37 AM
To: 

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

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

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] 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 
>
Sent: onsdag, mai 24, 2017 4:49 pm
Subject: [mssms] RE: Query assistance
To: >




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 for Windows 10

From: Enley, Carl
Sent: Wednesday, May 24, 2017 8:43 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: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
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
[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 

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 
>
Sent: onsdag, mai 24, 2017 4:49 pm
Subject: [mssms] RE: Query assistance
To: >



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 for Windows 10

From: Enley, Carl
Sent: Wednesday, May 24, 2017 8:43 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: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
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
[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












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 

Re: [mssms] RE: Query assistance

[Erik Wold]

Also configmgr uses wql, slightly dfifferent than sql.

Mvh
Erik
_
From: Collin Murphy 
>
Sent: onsdag, mai 24, 2017 4:49 pm
Subject: [mssms] RE: Query assistance
To: >


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 for Windows 10

From: Enley, Carl
Sent: Wednesday, May 24, 2017 8:43 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: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
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
[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

[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 for Windows 10

From: Enley, Carl
Sent: Wednesday, May 24, 2017 8:43 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: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
[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

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

[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

[mssms] Query assistance

[Enley, Carl]

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