Release Automation

Expand all | Collapse all

Could you please provide databse query for - "Audit History Logs" report available in RoC on Nolio Dashboard screen?

Jump to Best Answer
  • 1.  Could you please provide databse query for - "Audit History Logs" report available in RoC on Nolio Dashboard screen?

    Posted 09-06-2018 04:51 AM

    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



  • 2.  Re: Could you please provide databse query for - "Audit History Logs" report available in RoC on Nolio Dashboard screen?

    Broadcom Employee
    Posted 09-07-2018 06:08 AM

    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



  • 3.  Re: Could you please provide databse query for - "Audit History Logs" report available in RoC on Nolio Dashboard screen?

    Posted 09-07-2018 06:48 AM

    Thanks a lot Yas!

     

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

     

     

    Best regards,

    Vrunda



  • 4.  Re: Could you please provide databse query for - "Audit History Logs" report available in RoC on Nolio Dashboard screen?

    Posted 09-07-2018 07:56 AM

    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



  • 5.  Re: Could you please provide databse query for - "Audit History Logs" report available in RoC on Nolio Dashboard screen?

    Broadcom Employee
    Posted 09-10-2018 03:30 AM

    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



  • 6.  Re: Could you please provide databse query for - "Audit History Logs" report available in RoC on Nolio Dashboard screen?

    Posted 09-10-2018 05:10 AM

    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



  • 7.  Re: Could you please provide databse query for - "Audit History Logs" report available in RoC on Nolio Dashboard screen?

    Broadcom Employee
    Posted 09-10-2018 08:51 PM

    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



  • 8.  Re: Could you please provide databse query for - "Audit History Logs" report available in RoC on Nolio Dashboard screen?

    Posted 09-11-2018 11:04 AM

    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



  • 9.  Re: Could you please provide databse query for - "Audit History Logs" report available in RoC on Nolio Dashboard screen?

    Broadcom Employee
    Posted 09-12-2018 04:49 AM

    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



  • 10.  Re: Could you please provide databse query for - "Audit History Logs" report available in RoC on Nolio Dashboard screen?
    Best Answer

    Broadcom Employee
    Posted 09-11-2018 03:54 AM

    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;



  • 11.  Re: Could you please provide databse query for - "Audit History Logs" report available in RoC on Nolio Dashboard screen?

    Posted 09-14-2018 06:29 AM

    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



  • 12.  Re: Could you please provide databse query for - "Audit History Logs" report available in RoC on Nolio Dashboard screen?

    Broadcom Employee
    Posted 09-18-2018 12:44 AM

    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