SCCM Collection for active inactive computers using Last Logon timestamp and troubleshooting

Introduction:

In this blog post,i will discuss about some of the troubleshooting methods that i have used to identify the active/inactive computers on the network (Active is not based on SCCM agent ) .

Last week ,i was working on office 365 proplus deployment & training for customer in Vietnam. As part this ,one of the activity that i need to identify was,what are the actual number of computers that are talking to domain controller in last X days.

When i look at SCCM ,there are hundreds of computers without SCCM agent .So for me to start with the deployment/reports ,i need to know the actual number of computers on the network as there are lot of stale objects in active directory and also in SCCM.

Whatever the issue that am talking in this blog post may not be applicable to all or anyone and this can be improved /can be avoid using the best practices with the help of AD clean-up and also by implementing start-up script/other methods for client installation.

Coming back to the issue ,i was trying to identify the lit of computers that are active/inactive on the network in last 45 days and take this collection as base for the client health status and also deployments etc.

How do i identify the computers that are active/inactive on the network for last 45 days irrespective of whether they have SCCM agent or not ? For this ,i will use LastLogonTimeStamp .

If you have enabled AD system discovery then you can actually get LastLogonTimeStamp (is selected by default) of computers from Active Directory. To know more about LastLogonTimestamp ,please read Technet article.

So i started creating a collection using LastLogonTimeStamp . Following is the simple collection to identify the computers that are inactive on the network for last 45 days.

select SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,
SMS_R_SYSTEM.SMSUniqueIdentifier,SMS_R_SYSTEM.ResourceDomainORWorkgroup,
SMS_R_SYSTEM.Client from SMS_R_System
where DATEDIFF(dd,SMS_R_System.LastLogonTimestamp,GetDate()) > 45

With this ,i can get list of all computers that have connected to AD in last 45 days. Before i take any action ,i need to validate if these numbers correct or not.

So i looked at the computers in collection ,found that, some of them have actually agent installed and last policy request date as of current date (see fro the screenshot below) .

What went wrong with this collection ? why did it discovered the computer that have agent installed and active ?

When i look at the computer LastLogonTimeStamp ,it was showing very old date .So i went back to Active directory to tally this date. I can see that, the date that is shown in SCCM and what is shown in Active directory is no match.

From AD ,LastLogonTimeStamp shows few days ago but SCCM shows almost few months ago. Why is it so ?

As you know ,to successfully create a DDR for a computer with attributes like computer name,OS,IP Address,AD site etc , Active Directory System Discovery must be able to identify the computer account and then successfully resolve the computer name to an IP address (DNS name resolution).

So i open cmd and did ping and also nslookup for the computer that is discovered into the collection with SCCM agent installed and Active.

I cannot ping the computer and also no nslookup.

With this ,i conclude that, there is issue with name resolution and that must be be first action before trying anything else.

Use the following SQL query to identify the count of objects that are not discovered more than 30 days.

Agent discovery information stores in SQL view v_AgentDiscoveries

select distinct ad.AgentName [Discovery Method],
count(*) [Discovered Clients]
from v_R_System sys
inner join v_AgentDiscoveries AD on AD.ResourceId=sys.resourceid
and DATEDIFF(dd,AD.AgentTime,GetDate()) >30
group by ad.AgentName
order by ad.AgentName

Except MP_ClientRegistration ,rest of the count that is shown by discovery methods are something to be considered for troubleshooting.

with the help of SQL ,you can further drilldown to identify the list of computers

After all this troubleshooting ,it is required to work with Active Directory/DNS team to resolve the name resolution issues.

I have seen customers who don't even enable the AD system discovery and let the client installation happens through GPO/startup script/SUP /OSD that will help to maintain the accurate client information rather pumping all the junk from AD into SCCM.

I know that ,DNS name resolution issues there everywhere.So ,what are the other possible solutions to consider in this case ?

1. You can query computer information into SCCM database without depend on AD system discovery and then do compare with client information with AD & SCCM . For more information, please follow this blog post to discover computers from AD into SCCM .

2. Use startup script as client installation method as described in this article .This will help to get every domain join computer get client installed (if any issues ,it will write log to shared drive to troubleshoot). Along with startup script ,you also need to enable the following option in AD system discovery method.

Only discover computers that have logged onto a domain in given period of time. This is based on lastlogontimestamp that is available in AD .So if there is issue with DNS name resolution ,the computer will not discover into SCCM however ,if you use client startup script ,client will send DDR via heartbeat discovery method. This method help to achieve clean the computers that are inactive .

Hope this guide helps cleaning computer accounts in SCCM based on last logontime stamp.