CREATING A DATABASE VIEW FROM SYS_AUDIT_DELETE TABLE FILTERED FOR SYS_USER_GRMEMBER AND CMDB_REL_CI

[Randy TCTS]

Hi.  I am working on creating a database view from the sys_audit_delete table showing only data where the tablename is sys_user_grmember or cmdb_rel_ci.

I had the following conditions attempted in the where clause and they both does not apply the filter when trying out the view.  I also tried the business rule route and that did not work as well.  I am hoping to get some guidance as custom table and report solutions are out of the question.  Thank you for your help.

1. where clause field "viewprefix_tablename='sys_user_grmember'^ORviewprefix_tablename='cmdb_rel_ci'"

2. where clause field "viewprefix.tablename='sys_user_grmember'^ORviewprefix.tablename='cmdb_rel_ci'"

@tangcov 

[Mark Manders]

Why are you creating a DB view for this? A database view is to connect unrelated tables together. You are just mentioning a filter on the table. A DB view because report solution is out of the question? What is the purpose of a DB view when you aren't allowed to report on the data? This sounds like a terrible solution. What is your business case? Why this difficult path?

[Randy TCTS]

@Mark Manders .  Thank you for your reply.  The actual requirement is to provide a way to log or archive of deleted records from sys_user_grmember and ci_relationships(cmdb_rel_ci) without creating a new custom table to log the deleted record.  The logged deleted records from the two tables will be provide as a table API to an external consumer for further processing in their system.

[Mark Manders]

Then the solution is to provide access to the table. It absolutely doesn't make sense to create a database view for this. A database view is called a 'view' for a reason. It allows you to show people data from several tables through a link between the table(s). It's for reporting/visualizing. Not for integrating. Remember that it's not really a table. It's a view.

You should create a filter on the table and (if that's a consideration) use Security Data Policies for the integration user to only be able to find those specific records. I understand the requirement, but the solution you are trying doesn't really make sense. A DB view on one table is just a filter on that table (just way more expensive, performance wise). And I also don't think you can create a DB view with an encoded query (which is why yours isn't returning anything).

[Randy TCTS]

Hi Mark.  

When you indicated security data policy, do you mean creating security attributes and security data filters?  If that is the case, I am working on that one now.