Auditing with sql querys, examples in Wiki seems outdated?

[gronfelt]

I've tried to apply some of the sample sql querys from the wiki page about Audit, but they all seem to be outdated. Are there any newer examples to be found, that apply to db schemas of current Alfresco versions?

[andy]

Hi

Can you be more specific about the queries you tried, the database, the issues, and the configuration you used for auditing?

Andy

[gronfelt]

Ok, here's my auditConfig.xml:

<?xml version='1.0' encoding='UTF-8'?>

<!– Default Audit Configuration –>

<Audit xmlns="http://www.alfresco.org/model/audit/1.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" enabled="true" auditInternal="false" mode="all">
    
    <!–                –>
    <!– Global options –>
    <!–                –>
    
    <!– Do not record any additional information –>
    
    <RecordOptions>
        <recordPath>false</recordPath>
        <recordFilters>false</recordFilters>
        <recordSerializedReturnValue>false</recordSerializedReturnValue>
        <recordSerializedExceptions>false</recordSerializedExceptions>
        <recordSerializedMethodArguments>false</recordSerializedMethodArguments>
        <recordSerializedKeyPropertiesBeforeInvocation>false</recordSerializedKeyPropertiesBeforeInvocation>
        <recordSerializedKeyPropertiesAferInvocation>false</recordSerializedKeyPropertiesAferInvocation>
    </RecordOptions>
    
    <!–                          –>
    <!– Specific service options –>
    <!–                          –>
    
    <Service name="ServiceRegistry" mode="none" enabled="false"/>
    
    <!– The action service –>
    
    <Service name="ActionService" mode="none">
        <Method name="executeAction" mode="all" auditInternal="true"/>
        <Method name="saveAction" mode="all"/>
        <Method name="removeAction" mode="all"/>
        <Method name="removeAllActions" mode="all"/>
    </Service>
    
    <!– The COCI Service –>
    
    <Service name="CheckoutCheckinService">
        <Method name="getWorkingCopy" mode="none"/>
    </Service>
    
    <!– The DictionaryService –>
    <!– There are no audtiable methods in the data dictionary service –>
    <Service name="DictionaryService" mode="none" enabled="false"/>
    
    <!– The Lock Service –>
    
    <Service name="LockService">
        <Method name="getLockStatus" mode="none"/>
        <Method name="getLockType" mode="none"/>
        <Method name="getLocks" mode="none"/>
    </Service>
    
    <!– The File/Folder Service –>
    
    <Service name="FileFolderService" mode="none">
        <Method name="rename" mode="all"/>
        <Method name="move" mode="all"/>
        <Method name="copy" mode="all" auditInternal="true"/>
        <Method name="create" mode="all"/>
        <Method name="delete" mode="all"/>
        <Method name="makeFolders" mode="all"/>
        <Method name="getWriter" mode="all"/>
   <Method name="getReader" mode="all"/>
    </Service>
    
    <Service name="ContentService" mode="none">
        <Method name="getWriter" mode="all"/>
        <Method name="transform" mode="all"/>
   <Method name="getReader" mode="all"/>
    </Service>
    
    <Service name="CopyService" mode="none">
        <Method name="copy" mode="all" auditInternal="true"/>
    </Service>
    
    <!– The MimetypeService –>
    <!– There are no audtiable methods in the mime type service –>
    <Service name="MimetypeService" mode="none" enabled="false"/>
    
<!– The ContentFilterLanguagesService –>
    <Service name="ContentFilterLanguagesService" mode="none" >
        <Method name="getFilterLanguages" mode="all"/>
        <Method name="getMissingLanguages" mode="all"/>
        <Method name="getDefaultLanguage" mode="all"/>
    </Service>
    <Service name="NodeService" mode="none">
        <Method name="createStore" mode="all"/>
        <Method name="createNode" mode="all"/>
        <Method name="moveNode" mode="all"/>
        <Method name="setChildAssociationIndex" mode="all"/>
        <Method name="setType" mode="all"/>
        <Method name="addAspect" mode="all"/>
        <Method name="removeAspect" mode="all"/>
        <Method name="deleteNode" mode="all"/>
        <Method name="addChild" mode="all"/>
        <Method name="removeChild" mode="all"/>
        <Method name="setProperties" mode="all"/>
        <Method name="setProperty" mode="all"/>
        <Method name="createAssociation" mode="all"/>
        <Method name="removeAssociation" mode="all"/>
        <Method name="restoreNode" mode="all"/>
    </Service>
    
    <Service name="ScriptService" auditInternal="true"/>
    
    <Service name="TemplateService" auditInternal="true">
        <Method name="getTemplateProcessor" mode="none"/>
    </Service>
    
    <Service name="RuleService" mode="none" auditInternal="true">
        <Method name="disableRules" mode="all"/>
        <Method name="enableRules" mode="all"/>
        <Method name="disableRule" mode="all"/>
        <Method name="enableRule" mode="all"/>
        <Method name="createRule" mode="all"/>
        <Method name="saveRule" mode="all"/>
        <Method name="removeRule" mode="all"/>
        <Method name="removeAllRules" mode="all"/>
    </Service>
    
    <Service name="CategoryService" mode="none">
        <Method name="createClassifiction" mode="all"/>
        <Method name="createRootCategory" mode="all"/>
        <Method name="createCategory" mode="all"/>
        <Method name="deleteClassification" mode="all"/>
        <Method name="deleteCategory" mode="all"/>
    </Service>
    
    <Service name="SearchService" mode="none" enabled="false"/>
    
    <Service name="AuthenticationService" mode="none">
        <Method name="createAuthentication" mode="all"/>
        <Method name="updateAuthentication" mode="all"/>
        <Method name="setAuthentication" mode="all"/>
        <Method name="deleteAuthentication" mode="all"/>
        <Method name="setAuthenticationEnabled" mode="all"/>
        <Method name="authenticate" mode="all"/>
        <Method name="authenticateAsGuest" mode="all"/>
        <Method name="authenticationExists" mode="all"/>
        <Method name="invalidateUserSession" mode="all"/>
        <Method name="invalidateTicket" mode="all"/>
        <Method name="validate" mode="all"/>
        <Method name="clearCurrentSecurityContext" mode="all"/>
    </Service>
    
    <Service name="AuthorityService" mode="none">
        <Method name="createAuthority" mode="all"/>
        <Method name="addAuthority" mode="all"/>
        <Method name="removeAuthority" mode="all"/>
        <Method name="deleteAuthority" mode="all"/>
    </Service>
    
    <Service name="OwnableService" mode="none">
        <Method name="setOwner" mode="all"/>
        <Method name="takeOwnership" mode="all"/>
    </Service>
    
    <Service name="PermissionService" mode="none">
        <Method name="deletePermissions" mode="all"/>
        <Method name="clearPermission" mode="all"/>
        <Method name="deletePermission" mode="all"/>
        <Method name="setPermission" mode="all"/>
        <Method name="setInheritParentPermissions" mode="all"/>
    </Service>
    
    <Service name="PersonService" mode="none">
        <Method name="setCreateMissingPeople" mode="all"/>
        <Method name="setPersonProperties" mode="all"/>
        <Method name="createPerson" mode="all"/>
        <Method name="deletePerson" mode="all"/>
    </Service>
    
    <Service name="VersionService" mode="none">
        <Method name="createVersion" mode="all"/>
        <Method name="revert" mode="all"/>
        <Method name="restore" mode="all"/>
        <Method name="deleteVersionHistory" mode="all"/>
    </Service>
    
    <Service name="ExporterService"/>
    
    <Service name="ImporterService"/>
    
    <Service name="RepositoryExporterService"/>
    
    <Service name="DescriptorService" mode="none" enabled="false"/>
    
    <Service name="LicenseService" mode="none" enabled="false"/>
    
    <Service name="NamespaceService" mode="none" enabled="false"/>
    
    <Service name="TransactionService" mode="none" enabled="false"/>
    
    <Service name="WorkflowService" auditInternal="true">
        <Method name="isDefinitionDeployed" mode="none"/>
        <Method name="getDefinitions" mode="none"/>
        <Method name="getDefinitionById" mode="none"/>
        <Method name="getDefinitionByName" mode="none"/>
        <Method name="getActiveWorkflows" mode="none"/>
        <Method name="getWorkflowPaths" mode="none"/>
        <Method name="getTasksForWorkflowPath" mode="none"/>
        <Method name="getTaskById" mode="none"/>
        <Method name="getAssignedTasks" mode="none"/>
        <Method name="getPooledTasks" mode="none"/>
    </Service>
    
</Audit>
‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

I've tried this query, to show all content viewed by a user in the past 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)‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

The return is:

Failed to execute SQL : SQL 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) [b]failed : Table 'alfresco.alf_node_status' doesn't exist[/b]‍

I also tried this one, to find people who have never logged in:

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‍‍‍‍‍

That fails since there is no field named "qname".

I've tried a couple of other too, with similar problems.

Edit:

Oh, almost forgot, the database is MySQL 5.1.33, running on the same host as Alfresco.

[gronfelt]

I've rewritten the queries mentioned above so that they work with Alfresco Labs 3 Stable (haven't tried them with any other version). If anyone else finds them useful I suppose they could be added to the Wiki, I'm not sure what the policies are regarding wiki updates.

One note: The qname_id values supplemented by me might not be correct on all systems, so you might wanna look them up for yourself.

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 = 44  
       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)
‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

[mrogers]

Please go ahead and update the wiki with your examples.    Don't hold back, its the only way that the wiki will get better!

[uptime365]

Hi,

Can someone update the queries that can be used for alfresco version 3.3

Thanks,
Uptime