topic Link audit log entries to NodeIds in Alfresco Forum

Link audit log entries to NodeIds

Muffex — Wed, 19 Jan 2022 15:57:26 GMT

Hello there,

would it be possible to link/relate audit log entries of the db (alf_audit_entry) with the corresponding node of the "alf_node" table? Is there any information available in the db which could be used?

regards
Muffex

Re: Link audit log entries to NodeIds

kaynezhang — Fri, 21 Jan 2022 00:30:00 GMT

The audit entry is a map with complex key representing the audit data. An example could be like this

/alfresco-api/post/NodeService/createStore/args/protocol = "workspace"
/alfresco-api/post/NodeService/createStore/args/identifier = "SpacesStore"
/alfresco-api/post/NodeService/createStore/result = StoreRef[workspace://SpacesStore]

So,Ithink No directly in db.

Re: Link audit log entries to NodeIds

Muffex — Sun, 23 Jan 2022 16:45:00 GMT

Hey @kaynezhang

since I'm dependent on using the database I tried to find an approach to gather the audit log data from the db.
One approach I found (https://www.slideshare.net/konok/alfresco-devcon-2019-how-to-track-user-activities-without-using-the-audit-functions) was this:

SELECT
entry.id as Id,
entry.audit_time as Time,
user_string.string_value as User,
act_string.string_value as Application,
sv.string_value as Value
FROM alf_audit_entry as entry
JOIN alf_prop_value user_prop ON (entry.audit_user_id = user_prop.id)
JOIN alf_prop_string_value user_string ON ((user_prop.persisted_type = 3 OR user_prop.persisted_type = 5) AND user_prop.long_value = user_string.id)
JOIN alf_audit_app app ON (entry.audit_app_id = app.id)
JOIN alf_prop_value act ON (app.app_name_id = act.id)
JOIN alf_prop_link pl ON (pl.root_prop_id = entry.audit_values_id)
JOIN alf_prop_value pv on (pl.value_prop_id = pv.id)
JOIN alf_prop_string_value act_string ON (act_string.id in (SELECT ALF_PROP_STRING_VALUE.id FROM ALF_PROP_STRING_VALUE WHERE string_value like '%/alfresco-access/%'))
LEFT JOIN alf_prop_string_value sv on (sv.id = pv.long_value and (pv.persisted_type = 3 OR pv.persisted_type = 5))
WHERE sv.string_value IS NOT NULL
ORDER BY entry.audit_time;

However this does not contain the nodeIds or nodeUuids.
Is there any way to also get those?

kind regards
muffex

Re: Link audit log entries to NodeIds

Muffex — Thu, 27 Jan 2022 09:51:58 GMT

Hello @kaynezhang,

do you think it is possible to get the path (e.g.: "app:company_home/st:sites/cm:newsite/cm:discussions/post-1682415105354_6934/") of any given node by id or uuid?

I need to relate audit log entries to nodes.

So id, uuid or noderef would be alright. Then I could resolve my issue.

regards
Muffex