[mssms] RE: Query for users with multiple primary devices

[Beardsley, James]

That includes mobile devices. I'm trying to exclude those so it only shows me 
users with multiple laptops assigned.

I looked at the SQL behind the canned report to see if I could exclude mobile 
devices and its way too advanced for my novice SQL skills. If you have any 
tips, I'd love to hear them. Because I like the report otherwise.

From: listsad...@lists.myitforum.com [mailto:listsad...@lists.myitforum.com] On 
Behalf Of Heaton, Joseph@Wildlife
Sent: Monday, April 03, 2017 7:20 PM
To: mssms@lists.myitforum.com
Subject: [mssms] RE: Query for users with multiple primary devices

Why not just use the canned report?  You can set the number of relationships to 
greater than 1.

From: listsad...@lists.myitforum.com<mailto:listsad...@lists.myitforum.com> 
[mailto:listsad...@lists.myitforum.com] On Behalf Of Beardsley, James
Sent: Thursday, March 30, 2017 10:02 AM
To: mssms@lists.myitforum.com<mailto:mssms@lists.myitforum.com>
Subject: [mssms] Query for users with multiple primary devices

Anyone have a SQL query that'll show me all users with more than one primary 
device assigned to them through UDA?

Thanks,
James



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.




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 for users with multiple primary devices

[Heaton, Joseph@Wildlife]

Why not just use the canned report?  You can set the number of relationships to 
greater than 1.

From: listsad...@lists.myitforum.com [mailto:listsad...@lists.myitforum.com] On 
Behalf Of Beardsley, James
Sent: Thursday, March 30, 2017 10:02 AM
To: mssms@lists.myitforum.com
Subject: [mssms] Query for users with multiple primary devices

Anyone have a SQL query that'll show me all users with more than one primary 
device assigned to them through UDA?

Thanks,
James



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 for users with multiple primary devices

[Garth Jones]

This should do the trick.

SELECT
   UMR.MachineResourceID,
   UMR.MachineResourceName,
   UMR.UniqueUserName,
   U.Mail0
FROM
   dbo.v_R_User U
   JOIN dbo.v_UserMachineRelationship UMR ON UMR.UniqueUserName = 
U.Unique_User_Name0
   join (SELECT UMR.UniqueUserName as 'UniqueUserName' FROM 
dbo.v_UserMachineRelationship UMR group by UMR.UniqueUserName Having Count(*) 
>1) MT1 on MT1.UniqueUserName =  UMR.UniqueUserName


From: listsad...@lists.myitforum.com [mailto:listsad...@lists.myitforum.com] On 
Behalf Of Enley, Carl
Sent: Thursday, March 30, 2017 3:28 PM
To: mssms@lists.myitforum.com
Subject: [mssms] RE: Query for users with multiple primary devices

You could use this and just filter excel to show the duplicates in the name 
field?


SELECT UMR.MachineResourceID, UMR.MachineResourceName, UMR.UniqueUserName, Mail0
FROM v_R_User U
JOIN v_UserMachineRelationship UMR ON UMR.UniqueUserName = U.Unique_User_Name0

I also believe there is a built in report where you can specify a collection 
and the number of UDA relationships > greater than 1 for example.


From: listsad...@lists.myitforum.com<mailto:listsad...@lists.myitforum.com> 
[mailto:listsad...@lists.myitforum.com] On Behalf Of Beardsley, James
Sent: Thursday, March 30, 2017 1:02 PM
To: mssms@lists.myitforum.com<mailto:mssms@lists.myitforum.com>
Subject: [mssms] Query for users with multiple primary devices

Anyone have a SQL query that'll show me all users with more than one primary 
device assigned to them through UDA?

Thanks,
James



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.

Re: [mssms] RE: Query for users with multiple primary devices

[Adam Juelich]

Wouldn't this be a great feature?  ::wink wink::

https://configurationmanager.uservoice.com/forums/300492-ideas/suggestions/15040527-option-to-specify-maximum-number-of-primary-device

On Thu, Mar 30, 2017 at 2:28 PM, Enley, Carl  wrote:

> You could use this and just filter excel to show the duplicates in the
> name field?
>
>
>
>
>
> SELECT UMR.MachineResourceID, UMR.MachineResourceName, UMR.UniqueUserName,
> Mail0
>
> FROM v_R_User U
>
> JOIN v_UserMachineRelationship UMR ON UMR.UniqueUserName = U.
> Unique_User_Name0
>
>
>
> I also believe there is a built in report where you can specify a
> collection and the number of UDA relationships > greater than 1 for example.
>
>
>
>
>
> *From:* listsad...@lists.myitforum.com [mailto:listsadmin@lists.
> myitforum.com] *On Behalf Of *Beardsley, James
> *Sent:* Thursday, March 30, 2017 1:02 PM
> *To:* mssms@lists.myitforum.com
> *Subject:* [mssms] Query for users with multiple primary devices
>
>
>
> Anyone have a SQL query that’ll show me all users with more than one
> primary device assigned to them through UDA?
>
>
>
> Thanks,
>
> James
>
>
> --
>
> *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 for users with multiple primary devices

[Enley, Carl]

You could use this and just filter excel to show the duplicates in the name 
field?


SELECT UMR.MachineResourceID, UMR.MachineResourceName, UMR.UniqueUserName, Mail0
FROM v_R_User U
JOIN v_UserMachineRelationship UMR ON UMR.UniqueUserName = U.Unique_User_Name0

I also believe there is a built in report where you can specify a collection 
and the number of UDA relationships > greater than 1 for example.


From: listsad...@lists.myitforum.com [mailto:listsad...@lists.myitforum.com] On 
Behalf Of Beardsley, James
Sent: Thursday, March 30, 2017 1:02 PM
To: mssms@lists.myitforum.com
Subject: [mssms] Query for users with multiple primary devices

Anyone have a SQL query that'll show me all users with more than one primary 
device assigned to them through UDA?

Thanks,
James



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.