The Guide to Building an Active Directory Auditing Script

“We need a list of all disabled users.”

“How many users are locked out right now?”

“Do we have any inactive computer accounts?”

These questions – and questions like them – are popular in most environments using Active Directory. They’re not always easy to answer, though, because Active Directory doesn’t have any real built-in reporting mechanisms. If you’re looking for a do-it-yourself solution, there are two approaches you can take: The console, and PowerShell.

Let’s start with the console. All recent versions of the Active Directory Users and Computers console provide custom querying capabilities. As an example, let’s use that feature to create a report (well, sort of a report) on disabled user accounts. Start by opening the console application, and navigate to the “Saved Queries” item in the tree view. Right-click that item, and select “New,” then “New Query” from the context menu. Name your query, as shown here, and then click “Define Query…”.

Figure 1 – Creating a Query

From there, specify your query criteria. In this case, you would choose “Common Queries” from the “Find” drop-down, since that’s the easiest way to get to the “Disabled” attribute. You’ll find that attribute, shown as a checkbox, on the “Users” tab, as shown. This is also where you’ll find “Days since last logon,” “Non-Expiring Password,” and other key account attributes.

Figure 2 – Finding Common Queries

Click “OK” twice to run your query, and you’ll get a list of user accounts that match your criteria. Unfortunately, the console doesn’t actually provide a report, per se. What you get is a list of actual accounts that match your criteria. You can add columns to that list, just as you can in any list in the console, and you can export the account list to a tab- or comma-delimited file. If that’s all you need, then this might be the easiest way to create reports. You could, for example, import the CSV file into Excel to clean it up and make it prettier. The queries you create are saved, so you can always access them in the future to generate the same kind of list a bit more quickly.

Another approach is to use PowerShell, along with the Microsoft Active Directory module that ships with Windows Server 2008 R2 (and the Windows 7 Remote Server Administration Toolkit, or RSAT). This time, let’s say you want to find all computer accounts that have never changed their password. Remember that computer accounts, just like user accounts, have a password. Computers change that password themselves on a regular basis, but a computer that has never changed its password is one that’s probably never logged onto the domain. If the computer account isn’t fairly recent, then it probably can be deleted – or at least examined to find out why it isn’t in use.

Start by importing the module:

PS C:\> import-module activedirectory

Now run the command to retrieve the desired computer accounts:

PS C:\> get-adcomputer -filter * -Properties * | where { $_.passwordlastset -eq $null -and $_.created -lt (get-date).adddays(-30) }

  • There’s a lot going on in that command, so let’s break it down:
  • I started by getting all computers from the domain (-filter *) and getting all of their properties (-Properties *). The properties I need to examine – PasswordLastSet and Created – aren’t included by default, so I needed to make sure those came over.
  • I used Where to only keep computers that have a Null in their PasswordLastSet property. These are computers that have never set a password.
  • I also kept only those computers whose Created property – which is a DateTime value – is less than today’s date, minus 30 days. In other words, only keep computers that were created more than 30 days ago. That way, recently-created computers won’t be included in my list.

The result is a hodgepodge of data that’s too large to include here, and frankly not very useful. You can trim it down by just keeping the information you need to further examine those computers:

PS C:\> get-adcomputer -filter * -Properties * | where { $_.passwordlastset -eq $null -and $_.created -lt (get-date).adddays(-30) } | select -Property CanonicalName,Created

I’ve just specified that only the CanonincalName and Created properties be included in the output. Now, I want to make that a nicer-looking HTML report:

PS C:\> get-adcomputer -filter * -Properties * | where { $_.passwordlastset -eq $null -and $_.created -lt (get-date).adddays(-30) } | select -Property CanonicalName,Created | ConvertTo-HTML | Out-File \\intranet\docs\reports\computers.html

Here’s what that looks like:

Figure 3 – Formatted HTML Report

You can modify the critera used by the Where command to retrieve other result sets, or use commands such as Get-ADUser or Get-ADObject to report on other objects. One caveat: If your criteria becomes fairly complex, or needs to use the special value $Null, or needs to use calculated data (such as the date calculation I used), then you have to provide all of your criteria to the Where command; you can’t send the criteria to the domain controller. The result is that my example may take a long time to run in large domains, because I have to pull over every computer’s account data and examine them individually. So there’s definitely some inefficiency here. Running the command on a domain controller will be somewhat quicker, but we’re still asking that DC to cough up quite a lot of data.

You could schedule this to run on a regular basis by simply using Scheduled Tasks. You’d schedule PowerShell.exe, and then use its –command parameter to specify the above command (or whatever command you come up with). Be sure the task runs under a user account that has permission to query this information.

Let’s be clear: These do-it-yourself techniques can get you the answers you want, but I admit that they aren’t always quick and easy. When your needs exceed the capabilities of these techniques, it’s time to look at one of the many third-party Active Directory reporting solutions on the market. Those often let you automate reporting by creating and delivering reports on a scheduled basis, rather than making you go in and play with the console, or with PowerShell commands.