Solved

VBS Script to collecting information to CSV

Posted on 2010-11-17
9
805 Views
Last Modified: 2012-06-27
Hi,

I am trying to work on a script to collect a bit of information and drop it into a CSV file for a migration.

I was wondering if someone can help me with creating one that does that following:


host name, username, ip address, network drives
Input dialouge box "First name"
Input dialouge box "Last Name"
Input dialouge box "Email address"

This data to be saved to CSV files.

I already have the below for the network drives. (saying that network drives are not a piority).


Dim objNetwork, objDrives, intIndex, objFSO, objFile, objUser, strUsername
 
Set objSysInfo = CreateObject("ADSystemInfo")
Set objUser = GetObject("LDAP://" & objSysInfo.UserName)
strUsername = objUser.samAccountName
 
Set objFSO = CreateObject("Scripting.FileSystemobject")
'Change the file path on the next line'
Set objFile = objFSO.CreateTextFile("SERVERPATH\test.csv" & strUsername & ".csv")
 
Set objNetwork = CreateObject("WScript.Network")
Set objDrives = objNetwork.EnumNetworkDrives
For intIndex = 0 To objDrives.Length - 1 Step 2
    objFile.WriteLine  """" & strUsername & """,""" & objDrives(intIndex) & """,""" & objDrives(intIndex + 1) & """"
Next
objFile.Close
Set objFile = Nothing
Set objUser = Nothing
Set objFSO = Nothing
Set objDrives = Nothing
Set objNetwork = Nothing

Open in new window

0
Comment
Question by:Eirejp
  • 5
  • 4
9 Comments
 
LVL 1

Author Comment

by:Eirejp
ID: 34161059
Also the file must append.

Thanks!
0
 
LVL 1

Author Comment

by:Eirejp
ID: 34161332
Just an update to my previous entry.

I just need to add in hostname and ip address now.

Dim kNameFirst
Dim kNameLast
Dim EmailAdd

kNameFirst = InputBox("Please Enter your first name in Kanji? ¿¿¿¿¿¿", "Auditors 1")
kNameLast = InputBox("Please Enter your last name in Kanji? ¿?", "Auditor v1")
EmailAdd = InputBox("Please Enter your email address?", "Auditor v1")


Dim objNetwork, objDrives, intIndex, objFSO, objFile, objUser, strUsername
Set objSysInfo = CreateObject("ADSystemInfo")
Set objUser = GetObject("LDAP://" & objSysInfo.UserName)
strUsername = objUser.samAccountName

Set objFSO = CreateObject("Scripting.FileSystemobject")
'Change the file path on the next line'
Set objFile = objFSO.CreateTextFile("C:\Documents and Settings\cliebich\My Documents\test.csv" & strUsername & ".csv")

Set objNetwork = CreateObject("WScript.Network")
Set objDrives = objNetwork.EnumNetworkDrives
For intIndex = 0 To objDrives.Length - 1 Step 2
    objFile.WriteLine  """" & strUsername & """,""" & kNameFirst & """,""" & kNameLast & """,""" & EmailAdd & """,""" & ObjNetInfo & """,""" & objDrives(intIndex) & """,""" & objDrives(intIndex + 1) & """"
Next
objFile.Close
Set objFile = Nothing
Set objUser = Nothing
Set objFSO = Nothing
Set objDrives = Nothing
Set objNetwork = Nothing
 

Open in new window

0
 
LVL 65

Expert Comment

by:RobSampson
ID: 34161650
Hi there, try this.

Regards,

Rob.
Dim kNameFirst
Dim kNameLast
Dim EmailAdd

kNameFirst = InputBox("Please Enter your first name in Kanji? ¿¿¿¿¿¿", "Auditors 1")
kNameLast = InputBox("Please Enter your last name in Kanji? ¿?", "Auditor v1")
EmailAdd = InputBox("Please Enter your email address?", "Auditor v1")

Dim objNetwork, objDrives, intIndex, objFSO, objFile, objUser, strUsername
Set objSysInfo = CreateObject("ADSystemInfo")
Set objUser = GetObject("LDAP://" & objSysInfo.UserName)
strUsername = objUser.samAccountName

Set objFSO = CreateObject("Scripting.FileSystemobject")
'Change the file path on the next line'
Set objFile = objFSO.CreateTextFile("C:\Documents and Settings\cliebich\My Documents\test.csv" & strUsername & ".csv")

Set objWMIService = GetObject("winmgmts:{impersonationLevel=impersonate}!\\.\root\cimv2") 

Set colComputer = objWMIService.ExecQuery("Select * from Win32_ComputerSystem")
Set colComputerIP = objWMIService.ExecQuery("Select Name from Win32_NetworkAdapterConfiguration")
For Each objComputer In colComputer
    strHostName = objComputer.Name
Next
For Each IPConfig In colComputerIP
	If Not IsNull(IPConfig.IPAddress) Then 
        For intIPCount = LBound(IPConfig.IPAddress) To UBound(IPConfig.IPAddress)
			If strIPAddress = "" Then
				strIPAddress = IPConfig.IPAddress(intIPCount)
			Else
				strIPAddress = strIPAddress & " | " & IPConfig.IPAddress(intIPCount)
			End If
		Next
	End If
Next

Set objNetwork = CreateObject("WScript.Network")
Set objDrives = objNetwork.EnumNetworkDrives
For intIndex = 0 To objDrives.Length - 1 Step 2
    'objFile.WriteLine  """" & strUsername & """,""" & kNameFirst & """,""" & kNameLast & """,""" & EmailAdd & """,""" & ObjNetInfo & """,""" & objDrives(intIndex) & """,""" & objDrives(intIndex + 1) & """"
    objFile.WriteLine  """" & strUsername & """,""" & kNameFirst & """,""" & kNameLast & """,""" & EmailAdd & """,""" & strHostName & """,""" & strIPAddress & """,""" & ObjNetInfo & """,""" & objDrives(intIndex) & """,""" & objDrives(intIndex + 1) & """"
Next
objFile.Close
Set objFile = Nothing
Set objUser = Nothing
Set objFSO = Nothing
Set objDrives = Nothing
Set objNetwork = Nothing

Open in new window

0
Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

 
LVL 1

Author Comment

by:Eirejp
ID: 34161693
Hi Rob,

Seems to error on line 25

For Each IPConfig In colComputerIP

Open in new window

0
 
LVL 65

Expert Comment

by:RobSampson
ID: 34161701
Oops, change these lines:

Set colComputer = objWMIService.ExecQuery("Select * from Win32_ComputerSystem")
Set colComputerIP = objWMIService.ExecQuery("Select Name from Win32_NetworkAdapterConfiguration")

to this

Set colComputer = objWMIService.ExecQuery("Select Name from Win32_ComputerSystem")
Set colComputerIP = objWMIService.ExecQuery("Select IPAddress from Win32_NetworkAdapterConfiguration")


Regards,

Rob.
0
 
LVL 1

Author Comment

by:Eirejp
ID: 34161769
thanks this works perfectly!

The only issue now is the csv is been overwritten.

How can I change this to append?

Dim kNameFirst
Dim kNameLast
Dim EmailAdd

kNameFirst = InputBox("Please Enter your first name in Kanji? ??????", "Auditors 1")
kNameLast = InputBox("Please Enter your last name in Kanji? ??", "Auditor v1")
EmailAdd = InputBox("Please Enter your email address?", "Auditor v1")

Dim objNetwork, objDrives, intIndex, objFSO, objFile, objUser, strUsername
Set objSysInfo = CreateObject("ADSystemInfo")
Set objUser = GetObject("LDAP://" & objSysInfo.UserName)
strUsername = objUser.samAccountName

Set objFSO = CreateObject("Scripting.FileSystemobject")
'Change the file path on the next line'
Set objFile = objFSO.CreateTextFile("PATH\results.csv")

Set objWMIService = GetObject("winmgmts:{impersonationLevel=impersonate}!\\.\root\cimv2") 

Set colComputer = objWMIService.ExecQuery("Select Name from Win32_ComputerSystem")
Set colComputerIP = objWMIService.ExecQuery("Select IPAddress from Win32_NetworkAdapterConfiguration")


For Each objComputer In colComputer
    strHostName = objComputer.Name
Next
For Each IPConfig In colComputerIP
	If Not IsNull(IPConfig.IPAddress) Then 
        For intIPCount = LBound(IPConfig.IPAddress) To UBound(IPConfig.IPAddress)
			If strIPAddress = "" Then
				strIPAddress = IPConfig.IPAddress(intIPCount)
			Else
				strIPAddress = strIPAddress & " | " & IPConfig.IPAddress(intIPCount)
			End If
		Next
	End If
Next

Set objNetwork = CreateObject("WScript.Network")
Set objDrives = objNetwork.EnumNetworkDrives
For intIndex = 0 To objDrives.Length - 1 Step 2
        objFile.WriteLine  """" & strUsername & """,""" & kNameFirst & """,""" & kNameLast & """,""" & EmailAdd & """,""" & strHostName & """,""" & strIPAddress & """,""" & objDrives(intIndex) & """,""" & objDrives(intIndex + 1) & """"
Next
objFile.Close
Set objFile = Nothing
Set objUser = Nothing
Set objFSO = Nothing
Set objDrives = Nothing
Set objNetwork = Nothing

Open in new window

0
 
LVL 65

Accepted Solution

by:
RobSampson earned 500 total points
ID: 34162284
All you need to do is change this line:
Set objFile = objFSO.CreateTextFile("PATH\results.csv")

to this
Set objFile = objFSO.OpenTextFile("PATH\results.csv", 8, True)


Regards,

Rob.
0
 
LVL 1

Author Comment

by:Eirejp
ID: 34170504
Works like a charm!

Cheers Rob your a legend
0
 
LVL 65

Expert Comment

by:RobSampson
ID: 34170508
No problem.  Thanks for the grade.

Regards,

Rob.
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Vbscript 8 88
Need to Modify a Script I found 5 98
Delete out x file types from multiple network directories using batch script 8 74
WMIC Command LIne 5 25
Welcome, welcome!  If you are new to the series and haven't been following along, please take a brief moment to review the first three installments: Part 1 (http://www.experts-exchange.com/Programming/Languages/Visual_Basic/VB_Script/A_266-VBScri…
VALIDATING DATES One method of validating dates is to jam the date into the DATE command and see if it accepts it by examining the system's errorlevel value. A non-zero result indicates failure. A typical example might look something like the fol…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, just open a new email message. In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…

815 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now