Solved

VBS Script to collecting information to CSV

Posted on 2010-11-17
9
824 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
[Live Webinar] The Cloud Skills Gap

As Cloud technologies come of age, business leaders grapple with the impact it has on their team's skills and the gap associated with the use of a cloud platform.

Join experts from 451 Research and Concerto Cloud Services on July 27th where we will examine fact and fiction.

 
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

Technology Partners: 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!

Question has a verified solution.

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

This article is the result of a quest to better understand Task Scheduler 2.0 and all the newer objects available in vbscript in this version over  the limited options we had scripting in Task Scheduler 1.0.  As I started my journey of knowledge I f…
Not long ago I saw a question in the VB Script forum that I thought would not take much time. You can read that question (Question ID  (http://www.experts-exchange.com/Programming/Languages/Visual_Basic/VB_Script/Q_28455246.html)28455246) Here (http…
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.

636 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