Jira DB tables are empty since 2 months

[kishore singh]

Dear Team,

We have observed certain table are empty since 1.5 months in JIRA DB. following are teh tables which we are using.

Schema : DBO

Tables Name:

•         projectchangedtime
•         AO_7DEABF_SCHEDULE
•         AO_8B1069_STEP_STATUS
•         audit_log

Our expert's db team not able to find the root cause or not able to troubleshoot.

We need your support in this regards as our work is impacting.

I appreciate if you can share your support team email id on which we can do live trouble shooting with our db team over skype, teams or zoom which is highly appreciable.

Regards

Kishore Singh

966-500391330

[Nic Brough -Adaptavist-]

Why do you think this is a problem?

What is the actual problem you are having?  Not the guess at the underlying cause, but the symptoms - what can the users not do?

[kishore singh]

Yes, this is the problem. i am the user who are using backend table from DB.

unfortunately db tables are null so need your support to troubleshoot. Please share support team contact information for troubleshoot.

[Nic Brough -Adaptavist-]

We can't troubleshoot a problem you have not explained.

What is the actual problem?  Not the guess at the underlying cause, but the symptoms - what can the users not do?

[kishore singh]

Problem: We want to extract all project records for audit analysis from JIRA db and following tables are empty.

•         projectchangedtime
•         AO_7DEABF_SCHEDULE
•         AO_8B1069_STEP_STATUS
•         audit_log

[Nic Brough -Adaptavist-]

Repeating the statement does not explain it, but at least "audit analysis" gives us something to work with.

The solution is to stop reading the database, as it's not a good way to do auditing.  The tables are empty because either you've deleted the data from them, or the change logs have got past their retention date as set in the audit settings and hence been deleted.

[kishore singh]

Thanks, Can you please suggest how we can set retention date in audit settings for following tables.

•  projectchangedtime
•         AO_7DEABF_SCHEDULE
•         AO_8B1069_STEP_STATUS
•         audit_log

[Nic Brough -Adaptavist-]

It's the setting when you go to Admin -> Audit, but it does not affect those tables directly, it's more complex than that.  

[kishore singh]

Dear Team,

Is there any way to get the data from db for following tables which we used since 1 year.

also audit log present in JIRA GUI and it should store in backend db table too, so how we can identify the following tables.

[Nic Brough -Adaptavist-]

No, you're looking in the wrong place, stop reading the database.

[kishore singh]

Dear Nic,

Thanks for your response. I agree that we shall not directly access production db. We are accessing a replica db instead with read only access so no data can be deleted on this exercise.

Objective of accessing data is not to perform onetime audit that can be done by extracting data from frontend.

Our objective is to perform continuous auditing and monitoring on a monthly basis. We achieved this objective by extracting data from JIRA replica db (once a month) to our audit analytics tool where audit logic was applied and red flags regarding performance and compliance to SLAs were automated.

Since Aug’20 we are not getting the data in tables mentioned, we just wanted to know whether there has been any migration in JIRA or the data/records stored in any other tables. As per our understanding if records are present in JIRA gui for tables so it should be present in backend DB tables as well.

We need your support to understand the table information so that we can rebuild our logic for the continuous monitoring.

Empty Tables are after Aug'20:

•         projectchangedtime
•         AO_7DEABF_SCHEDULE
•         AO_8B1069_STEP_STATUS
•         audit_log

Thanks in advance for your cooperation

[Nic Brough -Adaptavist-]

You are missing the point.

The reason that you do not want to use SQL to "report" is that it's more complicated than you think it is, and hence your queries are likely to be wrong.  In this case, this is exactly what you are seeing.  It's not about the safety of reading from a replicated version, it's about it being the wrong way to do it because you don't understand the data.

Please, stop going on about the database tables, and do the work properly.

[kishore singh]

Please advise what is the proper way to extract the data in an automated and structured manner to enable setting the audit logic and perform the continuous monitoring which was stopped from Aug'20

[ziad_bakhiet]

Hello Nic,

I don't understand why you keep saying stop using the DB, there is so much data that won't be available into JIRA directly that's why we need to access the DB and retrieve the needed data. If you've a solution other than accessing the DB, go ahead and propose it.

Thanks

Ziad

[Nic Brough -Adaptavist-]

Please re-read my previous comment.

[Łukasz Sielski priv]

Hello @Nic Brough -Adaptavist- ,

Sorry but in your previous post you didn't suggest any other solution for for the proper way of exporting audit logs. 

Do you actually know any... it seem like you can only export latest 100,000 and onkly in DC version.

Best,

Lukasz

[Nic Brough -Adaptavist-]

There is no "other solution", you just read the logs as before.

[Łukasz Sielski priv]

Before they were reading logs from DB... and now DB table they were using is empty... 

[Nic Brough -Adaptavist-]

They should not have been reading the logs from the DB, for precisely this reason.

[Łukasz Sielski priv]

Hello @kishore singh ,

It seem like Atlassin moved audit log related record to the table called: AO_C77861_AUDIT_ENTITY but I have no idea if this are all data form 3 tables you mentioned or something totally different.

Best,

Lukasz 

[DKB Service GmbH]

hi

Please note that the audit table has changed from Jira v8.8 and onwards. The audit table is now called AO_C77861_AUDIT_ENTITY.

-->ok

But, at 15.05.2021 we updatet Jira from 8.5 to 8.13.6. so we expect the Audit entries are avriable in this Tabele. BUT in this Table are only entries till 15.05.2021. thats strange. Do anybody know which ist the actual DB- Table in which audit entries are saved?

[kishore singh]

Thanks for your revert, yes we found that table after lots of efforts, Can you please suggest what is alternate table for AO_7DEABF_SCHEDULE. as still we are struggling with the same.


following is the query which was running fine

SELECT
"AO_7DEABF_SCHEDULE"."ACTUAL_EXECUTION_TIME" AS "ACTUAL_EXECUTION_TIME",
"AO_7DEABF_SCHEDULE"."ASSIGNED_TO" AS "ASSIGNED_TO",
"AO_7DEABF_SCHEDULE"."COMMENT" AS "COMMENT",
"AO_7DEABF_SCHEDULE"."CREATED_BY" AS "CREATED_BY",
"AO_7DEABF_SCHEDULE"."CYCLE_ID" AS "CYCLE_ID",
"AO_7DEABF_SCHEDULE"."DATE_CREATED" AS "DATE_CREATED",
"AO_7DEABF_SCHEDULE"."EXECUTED_BY" AS "EXECUTED_BY",
DATEADD(second, cast("AO_7DEABF_SCHEDULE"."EXECUTED_ON" as bigint)/1000,{d '1970-01-01'}) AS "EXECUTED_ON",
"AO_7DEABF_SCHEDULE"."FOLDER_ID" AS "FOLDER_ID",
"AO_7DEABF_SCHEDULE"."ID" AS "ID",
"AO_7DEABF_SCHEDULE"."ISSUE_ID" AS "ISSUE_ID",
"AO_7DEABF_SCHEDULE"."MODIFIED_BY" AS "MODIFIED_BY",
"AO_7DEABF_SCHEDULE"."ORDER_ID" AS "ORDER_ID",
"AO_7DEABF_SCHEDULE"."PROJECT_ID" AS "PROJECT_ID",
"AO_7DEABF_SCHEDULE"."STATUS" AS "STATUS",
"AO_7DEABF_SCHEDULE"."VERSION_ID" AS "VERSION_ID",
"AO_7DEABF_SCHEDULE"."EXECUTION_WORKFLOW_STATUS" AS "EXECUTION_WORKFLOW_STATUS",
"AO_7DEABF_SCHEDULE"."ESTIMATED_TIME" AS "ESTIMATED_TIME",
"AO_7DEABF_SCHEDULE"."MODIFIED_DATE" AS "MODIFIED_DATE",
"AO_7DEABF_SCHEDULE"."LOGGED_TIME" AS "LOGGED_TIME"
FROM
"dbo"."AO_7DEABF_SCHEDULE" "AO_7DEABF_SCHEDULE"