• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 739
  • 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
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 
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
 
QlemoC++ DeveloperCommented:
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 CoachmanCommented:
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 CoachmanCommented:
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

Featured Post

Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

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