SCCM – lastlogontimestamp0 has changed in sccm2012
Here is an interesting change in the v_R_System view in SCCM 2012.
First the attribute is now named last_logon_timestamp0 rather than lastlogontimestamp0 and more seriously it is now convert to a datetime attribute rather than a bigint…. so it should be much more easy to use in reports…. but why it’s only the case for this attribute? What about the lastpasswordset, lastlogon, etc..?
So here’s a example on how you can use it;
DATEDIFF(day,Last_Logon_Timestamp0,GETDATE()) as ‘Last Logon Timestamp (DAYS)’,
Last_Logon_Timestamp0 as ‘Last Logon Timestamp’
DATEDIFF(day,Last_Logon_Timestamp0,GETDATE()) > 10
Also, if you want, you can get the same result in sccm 2007 with the BigInt attributes, but currently the only way I found is by using a temporary table and convert it.
Here’s the query;
DATEADD(mi,(lastLogontimeStamp0 / 600000000) – 157258080,0) AS ‘LLTS’,
DATEADD(mi,(PwdLastSet0 / 600000000) – 157258080,0) AS ‘PLS’,
GivenName0 as ‘FirstName’ ,
sn0 as ‘LastName’into #SMSClt FROM v_R_User WHERE PwdLastSet0 <> 0)Select
CASE WHEN #SMSClt.FirstName IS NULL THEN ‘Unknow’ ELSE #SMSClt.FirstName END as ‘First Name’,
CASE WHEN #SMSClt.LastName IS NULL THEN ‘Unknow’ ELSE #SMSClt.LastName END as ‘First Name’,
#SMSClt.LLTS as ‘Last Logon Timestamp Date’,
Datediff(DAY, #SMSClt.LLTS, GETDATE()) As ‘Last Logon Time Stamp (DAYS)’,
#SMSClt.PLS as ‘Last Password Set Date’,
DateDiff(Day, #SMSClt.PLS, GETDATE()) As ‘Last Password Set (DAYS)’
Datediff(DAY, #SMSClt.LLTS, GETDATE()) > 30 and Datediff(DAY, #SMSClt.PLS, GETDATE()) > 30
DROP table #SMSClt
reference ; http://blogs.technet.com/b/manageabilityguys/archive/2009/11/16/client-activity-summary-report.aspx