nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

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

Nallusamy Narayanaswamy

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?

Screen Shot 2021-02-24 at 3.38.09 PM.png

Can someone please help me?

Accepted answers

Andy Heinzer

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

All comments

Andy Heinzer

Hi,

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?

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<span style="border: 0px; margin: 0px; padding: 0px;">, '1970-01-01 00:00:00'</span>)&nbsp;as&nbsp;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 '<span style="border: 0px; margin: 0px; padding: 0px;">1970-01-01 00:00:00</span>') - 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

Welcome!

It looks like you're new here. Sign in or register to get started.

Quick Links

Find more posts tagged with

server

jira

jira-server