<StoreRef> getStores();
@Auditable(key = Auditable.Key.ARG_0 ,parameters = {'nodeRef'})
public boolean exists(NodeRef nodeRef);
@Auditable(key = Auditable.Key.ARG_0 ,parameters = {'parentRef', 'assocTypeQName', 'assocQName', 'nodeTypeQName'})
public ChildAssociationRef createNode(
NodeRef parentRef,
QName assocTypeQName,
QName assocQName,
QName nodeTypeQName)
throws InvalidNodeRefException, InvalidTypeException;
@NotAuditable
public Collection<QName> getAllModels();
Public services should be identified using a marker annotation
@PublicService
Simple audit template example
Basic audit information can be shown by applying the example audit template show_audit.tfl to spaces, folders, files etc.
MYSQL samples
These queries will not run on all database and may require modifcation for Oracle, etc etc
Changes to the schema between versions may mean the examples require modification for later versions of Alfresco.
An example for each schema is included for linking to properties.
MYSQL audit queries 1.4.x to 3.3.x
Note: This audit mechanism is not available from 3.4.0.
To pull back everything:
select * from alf_audit_fact fact
join alf_audit_date date on fact.audit_date_id = date.id
join alf_audit_source source on fact.audit_source_id = source.id
To find out what methods and services have been audited so far:
select * from alf_audit_source
To link person information (eg email, forename, surname. etc): add something like
select fact.*,
date.*,
source.*,
convert(p2.string_value using utf8) as Name,
convert(p3.string_value using utf8) as Surname,
convert(p4.string_value using utf8) as EMail
from alf_audit_fact fact
join alf_audit_date date on fact.audit_date_id = date.id
join alf_audit_source source on fact.audit_source_id = source.id
left outer join alf_node_properties p1
on fact.user_id = p1.string_value
and p1.qname = '{http://www.alfresco.org/model/content/1.0}userName'
left outer join alf_node_properties p2
on p1.node_id = p2.node_id
and p2.qname = '{http://www.alfresco.org/model/content/1.0}firstName'
left outer join alf_node_properties p3
on p1.node_id = p3.node_id
and p3.qname = '{http://www.alfresco.org/model/content/1.0}lastName'
left outer join alf_node_properties p4
on p1.node_id = p4.node_id
and p4.qname = '{http://www.alfresco.org/model/content/1.0}email'
To find people who have never logged in or caused anything to be audited:
select convert(prop.string_value using utf8)
from alf_audit_fact fact
right outer join alf_node_properties prop on fact.user_id = prop.string_value
where prop.qname = '{http://www.alfresco.org/model/content/1.0}userName'
and fact.user_id is null
To find the fist audit entry for all users:
select fact.user_id, min(timestamp)
from alf_audit_fact fact group by fact.user_id
To find the first login entry for all users:
select fact.user_id, min(timestamp)
from alf_audit_fact fact
join alf_audit_source source
on fact.audit_source_id = source.id
and source.service = 'AuthenticationService'
and source.method = 'authenticate'
group by fact.user_id
To get roll ups of login information for all time:
select fact.user_id, count(fact.id)
from alf_audit_fact fact
join alf_audit_source source
on fact.audit_source_id = source.id
and source.service = 'AuthenticationService'
and source.method = 'authenticate'
group by fact.user_id
To get the number of logins broken down by year:
select fact.user_id, date.year, count(fact.id)
from alf_audit_fact fact
join alf_audit_date date on fact.audit_date_id = date.id
join alf_audit_source source
on fact.audit_source_id = source.id
and source.service = 'AuthenticationService'
and source.method = 'authenticate'
group by fact.user_id, date.year
To get the number of logins broken down by year and quarter:
select fact.user_id, date.year, date.quarter+1, count(fact.id)
from alf_audit_fact fact
join alf_audit_date date on fact.audit_date_id = date.id
join alf_audit_source source
on fact.audit_source_id = source.id
and source.service = 'AuthenticationService'
and source.method = 'authenticate'
group by fact.user_id, date.year, date.quarter+1
To get the number of logins broken down by year and month:
select fact.user_id, date.year, date.month+1, count(fact.id)
from alf_audit_fact fact
join alf_audit_date date on fact.audit_date_id = date.id
join alf_audit_source source
on fact.audit_source_id = source.id
and source.service = 'AuthenticationService'
and source.method = 'authenticate'
group by fact.user_id, date.year, date.month+1
To get the number of logins broken down by year, month, and day of month
select fact.user_id, date.year, date.month+1, date.day_of_month, count(fact.id)
from alf_audit_fact fact
join alf_audit_date date on fact.audit_date_id = date.id
join alf_audit_source source
on fact.audit_source_id = source.id
and source.service = 'AuthenticationService'
and source.method = 'authenticate'
group by fact.user_id, date.year, date.month+1, date.day_of_month
To get the login history for a given user:
select fact.timestamp
from alf_audit_fact fact
join alf_audit_source source
on fact.audit_source_id = source.id
and source.service = 'AuthenticationService'
and source.method = 'authenticate'
where fact.user_id = 'admin'
To find all content 'viewed' by a given user in the last 7 days
select fact.timestamp, convert(prop.string_value using utf8)
from alf_audit_fact fact
join alf_audit_source source
on fact.audit_source_id = source.id
and (
(source.service = 'ContentService' and source.method = 'getReader')
or (source.service = 'FileFolderService' and source.method = 'getReader')
)
join alf_node_status ns on ns.guid = fact.node_uuid
join alf_node_properties prop
on ns.node_id = prop.node_id
and prop.qname = '{http://www.alfresco.org/model/content/1.0}name'
where
fact.user_id = 'admin'
and fact.timestamp > ADDDATE(now(), -7)
To find all items created via the node server or file folder service by a given user in the last 7 days (not rules, people, ....):
select fact.timestamp, convert(prop.string_value using utf8)
from alf_audit_fact fact
join alf_audit_source source
on fact.audit_source_id = source.id
and (
(source.service = 'NodeService' and source.method = 'createNode')
or (source.service = 'FileFolderService' and source.method = 'create')
)
join alf_node_status ns on ns.guid = fact.node_uuid
join alf_node_properties prop
on ns.node_id = prop.node_id
and prop.qname = '{http://www.alfresco.org/model/content/1.0}name'
where
fact.user_id = 'andy'
and fact.timestamp > ADDDATE(now(), -7)
If you can create functions on your mysql database you could create a function to build the UI displayed path - the path made up of each item's name, not its full path in the repository.
DELIMITER $$
DROP FUNCTION IF EXISTS `alfresco`.`getPath` $$
CREATE FUNCTION `alfresco`.`getPath` (id BIGINT(20)) RETURNS VARCHAR(2048)
BEGIN
DECLARE name VARCHAR(128);
DECLARE answer VARCHAR(2048);
DECLARE current BIGINT(20);
DECLARE previous BIGINT(20);
SET current = id;
SET previous = 0;
REPEAT
SELECT convert(prop.string_value using utf8) into name from alf_node_properties prop
where prop.node_id = current
and prop.qname = '{http://www.alfresco.org/model/content/1.0}name';
SET previous = current;
SELECT parent_node_id INTO current from alf_child_assoc
where child_node_id = current and is_primary > 0;
IF previous = id
THEN
SET answer = name;
ELSEIF current
If this function is defined, instead of displaying just the name of the docs found you can display the path. This call will not be cheap.
select fact.timestamp, convert(prop.string_value using utf8), getPath(ns.node_id)
from alf_audit_fact fact
join alf_audit_source source
on fact.audit_source_id = source.id
and (
(source.service = 'ContentService' and source.method = 'getReader')
or (source.service = 'FileFolderService' and source.method = 'getReader')
)
join alf_node_status ns on ns.guid = fact.node_uuid
join alf_node_properties prop
on ns.node_id = prop.node_id
and prop.qname = '{http://www.alfresco.org/model/content/1.0}name'
where
fact.user_id = 'andy'
and fact.timestamp > ADDDATE(now(), -7)
To link usernames and groups (to generate a report by group for example), you can use the following:
(this generates a 'group - member' list).
select anp2.string_value as GroupName, anp.string_value as MemberName
from alf_node_properties as anp
left join alf_qname as aq on aq.id=anp.qname_id
left join alf_node_properties as anp2 on anp.node_id = anp2.node_id
left join alf_qname as aq2 on aq2.id=anp2.qname_id
where aq.local_name='members' AND aq2.local_name='authorityName'
ORDER BY anp2.string_value ASC, anp.string_value ASC;
MYSQL Examples on 3 Stable database:
To find people who have never logged in or caused anything to be audited:
select convert(prop.string_value using utf8)
from alf_audit_fact fact
right outer join alf_node_properties prop on fact.user_id = prop.string_value
where prop.qname_id in (
select alf_qname.id
from alf_qname inner join alf_namespace on alf_qname.ns_id =alf_namespace.id
where local_Name='userName' and uri='http://www.alfresco.org/model/content/1.0'
)
and fact.user_id is null
To find all content 'viewed' by a given user in the last 7 days:
select fact.timestamp, convert(prop.string_value using utf8)
from alf_audit_fact fact
join alf_audit_source source
on fact.audit_source_id = source.id
and (
(source.service = 'ContentService' and source.method = 'getReader')
or (source.service = 'FileFolderService' and source.method = 'getReader')
)
join alf_node node on node.uuid = fact.node_uuid
join alf_node_properties prop
on node.id = prop.node_id
and prop.qname_id = 14
where
fact.user_id = 'username'
and fact.timestamp > ADDDATE(now(), -7)
To see an overview over which methods were audited, and how often:
select source.service, source.method, count(*)
from alf_audit_fact fact
join alf_audit_source source
on fact.audit_source_id = source.id
group by source.service, source.method
order by count(*)
MYSQL Example on a 3.1 database:
select alf_audit_fact.timestamp,alf_audit_source.service, alf_audit_source.method,alf_node_properties.string_value
from alf_audit_fact
join alf_audit_source on alf_audit_source.id=alf_audit_fact.audit_source_id
join alf_node on alf_node.uuid=alf_audit_fact.node_uuid
join alf_node_properties on alf_node_properties.node_id=alf_node.id
join alf_qname on alf_qname.id=alf_node_properties.qname_id
where
alf_qname.local_name='name'
and alf_audit_fact.user_id='admin'
and alf_audit_fact.timestamp > TO_DATE('20090101','YYYYMMDD')
MYSQL 3.2 Examples
From 3.2 onwards, the following additional options are required to enable auditing as described here:
# Audit configuration
audit.enabled=true
audit.useNewConfig=false
As the DB schema has changed, some of the example queries also need to change that link to a node, node properties and qnames.
To find people who have never logged in or caused anything to be audited:
select convert(prop.string_value using utf8)
from alf_audit_fact fact
right outer join alf_node_properties prop on fact.user_id = prop.string_value
where prop.qname_id in (
select alf_qname.id
from alf_qname inner join alf_namespace on alf_qname.ns_id =alf_namespace.id
where local_Name='userName' and uri='http://www.alfresco.org/model/content/1.0'
)
and fact.user_id is null
To find all content 'viewed' by a given user in the last 7 days:
select fact.timestamp, convert(prop.string_value using utf8)
from alf_audit_fact fact
join alf_audit_source source
on fact.audit_source_id = source.id
and (
(source.service = 'ContentService' and source.method = 'getReader')
or (source.service = 'FileFolderService' and source.method = 'getReader')
)
join alf_node node on node.uuid = fact.node_uuid
join alf_node_properties prop on node.id = prop.node_id
join alf_qname qname on prop.qname_id = qname.id and qname.local_name = 'name'
join alf_namespace namespace on qname.ns_id = namespace.id and namespace.uri = 'http://www.alfresco.org/model/content/1.0'
where
fact.user_id = '<USERNAME>'
and fact.timestamp > ADDDATE(now(), -7)
| 