Project creation events are not getting stored in Audit Log table in database

[Nallusamy Narayanaswamy]

Hi

Recently, We migrated JIRA to 8.13.x version from 8.4.2. After that, project creation events are not getting stored in Audit Log table. But, We still can see it in System -> Audit Log  page. 

How is it possible. 

I verified that, audit log events are enabled to capture the project creation. Is this bug? 

Can someone please help me? 

[Andy Heinzer · Accepted solution]

Hi,

I do not believe this is a bug, but rather I believe there was a change to the way that Jira Server/Data Center is storing some of the audit data.   This happened back in Jira 8.8 when we revamped the way the audit log works. More details about this change can be seen in the Jira Core 8.8.x release notes.

While the audit_log table still exists, in Jira 8.13.2, this table is not being updated with such information.

Instead this data is contained in an AO table now.  Try using a SQL query such as

SELECT * FROM "AO_C77861_AUDIT_ENTITY" WHERE "ACTION" = 'Project created';

to find such project creation events in the audit log.

Cheers,

Andy

[Nallusamy Narayanaswamy]

Hi @Andy Heinzer 

Thanks so much. 

The "AO_C77861_AUDIT_ENTITY" table does not have created column. I want to pull the records which created in last week. 

Created time stored somewhere else?

Thanks,

Nallu

[Andy Heinzer]

That AO table now has a column called "ENTITY_TIMESTAMP" which appears to be storing the date/time of that entry in Epoch time.  You can convert that epoch/unix timestamp back into a readable date and time value, but how you do that will likely depend on the flavor of SQL you are using. Postgresql, MySQL, MS SQL, Oracle, all seem to have slightly differing syntax for trying to convert such information in SQL.

I'm using Postgresql, so I was able to use a query such as

SELECT TIMESTAMP WITH TIME ZONE 'epoch' + "ENTITY_TIMESTAMP" * INTERVAL '1 millisecond' AS ReadableDate, * from "AO_C77861_AUDIT_ENTITY" 
WHERE "ACTION" = 'Project created';

to see those timestamps in a readable date format that humans can understand more easily.  Let me know if you're using some other type of SQL and perhaps I can investigate another way to extract that info.

Andy

[nnallusamy]

Hi @Andy Heinzer 

I m also using Postgresql. 

Here is my previous query to pull project which created in last 3 months. 

SELECT CAST (object_id AS INTEGER) FROM audit_log WHERE SUMMARY = 'Project created' and created > (CURRENT_DATE - INTERVAL '3 months')

Can you please help me to form a query for the same?

[Andy Heinzer]

Ah, well yeah, that would be a bit different in this newer version.  Try this instead:

SELECT TIMESTAMP WITH TIME ZONE 'epoch' + "ENTITY_TIMESTAMP" * INTERVAL '1 millisecond' AS ReadableDate, * 
FROM "AO_C77861_AUDIT_ENTITY" 
WHERE "ACTION" = 'Project created' 
AND "ENTITY_TIMESTAMP" > extract(epoch FROM now()-interval '3 months')*1000;

This should perform the same task, but in a slightly different way.  Since the data is using epoch time now instead of a human readable date format, I have used a few postgres functions to generate a proper epoch date for 3 months ago from the time run. 

Let me know if this helps.

Andy

[nnallusamy]

It worked. Thanks so much @Andy Heinzer 

[Diego Cañete]

Hi Andy,

By audit we need to automate a report that shows us the last actions of the administrators and the dates, for this I am using this direct query to the database, what I cannot achieve is to convert the data in ENTITY_TIMESTAMP in date and time.

So , I try this query:

SELECT a.ACTION AS ACCION,a.CATEGORY as CATEGORIA,a.USER_NAME AS USUARIO,a.RESOURCES as OBJETOMODIFICADO,a.ENTITY_TIMESTAMP as TIEMPO

FROM AO_C77861_AUDIT_ENTITY a,CWD_MEMBERSHIP b

WHERE

a.USER_NAME = b.CHILD_NAME

AND

b.LOWER_PARENT_NAME = 'jira-administrators'

AND "a.ENTITY_TIMESTAMP" > extract(epoch FROM now()-interval '3 months')*1000
ORDER BY a.ENTITY_TIMESTAMP DESC;

But, I can't get the date and time of every event using ENTITY_TIMESTAMP.

From what I saw in your comments and answers I am quite close, could you guide me or rather unblock me?

Thank you.

[Andy Heinzer]

I'm using postgres, and in that database, I have to use double quotes for the AO table names and their columns.  That might not be the case for other database types.  Not sure what database type you are using here (mysql, oracle, and Micosoft SQL are all supported in addition to Postgres for Jira Server/DC).

I adjusted your query to run and pull the epoch date into a human readable date first,

SELECT TIMESTAMP WITH TIME ZONE 'epoch' + a."ENTITY_TIMESTAMP" * INTERVAL '1 millisecond' AS ReadableDate, a."ACTION" AS ACCION,a."CATEGORY" as CATEGORIA,a."USER_NAME" AS USUARIO,a."RESOURCES" as OBJETOMODIFICADO,a."ENTITY_TIMESTAMP" as TIEMPO

FROM "AO_C77861_AUDIT_ENTITY" a, CWD_MEMBERSHIP b

WHERE

a."USER_NAME" = b.CHILD_NAME

AND

b.LOWER_PARENT_NAME = 'jira-administrators'

AND a."ENTITY_TIMESTAMP" > extract(epoch FROM now()-interval '3 months')*1000
ORDER BY a."ENTITY_TIMESTAMP" DESC;

Although this date conversion works in postgres, I'm not confident it will be the exact syntax needed for other database types (mysql, oracle, MSSQL).  If you can let me know what database type you are using, perhaps I can investigate further to see if there is some other means to convert these epoch date values into date values that are more easily understood by humans.

Andy

[Diego Cañete]

Hi Andy, thk y for reply me, We are using Oracle 19 , 

[Andy Heinzer]

Hi Diego,

I have not been able to test this out as I don't have immediate access to an Oracle database.  But I believe that there is a different conversion of these unix epoch dates that can be done with Oracle SQL. I found several different promising solutions over in https://stackoverflow.com/questions/2904256/how-can-i-convert-bigint-unix-timestamp-to-datetime-in-sql-server

I would try to start with something like this:

SELECT dateadd(ms, a.ENTITY_TIMESTAMP, '1970-01-01 00:00:00') as ReadableDate, a.ACTION AS ACCION,a.CATEGORY as CATEGORIA,a.USER_NAME AS USUARIO,a.RESOURCES as OBJETOMODIFICADO,a.ENTITY_TIMESTAMP as TIEMPO
FROM AO_C77861_AUDIT_ENTITY a, CWD_MEMBERSHIP b
WHERE
a.USER_NAME = b.CHILD_NAME
AND
b.LOWER_PARENT_NAME = 'jira-administrators'

ORDER BY a.ENTITY_TIMESTAMP DESC;

I'm curious to see if this can at least return to you these dates in a readable format.  If this does convert the dates as expected, then you could then use that ReadableDate field later as a condition in the query to look for issues in a specific time frame.

Alternatively, we could try to use a different means to compare these date values and there we could then try to convert the current time minus a 3 month interval, and then multiply this by these values to get the unix epoch value which could be compared against that entity_timestamp value. Which I think might look something like this:

SELECT a.ACTION AS ACCION,a.CATEGORY as CATEGORIA,a.USER_NAME AS USUARIO,a.RESOURCES as OBJETOMODIFICADO,a.ENTITY_TIMESTAMP as TIEMPO
FROM AO_C77861_AUDIT_ENTITY a, CWD_MEMBERSHIP b
WHERE
a.USER_NAME = b.CHILD_NAME
AND
b.LOWER_PARENT_NAME = 'jira-administrators'
AND
a.ENTITY_TIMESTAMP > ((SYSDATE - DATE '1970-01-01 00:00:00') - INTERVAL '3 months' ) * 24 * 60 * 60 * 1000 
ORDER BY a.ENTITY_TIMESTAMP DESC;

Again, I haven't been able to test this out to see if the syntax is valid here, but I suspect that the existing query's use of the

extract(epoch FROM now()-interval '3 months')

is the part that isn't working in Oracle. 

Try those two queries, I'd be interested to see what kind of error is returned if this doesn't work, as that might be helpful to better understand the formatting needed here.

Andy