Collecting SCCM computer object last logon time.

  • Article

Sometimes it may be usefull to see when the last time computer account contacted domain, so you could see which records are old and long time gone.

First you need to add an attribute to AD discovery.

Go to site management, open your site, Site Settings, Discovery Methods, open Active Directory System Discovery

Go to Active Directory Attribute

Click yellow start, and add "lastLogon"

Kick off Active Directory System Discovery

After it's collected, you can run a query like this:

 

 

 

select

DATEADD(hh, cast((LastLogon0 / (600000000))/ 60 as bigint), cast('1601-1-1' as datetime2) ) as  LastLogonDay,  * from v_R_System

order

by LastLogonDay desc

So, you can see which computers just logged on to domain, but don't have client installed, for instance.

Comments

  • Anonymous
    December 02, 2010
    I am getting error when doing this procedure :(Type datetime2 is not a defined system type.Error number: -21472117900Source: Microsoft OLE DB Provider for SQL ServerNative Error: 243I added the lastLogon discovery (but couldn't make it system required YES)Any advise?To test, I simply appended the query above to an existing report (Hardware01A - Summary of computers in a specific collection)
  • Anonymous
    December 14, 2010
    Hi Alex,Thought I'd let you know about an super-valuable AD reporting tool called "Gold Finger" for AD.It is designed by a former Microsoft AD Security expert, and it offers 400 reports including True Last Logon Reports (both for User and Computer accounts), Nested Group membership reports, over 50 valuable Account Management reports etc.I think you'll find it very useful and valuable.Thanks,Scott
  • Anonymous
    January 01, 2011
    Hi Alex,Thank you for sharing this, awesome tip. But, I tried to use the suggested query, but I found issue with DATETIME2 like François, then I did my own report, if someone need here we go:===========SELECT     Name0 AS 'Computer', CONVERT(varchar(10), DATEADD(ms, lastLogon0 / CAST(10000 AS bigint) % 86400000, DATEADD(day,                     lastLogon0 / CAST(864000000000 AS bigint) - 109207, 0)), 111) AS 'Last AD Logon'FROM         v_R_SystemWHERE     (lastLogon0 IS NOT NULL)ORDER BY 'Last AD Logon'===========Regards,
  • Anonymous
    April 13, 2011
    Hi!Unfortunately Last-Logon attribute is not replicated and is maintained separately on each domain controller in the domain. To get an accurate value for the user's last logon in the domain, the Last-Logon attribute for the user must be retrieved from every domain controller in the domain. The largest value that is retrieved is the true last logon time for that user.We can use Last-Logon-Timestamp attribute if we need collect last logon time.
  • Anonymous
    March 25, 2013
    I am using a query below but it shows 1 entry for every person who has logged onto a computer.  I had to extend the .mof to include the Network_login_profile parts.I have it sorted so by computer name and then with newest logon date at the top, so that if I export it to Excel I can simply remove duplicates and the one that remains is the latest.However, I would like my SQL query to remove the duplicates so it shows me only 1 computer and the very last user that logged onto that computer.SelectDISTINCT V_GS_SYSTEM.Name0 as [ComputerName],V_GS_NETWORK_LOGIN_PROFILE.LastLogon0 as [Last Login Time],V_GS_NETWORK_LOGIN_PROFILE.Name0 as [Logon User],V_GS_SYSTEM.Domain0 as [Logon Domain],V_GS_SYSTEM.SystemRole0 as [System Role],V_GS_SYSTEM.SystemType0 as [System Type]from V_GS_NETWORK_LOGIN_PROFILEleft JOIN v_GS_SYSTEM ON V_GS_NETWORK_LOGIN_PROFILE.ResourceID =v_GS_SYSTEM.ResourceIDwhere V_GS_NETWORK_LOGIN_PROFILE.LastLogon0 is not NULLANDV_GS_SYSTEM.SystemRole0='Workstation'Order by V_GS_SYSTEM.Name0, V_GS_NETWORK_LOGIN_PROFILE.LastLogon0 DESC