Collecting User Statistics

Just wondering what approaches people have used in collecting statistics on user entry. e.g. how many transactions did Joe enter.

Have you tried to use System Activity Log?

Company Maintenance -> Activity Tracking -> System Activities

Then check System Activity Log

Process the results daily and purge the log.

1 Like

System activity log only appears to show the logon/off

I have created a simple view which we can then crate a pivot table from. Not fancy but does the job.

CREATE VIEW [dbo].[SystemActivity_Pilot_Vw]
AS
/*SHALL 16/08/2018
generates simple data for key transaction counts by user and date
*/
SELECT 
'AP Invoice' [Function]
,entryperson
,CAST(InvoiceDate AS Date) [Date]
,CAST(changedate AS DATE)  [ChangeDate]
,count(entryperson) [TranCount]
FROM ttasesql01.EpicorERPPilot.erp.APInvHed
GROUP BY EntryPerson,InvoiceDate,changedate
UNION ALL
SELECT 
'AR Invoice' [Function]
,entryperson
,CAST(InvoiceDate AS DATE) [Date]
,CAST(changedate AS DATE) [ChangeDate]
,count(entryperson) [TranCount]
FROM ttasesql01.EpicorERPPilot.erp.InvcHead
GROUP BY EntryPerson,InvoiceDate,changedate
UNION ALL
SELECT 
'Sales Order' [Function]
,entryperson
,CAST(OrderDate AS DATE) [Date]
,CAST(changedate AS DATE) [ChangeDate]
,count(entryperson) [TranCount]
FROM ttasesql01.EpicorERPPilot.erp.OrderHed
GROUP BY EntryPerson,OrderDate,changedate
UNION ALL
SELECT 
'Cust PackSlip' [Function]
,entryperson
,CAST(ShipDate AS DATE) [Date]
,CAST(changedate AS DATE) [ChangeDate]
,count(entryperson) [TranCount]
FROM ttasesql01.EpicorERPPilot.erp.ShipHead
GROUP BY EntryPerson,ShipDate,changedate
UNION ALL
SELECT 
'POs' [Function]
,entryperson
,CAST(OrderDate AS DATE) [Date]
,CAST(changedate AS DATE) [ChangeDate]
,count(entryperson) [TranCount]
FROM ttasesql01.EpicorERPPilot.erp.POHeader
GROUP BY EntryPerson,OrderDate,changedate
UNION ALL
SELECT 
'PO Receipts Add' [Function]
,entryperson
,CAST(ReceiptDate AS DATE) [Date]
,CAST(changedate AS DATE) [ChangeDate]
,count(entryperson) [TranCount]
FROM ttasesql01.EpicorERPPilot.erp.RcvHead
GROUP BY EntryPerson,ReceiptDate,changedate
UNION ALL
SELECT 
'PO Receipts Rec' [Function]
,ReceivePerson
,CAST(ReceiptDate AS DATE) [Date]
,CAST(changedate AS DATE) [ChangeDate]
,count(ReceivePerson) [TranCount]
FROM ttasesql01.EpicorERPPilot.erp.RcvHead
GROUP BY ReceivePerson,ReceiptDate,changedate
UNION ALL
SELECT 
'AP Invoices' [Function]
,entryperson
,CAST(InvoiceDate AS DATE) [Date]
,CAST(changedate AS DATE) [ChangeDate]
,count(entryperson) [TranCount]
FROM ttasesql01.EpicorERPPilot.erp.APInvHed
GROUP BY EntryPerson,InvoiceDate,changedate
UNION ALL
SELECT 
'Transfer Ord' [Function]
,entryperson
,CAST(OrderDate AS DATE) [Date]
,CAST(OrderDate AS DATE) [changedate]
,count(entryperson) [TranCount]
FROM ttasesql01.EpicorERPPilot.erp.TFOrdHed
GROUP BY EntryPerson,OrderDate
UNION ALL
SELECT 
'TF Packslip' [Function]
,entryperson
,CAST(ShipDate AS DATE) [Date]
,CAST(ShipDate AS DATE)[changedate]
,count(entryperson) [TranCount]
FROM ttasesql01.EpicorERPPilot.erp.TFShipHead
GROUP BY EntryPerson,ShipDate,ShipDate
UNION ALL
SELECT 
'Reports Run' [Function]
,SubmitUser
,CAST(StartedOn AS DATE) [Date]
,CAST(EndedOn AS DATE) [ChangeDate]
,count(SubmitUser) [TranCount]
FROM ttasesql01.EpicorERPPilot.ice.SysTask
WHERE TaskType = 'Report'
GROUP BY SubmitUser,StartedOn,EndedOn
UNION ALL
SELECT 
'Process Subd.' [Function]
,SubmitUser
,CAST(StartedOn AS DATE) [Date]
,CAST(EndedOn AS DATE) [ChangeDate]
,count(SubmitUser) [TranCount]
FROM ttasesql01.EpicorERPPilot.ice.SysTask
WHERE TaskType = 'Process'
GROUP BY SubmitUser,StartedOn,EndedOn
UNION ALL
SELECT 
ActivityComment
,userid
,Cast(lastactivityon AS DATE) [Date]
,Cast(lastactivityon AS DATE) [ChangeDate]
,count(userid) TranCount
FROM ttasesql01.EpicorERPPilot.ice.sysactivitylog
GROUP BY userid,activitycomment,Cast(LastActivityOn AS DATE)

CREATE VIEW [dbo].[SystemActivity_Pilot_Vw]
AS
/*SHALL 16/08/2018
generates simple data for key transaction counts by user and date
*/
SELECT 
'AP Invoice' [Function]
,entryperson
,CAST(InvoiceDate AS Date) [Date]
,CAST(changedate AS DATE)  [ChangeDate]
,count(entryperson) [TranCount]
FROM ttasesql01.EpicorERPPilot.erp.APInvHed
GROUP BY EntryPerson,InvoiceDate,changedate
UNION ALL
SELECT 
'AR Invoice' [Function]
,entryperson
,CAST(InvoiceDate AS DATE) [Date]
,CAST(changedate AS DATE) [ChangeDate]
,count(entryperson) [TranCount]
FROM ttasesql01.EpicorERPPilot.erp.InvcHead
GROUP BY EntryPerson,InvoiceDate,changedate
UNION ALL
SELECT 
'Sales Order' [Function]
,entryperson
,CAST(OrderDate AS DATE) [Date]
,CAST(changedate AS DATE) [ChangeDate]
,count(entryperson) [TranCount]
FROM ttasesql01.EpicorERPPilot.erp.OrderHed
GROUP BY EntryPerson,OrderDate,changedate
UNION ALL
SELECT 
'Cust PackSlip' [Function]
,entryperson
,CAST(ShipDate AS DATE) [Date]
,CAST(changedate AS DATE) [ChangeDate]
,count(entryperson) [TranCount]
FROM ttasesql01.EpicorERPPilot.erp.ShipHead
GROUP BY EntryPerson,ShipDate,changedate
UNION ALL
SELECT 
'POs' [Function]
,entryperson
,CAST(OrderDate AS DATE) [Date]
,CAST(changedate AS DATE) [ChangeDate]
,count(entryperson) [TranCount]
FROM ttasesql01.EpicorERPPilot.erp.POHeader
GROUP BY EntryPerson,OrderDate,changedate
UNION ALL
SELECT 
'PO Receipts Add' [Function]
,entryperson
,CAST(ReceiptDate AS DATE) [Date]
,CAST(changedate AS DATE) [ChangeDate]
,count(entryperson) [TranCount]
FROM ttasesql01.EpicorERPPilot.erp.RcvHead
GROUP BY EntryPerson,ReceiptDate,changedate
UNION ALL
SELECT 
'PO Receipts Rec' [Function]
,ReceivePerson
,CAST(ReceiptDate AS DATE) [Date]
,CAST(changedate AS DATE) [ChangeDate]
,count(ReceivePerson) [TranCount]
FROM ttasesql01.EpicorERPPilot.erp.RcvHead
GROUP BY ReceivePerson,ReceiptDate,changedate
UNION ALL
SELECT 
'AP Invoices' [Function]
,entryperson
,CAST(InvoiceDate AS DATE) [Date]
,CAST(changedate AS DATE) [ChangeDate]
,count(entryperson) [TranCount]
FROM ttasesql01.EpicorERPPilot.erp.APInvHed
GROUP BY EntryPerson,InvoiceDate,changedate
UNION ALL
SELECT 
'Transfer Ord' [Function]
,entryperson
,CAST(OrderDate AS DATE) [Date]
,CAST(OrderDate AS DATE) [changedate]
,count(entryperson) [TranCount]
FROM ttasesql01.EpicorERPPilot.erp.TFOrdHed
GROUP BY EntryPerson,OrderDate
UNION ALL
SELECT 
'TF Packslip' [Function]
,entryperson
,CAST(ShipDate AS DATE) [Date]
,CAST(ShipDate AS DATE)[changedate]
,count(entryperson) [TranCount]
FROM ttasesql01.EpicorERPPilot.erp.TFShipHead
GROUP BY EntryPerson,ShipDate,ShipDate
UNION ALL
SELECT 
'Reports Run' [Function]
,SubmitUser
,CAST(StartedOn AS DATE) [Date]
,CAST(EndedOn AS DATE) [ChangeDate]
,count(SubmitUser) [TranCount]
FROM ttasesql01.EpicorERPPilot.ice.SysTask
WHERE TaskType = 'Report'
GROUP BY SubmitUser,StartedOn,EndedOn
UNION ALL
SELECT 
'Process Subd.' [Function]
,SubmitUser
,CAST(StartedOn AS DATE) [Date]
,CAST(EndedOn AS DATE) [ChangeDate]
,count(SubmitUser) [TranCount]
FROM ttasesql01.EpicorERPPilot.ice.SysTask
WHERE TaskType = 'Process'
GROUP BY SubmitUser,StartedOn,EndedOn
UNION ALL
SELECT 
ActivityComment
,userid
,Cast(lastactivityon AS DATE) [Date]
,Cast(lastactivityon AS DATE) [ChangeDate]
,count(userid) TranCount
FROM ttasesql01.EpicorERPPilot.ice.sysactivitylog
GROUP BY userid,activitycomment,Cast(LastActivityOn AS DATE)
2 Likes

Many thanks for this code!

You know I was looking for that a few months ago and forgot I posted it. So thank you for the reminder :relaxed:

1 Like

Yeah, it has already been helpful. I wonder if anyone has a code already also showing user uptime in Epicor?