Prev
Next
Forums Human Capital Management Infor Global HR (GHR)

TM reporting: audit log XML parsing

 3 Replies
 0 Subscribed to this topic
 45 Subscribed to this forum
Sort:
Author
Messages
bearcat32
Basic Member
Posts: 12
Basic Member
10/1/2014 12:07 AM
    Has anyone tackled parsing the TM audit logs and have sample SQL code to share or suggestion to tackle? If you don't use the audit log in reporting do you make use of the business class export tables for transactional history?

    Thanks.
    Woozy
    Veteran Member
    Posts: 709
    Veteran Member
    10/1/2014 6:11 PM
      Hi bearcat32,

      Ahh yes...the dreaded audit log. The bane of a our existance.

      At our company, we do parse the audit log - but it is a nightmare. Our TM system is on DB2, but we weren't able to do much with DB2 SQL directly. We actually ended up using MS SSIS packages and SQL Stored Procedures to pull the data and handle the parsing duties. We have a pretty limited set of tables (maybe 15?) and data items that we report on. All the parsed data is pushed into our MSSQLServer data warehouse which is used for historical and trend reporting.

      Unfortunately, I don't have anything to share with you. The process was built by our DBA team, and i really don't have any way to get to it.

      Sorry I can't help much more than this...
      Kelly Meade
      J. R. Simplot Company
      Boise, ID
      Terry Wier
      Basic Member
      Posts: 13
      Basic Member
      10/8/2014 6:15 PM

        Greetings all LTM users,

        Dashboard Gear's HR Reporting Tool Kits:

        As mentioned one of the issues that is particularly difficult in LTM is reporting out of the AUDIT_LOG fields which are chunks of XML. Dashboard Gear has a component in our reporting solution (a reporting and analytic data mart) where we provide easy access to the AUDIT_LOG data thus allowing you to use more traditional tools and report writers like Crystal, Cognos, SSRS, etc. to access that data.

         

        If you would like to speak with one of our developers feel free to let me know.

         

        Terry Wier

        Dashboard Gear

        terry.wier@dashboardgear.com

        Michael
        New Member
        Posts: 1
        New Member
        7/19/2019 8:12 PM

          I know that this is an old post but I had the same issue. After much investigation this is the SQL I use to parse the Employee Audit log in TM. Note that this is MS SQL Server.

          with temp as (
             SELECT
            emp.EMPLOYEE
               ,CAST('' + semp.[AUDITLOG] + '' as XML) as XMLSnip
               FROM
            [PRODTM].prodtm.[EMPLOYEE] emp
            INNER JOIN
            [PRODTM].prodtm.[S$EMPL] semp
            ON emp.UNIQUEID = semp.UNIQUEID
           WHERE  emp.RELATIONSHIPTOORGANIZATION = 'EMPLOYEE'
          -- AND
           --emp.RELATIONSHIPSTATUS IN ('TERMINATED', 'TERM PENDING')
          )
           select
            XMLSnip
           ,c.query('.') as XMLFragment
            ,EMPLOYEE
           ,c.value('(@action)','varchar(255)') as ActionType
           ,c.value('(@r)','varchar(255)') as AUDITACTIONREASON
           ,c.value('(@effective)','varchar(8)') as AUDITEFFECTIVEDATE
           
           ,c.value('(@timestamp)','varchar(16)') as AUDITENTRYSTAMP
           ,c.value('(@actor)','varchar(8)') as AUDITUSERID
           from temp
           CROSS APPLY XMLSnip.nodes('Root/entry') as t(c)
          "CROSS APPLY" causes the Query to loop into each entry using a little "XQuery".

          Hope this helps someone else.