Help

Auditing (Up to V3.2)

cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Auditing (Up to V3.2)

resplin
Intermediate
0 0 2,274
‎6 Jun 2015 0:40 AM

Obsolete Pages{{Obsolete}}

The official documentation is at: http://docs.alfresco.com



Core Repository ServicesAuditing


Note Regarding WCM


Please note that this functionality does not apply to Alfresco WCM.  Web Projects automatically capture a full version history of all changes to all assets, via the snapshot history, without requiring additional auditing functionality.


Introduction


Deprecation Warning: The audit support described below has been removed in V3.4, but is still available and supported as default up to V3.3.  The new audit functionality (Auditing (from V3.4)) has become the default and only implementation in V3.4.

Auditing is carried out at the service layer of the Repository. This captures both user and application interaction with the repository without recording all the underlying changes to nodes etc. required to achieve this. It is not low level auditing.

The service layer currently defines and enforces transactional behaviour and security.
Auditing fits naturally between the two. If auditing is done in transaction and recorded in the database then it reflects what has been done. It will not record actions that have been rolled back, which is a problem with file based auditing. Failed actions are recorded in another transaction.

From version 1.4, auditing is configured for all the write methods on all public services. Auditing is disabled by default and must be turned on via configuration file alfresco-global.properties if it is required.

As of 3.2r:


  • Auditing is enabled and disabled using a global property:
  # Audit configuration
  audit.enabled=false

  • A new implementation of audit has been introduced into 3.2r to accommodate new requirements.
  audit.useNewConfig=false

Structural Overview


This shows where the audit interceptor fits into the public service architecture.

















Service Layer
Transaction Interceptor
Audit Interceptor -------- Audit Component
Exception Translator Interceptor
Security Interceptor
Service Description Interceptor
Service Implementation Bean

Auditing flow


The diagram below shows how the auditing interceptor wraps calling the underlying service and the returned object or exceptions it produces.
























... TX Interceptor Audit Interceptor Exception Translator Interceptor ...
          (method call) -->           
                Check Audit Flag      
                  Suspend auditing        
                  Audit this call        
                  Record initial state        
                  Record method arguments        
                   (method call) -->  
                     ...
                   <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)
Labels
0 Kudos
Alfresco Content Services Hub Docs

Ask for and offer help to other Alfresco Content Services Users and members of the Alfresco team.

Related links:

We use cookies on this site to enhance your user experience

By using this site, you are agreeing to allow us to collect and use cookies as outlined in Alfresco’s Cookie Statement and Terms of Use (and you have a legitimate interest in Alfresco and our products, authorizing us to contact you in such methods).   If you are not ok with these terms, please do not use this website.