Try this. Down in the WHERE clause, you’ll see the Select Name from
_RES_COLL_SMS00001, change the SMS00001 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_SMS00001)
ORDER BY
sys.Name0, UniqueUserName
From: [email protected] [mailto:[email protected]] On
Behalf Of Enley, Carl
Sent: Wednesday, May 24, 2017 12:42 PM
To: [email protected]
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: [email protected]<mailto:[email protected]>
[mailto:[email protected]] On Behalf Of Ed Aldrich
Sent: Wednesday, May 24, 2017 12:11 PM
To: [email protected]<mailto:[email protected]>
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:[email protected]] Ent Cli Mgmt
(2003-2016)
From: [email protected]<mailto:[email protected]>
[mailto:[email protected]] On Behalf Of Erik Wold
Sent: Wednesday, May 24, 2017 11:29 AM
To: [email protected]<mailto:[email protected]>;
[email protected]<mailto:[email protected]>
Subject: Re: [mssms] RE: Query assistance
Also configmgr uses wql, slightly dfifferent than sql.
Mvh
Erik
_____________________________
From: Collin Murphy
<[email protected]<mailto:[email protected]>>
Sent: onsdag, mai 24, 2017 4:49 pm
Subject: [mssms] RE: Query assistance
To: <[email protected]<mailto:[email protected]>>
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:[email protected]>
Sent: Wednesday, May 24, 2017 8:43 AM
To: [email protected]<mailto:[email protected]>
Subject: [mssms] RE: Query assistance
Thanks, excuse my ignorance but if I run the query directly in SQL manager I
get accurate results. If I try to create a new query from the config mgr
console I get a syntax is not correct error, am I missing something? I will be
the first to admit that reporting and SQL are not my strong points. Also is it
possible to list the users primary device in the results output?
[cid:[email protected]]
From: [email protected]<mailto:[email protected]>
[mailto:[email protected]]On Behalf Of Garth Jones
Sent: Wednesday, May 24, 2017 8:19 AM
To: [email protected]<mailto:[email protected]>
Subject: [mssms] RE: Query assistance
The query will get you stated.
Select
U.Full_User_Name0,
Case
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:[email protected]]<http://www.enhansoft.com/>
Enhancing Your Business
[Description: Description: Description: Description: Description: Description:
Description:
cid:[email protected]]<http://www.enhansoft.com/blog>[Description:
Description: Description: Description: Description: Description: Description:
cid:[email protected]]<https://twitter.com/enhansoft>[Description:
Description: Description: Description: Description: Description: Description:
cid:[email protected]]<http://www.facebook.com/EnhansoftInc>[Description:
Description: Description: Description: Description: Description: Description:
Description: Description: Description: Description: Description: Description:
Description: Description: Enhansoft's YouTube
Page]<http://www.youtube.com/user/Enhansoft/videos>[Description: Description:
Description: Description: Description: Description: Description:
cid:[email protected]]<http://myitforum.com/myitforumwp/community/groups/enhansoft/>
Subscribe to Enhansoft’s Newsletter!<http://www.enhansoft.com/register>
From: [email protected]<mailto:[email protected]>
[mailto:[email protected]]On Behalf Of Enley, Carl
Sent: Wednesday, May 24, 2017 8:00 AM
To: [email protected]<mailto:[email protected]>
Subject: [mssms] Query assistance
I need help writing a query that will allow me to pull the primary user and the
mail0 attribute for each machine in a collection. I need to communicate with a
group of users about an anticipated software update and it would be great if I
could add all of the machines to a collection run a query against it to pull
the email address for the primary device user. I could then just export the
results and copy the email address into Outlook for the communication.
I do have the email address being discovered and UDA turned on and working, CM
1702 if it matters.
Thanks
________________________________
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 views of 1E Ltd. Nothing in this email will
operate to bind 1E to any order or other contract.
________________________________
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.
