Using lastLogontimeStamp for your Client Health
If you want to use the lastLogontimeStamp value in Config Mgr reports its fully possible. The value will give you a date when your machine last was in contact with your Active Directory with some modifications, In fact the date will be between 9-14 days behind the actual date with default settings. But at least it gives you a hint on your clients activity. More about the value here: http://blogs.technet.com/askds/archive/2009/04/15/the-lastlogontimestamp-attribute-what-it-was-designed-for-and-how-it-works.aspx .
So first you need to collect the value this is possible to do with your system discovery task. All we need to to is to configure an extra attribute to be collected “lastLogontimeStamp”.
Go to your discovery task and the Active Directory Attribute and add a new value lastLogontimeStamp, and press ok and at the next discovery the value will be collected.
The Value itself is is stored in INT8 se we need to convert the value to Date and Time when we Display it. My SQL Skills are note the best in the world so I asked my friend John to help me out and so he did. So for further references please read the blog post he wrote about it.
In my case I am on SQL 2008 so I did my function and used it in my query to get the machine name and the lastLogontimeStamp in my Report.
Function to Convert Int8 to Date Time
go
CREATE FUNCTION dbo.udf_Int8_to_DateTime(
@Int8 BIGINT
)
RETURNS DATETIME2
AS
BEGIN
RETURN (DATEADD(mi,@Int8 / 600000000 + DATEDIFF(Minute,GetUTCDate(),GetDate()),CAST(’1/1/1601′ AS DATETIME2)))
END
GO
SQL Report to get Machine Name, Usaername and Last AD Contact using the function created in the previous step.
SELECT v_R_System.Name0, v_R_System.User_Name0, CAST(dbo.udf_Int8_to_DateTime(v_R_System.LastLogonTimeStamp0) as varchar(11)) as LastADContact
FROM v_R_System
ORDER BY LastADContact asc
This has been tested on SQL 2008, but you should always test on lab systems before moving any code to production. This is released “AS IS” and confers no rights.
References
Tags: Articles, ConfigMgr 2007
February 25, 2010 21:46 
Subscribe 