Query for any machine that last contacted AD over 45 days

[khawkins]

Hi all,

 

I am struggling to find a query that can do this for me. I am deploying software and I am wanting to add a query that will exclude any machines that haven't contacted AD within the last 45 days.

 

Can anyone assist?

 

Many thanks,

Kevin

[therealjoshua]

If they haven't contacted AD, I'd say it's safe to assume that they probably haven't contacted SCCM either. You can use this WQL Query in sccm:

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
inner join
	SMS_G_System_CH_ClientSummary
on
	SMS_G_System_CH_ClientSummary.ResourceID = SMS_R_System.ResourceId
inner join
	SMS_G_System_SYSTEM
on
	SMS_G_System_SYSTEM.ResourceId = SMS_R_System.ResourceId
where
	SMS_G_System_CH_ClientSummary.ClientActiveStatus = 0

That'll find all inactive clients. So you can set your threshold for inactive clients to 45 days.

 

I did a bit of research and it looks like this should get clients that haven't logged into AD in 45 days. I haven't done extensive testing with this query, so do some spot checking to be sure it's getting what you need.

select
	SMS_R_System.Name,
	SMS_R_System.LastLogonTimestamp
from
	SMS_R_System
where
	DATEDIFF(dd,SMS_R_System.LastLogonTimestamp,GetDate()) > 45

[khawkins]

Thanks Josh,

 

This Query works perfectly!

 

Much appreciated,

Kevin