trim out the domain\...
like this
select
vrs.Name0 as [Machine Name] ,
SUBSTRING(vru.Unique_User_Name0, CHARINDEX('\', vru.Unique_User_Name0) + 1,
LEN(vru.Unique_User_Name0)) AS [User Name],
vru.Full_User_Name0 as [Full Name]
from v_UsersPrimaryMachines upm
left join v_R_User vru on upm.UserResourceID = vru.ResourceID
left join v_R_System vrs on upm.MachineID = vrs.ResourceID
where vru.Name0 IS NOT NULL
order by vru.Name0
or like this:
SELECT DISTINCT
dbo.v_r_system_valid.Netbios_Name0 AS [Computer Name],
v_GS_OPERATING_SYSTEM.Caption0 AS [OS],
dbo.v_r_system_valid.User_Name0 AS [User Name],
dbo.v_R_User.Full_User_Name0 AS [Full User Name],
dbo.v_R_User.department0 AS [Dept],
dbo.v_GS_COMPUTER_SYSTEM.Manufacturer0 AS Manufacturer,
dbo.v_GS_COMPUTER_SYSTEM.Model0 AS Model,
dbo.v_GS_PC_BIOS.SerialNumber0 AS [Serial Number]
FROM
dbo.v_R_System_valid
join
v_R_user on (v_R_user.user_name0 =
substring(v_R_System_Valid.user_name0,charindex('\',v_R_System_Valid.user_name0)+1,len(v_R_System_Valid.user_name0)))
JOIN
dbo.v_GS_COMPUTER_SYSTEM ON dbo.v_r_system_valid.ResourceID =
dbo.v_GS_COMPUTER_SYSTEM.ResourceID
JOIN
dbo.v_FullCollectionMembership ON dbo.v_r_system_valid.ResourceID =
dbo.v_FullCollectionMembership.ResourceID
join
dbo.v_GS_PC_BIOS ON dbo.v_r_system_valid.ResourceID = v_GS_PC_BIOS.ResourceID
JOIN v_GS_OPERATING_SYSTEM on
v_r_system_valid.ResourceID=v_GS_OPERATING_SYSTEM.ResourceID
________________________________
John Marcum
Lead Desktop Engineer
Bradley Arant Boult Cummings LLP
________________________________
From: [email protected] [mailto:[email protected]] On
Behalf Of Ryan
Sent: Tuesday, February 11, 2014 1:21 PM
To: [email protected]
Subject: Re: [mssms] SQL: UDA report for specific user
It is probably stored in this format: domain\username (lastname, firstname,
middleinitial)
I'm not in front of it right now, but I think there should be unique username
which is: domain\username
I think there is also just username, which is just the username with no domain.
On Tue, Feb 11, 2014 at 1:04 PM, Marcum, John
<[email protected]<mailto:[email protected]>> wrote:
Bigger question is.... Why does it "require" wildcards? Usernames should be a
known thing.
________________________________
John Marcum
Lead Desktop Engineer
Bradley Arant Boult Cummings LLP
________________________________
From: [email protected]<mailto:[email protected]>
[mailto:[email protected]<mailto:[email protected]>]
On Behalf Of [email protected]<mailto:[email protected]>
Sent: Tuesday, February 11, 2014 12:48 PM
To: [email protected]<mailto:[email protected]>
Subject: Re: [mssms] SQL: UDA report for specific user
vru.name0 like '%' + @userid + '%'
untested, but I'm pretty sure I've used that before in my shady past...
From: Beardsley, James<mailto:[email protected]>
Sent: Tuesday, February 11, 2014 9:39 AM
To: [email protected]<mailto:[email protected]>
SQL question: I'm trying to put together a report for our helpdesk to use which
will allow them the ability to confirm that a specific user is matched up to
the right device. This below works in SQL studio but it requires that I use
wildcards in the where statement. When building the report, I need that to be a
prompt/parameter. How do I write this so the @userid variable has wildcards
around it when it runs? Or if anyone has a better way of reporting on this, let
me know. I didn't see a built-in report for a specific user, only for users in
a specific collection. Maybe I missed it though...
select upm.UserResourceID,
upm.MachineID,
vru.Name0 as [User Name],
vrs.Name0 as [Machine Name]
from v_UsersPrimaryMachines upm
left join v_R_User vru on upm.UserResourceID = vru.ResourceID
left join v_R_System vrs on upm.MachineID = vrs.ResourceID
where vru.Name0 like @userid
Thanks,
James
________________________________
IRS Compliance: Any tax advice contained in this communication (including any
attachments) is not intended or written to be used, and cannot be used, for the
purpose of (i) avoiding penalties imposed under the Internal Revenue Code or
applicable state or local tax law or (ii) promoting, marketing, or recommending
to another party any transaction or matter addressed herein.
________________________________
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 from a law firm and may be protected by
the attorney-client or work product privileges. If you have received this
message in error, please notify the sender by replying to this e-mail and then
delete it from your computer.
________________________________
Confidentiality Notice: This e-mail is from a law firm and may be protected by
the attorney-client or work product privileges. If you have received this
message in error, please notify the sender by replying to this e-mail and then
delete it from your computer.
________________________________
Confidentiality Notice: This e-mail is from a law firm and may be protected by
the attorney-client or work product privileges. If you have received this
message in error, please notify the sender by replying to this e-mail and then
delete it from your computer.
________________________________
Confidentiality Notice: This e-mail is from a law firm and may be protected by
the attorney-client or work product privileges. If you have received this
message in error, please notify the sender by replying to this e-mail and then
delete it from your computer.
