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
1 Like
Yeah, it has already been helpful. I wonder if anyone has a code already also showing user uptime in Epicor?