Frequently Asked Questions
Frequently Asked Questions
HOW TO IMPORT EVENT LOG .XML FILES EXPORTED FROM MFADMIN INTO SQL
Posted by Josh Repp (M-Files) on 30 August 2019 05:43 PM

Overview

Here is some code that can be used to import an Event_Log.xml file that exported from M-Files Admin into SQL for a more in depth analysis.

There is also a sample query at the end that will provide a count of Events by Hour which is useful for finding times of high vault activity.

 

 --This section imports the raw XML data into a temporary table named XMLwithOpenXML
CREATE TABLE XMLwithOpenXML(
   Id INT IDENTITY PRIMARY KEY,
   XMLData XML,
   LoadedDateTime DATETIME
)

INSERT INTO XMLwithOpenXML(XMLData, LoadedDateTime)

SELECT CONVERT(XML, BulkColumn) AS BulkColumn, GETDATE()
FROM OPENROWSET(BULK 'D:\OpenXMLTesting.xml', SINGLE_BLOB) AS x;
--DECLARE FILENAME HERE^^^^^

--This section creates the Parsed_XML table that data will be retrieved from later
DECLARE @XML XML = (select XMLData from XMLwithOpenXML)
CREATE TABLE Parsed_XML(
   EventID varchar(200),
   EventType varchar(200),
   EventCategory varchar(200),
   EventTimestamp datetime,
   EventCausedBy varchar(200),
   "EventData" varchar(max)
)

--This section parses the raw data from the XMLwithOpenXML table and inserts it into the PARSED_XML as columns and rows
INSERT INTO Parsed_XML
SELECT
   EventID = Events.value('(id)[1]', 'varchar(200)'),
   EventType = Events.value('(type)[1]','varchar(200)'),
   EventCategory = Events.value('(category)[1]','varchar(200)'),
   EventTimestamp = Events.value('(timestamp)[1]','datetime'),
   EventCausedBy = Events.value('(causedbyuser)[1]','varchar(200)'),
   "EventData" = Events.value('(data)[1]','varchar(max)')
FROM @XML.nodes('/root/event') AS Xtbl(Events)

--See the full contents of the table imported from the .xml file
SELECT * FROM Parsed_XML ORDER BY EventTimestamp DESC

--Sample Query
--Once the Parsed_XML table is built, this query will return the number of events by hour. Useful for finding high activity periods.

SELECT
   CAST(EventTimeStamp as date) AS ForDate,
   DATEPART(hour,EventTimeStamp) AS OnHour,
   COUNT(*) AS Totals
FROM PARSED_XML
GROUP BY
   CAST(EventTimeStamp as date),
   DATEPART(hour,EventTimeStamp)
ORDER BY ForDate, OnHour

--Once you are done, remember clean up the imported data and drop the unnecessary tables.
/*
DROP TABLE XMLwithOpenXML
DROP TABLE Parsed_XML
*/

(0 vote(s))
This article was helpful
This article was not helpful

Comments (0)
Post a new comment
 
 
Full Name:
Email:
Comments:
CAPTCHA Verification 
 
Please enter the text you see in the image into the textbox below. This is required to prevent automated registrations and form submissions.

Help Desk Software by Kayako case