NONEXECUTABLE: To obtain the report.ws file, download the zip file.

Listing 2: Automatic Report Generation for Computer Accounts 

<job>

<script language="VBScript">

BEGIN CALLOUT A 
Option Explicit
' Declare and initialise the constants and variables
Const xlPie = 5
Const xlColumns = 2
Const xlLocationAsNewSheet = 1
Const xlRight = -4152 '(&HFFFFEFC8)
Const xlDataLabelsShowPercent = 3
END CALLOUT A

' Change this path to represent your domain
Const AD_ROOT = "LDAP://ou=Clients,dc=mycorp,dc=com"

Dim arrAttributes(), intClients, intRowIndex, strAttributes, appExcel
Dim intAttributeIndex, arrOSVersionTotals(), bolFound, intOSVIndex

' Set up the attribute string to search for 
BEGIN CALLOUT B
strAttributes = "whenCreated,cn,extensionAttribute1,extensionAttribute2," _
	& "extensionAttribute3,extensionAttribute4,extensionAttribute5," _
	& "extensionAttribute6,extensionAttribute7,extensionAttribute8," _
	& "extensionAttribute9,operatingSystemVersion,whenChanged"

' Specify the OS version attribute in the attributes string
Const OS_VERSION_ATTRIBUTE_INDEX = 11

MsgBox "This script can take a few minutes to run. PLEASE WAIT until " _
	& "the pie chart displays before viewing the Excel data!"
END CALLOUT B

' Search the full AD for all Client computer accounts
BEGIN CALLOUT C 
If 
SearchAD(AD_ROOT,"(objectClass=computer)","subtree",strAttributes,arrAttributes) Then
	intClients = UBound(arrAttributes)+1
Else
	intClients = 0
End If
END CALLOUT C

' Reference the Excel Application object, open the workbook, and rename  
' the sheets
BEGIN CALLOUT D 
Set appExcel = CreateObject("Excel.Application")
appExcel.Workbooks.Add
appExcel.Sheets("Sheet1").Select
appExcel.Sheets("Sheet1").Name = "Computer Data"
appExcel.Sheets("Sheet2").Select
appExcel.Sheets("Sheet2").Name = "Pie Chart Data"
appExcel.Sheets("Computer Data").Select
appExcel.Visible=True
END CALLOUT D

' Set up the computer data sheet and print the column headers
BEGIN CALLOUT E
appExcel.Cells(1,1).Value="When Created"
appExcel.Cells(1,2).Value="Computer Name"
appExcel.Cells(1,3).Value="MAC Address"
appExcel.Cells(1,4).Value="Installer"
appExcel.Cells(1,5).Value="Dept."
appExcel.Cells(1,6).Value="Building"
appExcel.Cells(1,7).Value="Floor"
appExcel.Cells(1,8).Value="Room"
appExcel.Cells(1,9).Value="Local Admin"
appExcel.Cells(1,10).Value="Telephone"
appExcel.Cells(1,11).Value="E-mail address"
appExcel.Cells(1,12).Value="OS Version"
appExcel.Cells(1,13).Value="When Changed"
END CALLOUT E

appExcel.Columns("A:A").ColumnWidth = 19
...

' Set up the pie chart data sheet & print the column headers

BEGIN CALLOUT F 
appExcel.Sheets("Pie Chart Data").Select
appExcel.Cells(1,1).Value="OS Version"
appExcel.Cells(1,2).Value="Total"
appExcel.Columns("A:A").ColumnWidth = 19
appExcel.Columns("B:B").ColumnWidth = 19
END CALLOUT F
...

' Count pie chart data from arrAttributes(x,OS_VERSION_ATTRIBUTE_INDEX)
BEGIN CALLOUT G
ReDim arrOSVersionTotals(1,0)
arrOSVersionTotals(0,0) = arrAttributes(0,OS_VERSION_ATTRIBUTE_INDEX)
arrOSVersionTotals(1,0) = 1

For intRowIndex = 1 To UBound(arrAttributes)
	bolFound = False 
	If IsNull(arrAttributes(intRowIndex,OS_VERSION_ATTRIBUTE_INDEX)) Then
		arrAttributes(intRowIndex,OS_VERSION_ATTRIBUTE_INDEX) = "UNKNOWN"
	End If
END CALLOUT G

' If the OS version from arrAttributes is the same as any other 
' arrOSVersionTotals name, increment the total by one. If the OS version 
' is new, redimension the array to include a new name and a total of 1.
BEGIN CALLOUT H
	For intOSVIndex = 0 To UBound(arrOSVersionTotals,2)
		If (arrOSVersionTotals(0,intOSVIndex) = _
			arrAttributes(intRowIndex,OS_VERSION_ATTRIBUTE_INDEX)) Then
			arrOSVersionTotals(1,intOSVIndex) = _
				arrOSVersionTotals(1,intOSVIndex) + 1
			bolFound = True
		End If
	Next
	If Not bolFound Then
		ReDim Preserve arrOSVersionTotals(1,UBound(arrOSVersionTotals,2)+1)
		arrOSVersionTotals(0,UBound(arrOSVersionTotals,2)) = 
			arrAttributes(intRowIndex,OS_VERSION_ATTRIBUTE_INDEX)
		arrOSVersionTotals(1,UBound(arrOSVersionTotals,2)) = 1
	End If
Next
END CALLOUT H

' Populate the pie chart and computer data sheet with data
BEGIN CALLOUT I 
appExcel.Sheets("Pie Chart Data").Select
For intRowIndex = 0 To UBound(arrOSVersionTotals,2)
	appExcel.Cells(intRowIndex+2,1).Value=arrOSVersionTotals(0,intRowIndex)
	appExcel.Cells(intRowIndex+2,2).Value=arrOSVersionTotals(1,intRowIndex)
Next
appExcel.Sheets("Computer Data").Select
For intRowIndex = 0 To UBound(arrAttributes)
	For intAttributeIndex = 0 To UBound(Split(strAttributes,","))
		appExcel.Cells(intRowIndex+2, intAttributeIndex+1).Value = _
			arrAttributes(intRowIndex,intAttributeIndex)
		If appExcel.Cells(intRowIndex+2, intAttributeIndex+1).Value = "" Then
			appExcel.Cells(intRowIndex+2, intAttributeIndex+1).Value = "UNKNOWN"
		End If
	Next
Next
END CALLOUT I 

' Create the Pie Chart
BEGIN CALLOUT J 
appExcel.Charts.Add
appExcel.ActiveChart.ChartType = xlPie
appExcel.ActiveChart.SetSourceData appExcel.Sheets("Pie Chart Data").Range("A1:B" & CStr(UBound(arrOSVersionTotals,2)+2)), xlColumns
appExcel.ActiveChart.Location xlLocationAsNewSheet, "OS Versions Pie Chart"
With appExcel.ActiveChart
	.HasTitle = True
	.ChartTitle.Characters.Text = "OS versions in use on clients"
End With
appExcel.ActiveChart.HasLegend = True
appExcel.ActiveChart.Legend.Select
appExcel.Selection.Position = xlRight
appExcel.ActiveChart.ApplyDataLabels xlDataLabelsShowPercent, False, True
appExcel.Sheets("OS Versions Pie Chart").Select
END CALLOUT J
...

</script>
</job>