subject:"RE\: \[mssms\] RE\: Query assistance"

RE: [mssms] RE: Query assistance

2017-05-30 Thread 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]&

RE: [mssms] RE: Query assistance

2017-05-24 Thread 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

RE: [mssms] RE: Query assistance

2017-05-24 Thread 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

2017-05-24 Thread 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

2017-05-24 Thread 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

Re: [mssms] RE: Query Assistance

2016-09-01 Thread 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

RE: [mssms] RE: Query assistance

RE: [mssms] RE: Query assistance

RE: [mssms] RE: Query assistance

RE: [mssms] RE: Query assistance

Re: [mssms] RE: Query assistance

Re: [mssms] RE: Query Assistance

6 matches

Site Navigation

Mail list logo

Footer information