Business Application Owner Stakeholders List

[Louis R Ronzitt]

Anyone have an idea on how to create a database view that would show us a list of business owners of business applications that only have the role: business_stakeholder?

In other words, we don't want anyone in the list that also have 'itil'.

Example, this was a great post/solution for another database view that @RichieP provided assistance.

Looking for a similar example but with I think the maybe the below tables, so the business_stakeholder role can be joined to the business owner (owned_by) of the business application. 

cmdb_ci_business_app

sys_user_has_role

Use Case: We have Business Owners of business appliations that only have the business_stakeholder role.

[Ankur Bawiskar]

@Louis R Ronzitt 

it would be something like this directly on Business App table and no database view required

You can dot walk to Business Owner Field on Business App table and mention the role

If my response helped please mark it correct and close the thread so that it benefits future readers.

[Louis R Ronzitt]

Thank you, and yes, that seems simple enough.  However, we then need to export to Excel and duplicate the list to within the Data Manager Attestation, We know business stakeholders cannot make an 'Additional comment (Customer visible)' or 'Work note' journal entry on a business application record as part of the attestation.  Therefore, we need to be clear about what we expect these users to do when attesting vs. a business stakeholder with 'itil', who can add a journal entry to the business application record. Therefore, the desire to isolate these users from others and have a separate list that is always accurate as users/roles change daily.

[Ankur Bawiskar]

@Louis R Ronzitt 

try this

last where clause

urr_user = usr_sys_id & urr.role = '3be5d3d1b7103300dff9fa13ee11a987'

If my response helped please mark it correct and close the thread so that it benefits future readers.

[Ankur Bawiskar]

@Louis R Ronzitt 

This worked for me

1) List giving 17 count with the filter

2) Database view also gave 17 count

Database view configuration

1st view table - cmdb_ci_business_app

2nd view table - sys_user

3rd view table - sys_user_has_role

4th view table - sys_user_role

If my response helped please mark it correct and close the thread so that it benefits future readers.