Getting Active Directory computer 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.
In this blog post, we will get the list of all computers from AD into the SCCM database. After which, we can create custom reports for identifying the list of the computer's AD and not within SCCM and do vice versa.
I have used the 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 discover by SCCM into Database.
This also helps you to focus more on clients' inventory issues. There are cases, wherein the user will be logged into a domain-joined PC (using the last login timestamp) but the client will not report to SCCM. With this blog post, you can create a hardware inventory report for computers not reported within 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 amd current branch environments.
Updated: Please be clear about what information, you want to retrieve from AD into SCCM Database in the initial phase else you may lead to an issue updating 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 it failed to update new attributes so 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 the SCCM server.If your SQL hosting on different Box,Install the tool 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 needs.
Move to Next tab Steps .This is the step where we will need to 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 have provided earlier in VB Script .
we now have the all computer inventory from AD into SCCM which will help us to create custom reports for validation of the devices.