vbscript manipulating text files

[sted]

im trying to read in a text file and alter values based on what it finds. i can get it to work if i insert value by hand but am stumped to get it to do it for me.

the idea is if i find a line that starts with a mac address thatb matches whats currently in the macaddress variable then effectively replace that whole line with new info (or even delete line and add a new one at the bottom the order is irrelevant (at least i see no reason why it wouldnt be the file is a csv so i can sort it in excel later. Possibly related if it cant find the mac address just write it as a new line

 

Const ForReading = 1
Const ForWriting = 2

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFile = objFSO.OpenTextFile("C:\Users\me\Desktop\ICTTECH.csv", ForReading)

strText = objFile.ReadAll
objFile.Close
strText = Replace(strText, from file, some variable
'strText = Replace(strText, "Aron", "Jason")
'strText = Replace(strText, "Sketa", "Skicia")

Set objFile = objFSO.OpenTextFile("C:\Users\me\Desktop\ICTTECH.csv", ForWriting)
objFile.WriteLine strText

objFile.Close

[mac_shinobi]

Can you post back what the text file looks like as far as what is on each line and where the mac address is on each line, also do a dummy example of how you want it to work ie

 

Variable = "AA:BB:CC:XX:YY:ZZ"

 

strLastLine = Code to get last line value of the text file

 

For i = 0 to strLastLine

 

strLine = 'code to read one line at a time

 

If strLine = Variable Then

 

'code to do whatever actions

else

'ignore or any code that you need here

 

End If

 

Next

 

etc etc

 

I basically want to see if we need to do any additional tasks ie using split function etc to get the mac address from each line of the csv or how you want / need this to work etc

@Steve21 is a lot better with scripts etc than myself so will most likely be able to knock something up quicker and better

[sted]

right after some fiddling i altered my code to the below as it seemed simpler. On its own it worked added it to my main script and its not happy. On its own it finds a value and then deletes any line that starts with the first 17 characters specified in my macaddress variable

 

Const ForReading = 1
Const ForWriting = 2

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFile = objFSO.OpenTextFile("c:\Users\me\Desktop\all.csv", ForReading)

Do Until objFile.AtEndOfStream
   strLine = objFile.ReadLine
   If InStr(left(strLine,17), "00:1C:C0:6E:5A:E2") = 0 Then
       strNewContents = strNewContents & strLine & vbCrLf
   End If
Loop

objFile.Close

Set objFile = objFSO.OpenTextFile("c:\Users\me\Desktop\all.csv", ForWriting)
objFile.Write strNewContents

objFile.Close

 

the text file is something like

00:00:00:00:00:00,pcname .................

 

as part of another script it dosent seem to like not having a file to start with hwich i think ive cured but am now getting a blank file and an error of

line 233 char 1 permissino denied code 800a0046

 

full code below granted its messy

Option Explicit
'On Error Resume Next

dim outputstring, computermodel, pcmake, pcmodel, cpu, ram, hddcapacity, hddfree, serialno, macaddress, operatingsystem, csvoutput, csvoutput2
dim stcomputer, colitems, objItem, colcpu, colPhysicalMemory, objPhysicalMemory, intMemory, colDisks, objhdd
dim colComputerSystem, colOperatingSystems, objOperatingSystem, colBIOS, objBIOS, colnicconfig, objnicconfig
dim objwmiservice, hddtemp, weidata, colWSA, operatingsystemno, Lastuser, oreg, spath, svalue, strvalue, svaluename
dim adoutputstring, adoRecordset, adoConnection, objRootDSE, objNetwork, objComputer, existing
dim pcname, wshShell, strDirectory, strFile, objFSO, objFolder, objFile, objTextFile, myDateString
dim colnet,objnet, linkspeed, objWMIService2, strline, strNewContents, strDirectory2, strFile2, objfolder2, objfile2

stcomputer = "."
myDateString = Date()

Set objRootDSE = GetObject("LDAP://RootDSE")
Set objNetwork = WScript.CreateObject("WScript.Network")
Set objWMIService = GetObject("winmgmts:\\" & stcomputer & "\root\CIMV2") 
Set colItems = objWMIService.ExecQuery("SELECT * FROM Win32_ComputerSystem") 
Set colcpu = objWMIService.ExecQuery("Select * from Win32_Processor")
Set colPhysicalMemory = objWMIService.ExecQuery("Select * From Win32_PhysicalMemory")
set coldisks = objWMIService.ExecQuery("Select * From Win32_LogicalDisk") 
Set colComputerSystem = objWMIService.ExecQuery("SELECT * FROM Win32_ComputerSystem")
Set colOperatingSystems = objWMIService.ExecQuery ("Select * from Win32_OperatingSystem")
Set colBIOS = objWMIService.ExecQuery("Select * From Win32_BIOS")
Set colnicconfig = objWMIService.ExecQuery("Select * From Win32_NetworkAdapterConfiguration Where IPEnabled = True")
Set colWSA = objWMIservice.ExecQuery("Select * From Win32_WinSAT")
Set adoConnection = CreateObject("ADODB.Connection")
adoConnection.Provider = "ADsDSOObject"
adoConnection.Open "Active Directory Provider"
Set adoRecordset = adoConnection.Execute(";(&(objectCategory=Computer)(name=" & objNetwork.Computername & "));adspath;subtree")
Set wshShell = WScript.CreateObject( "WScript.Shell" )
Set objWMIService2 = GetObject("winmgmts:\\" & stComputer & "\root\WMI")
Set colnet = objWMIService2.ExecQuery("SELECT * FROM MSNdis_LinkSpeed",,48)

'write file 1 location
pcname = wshShell.ExpandEnvironmentStrings( "%COMPUTERNAME%" )
strDirectory = "\\gateway\logs$\test\"
strFile = pcname & ".csv"

'write file 2 location

strDirectory2 = "\\gateway\logs$\test\"
strFile2 = "all.csv"


'make-model
For Each objItem In colItems 
computermodel= objItem.Manufacturer & " " & objItem.Model
pcmake=objItem.Manufacturer
pcmodel=objItem.Model
'wscript.echo computermodel
Next

' Create the File System Object
Set objFSO = CreateObject("Scripting.FileSystemObject")

'cpu info
For Each objItem in colcpu
   cpu = objItem.Name
'wscript.echo cpu
Next

'ram
If Not colPhysicalMemory Is Nothing Then
 intMemory = 0
 For Each objPhysicalMemory In colPhysicalMemory
	 intMemory = intMemory + Int(objPhysicalMemory.Capacity)
 Next
 ram = (intMemory / 1024 / 1024 ) & " MB"
 'wscript.echo ram
End If

'hdd capacity
for each objhdd in coldisks
If objhdd.DeviceID = "C:" Then
	hddcapacity = int(objhdd.Size/1073741824) & " GB"
	hddfree = int(objhdd.freespace/1073741824) & " GB"
	'wscript.echo hddfree
end if
next

'windows version
For Each objOperatingSystem in colOperatingSystems
   operatingsystem = objOperatingSystem.Caption & " sp " & objOperatingSystem.ServicePackMajorVersion
operatingsystemno = objOperatingSystem.Version
	'wscript.echo operatingsystem
Next

'serial no
If Not colBIOS Is Nothing Then
 For Each objBIOS in colBIOS
	 serialno = objBIOS.SerialNumber
	 'wscript.echo serialno
 Next
End If

'mac address(s)
If Not colnicconfig Is Nothing Then
 For Each objnicconfig in colnicconfig
	 If macaddress <> "" Then
		 macaddress = macaddress & " - "
	 End If
	 macaddress = macaddress & objnicconfig.MACAddress
	 'wscript.echo macaddress
 Next
End If

'windows experience
operatingsystemno = Left( operatingsystemno, 3 )  
if operatingsystemno >5.5 then
'vista+
For Each objItem in colWSA
'weidata = "Overall -" & "na" & " - CPU - " & "na" & " - Memory - " & "na" & " - Graphics - " & "na" & " - Gaming graphics - " & "na" & " - hdd - " & "na" 
	weidata = "Overall -" & objItem.WinSPRLevel & " - CPU - " & objItem.CPUScore & " - Memory - " & objItem.MemoryScore & " - Graphics - " & objItem.GraphicsScore & " - Gaming graphics - " & objItem.D3DScore & " - hdd - " & objItem.DiskScore
next
else
'xp-
weidata = "Overall -" & "na" & " - CPU - " & "na" & " - Memory - " & "na" & " - Graphics - " & "na" & " - Gaming graphics - " & "na" & " - hdd - " & "na" 
end if

'last user
if operatingsystemno >5.5 then  
'vista+
Const HKLM = &H80000002 'HKEY_LOCAL_MACHINE
Set oReg = GetObject("winmgmts:{impersonationLevel=impersonate}!\\" & stcomputer & "/root/default:StdRegProv")
If Err.Number <> 0 Then
	On Error Goto 0
Else
	On Error Goto 0
	sPath = "SOFTWARE\Microsoft\Windows\CurrentVersion\Authentication\LogonUI"
	sValueName = "LastLoggedonUser"
	If oReg.GetStringValue(HKLM, sPath, sValueName, sValue) = 0 Then
		lastuser = sValue
	Else
		lastuser = "unknown" & stcomputer
	End If
End If
else
'xp-
Const HKEY_LOCAL_MACHINE = &H80000002
Set oreg=GetObject("winmgmts:\\" & stComputer & "\root\default:StdRegProv")
spath = "SOFTWARE\Microsoft\Windows NT\CurrentVersion\WinLogon"
svalue = "DefaultUserName"
oreg.GetStringValue HKEY_LOCAL_MACHINE, spath, svalue, strValue
lastuser=strvalue
svalue = "DefaultDomainName"
oreg.GetStringValue HKEY_LOCAL_MACHINE, spath, svalue, strValue
lastuser = strvalue & "\" & lastuser
end if

'Scans data for Net Speed
For Each objnet in colnet

'Removes "slow/unused links"
if objnet.NdisLinkSpeed/10 > 1000 then
linkspeed= objnet.NdisLinkSpeed/10000 & " Mbps" & "-" & linkspeed '& "-" & objnet.NdisLinkSpeed/10000 & " Mbps"
end if
next

'concatanate data for output
outputstring = computermodel & "," & cpu &"," & ram &"," & hddcapacity& ","  & serialno & "," & macaddress & "," 
csvoutput = pcname & "," & computermodel & "," & cpu &"," & ram &"," & hddcapacity& "," & hddfree & "," & serialno & "," & operatingsystem & "," & macaddress & "," & weidata & "," & Lastuser & "," & linkspeed &"," & myDateString &","
csvoutput2 = macaddress & ", "&" ,"& "," & pcname & "," & "," & "," & ram &"," &"," &"," &"," &"," &"," & pcmake & "," & pcmodel & "," & serialno & ","  & "," & "," & "," & "," & "," & "," & hddcapacity & "," & hddfree & "," & "," & "," & "," & "," & "," & "," & "," & "," & "," & "," & "," & cpu & "," & "," & "," & "," & "," & "," & "," & "," & "," & "," & "," & "," & "," & "," & "," & "," & "," & "," & "," & "dell," & myDateString & ","
' wscript.echo outputstring

' Check that the strDirectory folder exists
If objFSO.FolderExists(strDirectory) Then
  Set objFolder = objFSO.GetFolder(strDirectory)
Else
  Set objFolder = objFSO.CreateFolder(strDirectory)
  'WScript.Echo "Just created " & strDirectory
End If

If objFSO.FileExists(strDirectory & strFile) Then
  Set objFolder = objFSO.GetFolder(strDirectory)
Else
  Set objFile = objFSO.CreateTextFile(strDirectory & strFile)
  'Wscript.Echo "Just created " & strDirectory & strFile
End If 

set objFile = nothing
set objFolder = nothing
' OpenTextFile Method needs a Const value
' ForAppending = 8 ForReading = 1, ForWriting = 2
Const ForAppending = 2

Set objTextFile = objFSO.OpenTextFile _
(strDirectory & strFile, ForAppending, True)

' Writes result every time you run this VBScript
objTextFile.WriteLine(csvoutput)
objTextFile.Close

If Err.Number <> 0 Then
 MsgBox "Query Error: " & Err.Description
 WScript.Quit
End If
'********************************************
'2nd file
'********************************************


' Check that the strDirectory folder exists
If objFSO.FolderExists(strDirectory2) Then
  Set objFolder2 = objFSO.GetFolder(strDirectory2)
Else
  Set objFolder2 = objFSO.CreateFolder(strDirectory2)
  WScript.Echo "Just created " & strDirectory
End If

If objFSO.FileExists(strDirectory2 & strFile2) Then
  Set objFolder2 = objFSO.GetFolder(strDirectory2)
Else
  Set objFile2 = objFSO.CreateTextFile(strDirectory2 & strFile2)
  Wscript.Echo "Just created " & strDirectory & strFile
End If 

Const ForReading = 1
Const ForWriting = 2

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFile = objFSO.OpenTextFile("\\gateway\logs$\test\all.csv", ForReading)

Do Until objFile.AtEndOfStream
   strLine = objFile.ReadLine
   If InStr(left(strLine,17), macaddress) = 0 Then
       strNewContents = strNewContents & strLine & vbCrLf
   End If
Loop

objFile.Close

[color="#FF0000"]Set objFile2 = objFSO.OpenTextFile("\\gateway\logs$\test\all.csv", ForWriting)[/color]
objFile.Write strNewContents
objFile.WriteLine(csvoutput2)

objFile.Close

Edited October 24, 2013 by sted

[sted]

sorted it i think it was mainly a case of using the same variables twice without noticing (always a possibility when you test scripts in parts)

[mac_shinobi]

would be interesting to see a sample csv file, just wondering if you could use the split function to get the correct part of the csv file line ( to obtain the mac address ) instead of using instrrev or instr to get the mac address etc for example

 

Line one may contain :

 

Computer Name, CPU, RAM, HDD, Serial Number, Make , Model, MAC Address of NIC

 

You could use a variable and assign the line to said variable ie

 

Dim strTemp, strString

 

strString = ReadLine(1) ' This would contain the first line it has read into strString which would contain something similar to the above as mentioned

 

strTemp = Split(strString, ",") ' This would split the string by using comma's which a CSV is generally comma delimited ( obviously )

 

Then to call the relevant item you would just do something like so

 

msgbox strTemp(5)

 

the number you place in between the brackets determines which item it shows you so if you put zero (0) ie strTemp(0) then that would show you the very first item vs if you put another value in. I think using the split function it assigns each item a value starting from zero , going by what I used as an example if you only used the items I mentioned in the same order then the ID Values would be something like :

 

Computer Name = 0

CPU, RAM = 1

HDD, Serial Number = 2

Make = 3

Model = 4

MAC Address of NIC = 5

 

Might make it a bit easier to seperate each of the values by using the commas instead of trying to use InStr or InStrRev ( In String or In String Reverse Functions )

Edited October 24, 2013 by mac_shinobi

[sted]

inventorylog.txt

 

for what its worth. Ive now got my code working at some point i may rewrite it to make it less "ugly" but the main thing is it works (its just been added to over time so what was done in one go is neat(ish) whats been bolted on later isnt so neat). As the order of items in the csv makes no odds im happy if i find a line with a specific mac address to just delete it and readd at the bottom. it might give me issues if multiple pcs run at the exact same time but on average it will be right enough and it also saves a slightly different set of info to a file just for that pc

 

webinfov4.txt

 

if youre interested