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 Send Private Message
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 Send Private Message
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 Send Private Message
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 Send Private Message
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.