Release Automation

Dear Team,

We are using Nolio version 6.6.

Nolio tool has a default asset account "superuser". Being the highest privileged account, we have been asked to monitor it. Hence I need a single Database query that gives the details of all activities performed by "superuser". Activities right from login to the tool with username superuser to any user management /deployment/any other admin related tasks performed.

I see Audit History logs pulls this report but we need to put it on monitoring so that any such activity can be captured in run time as an event.

Could you please provide the database query for the same urgently?

Thanks and regards,

Vrunda

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

Thanks a lot Yas!

Let me try this query , keeping in mind your suggestions and I will keep you posted.

Best regards,

Vrunda

Dear Yas,

I want to request you to share this query in Oracle format as I have to give it to our DB team and unfortunately I can't covert it myself as I do not have access to DB instances. Could you please share it?

Best regards,

Vrunda

Hi Vrunda,

Sorry, I cannot provide the query for Oracle soon. Providing query is not supported.

I'd like to mention my recommendation again. I believe you don't need to use my query actually if your purpose is to monitor. Please try to run simple query like

select * from auditreportentry where username = 'superuser' order by revisionId;

 

Note: you may need to add schema name like nolio.auditreportentry. I don't know actual name on your environment.

and check the result. The result is not same format as Dashboard, but it includes all information in Dashboard. 

If you need to filter time range, you can add a condition using timestamp. 'timestamp' column is UNIX time (millisecond).

Entity Type and User Action are part of 'comments' column. Entity Name is part of 'fqn' column.

Please discuss with your DB team to get the result you want.

Thanks

Yas

Thanks Yas, it is indeed helpful.

But still I am unable to get what I am looking for.

I executed the query - select * from auditreportentry where username = 'superuser' order by revisionId;

We perform superuser actions last month and with this query I was hoping to fetch a report which will show the result of last month's activities performed by superuser (including login to ROC). But it is giving me data from last year. 

In this case, can you confirm if I am looking at the right place?

Thanks and regards,

Vrunda

Hi Vrunda,

Previous query will return all records related to superuser. So, I asked you to filter time range using timestamp column. This matter is not RA knowledge but Oracle query technique.

On the other hand, I don't think Audit History includes login/logout event.  If need, please raise an idea.

If you want further help, I recommend to contact CA Services team because your requirement is not supported scope and it seems other customers don't have any experiences so far.

Thanks

Yas

Hi Yas,

Do you know which table/query will capture user login/logout events?

I assumed audit report will do that. But still I can use audit report for other events and seprate monitoring for logon activities.

Thanks & Regards,

Vrunda

Hi Vrunda,

I think RA handles only behaviors to change design because the audit setting is called "Audit Design Changes". So, I suppose login/logout events are not recorded into database.

If you need, you can raise an idea.

Thanks

Yas

Hi Vrunda,

I tried to convert my query for Oracle and added timestamp filter. You can modify the date/time range.

I hope this is final answer.

select
revisionId as "RevisionID",
to_char(to_date('1970/01/01 00:00:00','YYYY/MM/DD HH24:MI:SS')+timestamp/(24*60*60*1000),'YYYY/MM/DD HH24:MI:SS') as "Date",
username as "User",
substr(comments,1,instr(comments,',')-1) as "Entity Type",
case instr(fqn,'/')
when 0 then substr(fqn,instr(fqn,': ')+2,instr(fqn,'id={')-instr(fqn,': ')-2)
else
case instr(substr(fqn,instr(fqn,'/',-1)),':')
when 0 then substr(fqn,1,instr(fqn,'/',-1)-1)
else substr(substr(fqn,instr(fqn,'/',-1)),instr(substr(fqn,instr(fqn,'/',-1)),': ')+2,instr(substr(fqn,instr(fqn,'/',-1)),'id={')-instr(substr(fqn,instr(fqn,'/',-1)),': ')-2)
end
end as "Entity Name",
entityId as "Entity Id",
substr(comments,instr(comments,',')+1,length(comments)) as "User Action",
newValue as "New Value",
oldValue as "Old Value",
fqn as "Full Entity Path"
from nolio.auditreportentry
where username='superuser'
and timestamp >= to_number(to_date('2018/08/01 00:00:00','YYYY/MM/DD HH24:MI:SS')-to_date('1970/01/01 00:00:00','YYYY/MM/DD HH24:MI:SS'))*(24*60*60*1000)
and timestamp < to_number(to_date('2018/09/01 00:00:00','YYYY/MM/DD HH24:MI:SS')-to_date('1970/01/01 00:00:00','YYYY/MM/DD HH24:MI:SS'))*(24*60*60*1000)
order by revisionId;

Almost when I thought I am done, I have one final query to ask - 

I am working around the following query provided by you - select * from auditreportentry where username = 'superuser' order by revisionId;

I want to use this table to fetch records for only those users who's profile is superuser.

Could you please provide me with this last query?

Thanks and regards,

Vrunda

Hi Vrunda,

Do you mean that 'superuser' is not username but role name?

If so, I think you can join authorities table. 

This is an example.

select * from auditreportentry
left join authorities on auditreportentry.username = authorities.username
where authorities.authority = 'ROLE_SUPERUSER'
order by revisionId;

I hope it helps you.

Thanks

Yas