How to retrieve the name of the last modifier

[michielper]

Elements and diagrams contain the name of the Author, set to the user at creation time. But the name of the person who did the last modification to an element or diagram is not so easy to find. Is this information permanently lost or is there some way to retrieve it? As a related question, I would also like to know who created or modified a connector.
I am using Sparx EA version 13 with a shared SQL Server database.

[qwerty]

There are only two ways. Either you turn on auditing or you dare to write a trigger for that purpose.

q.

[michielper]

There are only two ways. Either you turn on auditing or you dare to write a trigger for that purpose.

q.

Allright, but neither of these methods give me the modifier from a time when I had neither of these mechanisms in place, right? Doesn't the SQL Server database store this information?

[qwerty]

Nope. What't lost is lost.

q.

[michielper]

There are only two ways. Either you turn on auditing or you dare to write a trigger for that purpose.

q.

I looked and saw a lot of audit information, with names and timestamps etc. but not easy to interpret. How can I access the audit information in a script or report template so that I can find out for a particular element who has changed it when?

[Modesto Vega]

There are only two ways. Either you turn on auditing or you dare to write a trigger for that purpose.

q.

I looked and saw a lot of audit information, with names and timestamps etc. but not easy to interpret. How can I access the audit information in a script or report template so that I can find out for a particular element who has changed it when?

It should be possible to write a document template with a full audit log for each package, diagram and element in the package being documented. There is an Audit item for each item included in a document template.

I think you could also write a SQL Statement but haven’t worked out the details.

[qwerty]

I haven't covered the audit trail in my Inside book so far. I'll have a look and see what can be deciphered on short terms.

q.

[qwerty]

So here's a short summary:

SnapshotID: primary key
SeriesID: was just 'LOG' but will dig a bit more
Position: not unique so obviously some changes take more than one row
SnapshotName: the affected table name
Notes: see below
Style: type of update like INSERT etc.
ElementID: No idea. Was just 1 or 0. Will see.
all other columns were just not occupied. Will dig a bit more

Now for the Notes:
I had lines staring with "Audit"... which obviously told that auditing was changed
Others started with a GUID that was the element/connector(/diagram? not tested) GUID of the respective table in SnapshotName followed by mixed data.

I think the user info is stored in the bincontent columns. Need a closer look but that will take a moment.

I'll keep you posted.

q.

[qwerty]

Some more info: bincontent1 contains a zip with a single str.dat file (the usual format). For me a sample looked like this:

Code: [Select]

<LogItem><Row Number="0"><Column Name="Audit Options"><Old Value=""/><New Value="All"/></Column><appliesTo/><Column Name="Auditing"><Old Value="Disabled"/><New Value="Enabled"/></Column></Row><Details User="Thomas" DateTime="2019-08-15 22:11:47"/></LogItem>
bincontent2 of the same record had an utf8 string like

Code: [Select]

<metadata><Row Number="0" Standard="1"><Level LevelName="Audit Settings" Name="Auditing" GUID="Auditing"/></Row><Details User="Thomas" DateTime="2019-08-15 22:11:47" Standard="1"/></metadata>

I guess that will already give you the right direction.

Note that using repository.SQLQuery returns the bincontent columns as base64 encoded!

q.

P.S. I just added this to my Inside book.