• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 904
  • Last Modified:

VBScript to parse XML log file for email

I have a VBScript that I am using to send a scheduled email through my Exchange server with an attached XML file of my ACT! Scheduler log file.  I would like the VBScript to parse the XML file and include the results of the last backup (and eventually last maintenace) in the body of the email.  I will need the answer to be explained so I can modify it if needed for future use of getting the last 2 events recorded or such.

Thanks!!!
<?xml version="1.0" encoding="utf-16" ?> 
- <ACTSCHEDLOG>
  <EVENT EVENTTIME="2009-02-12 22:46:44Z" EVENTDETAILS="========SCHEDULER SESSION - 2/12/2009 10:46:44 PM ======== Start Date/Time: 2/12/2009 10:45:00 PM Database: BJMclients User Name: btate Machine Name: \\SERVER1-WIN2K3 Type of Event: Backup Task Frequency: Daily, every weekday(M-F) Result: Task Completed Errors: None Backup Filename and Path: C:\Act! DB\Backup\ACT! BJMclients.zip" /> 
  <EVENT EVENTTIME="2009-02-13 22:46:43Z" EVENTDETAILS="========SCHEDULER SESSION - 2/13/2009 10:46:43 PM ======== Start Date/Time: 2/13/2009 10:45:01 PM Database: BJMclients User Name: btate Machine Name: \\SERVER1-WIN2K3 Type of Event: Backup Task Frequency: Daily, every weekday(M-F) Result: Task Completed Errors: None Backup Filename and Path: C:\Act! DB\Backup\ACT! BJMclients.zip" /> 
  </ACTSCHEDLOG>

Open in new window

0
tech1984
Asked:
tech1984
  • 4
  • 4
1 Solution
 
weellioCommented:
what do you want the output to look like?
0
 
tech1984Author Commented:
I would love the body to look like this.

========SCHEDULER SESSION - 2/23/2009 10:46:58 PM ========
Start Date/Time: 2/23/2009 10:45:00 PM
Database: BJMclients
User Name: btate
Machine Name: \\SERVER1-WIN2K3
Type of Event: Backup
Task Frequency: Daily, every weekday(M-F)
Result: Task Completed
Errors: None
Backup Filename and Path: C:\Act! DB\Backup\ACT! BJMclients.zip

Which should just be the last node in the XML.  I will be adding a Database Maintenance schedule soon at which point I would like the last 2 nodes, but I figure the code should be pretty easy to modify at that point.
0
 
weellioCommented:
here is a quick and dirty way

Const ForReading = 1
Const Forwriting = 2
 
'file to read
strreadfile = "c:\temp\my.xml"
 
Set objfso = CreateObject("Scripting.FileSystemObject")
 
Set R_tmp = objFSO.OpenTextFile(strreadfile, ForReading, true)
myval = split(R_tmp.ReadAll, vbcrlf)
R_tmp.close
 
for i = 0 to ubound(myval)
	myline = trim(myval(i))
	'here Is where you Split it up
	'there are many ways to do this, this is a simple method.
	Select Case Left(myline, 5)
	Case "<?xml"
	Case "- <AC"
	Case "<EVEN"
		parse_string(myline)
	Case "</ACT"
	Case Else
	end Select
 
Next
 
Sub parse_string(myline)
	'split the data
x = 1 ' start at the first spot
y = 3 ' remove 4 spots form the End
len_line = Len(myline) - y 'length of the line
 
	x = InStr(1, myline,"Backup Filename and Path:") ' C:\Act! DB\Backup\ACT! BJMclients.zip
results = Mid(myline,x,len_line-x) & VbCrLf & results
len_line = x
	x = InStr(1,myline,"Errors:") ' None	
results = Mid(myline,x,len_line-x) & VbCrLf & results
len_line = x
	x = InStr(1,myline,"Result:") ' Task Completed	
results = Mid(myline,x,len_line-x) & VbCrLf & results
len_line = x
	x = InStr(1,myline,"Task Frequency:") ' Daily, every weekday(M-F)
	results = Mid(myline,x,len_line-x) & VbCrLf & results
len_line = x
	x = InStr(1,myline,"Type of Event:") ' Backup
	results = Mid(myline,x,len_line-x) & VbCrLf & results
len_line = x
	x = InStr(1,myline,"Machine Name:") ' \\SERVER1-WIN2K3
	results = Mid(myline,x,len_line-x) & VbCrLf & results
len_line = x
	x = InStr(1,myline,"User Name:") ' btate
	results = Mid(myline,x,len_line-x) & VbCrLf & results
len_line = x
	x = InStr(1,myline,"Database:") ' BJMclients
	results = Mid(myline,x,len_line-x) & VbCrLf & results
len_line = x
	x = InStr(1,myline,"Start Date/Time:") '
	results = Mid(myline,x,len_line-x) & VbCrLf & results
len_line = x
	x = InStr(1,myline,"EVENTDETAILS=")+14 'add 14 to ignore the title
	results = Mid(myline,x,len_line-x) & VbCrLf & results
len_line = x
 
WScript.echo results 
End sub

Open in new window

0
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.

 
tech1984Author Commented:
weellio:

Thanks for the response.  I have been swamped and haven't had a chance to test the code.  I have done enough VB programming to know that it looks pretty good though.  I was expecting to use DOM, but quick and dirty works just as well.  As soon as I have a minute to test it I will grant the points.
0
 
weellioCommented:
oh you wanted something like this
Set objXMLDoc = CreateObject("Microsoft.XMLDOM") 
objXMLDoc.async = False 
objXMLDoc.load("config2.xml") 
 
Dim objChildNodes, strNode, x
for x = 0 to 1
	Set myitem = objXMLDoc.documentElement.childNodes.item(x).attributes.getNamedItem("EVENTDETAILS") 
	msgbox myitem.value
	parse_string(myitem.value)
Next
 
 
Sub parse_string(myline)
	'split the data
x = 1 ' start at the first spot
y = 3 ' remove 4 spots form the End
len_line = Len(myline) - y 'length of the line
 
	x = InStr(1, myline,"Backup Filename and Path:") ' C:\Act! DB\Backup\ACT! BJMclients.zip
results = Mid(myline,x,len_line-x) & VbCrLf & results
len_line = x
	x = InStr(1,myline,"Errors:") ' None	
results = Mid(myline,x,len_line-x) & VbCrLf & results
len_line = x
	x = InStr(1,myline,"Result:") ' Task Completed	
results = Mid(myline,x,len_line-x) & VbCrLf & results
len_line = x
	x = InStr(1,myline,"Task Frequency:") ' Daily, every weekday(M-F)
	results = Mid(myline,x,len_line-x) & VbCrLf & results
len_line = x
	x = InStr(1,myline,"Type of Event:") ' Backup
	results = Mid(myline,x,len_line-x) & VbCrLf & results
len_line = x
	x = InStr(1,myline,"Machine Name:") ' \\SERVER1-WIN2K3
	results = Mid(myline,x,len_line-x) & VbCrLf & results
len_line = x
	x = InStr(1,myline,"User Name:") ' btate
	results = Mid(myline,x,len_line-x) & VbCrLf & results
len_line = x
	x = InStr(1,myline,"Database:") ' BJMclients
	results = Mid(myline,x,len_line-x) & VbCrLf & results
len_line = x
	x = InStr(1,myline,"Start Date/Time:") '
	results = Mid(myline,x,len_line-x) & VbCrLf & results
len_line = x
	x = InStr(1,myline,"EVENTDETAILS=")+14 'add 14 to ignore the title
	results = Mid(myline,x,len_line-x) & VbCrLf & results
len_line = x
 
WScript.echo results 
End sub

Open in new window

0
 
tech1984Author Commented:
Ok, I finally just made time to test this out.  The first script didn't work.. never got any output.  The second script worked pretty good.  The msbox line gave me the output I wanted except it was for the first date in the XML file instead of the last.

In the for loop you have x = 0 to 1.  Is there a way to find out how many items are in the XML and set x to it so it pulls the last item?  I think that should work.

Thanks for your patience!  I promise to test the solution much faster in the future.
0
 
weellioCommented:
i set it to 100 and if it gets an error (because the node doesn't exist) then it will stop looping and pull the last entry
Set objXMLDoc = CreateObject("Microsoft.XMLDOM") 
objXMLDoc.async = False 
objXMLDoc.load("config2.xml") 
 
Dim objChildNodes, strNode, x
for x = 0 to 100
		on error resume next
		Set myitem = objXMLDoc.documentElement.childNodes.item(x).attributes.getNamedItem("EVENTDETAILS") 
		'msgbox myitem.value
		'parse_string(myitem.value)
		on error goto 0
			if err.number <> 0 then 
			err.clear
			exit for
		end if
Next
 
parse_string(myitem.value)
 
 
Sub parse_string(myline)
dim x, y, len_line, results
		'split the data
	x = 1 ' start at the first spot
	y = 3 ' remove 4 spots form the End
	len_line = Len(myline) - y 'length of the line
	 
		x = InStr(1, myline,"Backup Filename and Path:") ' C:\Act! DB\Backup\ACT! BJMclients.zip
	results = Mid(myline,x,len_line-x) & VbCrLf & results
	len_line = x
		x = InStr(1,myline,"Errors:") ' None	
	results = Mid(myline,x,len_line-x) & VbCrLf & results
	len_line = x
		x = InStr(1,myline,"Result:") ' Task Completed	
	results = Mid(myline,x,len_line-x) & VbCrLf & results
	len_line = x
		x = InStr(1,myline,"Task Frequency:") ' Daily, every weekday(M-F)
		results = Mid(myline,x,len_line-x) & VbCrLf & results
	len_line = x
		x = InStr(1,myline,"Type of Event:") ' Backup
		results = Mid(myline,x,len_line-x) & VbCrLf & results
	len_line = x
		x = InStr(1,myline,"Machine Name:") ' \\SERVER1-WIN2K3
		results = Mid(myline,x,len_line-x) & VbCrLf & results
	len_line = x
		x = InStr(1,myline,"User Name:") ' btate
		results = Mid(myline,x,len_line-x) & VbCrLf & results
	len_line = x
		x = InStr(1,myline,"Database:") ' BJMclients
		results = Mid(myline,x,len_line-x) & VbCrLf & results
	len_line = x
		x = InStr(1,myline,"Start Date/Time:") '
		results = Mid(myline,x,len_line-x) & VbCrLf & results
	len_line = x
		x = InStr(1,myline,"EVENTDETAILS=")+1 'add 1 to ignore the title
		results = Mid(myline,x,len_line-x) & VbCrLf & results
	len_line = x
	 
	WScript.echo results 
End sub

Open in new window

0
 
tech1984Author Commented:
Worked great!!!!  Thanks for the help!
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now