Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

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

Posted on 2011-10-18
14
Medium Priority
?
727 Views
Last Modified: 2012-08-13
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.
0
Comment
Question by:datacomsmt
  • 5
  • 4
  • 2
  • +2
14 Comments
 
LVL 8

Expert Comment

by:vinsvin
ID: 36990776
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
0
 
LVL 57

Expert Comment

by:Mike Kline
ID: 36990783
Would you be interested in other methods such as adfind or powershell that can get this for you in one line.  

Thanks

Mike
0
 

Author Comment

by:datacomsmt
ID: 36990795
Thanks,

but i need it in CSV format, not txt.

Thanks
0
Problems using Powershell and Active Directory?

Managing Active Directory does not always have to be complicated.  If you are spending more time trying instead of doing, then it's time to look at something else. For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why

 
LVL 57

Expert Comment

by:Mike Kline
ID: 36990811
ok,  adfind can output in csv...but i'll let the vbscript folks chime in.
0
 

Author Comment

by:datacomsmt
ID: 36990922
will be waiting. Thanks.
0
 
LVL 9

Expert Comment

by:pritamdutt
ID: 36991007
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,
0
 
LVL 3

Expert Comment

by:LindyS
ID: 36991062

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
0
 
LVL 3

Expert Comment

by:LindyS
ID: 36991080
Sorry...wrong question
0
 
LVL 9

Expert Comment

by:pritamdutt
ID: 36991121
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,
0
 

Author Comment

by:datacomsmt
ID: 36991387
pritamdutt,

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

Expert Comment

by:pritamdutt
ID: 36991684
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

0
 
LVL 9

Accepted Solution

by:
pritamdutt earned 1500 total points
ID: 36991691
You can actually remove Line 79 - 85 they were there only for some debugging...

Regards,
0
 

Author Comment

by:datacomsmt
ID: 37029204
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.
0
 

Author Closing Comment

by:datacomsmt
ID: 37062385
partial solution
0

Featured Post

Simplify Active Directory Administration

Administration of Active Directory does not have to be hard.  Too often what should be a simple task is made more difficult than it needs to be.The solution?  Hyena from SystemTools Software.  With ease-of-use as well as powerful importing and bulk updating capabilities.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Compliance and data security require steps be taken to prevent unauthorized users from copying data.  Here's one method to prevent data theft via USB drives (and writable optical media).
The article explains the process to deploy a Self-Service password reset portal I developed a few years ago. Hopefully, it will prove useful to someone.  Any comments, bug reports etc. are welcome...
This tutorial will show how to configure a new Backup Exec 2012 server and move an existing database to that server with the use of the BEUtility. Install Backup Exec 2012 on the new server and apply all of the latest hotfixes and service packs. The…
This tutorial will walk an individual through setting the global and backup job media overwrite and protection periods in Backup Exec 2012. Log onto the Backup Exec Central Administration Server. Examine the services. If all or most of them are stop…

571 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question