Thanks for the query
From: [email protected] [mailto:[email protected]] On
Behalf Of Krueger, Jeff
Sent: Dienstag, 22. April 2014 22:18
To: [email protected]
Subject: [mssms] RE: Get the SYSTEM_OU_NAME without the duplicate rows
Thanks, I got the example on the blog working, with the below query.
select dbo.v_R_System_Valid.Netbios_Name0,
(select top 1 ou2.System_OU_Name0 from v_RA_System_SystemOUName ou2
where ou.ResourceID = ou2.ResourceID and LEN(ou2.System_OU_Name0) =
MAX(LEN(ou.System_OU_Name0))) OU
from dbo.v_Add_Remove_Programs
INNER JOIN dbo.v_R_System_Valid ON dbo.v_Add_Remove_Programs.ResourceID
= dbo.v_R_System_Valid.ResourceID
INNER JOIN v_RA_System_SystemOUName ou on
dbo.v_R_System_Valid.ResourceID = ou.ResourceID
WHERE ou.System_OU_Name0 like '%Business%'
AND dbo.v_R_System_Valid.Operating_System_Name_and0 = 'Microsoft
Windows NT Workstation 6.1'
And dbo.v_R_System_Valid.ResourceID
Not IN (Select dbo.v_R_System.ResourceID From dbo.v_Add_Remove_Programs Inner
Join dbo.v_R_System ON dbo.v_Add_Remove_Programs.ResourceID =
dbo.v_R_System.ResourceID
WHERE dbo.v_R_System.Operating_System_Name_and0 =
'Microsoft Windows NT Workstation 6.1'
AND dbo.v_Add_Remove_Programs.DisplayName0 like
'%Microsoft Lync 2010%')
group by dbo.v_R_System_Valid.Netbios_Name0, ou.ResourceID
From: [email protected]<mailto:[email protected]>
[mailto:[email protected]] On Behalf Of Schwan, Phil
Sent: Tuesday, April 22, 2014 11:43 AM
To: [email protected]<mailto:[email protected]>
Subject: [mssms] RE: Get the SYSTEM_OU_NAME without the duplicate rows
Older (CM07), but still applicable logic:
http://www.systemcentercentral.com/sccm-reportingadding-the-system-ou-as-a-column/<https://urldefense.proofpoint.com/v1/url?u=http://www.systemcentercentral.com/sccm-reportingadding-the-system-ou-as-a-column/&k=DRaZFQufJSh%2Bz2CJu01vGA%3D%3D%0A&r=G7Rp%2FyVEkz9AB1xRQWzmh1E0dbzzZxlFIY6QTWSRqzc%3D%0A&m=sCE0eHGG8iSM1OY%2BFm2tx8iR6kPRjbcvq7x67zqMIjU%3D%0A&s=5317f84f7e23825aa453ba40905e0e049cbeac8103fd9d8185d776f54cf00383>
From: [email protected]<mailto:[email protected]>
[mailto:[email protected]] On Behalf Of Schwan, Phil
Sent: Tuesday, April 22, 2014 11:40 AM
To: [email protected]<mailto:[email protected]>
Subject: [mssms] RE: Get the SYSTEM_OU_NAME without the duplicate rows
I had a report somewhere for that. I'll see if I can dig it up...
-Phil
From: [email protected]<mailto:[email protected]>
[mailto:[email protected]] On Behalf Of Daniel Ratliff
Sent: Tuesday, April 22, 2014 11:32 AM
To: [email protected]<mailto:[email protected]>
Subject: [mssms] RE: Get the SYSTEM_OU_NAME without the duplicate rows
I literally had the same issue last week, and just filtered in Excel for the
sake of time. Would love to see some SQL magic to do the trick.
Daniel Ratliff
From: [email protected]<mailto:[email protected]>
[mailto:[email protected]] On Behalf Of Krueger, Jeff
Sent: Tuesday, April 22, 2014 11:25 AM
To: [email protected]<mailto:[email protected]>
Subject: [mssms] Get the SYSTEM_OU_NAME without the duplicate rows
Does anyone have any SQL Fu handy that will give you the longest value in the
dbo.v_RA_System_SystemOUName? Trying to put together a report where I want to
add the OU a system is in but that view has separate rows for each sub ou a
given system is in.
Jeff Krueger
IT - Endpoint Design Services
Henry Ford Health System
[email protected]<mailto:[email protected]>
248.853.4466
________________________________
CONFIDENTIALITY NOTICE: This email contains information from the sender that
may be CONFIDENTIAL, LEGALLY PRIVILEGED, PROPRIETARY or otherwise protected
from disclosure. This email is intended for use only by the person or entity to
whom it is addressed. If you are not the intended recipient, any use,
disclosure, copying, distribution, printing, or any action taken in reliance on
the contents of this email, is strictly prohibited. If you received this email
in error, please contact the sending party by reply email, delete the email
from your computer system and shred any paper copies.
Note to Patients: There are a number of risks you should consider before using
e-mail to communicate with us. See our Privacy & Security page on
www.henryford.com<https://urldefense.proofpoint.com/v1/url?u=http://www.henryford.com&k=DRaZFQufJSh%2Bz2CJu01vGA%3D%3D%0A&r=G7Rp%2FyVEkz9AB1xRQWzmh1E0dbzzZxlFIY6QTWSRqzc%3D%0A&m=sCE0eHGG8iSM1OY%2BFm2tx8iR6kPRjbcvq7x67zqMIjU%3D%0A&s=8930dcc961ec9a2233b4e415f0f595cb959151da27e3773a4fbb47ec6d738b6b>
for more detailed information as well as information concerning MyChart, our
new patient portal. If you do not believe that our policy gives you the privacy
and security protection you need, do not send e-mail or Internet communications
to us.
The information transmitted is intended only for the person or entity to which
it is addressed
and may contain CONFIDENTIAL material. If you receive this material/information
in error,
please contact the sender and delete or destroy the material/information.
________________________________
PRIVILEGED AND CONFIDENTIAL. This email and any files transmitted with it are
privileged and confidential and intended solely for the use of the individual
or entity to whom they are addressed. If you have received this email in error
please notify the sender. If you are not the named addressee you should not
disseminate, distribute or copy this e-mail or any of its attachments.
--
This e-mail message and any attachments are of a confidential nature. The
information is intended for the named addressee exclusively. If you are not the
addressee, you may not electronically disseminate, otherwise distribute or copy
this e-mail message, and you may also not use it for any purpose. Please notify
the sender immediately if you have received this e-mail message by mistake, and
delete this e-mail message and its attachments.
E-mail transmissions could be lost, intercepted, corrupted or destroyed. They
could arrive late or incomplete, or could even contain viruses. Confidentiality
and reliability of the information so transmitted cannot be guaranteed.
Rothschild Bank therefore does not accept any liability or responsibility for
errors or omissions regarding the information transmitted through e-mail.
If verification of the information transmitted through e-mail is required,
please ask for postal delivery by contacting Rothschild Bank/Trust.
This e-mail message is provided for information purposes only. It should not be
construed as an offer or solicitation to buy or sell any financial instruments
or services. It is not to be made available to US persons and is not to be
circulated within the USA.
