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

Collect information from a .txt file or email and insert into a database?

I have some log files that I would like to collect using a access database. The log files are located on a different machine than my workstation and are in xml format inside a .txt file. Below is an example of the XML.

I have no clue how to make an access db that can qurery this and make a report. Someone please help.

Thank you!!!!!!!!!!!!!!!! X 1million
<?xml version="1.0" encoding="UTF-8" ?>
<log uuid="6B72A05E-43CB-4505-9D1C-9216B629A9C3" product="Acronis True Image Echo Enterprise Server" version="1.0" build="174" task="6788B635-BAA8-4D13-9AC2-EC7902BB62BD" >
	<event id="1" level="2" module="100" code="2" time="1227057292" message="The &quot;Current restore operation&quot; operation started" />
	<event id="2" level="3" module="100" code="11" time="1227057292" message="Priority changed to High priority" />
	<event id="3" level="4" module="100" code="5" time="1227057333" message="Operation has completed with errors." />
</log>

Open in new window

0
junglecom
Asked:
junglecom
  • 9
  • 3
  • 2
  • +2
1 Solution
 
ramromconsultant Commented:
There are many ways to interpret your request. Please give an example of a report.
0
 
junglecomAuthor Commented:
 A report that will list the type of event, time and message of the each event in the log. There could be 10 log files in a single folder.


1    Information    11/18/2008 3:14:52 PM    The "Current restore operation" operation started  
2    Warning!    11/18/2008 3:14:52 PM    Priority changed to High priority  
3    Error    11/18/2008 3:15:33 PM    Operation has completed with errors.
0
 
ramromconsultant Commented:
Is there any special reason for using Access?

If all you want to do is read the file and generate the report then we could take other approaches.
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
junglecomAuthor Commented:
Thank you ramrom! That is the second time that has happened to me.

No it doesnt have to be in access, but I like access cause of the nice reports.
0
 
ramromconsultant Commented:
Any other requirements such as a sort order or events to be ignored, or ???

Does each record have the same format? Like this pattern?
        <event id="x" level="y" module="z" code="a" time="b" message="c" />

How does 1227057333 translate to a date-time?

Which field translates to Information etc and what is the coding?

How will the log files be accessed?

How many events could there be?
0
 
junglecomAuthor Commented:
1. Any other requirements such as a sort order or events to be ignored, or ???
I only need to know.a. What time it startedb. what time it finishedc. size of backupd. if it failede. what error message


Does each record have the same format? Like this pattern?
<event id="x" level="y" module="z" code="a" time="b" message="c" />
Yes, i believe they all have that pattern. View attached picture of the logs

How does 1227057333 translate to a date-time?
Have no clue. But i will call acronis to find out

Which field translates to Information etc and what is the coding?
Not sure what you are asking but see the attached log picture please

How will the log files be accessed?
I am guessing through an domain admin account that can pull the logs somehow to a central machine or read the file remotely.

How many events could there be?
More than I know. I would guess 100+

<?xml version="1.0" encoding="UTF-8" ?>
<log uuid="609B0D58-CBE6-45E3-8EEB-3018932349C3" product="Acronis True Image Echo Enterprise Server" version="1.0" build="174" task="1BD44053-CC3E-45C6-A3E7-08FCB58029D0" >
	<event id="1" level="2" module="100" code="2" time="1229313602" message="The &quot;backupjobName&quot; operation started" />
	<event id="2" level="2" module="1" code="503" time="1229313603" message="Analyzing partition 0-0..." />
	<event id="3" level="2" module="1" code="503" time="1229313604" message="Analyzing partition C:..." />
	<event id="4" level="3" module="100" code="11" time="1229313604" message="Priority changed to High priority" />
	<event id="5" level="2" module="1" code="503" time="1229313604" message="Analyzing partition C:..." />
	<event id="6" level="2" module="1" code="1008" time="1229313604" message="&lt;bold>Create Full Backup Archive&lt;/bold>&lt;endl/>&lt;tabpoint value=30>&lt;indent value=4>From:&#9;&lt;indent value=10>&lt;textcolor value=&quot;navyblue&quot;>Disk 1&lt;/textcolor>&lt;/indent>&lt;indent value=4>&lt;endl/>To file:&#9;&lt;indent value=10>&lt;textcolor value=&quot;navyblue&quot;>&quot;\\pathtoserverlocation\backupname.tib&quot;&lt;/textcolor>&lt;/indent>&lt;indent value=4>&lt;endl/>Compression:&#9;&lt;indent value=10>&lt;textcolor value=&quot;navyblue&quot;>Normal&lt;/textcolor>&lt;/indent>&lt;indent value=4>&lt;endl/>&lt;/indent>" />
	<event id="7" level="2" module="1" code="504" time="1229313604" message="Pending operation 135 started: &quot;Creating partition image&quot;" />
	<event id="8" level="2" module="1" code="506" time="1229313608" message="Locking partition C:..." />
	<event id="9" level="2" module="1" code="504" time="1229313960" message="Pending operation 132 started: &quot;Saving partition structure&quot;" />
	<event id="10" level="2" module="100" code="6" time="1229313961" message="Operation has succeeded." />
</log>

Open in new window

0
 
junglecomAuthor Commented:
Attached is the photo and the logs of a failed tasks.

The failed tasks always have the message of  "Operation has completed with errors."

<?xml version="1.0" encoding="UTF-8" ?>
<log uuid="5E9B4F57-B452-49AA-82D7-A7E73FEF8D52" product="Acronis True Image Echo Enterprise Server" version="1.0" build="174" task="C3BCED94-D7CF-41A4-BE4B-091C4AA19B41" >
	<event id="1" level="2" module="100" code="2" time="1229517005" message="The &quot;backupjobName&quot; operation started" />
	<event id="2" level="2" module="1" code="503" time="1229517009" message="Analyzing partition 0-0..." />
	<event id="3" level="2" module="1" code="503" time="1229517010" message="Analyzing partition C:..." />
	<event id="4" level="3" module="100" code="11" time="1229517010" message="Priority changed to Low priority" />
	<event id="5" level="2" module="1" code="503" time="1229517010" message="Analyzing partition C:..." />
	<event id="6" level="2" module="1" code="1008" time="1229517030" message="&lt;bold>Create Differential Backup Archive&lt;/bold>&lt;endl/>&lt;tabpoint value=30>&lt;indent value=4>From:&#9;&lt;indent value=10>&lt;textcolor value=&quot;navyblue&quot;>Disk 1&lt;/textcolor>&lt;/indent>&lt;indent value=4>&lt;endl/>To file:&#9;&lt;indent value=10>&lt;textcolor value=&quot;navyblue&quot;>&quot;\\pathtoserverlocation\backup.tib&quot;&lt;/textcolor>&lt;/indent>&lt;indent value=4>&lt;endl/>Compression:&#9;&lt;indent value=10>&lt;textcolor value=&quot;navyblue&quot;>Normal&lt;/textcolor>&lt;/indent>&lt;indent value=4>&lt;endl/>&lt;/indent>" />
	<event id="7" level="2" module="1" code="504" time="1229517035" message="Pending operation 135 started: &quot;Creating partition image&quot;" />
	<event id="8" level="2" module="1" code="506" time="1229517036" message="Locking partition C:..." />
	<event id="9" level="4" module="7" code="33" time="1229517044" message="&#10;Unable to create volume snapshot: None" />
	<event id="10" level="2" module="7" code="33" time="1229564452" message="&#10;Unable to create volume snapshot: None: User replied: oK" />
	<event id="11" level="4" module="1" code="502" time="1229564452" message="Operation with partition &quot;0-0&quot; was terminated.&#10;Details:&#10;&lt;indent>Unable to create volume snapshot (0x70021)&#10; &#32; &#32;Tag = 0xA5695862AAF8E76F&#10;Failed to set snapshot bitmap. (0x10C44E)&#10; &#32; &#32;blocks = AE9ED75)&#10; &#32; &#32;Tag = 0x14181C22EF45AD9A&#10;Not enough storage is available to process this command (0xFFF0)&#10; &#32; &#32;code = 80070008)&#10; &#32; &#32;Tag = 0xBD28FDBD64EDB8AF&lt;/indent>" />
	<event id="12" level="4" module="100" code="5" time="1229564453" message="Operation has completed with errors." />
</log>

Open in new window

logs.jpg
0
 
junglecomAuthor Commented:
Acronis would not reveal much to me, but time="1229313602" is in seconds, so they say
0
 
junglecomAuthor Commented:
Sorry,

To be more specific the epoch time format
0
 
junglecomAuthor Commented:
Forgot to mention that the log file is not a .txt but a .log file. Not sure if that makes a difference
0
 
junglecomAuthor Commented:
Here is the actual log file

Friday--December-12--2008-10-00-.log
0
 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
One way would be to use MSSQL Server Express (any version). It allows for OLEDB access to XML files, and you get all database features for free. For management, you will need the Express Studio (extra MS download).
However, access to a network drive might be a problem, as there are permission restrictions if you try to automate import (what is feasable with MSSQL).

0
 
jjafferrCommented:
Please have a look at this link, it does a similar task.

http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_20983597.html

jaffer
0
 
Jeffrey CoachmanMIS LiasonCommented:
junglecomDate,

A few other notes:

A) You say you want the report to look like this:
1    Information    11/18/2008 3:14:52 PM    The "Current restore operation" operation started  
2    Warning!    11/18/2008 3:14:52 PM    Priority changed to High priority  
3    Error    11/18/2008 3:15:33 PM    Operation has completed with errors.

Yet none of the log files you have posted contain: "Information", "Warning" or "Error"?

B) This log file does not seem to follow the basic format that an XML file needs to have in order to be inserted into an Access Table.
For example:
<?xml version="1.0" encoding="UTF-8"?>
<dataroot xmlns:od="urn:schemas-microsoft-com:officedata" generated="2008-12-28T20:25:35">
<tblAccessToXML>
<fldText1>rbtr6</fldText1>
<fldNumber1>22</fldNumber1>
<fldText2>v54v45v</fldText2>
<fldNumber2>44</fldNumber2>
</tblAccessToXML>
<tblAccessToXML>
<fldText1>j768</fldText1>
<fldNumber1>68</fldNumber1>
<fldText2>fh834</fldText2>
<fldNumber2>112</fldNumber2>
</tblAccessToXML>
<tblAccessToXML>
<fldText1>b6ir</fldText1>
<fldNumber1>41</fldNumber1>
<fldText2>j900u</fldText2>
<fldNumber2>319</fldNumber2>
</tblAccessToXML>
</dataroot>

I even tried just importing this Log file as simple text, but it always failed.

So it seems to me that you have two options:
1. Have someone write a "Parser" for this file.
2. Contact Acronis and ask if they can provide some sort of interface that will allow you to analyze this data.

FWIW, here is a formula to convert Epoch Time to standard Time:
http://www.blindhog.net/convert-epoch-time-in-excel/

;-)

JeffCoachman
0
 
junglecomAuthor Commented:
It seems as if what I am asking is impossible. Thanks everyone for trying!
0
 
Jeffrey CoachmanMIS LiasonCommented:
You may want to put in a call to Acronis.

As the underdog competitor to Norton Ghost, they sould be more that willing to help you out, if you inform then that you might be thinking of switching to Norton Ghost.
;-)

Jeff
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

  • 9
  • 3
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now