| Downloads |
|---|
| 101930.zip |
Sometimes you need to get a quick look at just your Active Directory (AD) numbers: how many Account Operators you have, how many Domain Admins, how many Groups. I've created a script you can use, ADacctCounts.vbs, to keep track of actual numbers of AD objects so you can see current numbers and how the counts have varied from previous runs. A companion script, ADacctCountsToXL.vbs, lets you produce a Microsoft Excel report from the resulting database.
As a foundation for ADacctCounts.vbs, I used a previous script I created called AccountTracker.vbs. You can read more about that script, which keeps track of changes to various AD objects, in “Track Active Directory Changes.” The script I’ll walk you through now, ADacctCounts.vbs, and its companion script, retrieves just the numbers of AD objects.
How to Use the Script
Currently I am running the script once a week via a scheduled task. At the end of the month, the data is analyzed to get an idea of that month’s growth or decline in specific areas of AD. A monthly report helps our managers visualize the ratio of admins to users and computers that currently exists.
The use of this script is relatively new, so we haven’t produced a yearly report from it yet. However, at the end of the fiscal year we will perform a similar analysis with it, perhaps create some tend charts, and send those reports to upper management to use with other reports. These will provide solid information to aid in employment and equipment forecasting, growth analysis, and support requirement analysis.
The code in ADacctCounts.vbs gathers totals for each of the defined categories and writes a total to that category’s field. The database created and maintained by ADacctCounts.vbs contains only one record in the database per run. That one record does however, contain quite a few fields: one field for every category plus a Rundate field--totaling twenty fields in all.
You can run ADacctCounts.vbs manually as needed, but I recommend setting it up to run as a scheduled task that runs every week or two. To produce the Excel report from the database, you will need to run the companion script ADacctCountsToXL.vbs, which is included in the download files and the main focus of this article.
You can run ADacctCountsToXL.vbs as often as you like or whenever you need to—the script doesn’t make any changes to the database; it simply reads the database and populates an Excel spreadsheet with the AD category counts listed in columns by Rundate. The spreadsheet would look something similar to what you see in Figure 1. Notice the Rundates appear as column headers and the Categories all appear in Column A as individual row headers. Don’t pay too much attention to the totals in the example—I just made those numbers up.
Getting Started with ADacctCounts.vbs and ADacctCountsToXL.vbs
To run these two scripts properly, you will need to create a C:\Scripts\ADacctTrack folder or edit each of the scripts and modify the DBPath statement in each script to point to the folder of your choice. You’ll also want to check the DistinguishedName Query Array (DNQA) element values in ADacctCounts.vbs and insure that the distinguished names are correct for your domain.
If you haven’t moved any of the default Builtin or User groups you probably won’t have to change any of these elements, but if your Domain Admins were in the Builtin container and not the Users container for instance, you’d need to change
DNQA(3) = "CN=Domain Admins,CN=Users," & DNC
to
DNQA(3) = "CN=Domain Admins,CN=Builtin," & DNC
DNC should remain untouched; that’s the Domains Default naming context which gets concatenated to the portion of the DistinguishedName that you see within the quotation marks.
If you haven’t read “Track Active Directory Changes” yet, you might want to take a look at it. It will give you a more thorough explanation of the inner workings of ADacctCounts code and the logic behind it.
Using ADacctCountsToXL.vbs
Let's examine ADacctCountsToXL.vbs, which creates the Excel spreadsheet from the database. This script has some unique characteristics that I think you’ll find interesting. First, its main function is to retrieve data from a database and lay that data out in a spreadsheet in a manner conducive to creating Excel Growth or Trend Charts. That is, the column headers run along the horizontal axis, the categories run along the vertical axis, and the associated counts fall into place where horizontal and vertical meet.
Make sure that you have the appropriate path set up to access the ADacctCounts database by checking the DBPath statement, which callout A in Listing 1 shows. Also note at callout B how I sort the database by Rundate:
DRS.Sort = "RunDate ASC"
If you prefer to have the Rundate columns appear in the spreadsheet so that the most recent date is always in view (always the leftmost column), simply change ASC (ascending) in the sort statement to DESC (descending).
Pay Attention to This Script Technique
I want to point out a technique I use in this script that virtually eliminates the need to hard code the field names of the categories. Remember, I have 19 category fields--and the only hard-coded field name out of the entire database in this script is “Rundate” and that’s mainly because it’s used as the looping control for this routine. Basically when the Rundate changes while stepping through the database, it’s time to move on to the next record and increment the column number for the next Rundate column.
The technique deals with retrieving any number of category fields and programmatically determining which row each of the category counts will be placed in. The trick is to utilize a dictionary object that holds each of the category field names as dictionary keys and an associated row number as the dictionary item element. All you have to do is cycle through all of the fields and store the fieldname and row number to the dictionary, which the code in callout C shows. Please make sure you read the comments within the entire callout, as they elaborate key points.
You’ll also notice that within this section I store the field names to an array called DRSFields. As I mentioned earlier, I take advantage of acquiring the category headers by storing them to an array. Then I simply iterate through the array and fill in the category headers in the spreadsheet by using the code at callout D.
As you’re stepping through each field of each record in the database, you use the field names to look up the row number in the dictionary, and use that number along with the column number to place the actual Category Count value in place on the spreadsheet. You can see how that’s done by reviewing the code at callout E.
I think this routine is fairly portable and something that you might want to consider incorporating into your ADO-based scripts the next time you’re thinking of creating spreadsheet output from databases that have a number of fields to traverse; it might just save you a ton of time and coding.
LISTING 1: ADacctCountsToXL.vbs
On Error Resume Next
******* Begin Callout A *******
DBPath = "C:\scripts\ADacctTrack\"
******* End Callout A *******
AccountCountDB = DBPath & "ADAccountCounts.xml"
Set fso = CreateObject("Scripting.FileSystemObject")
If fso.FileExists(AccountCountDB) Then
Set DRS = CreateObject("ADODB.Recordset")
DRS.Open AccountCountDB
******* Begin Callout B *******
DRS.Sort = "RunDate ASC"
******* End Callout B *******
Else
Set fso = Nothing
strMessage = AccountCountDB & " Not Found...Terminating Script!"
strScriptName = "AD Account Counts"
CreateObject("WScript.Shell").Popup strMessage,10,strScriptName,vbInformation
Wscript.Quit
End If
Set XL = CreateObject("Excel.Application")
XL.Workbooks.Add
XL.Sheets.Add.name = "AccountCounts"
XL.Sheets("AccountCounts").Select
XL.Visible = TRUE
******* Begin Callout C *******
Set FldRef = CreateObject("Scripting.Dictionary")
Set objFields = DRS.Fields
'*** Use number of fields to set array dimension
'*** reduce number by 2. One to account for zero based array
'*** and another to omit 'RunDate' field
FldDim = objFields.count - 2
Dim DRSFields()
Redim Preserve DRSFields(flddim)
incr = 0
For Each objField In objFields
'*** This block of code sets up a Field/Row association
'*** A specific field will have a specific Row in the Excel spreadsheet
'*** 'Rundate' is the first field in the database and is not
'*** used as a Row so it is ignored. Fields start on Row 2
'*** Rundate dates start in Col 2. This format is good for charts
If Lcase(objField.Name) "rundate" Then
FldRef.Add objField.Name,incr+2 'Field name and Row assignment
DRSFields(incr) = objField.Name
incr = incr + 1
End If
Next
******* End Callout C *******
******* Begin Callout D *******
'*** Fill Column A with Fieldnames For i = 0 to Ubound(DRSFields) XL.Cells(i+2,1).Value = DRSFields(i) 'start at row 2 Next
******* End Callout D *******
Col = 2
******* Begin Callout E *******
DRS.MoveFirst
Do while Not DRS.EOF
StoreDate = DRS.Fields.Item("RunDate")
XL.Cells(1,Col).Value = Cstr(DRS.Fields.Item("RunDate"))
Do While StoreDate = DRS.Fields.Item("RunDate")
For i = 0 to Ubound(DRSFields)
If FldRef.Exists(DRSFields(i)) Then
'*** find associated Field/Row
Row = FldRef.item(DRSFields(i))
XL.Cells(Row,Col).Value = Cdbl(DRS.Fields.Item(DRSFields(i)))
End If
Next
DRS.MoveNext
If DRS.EOF Then
Exit Do
End If
Loop
Col = Col + 1 'put next rundate in next column
Loop
******* End Callout E *******
DRS.Close
Set fso = nothing
Set DRS = nothing
XL.Cells.EntireColumn.AutoFit
XL.Range("A1").Select
strMessage = "Done"
strScriptName = "AD Account Counts"
CreateObject("WScript.Shell").Popup strMessage,15,strScriptName,vbInformation
