Follow-up question: Is there an easy way to get this directly from AD and get more accurate login time info?
select givenName0 as 'First Name',
sn0 as 'Last Name',
user_Name0 as 'User Name',
title0 as 'Title',
l0 as 'Location',
CASE WHEN User_Account_Control0 in ('544', '512', '66048', '66080', '590336')
THEN 'Enabled'
ELSE 'Disabled'
END as 'Account Status',
CAST((lastLogonTimestamp0 / 864000000000.0 - 109207) AS DATETIME) as 'Last
Logon'
from v_R_User
order by lastLogonTimestamp0 desc
________________________________
John Marcum
Sr. Desktop Architect
Bradley Arant Boult Cummings LLP
________________________________
From: [email protected] [mailto:[email protected]] On
Behalf Of Marcum, John
Sent: Tuesday, November 26, 2013 10:28 AM
To: [email protected]
Subject: [mssms] RE: v_R_User Attributes
Thanks!
Are you sure it's inaccurate for the USER or is that only true for the system?
(it is in both actually)
[cid:[email protected]]
select givenName0 as 'First Name',
sn0 as 'Last Name',
user_Name0 as 'User Name',
title0 as 'Title',
l0 as 'Location',
CASE WHEN User_Account_Control0 in ('544', '512', '66048', '66080', '590336')
THEN 'Enabled'
ELSE 'Disabled'
END as 'Account Status',
CAST((lastLogonTimestamp0 / 864000000000.0 - 109207) AS DATETIME) as 'Last
Logon'
from v_R_User
order by lastLogonTimestamp0 desc
________________________________
John Marcum
Sr. Desktop Architect
Bradley Arant Boult Cummings LLP
________________________________
From: [email protected]<mailto:[email protected]>
[mailto:[email protected]] On Behalf Of Koster, Maik
Sent: Tuesday, November 26, 2013 10:16 AM
To: [email protected]<mailto:[email protected]>
Subject: [mssms] RE: v_R_User Attributes
1. According to the documentation at
http://msdn.microsoft.com/en-us/library/hh949577.aspx,
UserAccountControl
Data type: UInt32
Access type: Read-only
Qualifiers: None
User account control value retrieved from Active Directory
Find a list of all values at:
http://msdn.microsoft.com/en-us/library/windows/desktop/ms680832(v=vs.85).aspx
2. LastLogonTimestamp is from SMS_R_System, not SMS_R_User. However, it
might be pretty inaccurate, if your purpose is "real-time" logon tracking:
http://support.microsoft.com/kb/2679653/en-us
Regards
Maik
From: [email protected]<mailto:[email protected]>
[mailto:[email protected]] On Behalf Of Marcum, John
Sent: Dienstag, 26. November 2013 16:51
To: SMS List ([email protected]<mailto:[email protected]>)
Subject: [mssms] v_R_User Attributes
A couple of questions here;
1. Does anyone know what all the possibilities on
v_R_User.userAccountControl mean?
2. How accurate is v_R_User.Last_LogonTimestamp0? The reason I ask is
this sounds like it may not be very accurate in AD:
http://blogs.technet.com/b/askds/archive/2009/04/15/the-lastlogontimestamp-attribute-what-it-was-designed-for-and-how-it-works.aspx
________________________________
John Marcum
Sr. Desktop Architect
Bradley Arant Boult Cummings LLP
________________________________
________________________________
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.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
This email message is for the sole use of the intended recipient(s) and may
contain confidential and privileged information of Cameron and its Operating
Divisions. Any unauthorized use or disclosure is prohibited. If you are not the
intended recipient, please contact the sender by reply email and delete and
destroy all copies of the original message inclusive of any attachments.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
________________________________
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.
<<inline: image001.png>>
