Getting Active Directory information into SCCM Database can be done by configuring Active Directory discovery Methods in SCCM Configmgr but there are cases, wherein some of the computers may not be discovered or Computers do not exist in AD but do available in SCCM Database.
I used MaikKoster Old blog post as reference and made some customization's/Updates to the query to retrieve information about only Active Computers (Exclude disabled Machines).
Comparing AD Computer information with SCCM Database sometimes interesting to know about client health and are there any missing clients that did not discovered by SCCM into Database.
This also helps you to focus more on clients inventory issues .There are cases ,wherein User will be logged into domain(using last logon timestamp) but client will not report to site .With this you can create hardware inventory report for computers not reported with in X number of Days with AD last logon Timestamp. (if your discovery is not customized and run frequently).
You can use this procedure in SMS 2003, SCCM 2007 and SCCM 2012 environments.
Updated : Please be clear what information ,you want to retrieve from AD into SCCM Database in the initial phase else you may lead to an issue to update the existing table with custom attribute values.
I tried adding more attributes from AD to the existing SQL Job what is already been created but i failed . I have to drop the table ,Change the query and run the job to make it Work.
This procedure requires you to download Log Parser (http://www.microsoft.com/en-us/download/details.aspx?id=24659) and install it on CM server.If your SQL hosting on different Box,Install it on SQL Box with default settings (next,next,next,close 🙂 )
Start SQL server Management Studio
Move onto SQL server Agent—>Jobs—>Create New Job
On the General Tab,Enter the required Fields as per appropriate.
Move to Next tab Steps .This is the step where you Create Job to execute and get AD computer Information into SCCM Database.
Click on New
You are prompted to select Different fields like Step Name (AD info), Type(ActiveX Script) and Run as(SQL server Agent Service Account).
Next is to import the the script to execute. Download the Script from Maikkoster website here and Import it using Open.
Once you are imported , you are required to supply values for the below Objects :
strLDAP = "LDAP://eskonr.com"
strSQLServer = "sccmSQL"
strDatabase = "SMS_CEN"
strTable = "ADComputersInfo"
Note: Strtable is Table not view
Replace the below customized query with existing One after ' Create query text
strQuery = "SELECT cn, objectpath, operatingSystem, operatingSystemServicePack"
strQuery = strQuery & ", CASE LastLogonTimestamp WHEN 0 THEN NULL ELSE "
strQuery = strQuery & " TO_TIMESTAMP(ADD(DIV(TO_REAL(LastLogon), 10000000.0), TO_REAL(TIMESTAMP('1601','yyyy')))) END AS [LastLogon]"
strQuery = strQuery & ", CASE pwdLastSet WHEN 0 THEN NULL ELSE "
strQuery = strQuery & " TO_TIMESTAMP(ADD(DIV(TO_REAL(pwdLastSet), 10000000.0), TO_REAL(TIMESTAMP('1601','yyyy')))) END AS [PwdLastSet]"
strQuery = strQuery & " INTO " & strTable & " FROM"
strQuery = strQuery & "'" & strLDAP & "'" & " where "
StrQuery = strQuery & "userAccountControl" & "=" & "4096"
Note : You can do customizations to the above query depends on your requirement.
“Please replace the Quotes as the blog converts them to fancy ”
I have uploaded the modified script here. You just need to replace the domain ,SCCM SQL server info ,SQL table and LAP path.
Go to Schedule Tab,Click New
Schedule it as per the requirement.
Click on Ok, OK
Start the job which we created now.
You see from below,Job is executed successfully.
Now its time to create Various reports based on your needs.
Simple report to list Computers which are not available in SCCM but do Exist in AD as Active.
SELECT [cn] AS 'Computer Name'
,[ObjectPath] AS 'Path'
,[operatingSystem] AS 'OS'
,[operatingSystemServicePack] AS 'SP'
,DATEDIFF(dd, [LastLogon], getdate()) AS 'days LastLogon'
,DATEDIFF(dd, [PwdLastSet], getdate()) AS 'days PwdLastSet'
WHERE cn NOT IN (SELECT name0 FROM v_GS_Computer_System)
ORDER BY LastLogon
Replace the Database name and table what you given in VB Script earlier.
Until Next !