SCCM – Find AD Computer Without CM Client Installed

If you’re trying to list all active directory computers without sccm client, here’s a report that can help you to get this information. First you have to configure a linked server in sql (http://msdn.microsoft.com/en-us/library/windows/desktop/aa772380(v=vs.85).aspx), then create a report with the following query;

***************************************************************

SQL Query

select
AD.cn as ‘Computer Name’,
CASE When AD.operatingSystem is null then ‘*’
Else AD.operatingSystem END as ‘Operating System’,
CASE When AD.operatingSystemServicePack is null then ‘*’
Else AD.operatingSystemServicePack END as ‘Service Pack’,
case
when AD.lastLogontimeStamp is not null then
cast(DATEADD(mi,(cast(AD.lastLogontimeStamp as bigint) / 600000000) – 157258080
+ DATEDIFF(Minute,GetUTCDate(),getdate()),0) as varchar(32))
else ‘*’
end as ‘Lastlogon Timestamp’,
case
when AD.lastLogontimeStamp is not null then
cast(DATEDIFF(D,DATEADD(mi,(cast(AD.lastLogontimeStamp as bigint) / 600000000) – 157258080
+ DATEDIFF(Minute,GetUTCDate(),GetDate()),0), GETDATE()) as varchar(32))
else ‘*’
end as ‘days’,
case when ad.pwdLastSet is null then ‘*’
when ad.pwdLastSet = ‘0’ then ‘*’
ELSE cast (DATEADD(mi,(cast(ad.pwdLastSet as bigint) / 600000000) – 157258080
+ DATEDIFF(Minute,GetUTCDate(),GetDate()),0) as varchar(32))
end as ‘Password Last Set’,

case when ad.pwdLastSet is null then ‘*’
when ad.pwdLastSet = ‘0’ then ‘*’
ELSE cast(DATEDIFF(D,DATEADD(mi,(cast(ad.pwdLastSet as bigint) / 600000000) – 157258080
+ DATEDIFF(Minute,GetUTCDate(),GetDate()),0), GETDATE()) as varchar(32))
end as ‘days’

From
openquery (ADSI, — linked server Name
‘SELECT cn,
operatingSystem,
operatingSystemServicePack,
lastLogonTimestamp,
pwdLastSet
FROM ”LDAP://DCNAME
WHERE objectCategory = ”Computer”’) as AD

Where
AD.cn not in (Select name0 from v_GS_Computer_System as CS)

order by
AD.lastlogontimestamp

***************************************************************

Also, if you want you can modify the where statement and monitor Active Directory stale client;

***************************************************************

DATEDIFF(D,DATEADD(mi,(cast(ad.lastlogontimestamp as bigint) / 600000000) – 157258080 + DATEDIFF(Minute,GetUTCDate(),GetDate()),0),GETDATE()) > 30 — threshold

and DATEDIFF(D,DATEADD(mi,(cast(ad.pwdlastset as bigint) / 600000000) – 157258080 + DATEDIFF(Minute,GetUTCDate(),GetDate()),0),GETDATE()) > 30  — threshold

***************************************************************

based on Garth Jones post;

http://smsug.ca/blogs/garth_jones/archive/2008/12/03/how-to-add-ad-data-to-configmgr-reporting.aspx

reference;

http://blogs.technet.com/b/ken_brumfield/archive/2008/09/16/identifying-stale-user-and-computer-accounts.aspx

Modify Fallback status point with GPO preferences

In some scenario, either if it wasn’t specified during the installation or you need to change your FPS Site system server, you’ll may have to change the fallback status point for all your sccm clients. Here’s one way from which you can accomplish this task.
Create or edit a GPO, which must target all you sccm client, and go to the Computer configuration \ preferences \ registry then create the following key


Note, even if this work great the only way microsoft support it, it’s by using the switch FSP=server1.contoso.com  ;

http://technet.microsoft.com/en-us/library/bb633164.aspx

Also, If you dont have windows 2008 DC’s, you can use preferences from  RSAT on Windows vista/7 or on a windows 2008 member server, and XP client must havee this update installed;

http://www.microsoft.com/download/en/details.aspx?id=3628