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

LVL 2
junglecomAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

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
Qlemo"Batchelor", 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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.