asked on

VB Script to output in CSV Format: AD username, discription, OU, Last logon date/time

I need a VB Script that outputs in CSV Format the following info about USERS in AD:

Domain Name: Contoso.local
PDC Name: ContosoDC1 (All DCs Windows 2008)
Info Needed:

username, discription, Modified, OU, Last logon date/time (In Readable format)

your hel is much appreciated.

vinsvin

The below link has a VB script, you can use. Copy the content to a text file and name is <filename>.vbs and run from command prompt
http://technet.microsoft.com/en-us/library/bb727091.aspx

Mike Kline

Would you be interested in other methods such as adfind or powershell that can get this for you in one line.

Thanks

Mike

datacomsmt

ASKER

Thanks,

but i need it in CSV format, not txt.

Thanks

Mike Kline

ok, adfind can output in csv...but i'll let the vbscript folks chime in.

datacomsmt

ASKER

will be waiting. Thanks.

pritamdutt

Hi,

I am providing below code in MS Excel format, hope this meets your requirement.
I have used only desired User Attributes for export, you find a list of various User Attributes here http://www.kouti.com/tables/userattributes.htm

SET objRootDSE = GETOBJECT("LDAP://RootDSE") 
strExportFile = "C:\temp\MyExport.xls"  
 
strRoot = objRootDSE.GET("DefaultNamingContext") 
strfilter = "(&(objectCategory=Person)(objectClass=User))" 

'Set attributes to the information you desire to extract
'sAMAccountName - Logon Name (pre-Windows 2000)
'userPrincipalName - Logon Name
'displayName - Display Name
'description - Description
'whenChanged - Last Modification
'department - Department


strAttributes = "sAMAccountName,userPrincipalName," & _ 
                                "displayName, description, whenChanged, ou, " & _ 
                                "department" 
strScope = "subtree" 
SET cn = CREATEOBJECT("ADODB.Connection") 
SET cmd = CREATEOBJECT("ADODB.Command") 
cn.Provider = "ADsDSOObject" 
cn.Open "Active Directory Provider" 
cmd.ActiveConnection = cn 
 
cmd.Properties("Page Size") = 1000 
 
cmd.commandtext = "<LDAP://" & strRoot & ">;" & strFilter & ";" & _ 
                                   strAttributes & ";" & strScope 
 
SET rs = cmd.EXECUTE 
 
SET objExcel = CREATEOBJECT("Excel.Application") 
SET objWB = objExcel.Workbooks.Add 
SET objSheet = objWB.Worksheets(1) 
 
FOR i = 0 To rs.Fields.Count - 1 
                objSheet.Cells(1, i + 1).Value = rs.Fields(i).Name 
                objSheet.Cells(1, i + 1).Font.Bold = TRUE 
NEXT 
 
objSheet.Range("A2").CopyFromRecordset(rs) 
objWB.SaveAs(strExportFile) 
 
 
rs.close 
cn.close 
SET objSheet = NOTHING 
SET objWB =  NOTHING 
objExcel.Quit() 
SET objExcel = NOTHING 
 
Wscript.echo "Script Finished..Please See " & strExportFile

Open in new window

Regards,

LindyS

This is what I use. It outputs a txt file, but it opens with Excel easily.
It only returns connected mailboxes.

Get-ExchangeServer 'ServerPrefix*' |? {$_.serverrole -match "Mailbox"} | Get-MailboxStatistics | Where-Object { $_.DisconnectDate -eq $null } | Format-Table -AutoSize Database, @{label=”User”;expression={$_.DisplayName}}, @{label="Last Logged On By";expression={$_.LastLoggedOnUserAccount}}, @{label="Item Count";expression={$_.ItemCount}}, @{expression={$_.totalitemsize.value.ToMB()};label="Size (MB)"}, @{label=”Storage Limit”;expression={$_.StorageLimitStatus}}, @{label="Last Logon";expression={$_.LastLogonTime}}, @{label="Last Logoff";expression={$_.LastLogoffTime}} | Out-File filename.txt -width 375

LindyS

Sorry...wrong question

pritamdutt

Anyways... Here is script code to generate CSV file...

Dim strExportFile, objOutputFileName
Dim vUserInfo 

SET objRootDSE = GETOBJECT("LDAP://RootDSE") 
strExportFile = "C:\temp\MyExport.csv"  
Set FileSystem = WScript.CreateObject("Scripting.FileSystemObject")
Set objOutputFileName = FileSystem.CreateTextFile(strExportFile, True)

strRoot = objRootDSE.GET("DefaultNamingContext") 
strfilter = "(&(objectCategory=Person)(objectClass=User))" 

'Set attributes to the information you desire to extract
'sAMAccountName - Logon Name (pre-Windows 2000)
'userPrincipalName - Logon Name
'displayName - Display Name
'description - Description
'whenChanged - Last Modification
'department - Department

	
strAttributes = "sAMAccountName,userPrincipalName," & _ 
                                "displayName, whenChanged, department" 
strScope = "subtree" 
SET cn = CREATEOBJECT("ADODB.Connection") 
SET cmd = CREATEOBJECT("ADODB.Command") 
cn.Provider = "ADsDSOObject" 
cn.Open "Active Directory Provider" 
cmd.ActiveConnection = cn 
 
cmd.Properties("Page Size") = 1000 
 
cmd.commandtext = "<LDAP://" & strRoot & ">;" & strFilter & ";" & _ 
                                   strAttributes & ";" & strScope 

SET rs = cmd.EXECUTE 

FOR i = 0 To rs.Fields.Count - 1 
	vUserInfo = vUserInfo & Chr(34) &  rs.Fields(i).Name & Chr(34) & ","
NEXT 

objOutputFileName.Writeline(vUserInfo)
rs.MoveFirst 
Do Until rs.EOF  
	vUserInfo=""


	FOR i = 0 To rs.Fields.Count - 1 
		vUserInfo = vUserInfo & Chr(34) & rs.Fields(i).Value & Chr(34) & ","
	NEXT 	
	objOutputFileName.Writeline(vUserInfo)
	rs.MoveNext
Loop

objOutputFileName.Close  
 
rs.close 
cn.close 

Wscript.echo "Script Finished..Please See " & strExportFile

Open in new window

Regrards,

datacomsmt

ASKER

pritamdutt,

when i modify your script to include lastlogon instead of whenchanged... it does not work... any idea??

pritamdutt

It is because of different data type in play for that field.. Please find updated code for same.

Regards,

Dim strExportFile, objOutputFileName
Dim vUserInfo 

SET objRootDSE = GETOBJECT("LDAP://RootDSE") 
strExportFile = "C:\temp\MyExport.csv"  
Set FileSystem = WScript.CreateObject("Scripting.FileSystemObject")
Set objOutputFileName = FileSystem.CreateTextFile(strExportFile, True)

strRoot = objRootDSE.GET("DefaultNamingContext") 
strfilter = "(&(objectCategory=Person)(objectClass=User))" 

'Set attributes to the information you desire to extract
'sAMAccountName - Logon Name (pre-Windows 2000)
'userPrincipalName - Logon Name
'displayName - Display Name
'description - Description
'whenChanged - Last Modification
'department - Department

	
strAttributes = "sAMAccountName,userPrincipalName," & _ 
                                "displayName, whenChanged, department,lastLogon" 
strScope = "subtree" 
SET cn = CREATEOBJECT("ADODB.Connection") 
SET cmd = CREATEOBJECT("ADODB.Command") 
cn.Provider = "ADsDSOObject" 
cn.Open "Active Directory Provider" 
cmd.ActiveConnection = cn 
 
cmd.Properties("Page Size") = 1000 
 
cmd.commandtext = "<LDAP://" & strRoot & ">;" & strFilter & ";" & _ 
                                   strAttributes & ";" & strScope 

SET rs = cmd.EXECUTE 

FOR i = 0 To rs.Fields.Count - 1 

	vUserInfo = vUserInfo & Chr(34) &  rs.Fields(i).Name & Chr(34) & ","
NEXT 

objOutputFileName.Writeline(vUserInfo)
rs.MoveFirst 
Do Until rs.EOF  
	vUserInfo= _
		Chr(34) & rs.Fields("sAMAccountName").value & Chr(34) & "," & _
		Chr(34) & rs.Fields("userPrincipalName").value & Chr(34) & "," & _
		Chr(34) & rs.Fields("displayName").value & Chr(34) & "," & _
		Chr(34) & rs.Fields("whenChanged").value & Chr(34) & "," & _
		Chr(34) & rs.Fields("department").value & Chr(34) & "," 
		
		On Error Resume Next
		Set objDate = rs.Fields("lastLogon").Value
		If (Err.Number <> 0) Then
			On Error GoTo 0
			dtmDate = #1/1/1601#
		Else
			On Error GoTo 0
			lngHigh = objDate.HighPart
			lngLow = objDate.LowPart
			If (lngLow < 0) Then
				lngHigh = lngHigh + 1
			End If
			If (lngHigh = 0) And (lngLow = 0) Then
				dtmDate = #1/1/1601#
			Else
				dtmDate = #1/1/1601# + (((lngHigh * (2 ^ 32)) _
					+ lngLow)/600000000 - lngBias)/1440
			End If
		End If

'		wscript.echo FormatDateTime(dtmDate)
		If dtmDate <> #1/1/1601# Then
			vUserInfo = vUserInfo &	Chr(34) & dtmDate & Chr(34) 
		Else
			vUserInfo = vUserInfo &	Chr(34) & "" & Chr(34) 
		End If

wscript.echo vUserInfo

'	FOR i = 0 To rs.Fields.Count - 1
'	wscript.echo rs.Fields(i).Name '& ":" & FormatDateTime(rs.Fields("lastLogon").Value)
'		vUserInfo = vUserInfo & Chr(34) & rs.Fields(i).Value & Chr(34) & ","
'	NEXT 	
	

	objOutputFileName.Writeline(vUserInfo)
	rs.MoveNext
Loop

objOutputFileName.Close  
 
rs.close 
cn.close 

Wscript.echo "Script Finished..Please See " & strExportFile

Open in new window

ASKER CERTIFIED SOLUTION

pritamdutt

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

datacomsmt

ASKER

pritamdutt, that was great thanks mate..

there is one more thing im trying to get and that is the OU for each user. appreciate your help.

datacomsmt

ASKER

partial solution