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

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s