Leveraging Intelligent Recommendations for Operational Transformation. AMS Partners click HERE | EMEA/APJ Partners click HERE |
07/05/2024 05:36 AM
Hi All,
We are facing an issue. We have created an analytics report for the Audit Trail. We have selected the column 'detail' from the 'userlogin_access' as the 'Message'.
Now the problem is that this detail column has the data in form of a JSON string
Example:
{"data":"customproperty10:, customproperty12:, customproperty47Label:, customproperty11:, customproperty9Label:, remoteHost:103.170.182.129, customproperty18:, customproperty17:, customproperty19:, customproperty16Label:, customproperty14:, customproperty13:, customproperty50Label:, customproperty16:, customproperty15:, allowChangePassword_sqlquery:*****, customproperty48Label:, requestowner:, DISABLENEWACCOUNT:on, customproperty33Label:, customproperty17Label:, customproperty29Label:, customproperty15Label:, customproperty34Label:, customproperty18Label:, customproperty45Label:, savmodule:, customproperty8Label:, customproperty31Label:, customproperty20Label:, ownerkeytemp:, requestownertype:0, customproperty4:, customproperty5:, customproperty2:, securitysystemkey.id:22, customproperty3:, customproperty1:, customproperty44Label:, displayName:Oracle Subscription Management Cloud (OSMC), customproperty19Label:, accountNameRule:, customproperty13Label:, customproperty1Label:, customproperty7Label:, customproperty28Label:, customproperty22Label:, customproperty8:, customproperty9:, customproperty6:, customproperty7:, DISABLEREMOVEACCOUNT:on, customproperty51Label:, customproperty38Label:, customproperty32Label:, customproperty35Label:, Copy Service Account Request Data:on, securitysystemkey:id:22, customproperty54Label:, ownerType:, customproperty39Label:, actionUri:\/endpoints\/save, customproperty55Label:, customproperty10Label:, customproperty3Label:, objectType:ENDPOINT, CONNECTIONCONFIG:, accessquery:, customproperty11Label:, USERLOGINS_KEY:com.saviynt.ecm.utility.UserLogins : 461050, customproperty56Label:, customproperty25Label:, customproperty4Label:, customproperty41Label:, remoteAddress:103.170.182.129, customproperty43:, customproperty42:, eventId:1716202097908, customproperty45:, customproperty12Label:, customproperty26Label:, customproperty44:, customproperty41:, customproperty40:, customproperty23Label:, customproperty37Label:, customproperty42Label:, customproperty5Label:, customproperty2Label:, customproperty32:, customproperty30Label:, customproperty31:, customproperty34:, customproperty6Label:, customproperty33:, customproperty36Label:, customproperty30:, description:, customproperty39:, customproperty36:, customproperty53Label:, customproperty35:, customproperty38:, customproperty37:, customproperty21Label:, customproperty14Label:, customproperty52Label:, requestownertemp:, customproperty21:, customproperty20:, customproperty23:, customproperty22:, customproperty29:, customproperty28:, customproperty40Label:, customproperty25:, ownerkey:, customproperty24:, customproperty27:, customproperty26:, actionType:Create, customproperty24Label:, customproperty27Label:, endpointname:OSMC, customproperty43Label:, customproperty46Label:, customproperty49Label:","objectName":"OSMC","message":"Endpoint OSMC created by user DF6789"}
From the above String we just want the value for the key 'message' as the Message column in our custom Audit Trail report. Is this achievable through SQL queries?. Please advice. This is urgent.
Thanks,
Atul Singh
Solved! Go to Solution.
07/05/2024 06:03 AM
Hi @AS5278 you can use substring_index to retrieve the message value.
07/05/2024 06:24 AM
SELECT ua.typeofaccess AS
'Object Type',
ua.actiontype AS
'Action Taken',
u.username AS
'Accessed By',
ua.ipaddress AS
'IP Address',
ua.accesstime AS
'Event Time',
ua.detail AS
'Message',
Substring_index(Substring_index(ua.detail, 'message":"', -1), '"', 1)AS
'ShortMessage'
FROM users u,
userlogin_access ua,
userlogins ul
WHERE ul.loginkey = ua.loginkey
AND ul.userkey = u.userkey
AND ua.detail IS NOT NULL