Active Directory clean up? what the hell I’m talking about? I’m not that kind of expert! This little script idea starting with the fact that many of inactive user and computer objects inside OU which I’m responsible of. Staffs come and go, computers in and out but – at the moment – no automated process to remove these objects after certain time inactive. After awhile, number of these inactive objects increase and might raise security issue someday and definitely makes you not pass the security audit.

This little script did not intend to be automated either, but at least, it will gives you an idea which object that could be considered as inactive object by comparing the actual date/time with the object’s lastlogontimestamp attibute. This script will list all these object into the excel spreadsheet and highlighted an object which having lastlogontimestamp value higher than threshold value.

Threshold value is max number where an object have to logon to the domain. For both of users and computers object, I’m considering 95 days quite safe and moderate. Which mean, if those objects, users and computers, not logon to the domain after 95 days could be considered as inactive. This threshold value is modifiable, depend on your own consideration.

The way it populate the LastLogonTimeStamp attibute value is similar, but the attributes on each object slightly difference, hence this script splitted into two separate script, for Users and Computers. Users attribute, in particularly, have many attribute to query and its depend on what attributes you want to show on your spreadsheet. In my case, because I love details, I query a lot of attributes such altRecipient, which user’s attribute if they have email forward automatically to someone. This helpful for me, because sometimes there are request to forward someone emails to others during his/her absence for certain of time.

Computer LastLogonTimeStamp script

'------------------------------------------------------------'
'Description: Computer LastLogonTimeStamp Scanner
'Filename: ComputerLastLogonTimeStamp.vbs
'Author: dede@nurmansyah.or.id
'Date Created: December 23, 2009
'Last Modified: March 18, 2010
'Credit: hilltoplab@rlmueller.net
'------------------------------------------------------------'
Option Explicit 

Const Threshold = -95 'the threshold value, modify here
Const ADS_SCOPE_SUBTREE = 2
Const TargetOU = "OU=JKT,DC=fakedom,DC=com" 'OU where objects populated from, modify here
Const strQuery = "name,distinguishedName,lastLogonTimeStamp,whenCreated,whenChanged" 'Object's attibute
Const XLSFile = "C:\Users\fake.user\My Documents\Temp\ComputerLastLogonTimeStamp.xlsx" 'Spreadsheet location

Dim WSHShell, objFso
Dim objConnection, objCommand, objRecordSet
Dim objComputer, objlastLogonTimeStamp
Dim intRow, objExcel, objWorkbook, objRange

Set WSHShell = CreateObject("Wscript.Shell")
Set objFso = CreateObject("Scripting.FileSystemObject")

If Not objFso.FileExists(XLSFile) Then
	Wshshell.Popup "The template file ("& XLSFile &") doest not exist." & Chr(13) & _
	 			"Please copy the template file to correct location and try again.", _
	 			vbOKOnly,"Information",vbInformation
	WScript.Quit
End If

Set objExcel = CreateObject("Excel.Application")
Set objWorkbook = objExcel.Workbooks.Open(XLSFile)
	objExcel.Sheets("DPS").select()

Function RLMUELLER(attr)
Dim lngBias, lngBiasKey
	' Obtain local Time Zone bias from machine registry.
	'This bias changes with Daylight Savings Time.
	lngBiasKey = WSHShell.RegRead("HKLM\System\CurrentControlSet\Control\TimeZoneInformation\ActiveTimeBias")	

	If (UCase(TypeName(lngBiasKey)) = "LONG") Then
		lngBias = lngBiasKey
	ElseIf (UCase(TypeName(lngBiasKey)) = "VARIANT()") Then
		lngBias = 0
    	For K = 0 To UBound(lngBiasKey)
    		lngBias = lngBias + (lngBiasKey(K) * 256^K)
		Next
	End If

	If (attr.LowPart< 0) Then
        attr.HighPart = attr.HighPart + 1
	End If

	If (attr.HighPart = 0) And (attr.LowPart = 0) Then
        RLMUELLER = "Never"
	Else
		RLMUELLER = _
		#1/1/1601# + (((attr.HighPart * (2 ^ 32)) + attr.LowPart)/600000000 - lngBias)/1440
	End If
End Function

Set objConnection = CreateObject("ADODB.Connection")
Set objCommand =   CreateObject("ADODB.Command")
objConnection.Provider = "ADsDSOObject"
objConnection.Open "Active Directory Provider"
Set objCommand.ActiveConnection = objConnection
	objCommand.Properties("Page Size") = 1000
	objCommand.Properties("Searchscope") = ADS_SCOPE_SUBTREE
	objCommand.CommandText = _
	"SELECT "& strQuery &" FROM 'LDAP://"& TargetOU &"' WHERE objectCategory='computer'"

Set objRecordSet = objCommand.Execute
	objRecordSet.MoveFirst

intRow = 2
Do Until objrecordset.EOF
	objexcel.Cells(introw,1).value = objrecordset.Fields("name").Value
	objexcel.Cells(intRow,2).value = objrecordset.Fields("whenCreated").Value
	objexcel.Cells(intRow,3).value = objrecordset.Fields("whenChanged").Value
	objexcel.Cells(intRow,4).value = _
	RLMUELLER(objRecordSet.Fields("lastLogonTimeStamp").Value)

	With objexcel.Cells(intRow,5)
		.value = DateDiff("d",Now(),objexcel.Cells(intRow,4).value)
		If .Value < Threshold Then
			Set objRange = objexcel.Range("A"& intRow &"", "E" & intRow &"" )
				objRange.Interior.ColorIndex = 7
				objRange.Font.Bold = True
				objRange.Font.ColorIndex = 32
		End If
	End With

	intRow = intRow + 1
	objRecordSet.MoveNext
Loop

objWorkbook.Close True
objExcel.Quit
Set objFso = Nothing
Set WSHShell = Nothing
objRecordSet.Close
objConnection.Close

Users LastLogonTimeStamp script

'---------------------------------------------------'
'Description: User LastLogonTimeStamp Scanner
'Filename: UserLastLogonTimeStamp.vbs
'Author: dede@nurmansyah.or.id
'Date: December 23, 2009
'Last Modified: September 8th, 2010
'Modification comment: Insert altRecipient
'Credit: hilltoplab@rlmueller.net
'---------------------------------------------------'
Option Explicit 

Const Threshold = -95 'modify here
Const ADS_SCOPE_SUBTREE = 2
Const TargetOU = "OU=JKT,DC=fakedom,DC=com"
Const strQuery = _
"sAMAccountName,cn,department,whenCreated,whenChanged,lastLogonTimeStamp,pwdLastSet,badPasswordTime,lockoutTime,accountExpires,altRecipient,userAccountControl"
Const XLSFile = "C:\Documents and Settings\dede.nurmansyah\My Documents\Work Temp\UserLastLogonTimeStamp.xlsx"

Dim WSHShell, objFso
Dim objConnection, objCommand, objRecordSet
Dim intRow, objExcel, objWorkbook, objRange

On Error Resume Next
Set WSHShell = CreateObject("Wscript.Shell")
Set objFso = CreateObject("Scripting.FileSystemObject")

If Not objFso.FileExists(XLSFile) Then
	Wshshell.Popup "The template file ("& XLSFile &") doest not exist." & Chr(13) & _
	 			"Please copy the template file to correct location and try again." , _
	 			vbOKOnly,"Information",vbInformation
	WScript.Quit
End If

Set objExcel = CreateObject("Excel.Application")
Set objWorkbook = objExcel.Workbooks.Open(XLSFile)
objExcel.Sheets("JKT").select()

Function RLMUELLER(attr)
On Error Resume Next
Dim lngBias, lngBiasKey
	' Obtain local Time Zone bias from machine registry.
	' This bias changes with Daylight Savings Time.
	lngBiasKey = WSHShell.RegRead("HKLM\System\CurrentControlSet\Control\TimeZoneInformation\ActiveTimeBias")	

	If (UCase(TypeName(lngBiasKey)) = "LONG") Then
		lngBias = lngBiasKey
	ElseIf (UCase(TypeName(lngBiasKey)) = "VARIANT()") Then
		lngBias = 0
    	For K = 0 To UBound(lngBiasKey)
    		lngBias = lngBias + (lngBiasKey(K) * 256^K)
		Next
	End If

	If (attr.LowPart< 0) Then
		attr.HighPart = attr.HighPart + 1
	End If

	If (attr.HighPart = 0) And (attr.LowPart = 0) Then
        RLMUELLER = "Never"
	Else
		RLMUELLER = #1/1/1601# + (((attr.HighPart * (2 ^ 32)) + attr.LowPart)/600000000 - lngBias)/1440
	End If

	If Err.Number <> 0 Then
		RLMUELLER = Err.Description
	End If
End Function

Set objConnection = CreateObject("ADODB.Connection")
Set objCommand =   CreateObject("ADODB.Command")
	objConnection.Provider = "ADsDSOObject"
	objConnection.Open "Active Directory Provider"
Set objCommand.ActiveConnection = objConnection
	objCommand.Properties("Page Size") = 1000
	objCommand.Properties("Searchscope") = ADS_SCOPE_SUBTREE
	objCommand.CommandText = _
	"SELECT "& strQuery &" FROM 'LDAP://"& TargetOU &"' WHERE objectCategory='user'"

Set objRecordSet = objCommand.Execute
	objRecordSet.MoveFirst

intRow = 2
Do Until objrecordset.EOF
	If objRecordSet.Fields("sAMAccountName").Value <> "" Then
		objExcel.Cells(introw,1).value = objRecordSet.Fields("sAMAccountName").Value
		objExcel.Cells(introw,2).value = objrecordset.Fields("cn").Value
		objExcel.Cells(introw,3).value = objrecordset.Fields("department").Value
		objExcel.Cells(intRow,4).value = objrecordset.Fields("whenCreated").Value
		objExcel.Cells(intRow,5).value = objrecordset.Fields("whenChanged").Value
		objExcel.Cells(intRow,6).value = _
			RLMUELLER(objrecordset.Fields("lastLogonTimeStamp").Value)
		objExcel.Cells(intRow,7).value = _
			RLMUELLER(objRecordSet.Fields("pwdLastSet").Value)
		objExcel.Cells(intRow,8).value = _
			RLMUELLER(objrecordset.Fields("badPasswordTime").Value)
		objExcel.Cells(intRow,9).value = _
			RLMUELLER(objrecordset.Fields("lockoutTime").Value)
		objExcel.Cells(intRow,10).value = _
			RLMUELLER(objRecordSet.Fields("accountExpires").Value) 

		If IsNull(objRecordSet.Fields("altRecipient").Value) Then
			objExcel.Cells(intRow,11).value = "N/A"
		Else
			objExcel.Cells(intRow,11).value = Mid(objRecordSet.Fields("altRecipient").Value,4,Len(objRecordSet.Fields("altRecipient").Value)-47)
		End If

		objExcel.Cells(intRow,12).value = objrecordset.Fields("userAccountControl").Value
		With objexcel.Cells(intRow,13)
			.value = _
			DateDiff("d",Now(),RLMUELLER(objrecordset.Fields("lastLogonTimeStamp").Value))
			If .Value < Threshold Or objExcel.Cells(intRow,12).value = "514" Then
				Set objRange = objexcel.Range("A"& intRow &"", "M" & intRow &"" )
					objRange.Interior.ColorIndex = 7
					objRange.Font.Bold = True
					objRange.Font.ColorIndex = 32
			End If
		End With
	End If
	intRow = intRow + 1
	objRecordSet.MoveNext
Loop

objWorkbook.Close True
objExcel.Quit
Set WSHShell = Nothing
Set objFso = Nothing
objRecordSet.Close
objConnection.Close

You may download the template of spreadsheet for user report here, and computer report here.