Collecting SCCM computer object last logon time.


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 (5)

  1. Francois Bouchard says:

    I am getting error when doing this procedure 🙁

    Type datetime2 is not a defined system type.

    Error number: -21472117900

    Source: Microsoft OLE DB Provider for SQL Server

    Native Error: 243

    I 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)

  2. Scott says:

    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

  3. Cleber Marques says:

    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_System

    WHERE     (lastLogon0 IS NOT NULL)

    ORDER BY 'Last AD Logon'

    ===========

    Regards,

  4. Alexey Taranenko says:

    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.

  5. 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.

    Select

    DISTINCT 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_PROFILE

    left JOIN v_GS_SYSTEM ON V_GS_NETWORK_LOGIN_PROFILE.ResourceID =

    v_GS_SYSTEM.ResourceID

    where V_GS_NETWORK_LOGIN_PROFILE.LastLogon0 is not NULL

    AND

    V_GS_SYSTEM.SystemRole0='Workstation'

    Order by V_GS_SYSTEM.Name0, V_GS_NETWORK_LOGIN_PROFILE.LastLogon0 DESC