We are trying to track how many times a case is updated from one state to another or when it gets reassigned. I can export the most recent case information, but so far I cannot find a way to locate the logs to export and report on. Does anyone know if this is possible?
Thank you Lori!
How would I find out if I have the Enterprise edition? I believe we are running on version 6.7.12 (Build 14). I checked in our Reports and do not see an option to create a report from the cases_audit table.
If you click on the action menu where your User profile is, and select "About", you should see the edition. Alternatively, if you have Advanced Reports, within the Reports module's action menu you should see something called, "Advanced Reports".
Lori
Jennifer,
I'd also like to add that Advanced Reports requires the knowledge of writing SQL queries.
Lori
Ah, I see now. We have Sugar Professional Version 6.7.12 (Build 14). So we would not be able to report on this correct? It is only available on Enterprise?
Jennifer,
Correct. You may want to check out the Sugar Exchange site to see if there are any SugarCRM Add-On applications that other developers have built to see if there is something that provides you with the features that you are looking for.
Lori
SQL Reporting is Enterprise only
We had a need to be able to query audited fields and I built an entry point in 6.5.x that is not very sophisticated to do just that. It still works in 7.6.x
The idea is as follows: a first entry point (query_audited_fields.php) presents the modules for which we have audit fields.
An exclude list explicitly removes those modules we do not wish to include.
When a module is selected it presents a choice of audited fields to search as well as parameters like date range and assigned users.
The search is then submitted and executed by a second entry point (custom/entry_points/Metrics/query_audited_fields.php) performs the search and presents the results.
Note that if you decide to use the code below, there are provisions here for custom fields that you'll want to edit out/change to your specific situation.
custom/entry_points/Metrics/query_audited_fields.php
<?php
//error_reporting(E_ALL);
//ini_set('display_errors', 'On');
require_once('custom/entry_points/Metrics/audit_metrics_css.php');
include_once('include/TimeDate.php');
//Select the module for which you want to get audit info
function select_module(){
global $db,$current_user,$moduleList,$mod_strings,$app_list_strings;
sort($moduleList, SORT_STRING);
//echo(get_available_modules());die();
$exclude_list=array('Home','Calendar','Bugs','Reports','Emails','Project','Prospects','ProspectLists','Products','Documents','Meetings','Notes','KBDocuments','Forecasts','Tasks','Campaigns');
$module_select = "<select name='module_name'>";
foreach($moduleList as $key=>$module){
if(!in_array($module, $exclude_list)){
$module_label = $app_list_strings['moduleList'][$module];
$module_select .= "<option value='{$module}'>$module_label</option>";
}
}
$module_select .= "</select>";
$form =<<<FORM
<head>
<title>SugarCRM Query Audit Tables</title>
</head>
<body>
<div class='title'>
<span style='color: #C11B17;'>Our Company's Name</span> SugarCRM Query Audit Tables
</div>
<form action='index.php' method='get'>
<div class="section">Select Module</div>
<input type='hidden' name='entryPoint' value='query_audited_fields' />
$module_select
<input type='submit' value='Search' />
</form>
FORM;
return($form);
}
if(!isset($_REQUEST['module_name']) || $_REQUEST['module_name'] ==''){
echo(select_module());
}else{
global $db,$current_user,$moduleList,$current_language,$app_list_strings;
$case_product_fields = array('case_edition_c','case_license_number_c','case_platform_c','case_product_c','case_product_version_c');
$module_name = $_REQUEST['module_name'];
$module_label = $app_list_strings['moduleList'][$module_name];
$current_module_strings = return_module_language($current_language, $module_name);
$bean = get_module_info($module_name);
//$now = TimeDate::getInstance()->nowDate(); //this date is as User Date as a string
$now = date("Y-m-d");
$query = <<<QUERY
select distinct(field_name) from {$bean->table_name}_audit order by field_name
QUERY;
// this query only pulls custom audited fields, TODO: look for a better way than distinct used above
// select name as field_name from fields_meta_data where custom_module = '{$bean->table_name}' and audited='1' order by field_name
$records = $db->query($query);
$number_of_records = $records->num_rows;
$fields = array();
$checkboxes = '';
if ($number_of_records > 0) {
for ($i = 0; $i < $number_of_records; $i++){
//create an array of audited field names by categories
$row = $db->fetchByAssoc($records,$i);
$class = 'Other';
//set fields to be excluded to class exclude, these will not be in the checkboxes list
if (stristr($row['field_name'],'_id') !== FALSE && stristr($row['field_name'], 'assigned_user_id') === FALSE) $class = 'exclude';//exclude ids
elseif (stristr($row['field_name'],'reporting') === FALSE && stristr($row['field_name'],'value') !== FALSE) $class = 'exclude';//exclude values that are not reporting values
elseif (stristr($row['field_name'],'usdollar') !== FALSE) $class = 'exclude';//exclude values that are not reporting values
//set titles for columns to be included, note, order matters: from most specific to most generic
//handle product info specifically
elseif (in_array($row['field_name'],$case_product_fields)) $class = 'Product';
//handle other categories
elseif (stristr($row['field_name'], 'reporting') !== FALSE) $class = 'Reporting Amounts';
elseif (stristr($row['field_name'],'date') !== FALSE) $class = 'Dates';
elseif (stristr($row['field_name'],'time') !== FALSE) $class = 'Time';
elseif (stristr($row['field_name'],'number') !== FALSE) $class = 'Numbers/Counts';
elseif (stristr($row['field_name'],'descriptor') !== FALSE) $class = 'Descriptors';
elseif (stristr($row['field_name'], '_c') === FALSE) $class = 'Core'; //group standard sugar fields, just a way to shorten columns
elseif (stristr($row['field_name'], 'assigned_user_id') === TRUE) $class = 'Core';
$fields[$class][$row['field_name']]=trim(isset($mod_strings[$bean->field_defs[$row['field_name']]['vname']])?$mod_strings[$bean->field_defs[$row['field_name']]['vname']]:$row['field_name'],':');
}
//alpha sort categories
ksort($fields);
//put the 'Other' category at the end of the arry - will display last
$other = array();
$other = $fields['Other'];
unset($fields['Other']);
$fields['Other']= $other;
//build checkboxes for each field
$checkboxes = '<table border =0 cellspacing = "30"><tr>';
$category_count = 0; //used for formatting
foreach ($fields as $category=>$field_array){
if($category != 'exclude'){
if($category_count != 0 && $category_count % 8 === 0) $checkboxes .= '</tr><tr colspan=2>';
$checkboxes .= "<td valign=top cellpadding='0'><b>{$category}</b><br /><div class='{$category}'>
<font size =-2><input type='checkbox' name='All{$category}' onClick=toggle(this,'input')>All</ font><br />";
foreach($field_array as $key=>$val){
$label = str_replace(':','',$current_module_strings[$bean->field_name_map[$val]['vname']]);
$label = preg_replace("/\([^)]+\)/","",$label);
$checkboxes .= "<font size = -2><input type='checkbox' name='{$key}' value='{$key}'>{$label}</ font><br />";
}
$checkboxes .= '</div></td>';//new col every category
}
$category_count++;
}
$checkboxes .= '</tr></table>';
//build multi-select for users
$assigned_to = "<select size='8' multiple='multiple' name='assigned_users[]'>";
$users_query = "SELECT id, user_name, status FROM users, users_cstm WHERE users.id = users_cstm.id_c AND deleted = '0' AND support_group_c = '{$current_user->support_group_c}' ORDER BY user_name";
$users_records = $db->query($users_query);
$number_of_users_records = $users_records->num_rows;
if ($number_of_users_records <> 0) {
//read values from query into row array
for ($i = 0; $i < $number_of_users_records; $i++){
$row = $db->fetchByAssoc($users_records,$i);
$inactive = '';
if($row['status']== 'Inactive') $inactive=' (I)' ;
$assigned_to .= "<option value='{$row['id']}'>{$row['user_name']}{$inactive}</option>";
}
//add sugaradmin
$assigned_to .= "<option value='1'>sugaradmin</option>";
}
$assigned_to .= '</select>';
$department = '';
if($module_name == 'Cases'){
//build the select for department
$department = "<select size='8' multiple='multiple' name='department[]'>";
$departments = $app_list_strings['case_department_dd'];
foreach ($departments as $k=>$d){
if($k == $current_user->support_group_c) $selected = "selected = 'selected'";
else $selected = '';
$department .= "<option value='{$k}' $selected>$d</option>";
}
$department .= '</select>';
$department_header = "<th align='left'>Department</ th>";
}
$form =<<<FORM
<head>
<title> SugarCRM Query Audit Tables</title>
</head>
<body>
<script language="JavaScript">
function toggle(source, element) {
checkboxes = source.parentNode.getElementsByTagName(element);
for (var i = 0; i < checkboxes.length; i++) {
checkboxes[i].checked = source.checked;
}
}
</script>
<div class='title'>
<span style='color: #C11B17;'>Our Company's Name</span> SugarCRM Query Audit Tables
</div>
<form action='index.php' method='get' target='_blank'>
<table border=0 cellspacing=30>
<tr><td colspan=2>
<font size = -2><input type='checkbox' name='output' checked />CSV (Uncheck to see formatted output and option to download as CSV</font>
<p>
<input type='submit' value='Search' />
</td></tr>
<tr>
<th align='left'>Select date range (query changes made within this date range)</ strong></th>
<th align='left'>Restrict by Created by user/s</ th>
{$department_header}
</tr>
<tr>
<td>
<input type='hidden' name='entryPoint' value='get_audit_metrics' />
<input type='hidden' name='module_name' value="{$module_name}" />
From: <input name="from_date" id="from_date" value="{$now}" title="" tabindex="0" size="11" maxlength="10" />
To: <input name="to_date" id="to_date" value="{$now}" title="" tabindex="0" size="11" maxlength="10" />
</td>
<td valign = 'top' align='left'>
{$assigned_to}
</td>
<td valign = 'top' align='left'>
{$department}
</td>
</tr>
<tr colspan=2>
<th colspan=2 align='left'> Which fields are you interested in? ({$module_label} audited fields)</ th>
</tr>
<tr>
<td colspan=2>
{$checkboxes}
<p>
<input type='submit' value='Search' />
</td>
</tr>
</table>
</form>
</body>
</html>
FORM;
echo ($form);
}else{
echo(select_module());
echo("<div class='section'>{$module_label} has no information in the audit table, please select another module<div>");
}
}
custom/entry_points/Metrics/query_audited_fields.php
<?php
//error_reporting(E_ALL);
//ini_set('display_errors', 'On');
require_once('custom/entry_points/Metrics/audit_metrics_css.php');
include_once('include/TimeDate.php');
//Select the module for which you want to get audit info
function select_module(){
global $db,$current_user,$moduleList,$mod_strings,$app_list_strings;
sort($moduleList, SORT_STRING);
//echo(get_available_modules());die();
$exclude_list=array('Home','Calendar','Bugs','Reports','Emails','Project','Prospects','ProspectLists','Products','Documents','Meetings','Notes','KBDocuments','Forecasts','Tasks','Campaigns');
$module_select = "<select name='module_name'>";
foreach($moduleList as $key=>$module){
if(!in_array($module, $exclude_list)){
$module_label = $app_list_strings['moduleList'][$module];
$module_select .= "<option value='{$module}'>$module_label</option>";
}
}
$module_select .= "</select>";
$form =<<<FORM
<head>
<title> SugarCRM Query Audit Tables</title>
</head>
<body>
<div class='title'>
<span style='color: #C11B17;'>Our Company's Name</span> SugarCRM Query Audit Tables
</div>
<form action='index.php' method='get'>
<div class="section">Select Module</div>
<input type='hidden' name='entryPoint' value='query_audited_fields' />
$module_select
<input type='submit' value='Search' />
</form>
FORM;
return($form);
}
if(!isset($_REQUEST['module_name']) || $_REQUEST['module_name'] ==''){
echo(select_module());
}else{
global $db,$current_user,$moduleList,$current_language,$app_list_strings;
$case_product_fields = array('case_edition_c','case_license_number_c','case_platform_c','case_product_c','case_product_version_c');
$module_name = $_REQUEST['module_name'];
$module_label = $app_list_strings['moduleList'][$module_name];
$current_module_strings = return_module_language($current_language, $module_name);
$bean = get_module_info($module_name);
//$now = TimeDate::getInstance()->nowDate(); //this date is as User Date as a string
$now = date("Y-m-d");
$query = <<<QUERY
select distinct(field_name) from {$bean->table_name}_audit order by field_name
QUERY;
// this query only pulls custom audited fields, TODO: look for a better way than distinct used above
// select name as field_name from fields_meta_data where custom_module = '{$bean->table_name}' and audited='1' order by field_name
$records = $db->query($query);
$number_of_records = $records->num_rows;
$fields = array();
$checkboxes = '';
if ($number_of_records > 0) {
for ($i = 0; $i < $number_of_records; $i++){
//create an array of audited field names by categories
$row = $db->fetchByAssoc($records,$i);
$class = 'Other';
//set fields to be excluded to class exclude, these will not be in the checkboxes list
if (stristr($row['field_name'],'_id') !== FALSE && stristr($row['field_name'], 'assigned_user_id') === FALSE) $class = 'exclude';//exclude ids
elseif (stristr($row['field_name'],'reporting') === FALSE && stristr($row['field_name'],'value') !== FALSE) $class = 'exclude';//exclude values that are not reporting values
elseif (stristr($row['field_name'],'usdollar') !== FALSE) $class = 'exclude';//exclude values that are not reporting values
//set titles for columns to be included, note, order matters: from most specific to most generic
//handle product info specifically
elseif (in_array($row['field_name'],$case_product_fields)) $class = 'Product';
//handle other categories
elseif (stristr($row['field_name'], 'reporting') !== FALSE) $class = 'Reporting Amounts';
elseif (stristr($row['field_name'],'date') !== FALSE) $class = 'Dates';
elseif (stristr($row['field_name'],'time') !== FALSE) $class = 'Time';
elseif (stristr($row['field_name'],'number') !== FALSE) $class = 'Numbers/Counts';
elseif (stristr($row['field_name'],'descriptor') !== FALSE) $class = 'Descriptors';
elseif (stristr($row['field_name'], '_c') === FALSE) $class = 'Core'; //group standard sugar fields, just a way to shorten columns
elseif (stristr($row['field_name'], 'assigned_user_id') === TRUE) $class = 'Core';
$fields[$class][$row['field_name']]=trim(isset($mod_strings[$bean->field_defs[$row['field_name']]['vname']])?$mod_strings[$bean->field_defs[$row['field_name']]['vname']]:$row['field_name'],':');
}
//alpha sort categories
ksort($fields);
//alpha sort fields
foreach ($fields as $category=>$field_array){
ksort($fields[$category]);
}
//put the 'Other' category at the end of the arry - will display last
$other = array();
$other = $fields['Other'];
unset($fields['Other']);
$fields['Other']= $other;
//build checkboxes for each field
$checkboxes = '<table border =0 cellspacing = "30"><tr>';
$category_count = 0; //used for formatting
foreach ($fields as $category=>$field_array){
if($category != 'exclude'){
if($category_count != 0 && $category_count % 8 === 0) $checkboxes .= '</tr><tr colspan=2>';
$checkboxes .= "<td valign=top cellpadding='0'><b>{$category}</b><br /><div class='{$category}'>
<font size =-2><input type='checkbox' name='All{$category}' onClick=toggle(this,'input')>All</ font><br />";
foreach($field_array as $key=>$val){
$label = str_replace(':','',$current_module_strings[$bean->field_name_map[$val]['vname']]);
$label = preg_replace("/\([^)]+\)/","",$label);
$checkboxes .= "<font size = -2><input type='checkbox' name='{$key}' value='{$key}'>{$label}</ font><br />";
}
$checkboxes .= '</div></td>';//new col every category
}
$category_count++;
}
$checkboxes .= '</tr></table>';
//build multi-select for users
$assigned_to = "<select size='8' multiple='multiple' name='assigned_users[]'>";
$users_query = "SELECT id, user_name, status FROM users, users_cstm WHERE users.id = users_cstm.id_c AND deleted = '0' AND support_group_c = '{$current_user->support_group_c}' ORDER BY user_name";
$users_records = $db->query($users_query);
$number_of_users_records = $users_records->num_rows;
if ($number_of_users_records <> 0) {
//read values from query into row array
for ($i = 0; $i < $number_of_users_records; $i++){
$row = $db->fetchByAssoc($users_records,$i);
$inactive = '';
if($row['status']== 'Inactive') $inactive=' (I)' ;
$assigned_to .= "<option value='{$row['id']}'>{$row['user_name']}{$inactive}</option>";
}
//add sugaradmin
$assigned_to .= "<option value='1'>sugaradmin</option>";
}
$assigned_to .= '</select>';
$department = '';
if($module_name == 'Cases'){
//build the select for department
$department = "<select size='8' multiple='multiple' name='department[]'>";
$departments = $app_list_strings['case_department_dd'];
foreach ($departments as $k=>$d){
if($k == $current_user->support_group_c) $selected = "selected = 'selected'";
else $selected = '';
$department .= "<option value='{$k}' $selected>$d</option>";
}
$department .= '</select>';
$department_header = "<th align='left'>Department</ th>";
}
$form =<<<FORM
<head>
<title>SugarCRM Query Audit Tables</title>
</head>
<body>
<script language="JavaScript">
function toggle(source, element) {
checkboxes = source.parentNode.getElementsByTagName(element);
for (var i = 0; i < checkboxes.length; i++) {
checkboxes[i].checked = source.checked;
}
}
</script>
<div class='title'>
<span style='color: #C11B17;'>Our Company's Name</span> SugarCRM Query Audit Tables
</div>
<form action='index.php' method='get' target='_blank'>
<table border=0 cellspacing=30>
<tr><td colspan=2>
<font size = -2><input type='checkbox' name='output' checked />CSV (Uncheck to see formatted output and option to download as CSV</font>
<p>
<input type='submit' value='Search' />
</td></tr>
<tr>
<th align='left'>Select date range (query changes made within this date range)</ strong></th>
<th align='left'>Restrict by Created by user/s</ th>
{$department_header}
</tr>
<tr>
<td>
<input type='hidden' name='entryPoint' value='get_audit_metrics' />
<input type='hidden' name='module_name' value="{$module_name}" />
From: <input name="from_date" id="from_date" value="{$now}" title="" tabindex="0" size="11" maxlength="10" />
To: <input name="to_date" id="to_date" value="{$now}" title="" tabindex="0" size="11" maxlength="10" />
</td>
<td valign = 'top' align='left'>
{$assigned_to}
</td>
<td valign = 'top' align='left'>
{$department}
</td>
</tr>
<tr colspan=2>
<th colspan=2 align='left'> Which fields are you interested in? ({$module_label} audited fields)</ th>
</tr>
<tr>
<td colspan=2>
{$checkboxes}
<p>
<input type='submit' value='Search' />
</td>
</tr>
</table>
</form>
</body>
</html>
FORM;
echo ($form);
}else{
echo(select_module());
echo("<div class='section'>{$module_label} has no information in the audit table, please select another module<div>");
}
}
</td>
</tr>
</table>
</form>
</body>
</html>
FORM;
echo ($form);
}else{
echo(select_module());
echo("<div class='section'>{$module_label} has no information in the audit table, please select another module<div>");
}
custom/entry_points/Metrics/query_audited_fields.php
stom/
<style type="text/css">
body {
background-color: #DEDEDE;
background-image: url("index.php?entryPoint=getImage&themeName=Sugar&imageName=bg.png");
background-repeat: repeat;
color: #444444;
font-family: Verdana,Arial,Helvetica,sans-serif;
font-size: 13px;
margin: 0;
padding: 15px;
}
.title {
font-family: Helvetica,Arial,Verdana,sans-serif;
font-size: 24px;
font-weight: bold;
margin: 0;
padding: 5px 0 15px 5px;
}
.section {
font-family: Arial,Verdana,Helvetica,sans-serif;
font-size: 16px;
font-weight: bold;
margin: 0;
padding: 5px 0 15px 5px;
}
.table_label {
font-size: 14px;
padding-left: 15px;
clear: both;
}
.audit_tables {
}
.audit_table {
color: black;
padding-left: 30px;
padding-top: 15px;
}
.header_row {
width: 20000px;
}
.row {
width: 20000px;
}
.content_cell {
margin-left: 1px;
margin-right 1px;
}
.row_alt {
background-color: #E3E4FA;
}
.header_cell {
background-color: #BBBBBB;
border-right: 1px solid black;
}
.cell_sched {
border-top: 1px solid black;
float: left;
position: relative;
padding: 5px;
font-size: 10pt;
width: 250px;
}
.comments {
width: 150px;
}
.message {
background-color: #DDDDDD;
width: 1000px;
font-size: 10px;
}
.smacked {
color: #FF0000;
}
.header_first_column {
float: left;
border-left: 1px solid black;
}
.cell_first_column {
clear: left;
margin-right 1px;
}
.quicklinks1 {
color: #2B65EC;
}
.linenumber {
font-style: italic;
color: #347C2C;
}
a {
text-decoration: none;
}
</style>
Having said all this... in 7.6 your best solution is probably to build yourself a custom API to get the information you need and call it from an external source that can manipulate the results and present them in whichever way you choose.
I have a couple of APIs that are called from Wolfram Language notebook (Wolfram: Computation Meets Knowledge ) where the data is subsequently manipulated with calculations and diagrammed for management perusal.
FrancescaS
Thank you both very much!
Jennifer,
The Change Log is not directly exportable from the UI. If you have Enterprise edition or above, you can create an Advanced Report where the query is pulling from the cases_audit table. Once you build your query and run the report, you will be able to export it.
Other than that, if you have direct access to your database, you can access that information within the cases_audit table.
Hope this helps,
Lori