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 07 August 2018 08:35 PM

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

I've also provided a sample query at the end that will provide a count of Events by Hour which has been 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 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 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 results
SELECT * FROM Parsed_XML ORDER BY EventTimestamp DESC

--Sample Query
--Once the above tables are built, this 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 analysis is complete, remember to clean up after yourself and drop the unnecessary tables.
/*
DROP TABLE XMLwithOpenXML
DROP TABLE Parsed_XML
*/

 

 

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

Help Desk Software by Kayako case