Compare Active Directory computer accounts with Configuration Manager resources

Despite all discovery methods, Health scripts and other cleanup efforts it might be helpful from time to time to simply compare all the computer account objects that we have in Active Directory with our resources in ConfigMgr.

There are a couple posts available, that demonstrate how to integrate information from Active Directory into SQL reports. See e.g. “How to add AD data to ConfigMgr reporting” from Garth Jones. As you can see, in his post, Garth shows already how to compare AD accounts with ConfigMgr, so why this post?

Well, they mainly use the ADSI provider as a linked server and the OPENQUERY SQL command to query AD. However most of them suffer from a limitation, that comes from Active Directory itself and will actually hurt most people in a even slightly larger environments. The ADSI provider will only return 1000 rows per query.  And there is not way to tell the “OPENQUERY” command to return more rows.

There are a couple ways around this problem:

Increase the limit in Active Directory

Naaaaah. If you don’t know exactly what your are doing, don’t do this, as it has effect on all queries against AD! Also this is not a long term fix, as the company might grow, which would require to increase the limit again.

Call OPENQUERY in batches

Works, but is pretty complicated.

You would need to create a T-SQL script that selects only a subset of accounts, e.g. based on the first character of the name. Every time it reaches the limit of 1000 objects in this subset it would need to divide it again, by e.g taking also the next character of the name into consideration and so on.

If you would like to give this a try, you will find some samples on the internet. I don’t supply a link, as none of them has really fulfilled my needs so far.

Adding a CLR procedure to query AD

See this post from Igor Kovalenko for more information. He basically uses a pre-compiled dll that you have to register in your SQL Server. Again, this will work, but I’m not a big fan of registering some custom dll’s in my sql server. Especially if it is for such a crucial system like ConfigMgr.

Using LogParser to fill a temporary table

LogParser?

YES!

If you don’t know LogParser, you might want to have a look on some IIS query examples that give you a pretty good idea on its capabilities. It’s been initially created to parse IIS log files and haven’t been updated since quite some time (the current download still dates back to 2005), but it’s still some of the most powerful and underestimated tools around. Download it here.

LogParser uses a SQL like syntax to query from IIS logs (of course), text/XML files, Event logs, the File System, Registry and Active Directory (that’s what we need here). It can even parse network captures from Netmon or Windows trace logs. It writes the output to text/xml files, but can also write directly into SQL tables (what we will use in this post).

Most importantly, it’s command line based, so pretty easy to automate. However, there is a GUI called Log Parser Lizzard GUI available for free, in case you struggle with the syntax.

After we installed logparser, we can use something like the following to have it crawl through our AD and return a list of computer with some common attributes we might need (line breaks just added for readability):

Logparser will now write a list of computers in bunches of 10 entries to the default output. In our case the command line window.

As you can see, we selected the LastLogonTimestamp and PwdLastSet attributes. These attributes give a quite good indication if a computer is still active. But these are Active Directory timestamps so not really handy if we want to upload them to a SQL table for some further investigation, as SQL uses a different date/time format. Now Logparser can also help us here. It doesn’t have a native function for the conversion, but if we know, that Active Directory stores date/time values as a number of 100-nanosecond intervals that have elapsed since the 0 hour on January 1, 1601 in GMT, we can use some logparser native functions to do the conversion for us. So to convert the LastLogonTimestamp, we could use the following:

Now we add this to the original query and execute this (line breaks just added for readability):

and we get a nicely formatted output.

Now it’s time to export this information directly into a SQL table. So we need to specify the SQL Server, the Database and the table. We can optionally specify a username and password. We also tell it to create the table if it doesn’t exist and to empty it first, before adding new values, in case it already exists (as we might want to execute this regularly (line breaks just added for readability):

After we executed logparser, we can open the SQL Management Studio and check our TempDB Database and will find a new table, filled with all computer accounts from AD.

Compare with ConfigMgr

Now we can add a view to our ConfigMgr Database, that correlates the information from this temporary table with the ConfigMgr resources. The view could look like:

Which will give you a list of computers that are in AD but not in ConfigMgr, ordered by their last logon timestamp.

Feel free to take this as a starting point to implement your own, way more advanced queries

Automating the process

So far, this has been a one-time effort and only reflects the AD information from the time we executed the query against AD. Now you probably want to keep this information up to date, which makes it necessary to automate this process.

There are several ways to automate it. We could run this as a scheduled task. Just wrap the above command in a batch file and execute it on schedule. Easy. Done.

Instead of using the scheduled tasks of the operating system, we could also make use of the SQL Server itself and have the SQL Agent run a job regularly. For demonstration purposes, we make use of another feature of logparser, and that is its capability to be scripted. During the installation it also registers a couple COM components, that we can use from any script language (VBScript, PowerShell, etc). And as we can natively execute scripts in a SQL Agent Job. we have a perfect fit. You need to make sure, that logparser is also installed on the SQL Server.

So let’s create a new Job in SQL Management Studio

and give it a proper name

Then we add a new step and select ActiveX Script

To make it as easy as possible for you and keep this post a bit shorter, you can download a prepared script from CodePlex (Download from here), that contains all necessary steps to call logparser from VBScript.

Click on “Open” and point it to the supplied script (WriteADComputerInfoToSQL.vbs) and the content will pop up in the script window. Now all you need is to adjust some values in the script like giving it the proper domain name, sqlserver name etc.

Define a schedule that fits to your needs and from then the view you have created before is always up to date.

That’s it.

As you have seen, this was just a basic scenario. I would be happy to hear/read from your solutions.