SCCM – Lastlogontimestamp0 has changed in sccm2012

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;

Select
Netbios_Name0,
DATEDIFF(day,Last_Logon_Timestamp0,GETDATE()) as ‘Last Logon Timestamp (DAYS)’,
Last_Logon_Timestamp0 as ‘Last Logon Timestamp’
FROM v_R_System

Where
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;

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

Where

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

Advertisements

4 thoughts on “SCCM – Lastlogontimestamp0 has changed in sccm2012

  1. Rich

    Whoops sorry, didn’t see that at the bottom there. i thought it was part of the other one above. However, it doesn’t work. I get syntax errors.

    • Simon Brouillard

      — Try this;

      Select
      Netbios_Name0,
      DATEDIFF(day,Last_Logon_Timestamp0,GETDATE()) as Last_Logon_Timestamp_Days,
      Last_Logon_Timestamp0 as Last_Logon_Timestamp

      FROM v_R_System

      Where
      DATEDIFF(day,Last_Logon_Timestamp0,GETDATE()) > 30

      — if it’s work, make sure to use single quotes for each of the as value;

      • Rich

        So, this looks like an old computer account query. It’s pulling from v_R_System. Should I add LLTS to AD system discovery too?

        R

  2. Rich

    I got this one working. I had not added all the proper fields to Ad user discovery, you should do a full on tutorial for all these types of queries/reports. Starting with adding the attributes.. Step by step would be nice.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s