How to Compare AD computers to SCCM computers using Log Parser ?

 

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

image

Move onto SQL server Agent—>Jobs—>Create New Job

image

On the General Tab ,enter the required fields as per needs.

image

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

image

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.

image

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

image

Schedule it as per the requirement.

image

Click on Ok, OK

Start the job which we created now.

image

You see from below,Job is executed successfully.

image

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 Responses to "How to Compare AD computers to SCCM computers using Log Parser ?"

  1. 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")

    Reply
  2. 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.

    Reply
    1. 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?

      Reply
      1. 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?

        Reply
  3. 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"

    Reply
    1. 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 .

      Reply
  4. 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”

    Reply
  5. 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..

    Reply
    1. 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;

      Reply
  6. 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.

    Reply
    1. 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.

      Reply
    1. 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?

      Reply
  7. 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?

    Reply
  8. 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

    Reply
  9. 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″

    Reply
  10. 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.

    Reply
  11. 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.

    Reply
    1. 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.

      Reply
  12. 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

    Reply
    1. 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.

      Reply

Post Comment