Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

VBS Script to collecting information to CSV

Posted on 2010-11-17
9
Medium Priority
?
835 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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 2000 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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

Well hello again!  Glad to see you've made it this far without giving up.  In this, the fourth installment of my popular series, I'm going to cover functions and subroutines, what they are, and why they are useful.  Just in case you stumbled onto th…
TOMORROW TOMORROW.BAT is inspired by a question I get asked over and over again; that is, "How can I use batch file commands to obtain tomorrow's date?" The crux of this batch file revolves around the XCOPY command - a technique I discovered w…
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …

879 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