Your company's ad could live here and reach over 50,000 people a month!

Share This Post

Sccm – Last Logon Timestamp, Last Password Set, and other BigInt vs reporting

Often I had to convert BigInt attributes discovered by Sccm for reporting. Whether to create reports about the LastPasswordSet, lastogontimestamp, etc . So here’s an example of the query I use in these situations;

SQLQuery

(SELECT User_Name0 as ‘UserName’,
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.UserName,
#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)’

FROM #SMSClt
DROP table #SMSClt
************************************

Original Post; http://anotherblabla.wordpress.com/2012/03/18/lastlogontimestamp0-has-changed-in-sccm2012/

 

reference ;  https://myitforum.com/absolutenm/templates/Articles.aspx?articleid=14349&zoneid=89

 

 

Share This Post

Leave a Reply