Export and use M-Files event log

Exporting and analysing the M-Files event log

Export the event log

EXEC dbo.spMFGetMfilesLog @IsClearMfilesLog = 0 -- bit  select 1 to delete the log in M-Files
                             ,@Debug = 0;

Review the result

-- record of each export in XML format
SELECT *
FROM dbo.MFEventLog_OpenXML;

-- listing of each exported event
SELECT *
FROM dbo.MFilesEvents AS mfe;

SELECT MAX(id) FROM dbo.MFilesEvents AS mfe

Analyse the events using XML queries

show object that is not system objects

SELECT me.ID
      ,me.Category
      ,me.CausedByUser
      ,me.TimeStamp
      ,me.Events.value('(/event/data/objectversion/title)1', 'varchar(100)')              AS NameOrTitle
      ,me.Events.value('(/event/data/objectversion/objver/objtype/@id)1', 'varchar(100)') AS ObjectType_ID
      ,me.Events.value('(/event/data/objectversion/objver/objtype)1', 'varchar(100)')     AS ObjectType
      ,me.Events.value('(/event/data/objectversion/objver/objid)1', 'varchar(100)')       AS Objid
FROM dbo.MFilesEvents me
WHERE me.Category <> 'System';

how files downloaded

SELECT me.ID
      ,me.Category
      ,me.CausedByUser
      ,me.TimeStamp
      ,me.Events.value('(/event/data/objectversion/title)1', 'varchar(100)')              AS NameOrTitle
      ,me.Events.value('(/event/data/filename)1', 'varchar(100)')                         AS FileName
      ,me.Events.value('(/event/data/objectversion/objver/objtype/@id)1', 'varchar(100)') AS ObjectType_ID
      ,me.Events.value('(/event/data/objectversion/objver/objtype)1', 'varchar(100)')     AS ObjectType
      ,me.Events.value('(/event/data/objectversion/objver/objid)1', 'varchar(100)')       AS Objid
FROM dbo.MFilesEvents me
WHERE me.Category = 'FileAccess';

how public files downloaded

SELECT me.ID
      ,me.TimeStamp   AS Timestamp
      ,me.Events.value('(/event/data/objectversion/title)1', 'varchar(100)')          AS NameOrTitle
      ,me.Events.value('(/event/data/filename)1', 'varchar(100)')                     AS FileName
      ,me.Events.value('(/event/data/ipaddress)1', 'varchar(100)')                    AS IPAddress
      ,me.Events.value('(/event/data/objectversion/objver/objtype)1', 'varchar(100)') AS ObjectType
      ,me.Events.value('(/event/data/objectversion/objver/objid)1', 'varchar(100)')   AS Objid
      ,me.Events.value('(/event/data/objectversion/objver/version)1', 'varchar(100)') AS Version
      ,me.Events
FROM dbo.MFilesEvents me
WHERE me.Type = 'File downloaded via public link';

Performance of a process

WITH cte
AS (SELECT me.ID
          ,me.TimeStamp
          ,me.Events.value('(/event/data/objectversion/title)1', 'varchar(100)')          AS NameOrTitle
          ,me.Events.value('(/event/data/filename)1', 'varchar(100)')                     AS FileName
          ,me.Events.value('(/event/data/ipaddress)1', 'varchar(100)')                    AS IPAddress
          ,me.Events.value('(/event/data/objectversion/objver/objtype)1', 'varchar(100)') AS ObjectType
          ,me.Events.value('(/event/data/objectversion/objver/objid)1', 'varchar(100)')   AS Objid
          ,me.Events.value('(/event/data/objectversion/objver/version)1', 'varchar(100)') AS Version
          ,me.Events
    FROM dbo.MFilesEvents me
    WHERE me.Category = 'NewObject')
    ,CTE2
AS (SELECT cte.Objid
          ,cte.NameOrTitle
          ,LEAD(cte.TimeStamp) OVER (ORDER BY cte.ID) ProcessEnd
          ,LAG(cte.TimeStamp) OVER (ORDER BY cte.ID)  ProcessStart
    FROM cte)
SELECT CTE2.Objid
      ,CTE2.NameOrTitle
      ,CTE2.ProcessStart
      ,CTE2.ProcessEnd
      ,DATEDIFF(MILLISECOND,CONVERT(DATETIME, SUBSTRING(CTE2.ProcessStart,1,22)),CONVERT(DATETIME, SUBSTRING(CTE2.ProcessEnd,1,22))) AS Diff
FROM CTE2;
GO

show number of object created in a particular timeframe

WITH cte
AS (
SELECT me.ID
          ,CONVERT(DATETIME, SUBSTRING(me.TimeStamp,1,22)) AS EventDate
          ,me.Events.value('(/event/data/objectversion/title)1', 'varchar(100)')          AS NameOrTitle
          ,me.Events.value('(/event/data/objectversion/objver/objtype)1', 'varchar(100)') AS ObjectType
          ,me.Events.value('(/event/data/objectversion/objver/objid)1', 'varchar(100)')   AS Objid
          ,me.Events.value('(/event/data/objectversion/objver/version)1', 'varchar(100)') AS Version
    FROM dbo.MFilesEvents me
    WHERE me.Category = 'NewObject'
    )
   SELECT ObjectType, MIN(EventDate) AS fromDate ,MAX(Eventdate) AS ToDate, COUNT(*) AS RecCount FROM cte
-- WHERE EventDate BETWEEN
   GROUP BY ObjectType