Creating a Database View between Incident and Problem Tables

[simonbourne]

I have a reporting requirement that requires dta from both the Incident and Problem tables.

I want a report that will list ALL the incidents and include the Problem ID, State and Created Date where there is a relationship between the Incidents and Problems.  If  I try this by setting up a report that dot walks from the Incident table to the Problem table then it works fine....for our internal analysts.  But our external clients cannot pull back the dot walked Problem fields.  They do have access to the Problem table because the same report will run successfully on the Problem table.

My thinking is that a database view between Incident and Problem could help.  I have a Reference field on the Incident Table called Problem_ID that maps directly to the Problem.Number field, so Incident.Problem_ID = Problem.Number.

How do I express that in the Where Clause of the database view?

How do I create that Database view so that it contains ALL incidents INCLUDING those with a Problem_ID and is not restricted merely to those where the relationship exists?

[Goran WitchDoc · Accepted solution]

Hi Simon,

Not sure what happen to my last post, but do it like this, and it will work:

and you will see the difference

[Mwatkins]

Something isn't adding up. I'm confused why your external analysts can not see the dot-walked fields when viewing the incident report yet they can see those fields when viewed directly on the problem field. That doesn't make sense. Can you turn on Security (ACL) debugging and then impersonate the external analysts? Can you post a screenshot of the read ACL's in both cases?

What roles are required to access X? Using Session Debugging for ACL Security

Is it the exact same PRB that can be seen in one case and not seen in another?

[simonbourne]

I agree.   I was expecting to be able to create a report that dot-walked from Incident to Problem, but each time I tried this they just retrieved blank columns.   The end users do not have any roles and access their data via an ESS portal.

They have access to incident and Problem data via the Portal and can read/write to Incident and Problems.

My understanding is that if you have a problem.* read ACL with no associated roles then all columns in the problem table are readable by all users.   If you then apply a similar read ACL to a specific column (eg problem.number) with no associate roles then the end user (with no roles) will only be able to read that column on the Problem table.   Is this understanding correct?

The Incident read ACLs are pretty straight forward.   We restrict access to a couple of fields to protect them for internal use only

The Problem read ACLs are more complex.   There are a lot more field based ACLs

[Goran WitchDoc]

Hi Simon,

Not sure I understand what you wrote. but ACL work like this:

1. First it checks if there is a specific ACL for a field. e.g. problem.number. If there isn't any ACL, it will look for problem.* and apply that.

This means that problem.* will only be affecting those fields that doesn't have their own ACL.

If you are on a acl, you got a related link called "Show ACL Execution Plan".

If you click on this, you will see exactly which ACL is currently active for that field your ACL is pointing at.

[Aditya Telideva]

Hi Simon,

The below link could be of some help:

https://www.servicenowguru.com/scripting/promote-field-extended-table-servicenow/

Database views

Thanks,

aditya telidevara

PS: PLease do mark as helpful, if you have found so