Solved

VBS Script to collecting information to CSV

Posted on 2010-11-17
9
800 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
 
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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

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…
This script will sweep a range of IP addresses (class c only, 255.255.255.0) and report to a log the version of office installed. What it does: 1.)      Creates log file in the directory the script is run from (if it doesn't already exist) 2.)      Sweep…
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…
Video by: Mark
This lesson goes over how to construct ordered and unordered lists and how to create hyperlinks.

912 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

20 Experts available now in Live!

Get 1:1 Help Now