User Tracking Statistics

[Automotive]

Our EA project has multiple users configured.

Can EA produce any metric for detailing who has modified what files, when, how often. To create a pie chart of workload vs personnel ?

[qwerty]

Not directly. You can turn on EA's audit feature and run some evaluation on your own.

q.

[OpenIT Solutions]

Hi,

We are doing this by putting an update trigger on t_object which writes to an audit table (t_object with an extra user_id and timestamp column) ... this way we can write custom SQL in our dashboards that tracks who has changed what and when...

[Ian Mitchell]

Is this available as a solution for others? I get asked for this kind of thing quite a lot.

[OpenIT Solutions]

Well for SQL server you would have:

USE [sparx_bpm]
GO

/****** Object:  Table [dbo].[th_object]    Script Date: 09/06/2015 13:01:34 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[th_object](
      [Object_ID] [int] NOT NULL,
      [Object_Type] [nvarchar](255) NULL,
      [Diagram_ID] [int] NULL,
      [Name] [nvarchar](255) NULL,
      [Alias] [nvarchar](255) NULL,
      [Author] [nvarchar](255) NULL,
      [Version] [nvarchar](50) NULL,
      [Note] [ntext] NULL,
      [Package_ID] [int] NULL,
      [Stereotype] [nvarchar](255) NULL,
      [NType] [int] NULL,
      [Complexity] [nvarchar](50) NULL,
      [Effort] [int] NULL,
      [Style] [nvarchar](255) NULL,
      [Backcolor] [int] NULL,
      [BorderStyle] [int] NULL,
      [BorderWidth] [int] NULL,
      [Fontcolor] [int] NULL,
      [Bordercolor] [int] NULL,
      [CreatedDate] [datetime] NULL,
      [ModifiedDate] [datetime] NULL,
      [Status] [nvarchar](50) NULL,
      [Abstract] [nvarchar](1) NULL,
      [Tagged] [int] NULL,
      [PDATA1] [nvarchar](255) NULL,
      [PDATA2] [ntext] NULL,
      [PDATA3] [ntext] NULL,
      [PDATA4] [ntext] NULL,
      [PDATA5] [nvarchar](255) NULL,
      [Concurrency] [nvarchar](50) NULL,
      [Visibility] [nvarchar](50) NULL,
      [Persistence] [nvarchar](50) NULL,
      [Cardinality] [nvarchar](50) NULL,
      [GenType] [nvarchar](50) NULL,
      [GenFile] [nvarchar](255) NULL,
      [Header1] [ntext] NULL,
      [Header2] [ntext] NULL,
      [Phase] [nvarchar](50) NULL,
      [Scope] [nvarchar](25) NULL,
      [GenOption] [ntext] NULL,
      [GenLinks] [ntext] NULL,
      [Classifier] [int] NULL,
      [ea_guid] [nvarchar](40) NULL,
      [ParentID] [int] NULL,
      [RunState] [ntext] NULL,
      [Classifier_guid] [nvarchar](40) NULL,
      [TPos] [int] NULL,
      [IsRoot] [int] NOT NULL,
      [IsLeaf] [int] NOT NULL,
      [IsSpec] [int] NOT NULL,
      [IsActive] [int] NOT NULL,
      [StateFlags] [nvarchar](255) NULL,
      [PackageFlags] [nvarchar](255) NULL,
      [Multiplicity] [nvarchar](50) NULL,
      [StyleEx] [ntext] NULL,
      [EventFlags] [ntext] NULL,
      [ActionFlags] [nvarchar](255) NULL,
      [User_Id] [varchar](100) NOT NULL,
      [Change_Date] [datetime] NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO


Then create a trigger on t_object:

USE [sparx_bpm]
GO
/****** Object:  Trigger [dbo].[t_object_history]    Script Date: 09/06/2015 13:02:20 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER TRIGGER [dbo].[t_object_history]
ON [dbo].[t_object]
AFTER UPDATE
AS
DECLARE @i_Object_ID int
DECLARE @i_ModifiedDate datetime
DECLARE @d_ModifiedDate datetime
SELECT @i_Object_ID = [Object_ID], @i_ModifiedDate = [ModifiedDate] FROM inserted
SELECT @d_ModifiedDate = [ModifiedDate] FROM deleted;
IF @i_ModifiedDate != @d_ModifiedDate
BEGIN
INSERT INTO th_object
( [Object_ID]
, [Object_Type]
, [Diagram_ID]
, [Name]
, [Alias]
, [Author]
, [Version]
, [Note]
, [Package_ID]
, [Stereotype]
, [NType]
, [Complexity]
, [Effort]
, [Style]
, [Backcolor]
, [BorderStyle]
, [BorderWidth]
, [Fontcolor]
, [Bordercolor]
, [CreatedDate]
, [ModifiedDate]
, [Status]
, [Abstract]
, [Tagged]
, [PDATA1]
, [PDATA2]
, [PDATA3]
, [PDATA4]
, [PDATA5]
, [Concurrency]
, [Visibility]
, [Persistence]
, [Cardinality]
, [GenType]
, [GenFile]
, [Header1]
, [Header2]
, [Phase]
, [Scope]
, [GenOption]
, [GenLinks]
, [Classifier]
, [ea_guid]
, [ParentID]
, [RunState]
, [Classifier_guid]
, [TPos]
, [IsRoot]
, [IsLeaf]
, [IsSpec]
, [IsActive]
, [StateFlags]
, [PackageFlags]
, [Multiplicity]
, [StyleEx]
, [EventFlags]
, [ActionFlags]
, [User_Id]
, [Change_Date]
)
SELECT t_object.[Object_ID]
, t_object.[Object_Type]
, t_object.[Diagram_ID]
, t_object.[Name]
, t_object.[Alias]
, t_object.[Author]
, t_object.[Version]
, t_object.[Note]
, t_object.[Package_ID]
, t_object.[Stereotype]
, t_object.[NType]
, t_object.[Complexity]
, t_object.[Effort]
, t_object.[Style]
, t_object.[Backcolor]
, t_object.[BorderStyle]
, t_object.[BorderWidth]
, t_object.[Fontcolor]
, t_object.[Bordercolor]
, t_object.[CreatedDate]
, t_object.[ModifiedDate]
, t_object.[Status]
, t_object.[Abstract]
, t_object.[Tagged]
, t_object.[PDATA1]
, t_object.[PDATA2]
, t_object.[PDATA3]
, t_object.[PDATA4]
, t_object.[PDATA5]
, t_object.[Concurrency]
, t_object.[Visibility]
, t_object.[Persistence]
, t_object.[Cardinality]
, t_object.[GenType]
, t_object.[GenFile]
, t_object.[Header1]
, t_object.[Header2]
, t_object.[Phase]
, t_object.[Scope]
, t_object.[GenOption]
, t_object.[GenLinks]
, t_object.[Classifier]
, t_object.[ea_guid]
, t_object.[ParentID]
, t_object.[RunState]
, t_object.[Classifier_guid]
, t_object.[TPos]
, t_object.[IsRoot]
, t_object.[IsLeaf]
, t_object.[IsSpec]
, t_object.[IsActive]
, t_object.[StateFlags]
, t_object.[PackageFlags]
, t_object.[Multiplicity]
, t_object.[StyleEx]
, t_object.[EventFlags]
, t_object.[ActionFlags]
, SYSTEM_USER
, SYSDATETIME()
FROM t_object
WHERE t_object.[Object_ID] = @i_Object_ID
END


Then you can add SQL to charts in v12 that pull change history from th_object...

[Automotive]

Thank you for your replies. SQL goes a bit over my head.
I've tried using various aspect of the Auditing BUT it does not show all the users?

We have about 10 users, 4 almost permanently logged on to the model but yet only two of us show up in the audit trail.

If I log in as admin, a third user appears in the audit, but I never see the other two members of the core team.

Is this a bug in Version 11?

Or are two users configured differently in some hidden menu I've yet to find?

[qwerty]

If you configured A to log on with a database user and that is the same for all you will only see that DB user. Probably you need to turn on AD authentication in EA.

q.