Creating and Using a Database View in ServiceNow
Updated: Jun 14, 2022
With Aaron Elder
In this article, you will learn how to create and utilize a database view in ServiceNow.
What Do I Need?
You might be wondering what type of scenario you would need to do this for. A pretty common scenario for database views is where you want to report on a specific type of record, but information relating to that is stored in a separate table. The most common example would be relating metrics to incidents in a report.
Here’s a hypothetical situation: let's say a user tries to report on longest resolve times for incidents, and because they're just a user, try to do that against the regular incident table, but they don't get the results they want, so they reach out to you. Because you’re the awesome administrator and know exactly what table you need for that, you create a report for them.
Creating the Report
For this situation, we create a report and title it incidents with longest resolve time. Keep in mind, we have not created our database view yet. To create the report, we click next, show bars, group by the incident ID, sum our duration, and click next.
We have now just run our basic starter chart from our metric instance table. One thing to notice is that it's pulled in the incident and the problem.The next step is to filter this down.
When choosing the conditions, let’s run the following: definition table is incident. After, we'll hit the run so that it’s narrowed down. We can filter the initial chart further by the fields that are on the incident. Let's say the user wants incidents from specific assignment groups. Because this is a document ID type field that is pointing at the incidents, we're not able to dot walk back into the incidents to filter that way. Now we’re starting to hit the limits of the information that we can extract from just one table, and we need to look at doing some type of SQL join statement in order to bring multiple records together and get more robust reporting.
Incident Metric Database
Let’s go ahead and look at our database views. There is an incident metric database view already created out of the box, but for this training exercise, let's remake it (reference video).
Now that we've created our top-level view which is a virtualized database table, we're going to bring in the tables that make up the view. The first one will be our metric definition table. We need to put a variable prefix on our view tables so that when we try to reference a database field from a script, the system knows exactly which view it's supposed to pull from to get that information.
Let's say the tables that you’re pulling together have the same name on different tables. This becomes confusing for the system — that's what the variable prefix is for. Since this is a metric definition, we'll call that MD. The where clause is basically a SQL join, so where the metric definition underscore table field equals incident. Notice I added the prefix inside the where clause so that it will know exactly what field to pull from.
Now you’re just going to repeat this process with the metric instance
table. Give that a variable prefix, and make sure to specify the order because these things have to run in order. The where clause will be metric instance underscore definition equal to metric definition underscore Sys ID. The next step is to bring in the incident table, assign it a variable prefix, specify the order, and specify our join clause. The join clause metric instance underscore ID is equal to incident underscore Sys ID. After that is done, the database view is created!
Incident Metric Database
It’s time to run the report against the database view (reference video) We can see now that we have our incident fields being pulled. This happens due to the fact that we have them joined through our where clauses. There is now a more robust filtering; for example, we can filter by assignment groups if our instances have that information available.
This is a scenario where you want to use a database view — when you want to pull in data from multiple tables into your reporting solutions. Database view can also be used in a back-end script.just make sure when you use GlideRecord against your database view, you also use your variable prefixes before column names so that the system knows which view table to pull those from.
Did you find Creating and Using a Database View in ServiceNow article helpful? Are you ready to start your journey with ServiceNow? If you want to find out more information about GlideFast Consulting and our ServiceNow implementation services, you can reach out to us here.
About GlideFast Consulting
GlideFast is a ServiceNow Elite Partner and professional services firm that provides tailored solutions and professional services for ServiceNow implementations, integrations, managed support services, application development, and training. Reach out to our team here.