Hi,
First of all, CA doesn't publish the database schema of Release Automation officially. So, I don't recommend to access database directly. CA may update database schema without any notifications.
If you want to get the data from database directly, please take own risk without CA Support.
I analyzed Audit History Report on Dashboard and source code, and tried to prepare a query. However, some parts are hard corded, so it is pretty complex to align data in this query. This query is only for SQL Server. Although you may use Oracle, I don't have the environment at this stage. You may need to arrange some function and statement.
I think you don't have to use this query. If your requirement is monitoring only, it maybe ok that you check added record in auditreportentry table. If you need, you can filter with applicationId, username, timestamp, etc.
select
revisionId as "Revision ID",
dateadd(S,timestamp/1000,'19700101') as "Date",
username as "User",
SUBSTRING(comments,1,charindex(',',comments)-1) as "Entity Type",
case when CHARINDEX('/',fqn) = 0 then
SUBSTRING(fqn, charindex(': ',fqn)+2,CHARINDEX('id={',fqn)-charindex(': ',fqn)-2)
else
case when CHARINDEX(':',SUBSTRING(fqn, len(fqn)-charindex('/',reverse(fqn))+1,len(fqn))) = 0 then
SUBSTRING(fqn,1,charindex('/',fqn)-1)
else
substring(
SUBSTRING(fqn, len(fqn)-charindex('/',reverse(fqn))+2,len(fqn)),
charindex(': ',SUBSTRING(fqn, len(fqn)-charindex('/',reverse(fqn))+2,len(fqn)))+2,
CHARINDEX('id={',SUBSTRING(fqn, len(fqn)-charindex('/',reverse(fqn))+2,len(fqn)))-charindex(': ',SUBSTRING(fqn, len(fqn)-charindex('/',reverse(fqn))+2,len(fqn)))-2)
end
end as "Entity Name",
entityId,
substring(comments,CHARINDEX(',',comments)+1,len(comments)) as "User Action",
newValue as "New Value",
oldValue as "Old Value",
fqn as "Full Entity Path"
FROM [nolio].[dbo].[auditreportentry]
where username is not NULL
order by revisionId
This is the report in ROC.
The result of my query:
I hope my analysis helps you.
Thanks
Yas