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.
Click Ok.
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'
,[LastLogon]
,DATEDIFF(dd, [LastLogon], getdate()) AS 'days LastLogon'
,[PwdLastSet]
,DATEDIFF(dd, [PwdLastSet], getdate()) AS 'days PwdLastSet'
FROM [DatabaseName].[dbo].[tablename]
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.
33 Comments
Pingback: PowerShell – How to import AD computer object data into SCCM database using PowerShell – Jacques in IT
Hi Eswar,
I have changed the quotes to the original and below are the 2 lines whi I see at line 35 and 36.
' Create LogParser object
Set oLogQuery = CreateObject("MSUtil.LogQuery")
Hi,
I uploaded the script for you http://eskonr.com/wp-content/uploads/2014/04/WriteADComputerinfoToSql.zip .change the LDAP path,AD info,SQL Table and SQL server info
Hello Eswar,
While trying to start the job Iam getting the below error can you please let me know where Iam going wrong.
Error :
Error Code: 0 Error Source= Microsoft VBScript runtime error Error Description: ActiveX component can't create object: 'MSUtil.LogQuery' Error on Line 35. The step failed.
Can you check the script at line 35, there will be something mistake... What is it from line 35? Have you changed the quotes to original?
Can you check the script at line 35, there will be something mistake... What is it from line 35? Have you changed the quotes to original?
Yeah thats what I have done but it still says "Executed as user: DOMAIN\SQLserviceaccount. The step did not generate any output. The step succeeded"
not generated any output ? are you referring to the table created for AD info which is blank ?
or newly created table has info but you are failed to retrive it ? if the task is executed successfully but no results,then i would suspect something wrong with the LDAP you have provided .
Sorry to bother you again but I just wanted to ask one more thing.
When the SQL Job is run, it should import AD data into the SQL database. I was just looking at the log of that job and got the below.
Date 4/2/2014 12:00:01 AM
Log Job History (AD to SCCM Computer Information)
Step ID 1
Server SQLSERVERNAME
Job Name AD to SCCM Computer Information
Step Name Execute LogParser
Duration 00:00:27
Sql Severity 0
Sql Message ID 0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted 0
Message
Executed as user: DOMAIN\SQLserviceaccount. The step did not generate any output. The step succeeded.
I am wondering if it is working correctly as it says "The step did not generate any output".
(I changed the names of the server and domain)
I also am pointing this section to a OU as I don't want to import all of AD, I assume that that is OK?
strLDAP = “LDAP://eskonr.com”
yes,if you do not want the entire AD computers to be discovered from,you can provide custom LDAP path.some examples how to provide custom LDAP: http://social.technet.microsoft.com/wiki/contents/articles/1773.ldap-path-active-directory-distinguished-and-relative-distinguished-names.aspx
Yes the SQL runs on another server but I logged into that server with my admin account and created. I then logged into my SCCM server with same account and tried to run the report.
If it is a permission issue I will just have to keep at it but I can't see how it could be.. I guess it might have something to do with my SQL being on another server..
ok,so the SQL query works fine on SQL server but not from Configmgr server ? Yes,I suspect too.Contact DBA guys for permissions related;
Sorry took so long to get back to you but I had to track down the account details.
I have since tested with the Service account and have the same issue.
Are you using the same account which was used to run the SQL job ?If the account which was used to run the job has enough rights to create the View,then should have the account with full permissions.
I am sure this is something to do with security permissions.Use the account which was created SQL job to get the AD info into Configmgr DB.
Still get same error even after adding.
Can you try with admin account or service account which has been used to create/install sql server to check if it is really permissions issue or something else?
In fact shouldn't SCCM be connecting with its own account to run reports?
Nope. It takes user permissions what is allowed for the user to run and display results.
My admin id already has read\write to my database (I can open and run query's in SSMS) but I added it anyway and still have same issue.
If this is just a table in my main SCCM database why is it not just connecting with the same details as all the other reports? Is it due to the way I created it?
Have you tried adding the permissions as i said in the blog post? Did it work after?
I can see the table in SQL but when I run that report I get this error. Do you know what it means?
An error occurred when the report was run. The details are as follows:
The SELECT permission was denied on the object 'ADComputersInfo', database 'APAC_SCCM2007', schema 'dbo'.
Error Number: -2147217911
Source: Microsoft OLE DB Provider for SQL Server
Native Error: 229
Report as I have in SCCM (replace quotes)
SELECT [cn] AS 'Computer Name'
,[ObjectPath] AS 'Path'
,[operatingSystem] AS 'OS'
,[operatingSystemServicePack] AS 'SP'
,[LastLogon]
,DATEDIFF(dd, [LastLogon], getdate()) AS 'days LastLogon'
,[PwdLastSet]
,DATEDIFF(dd, [PwdLastSet], getdate()) AS 'days PwdLastSet'
FROM [APAC_SCCM2007].[dbo].[ADComputersInfo]
WHERE cn NOT IN (SELECT name0 FROM v_GS_Computer_System)
ORDER BY LastLogon
hi,
It is related to permissions issues.Did you try this ? http://eskonr.com/2013/09/sccm-configmgr-sql-errorthe-select-permission-was-denied-on-the-object-database-cm_sitecode-schema-dbo/ .
Hi Eswar,
Below is the error Iam getting when Iam executing the Job
Error Code: 0 Error Source= Microsoft VBScript compilation error Error Description: Expected end of statement Error on Line 85. The step failed.
This is the code I replaced
' 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″
did u replace all the ' and " quotes after you copy the script ?
can u look for the line 85 ? what does it tells ? any syntax missing ?
Hi Eswar,
I have tried the steps exactly mentioned in the blog but Iam getting the below error.
Error Code: 0 Error Source= Microsoft VBScript compilation error Error Description: Invalid character Error on Line 55. The step failed.
Please suggest what should be done.
seems you are missing something from the script.can u or post me the script not completely but the error code line.
Error code:0 Error Source=Microsoft VBScript runtime error Error Description: ActiveX component can't create object: "M S Util.logQuery" Error on Line 59. The step failed.
Hi,
Did you imported the query from Maikkoster Blog ? and just only edit the content what is the blog post.
Also please replace the quoates(' and " ) .existing are fancy quoates so they do not work.
Execute job 'AD Computer Information : Error
Hi Eswar
I confirm, it's running on sms 2003 🙂
However, I search (because i don't know to do it) a script to enumerate from a computer list the value on attribute "description".
I can't put on my post the sreen shot, that to explain correctly my resquet 🙁
Can you help me ?
best regards
George
Hi,
Yes ,that is expected. Please refer the blog post with updated information.That should clear your mind.
Hi
is it running on SMS 2003 ?
thanks in advance
Best Regard
Hi George,
Yes it will work but if you are running SQL sever 2005 and more,should not be an issue.You should get it as same as SCCM 2007/2012.