http://dede.nurmansyah.or.id

Recently, i was notice that numbers of computers inside of Computer OU in Active Directory larger than the actual “running” machine in the network. I believe, it’s happen because those computers – that no longer exist in the domain or network – was not deleted from Active Directory properly. The advantages of this situation are no one have idea which computer that no longer exist or removed from the network permanently. Scanning all those machines with ping test will not help either, because some of machine probably on remote location which disconnected temporary from the network. And don’t tell me to manually check it, one-by-one!

The only possible way, by checking the time of those computer machine last log on to Active Directory. This information could get by querying computer’s object lastLogonTime or lastLogonTimeStamp attribute. The lastLogonTime attribute doesn’t replicate in Active Directory while lastLogonTimeStamp does. Hence, by querying lastLogonTimeStamp attribute seem more promising to get better result. However, both of lastLogonTime and lastLogonTimeStamp attribute are stored in Active Directory as Integer8 (8 bytes), it’s means is a 64-bit number, which cannot be handled directly by VBScript. Richard Mueller’s website does provide tricks on how to handle this such of data with VBScript. His website also contain samples script on how to read lastLogonTime and other Active Directory attribute that stored as Integer8.

Below script will query all computers object under specified OU – variable TargetOU – and write the result of query into an excel file that specified on variable XLSFile. You could get the script here and the excel template here. Hope this script will help. For any corrections are most than welcome! Enjoy!

THE CODE

'---------------------------------------------------'
'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 = -30 'modify here
Const ADS_SCOPE_SUBTREE = 2
Const TargetOU = "OU=TARGETOU,DC=FAKEDOM,DC=com" 'modify here
Const strQuery = "name,distinguishedName,lastLogonTimeStamp,whenCreated,whenChanged"
Const XLSFile = "D:\My Documents\My Work Temp\ComputerLastLogonTimeStamp.xls"

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("TARGETOU").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