NOTE: MY POSTINGS REFLECT MY OWN VIEWS AND DO NOT NECESSARILY REPRESENT THE VIEWS OF MY EMPLOYER, ACCENTURE.
DIFFICULTY LEVEL: INTERMEDIATE
Assumes having taken the class SSNF and has good intermediate level of knowledge and/or familiarity with Scripting in ServiceNow.
Database Views are useful for filling an important gap in ServiceNow: joining two or more tables together. In a normal report, you can only select a single table, but Database Views allow you to pull together data from two or more tables into a single "table" to report against.
Given the importance of Database Views, I wanted to take the opportunity to share some observations I have either read about, stumbled across or just plain hit-and-miss created from scratch.
To start off, check out the introduction to Database Views in the docs.
Some Techno-babble
- Constrain / Constraint — to limit the View
- Where Clause — references the Constraint part of a SQL query. For example:
SELECT * FROM cmdb_ci WHERE install_status = 1
The "*" means to retrieve all fields. This example returns all records from the cmdb_ci table where the install_status field has a value of one.
- Join — to bring together two related tables into a single View.
- Union — to bring together two not necessarily related tables into a single View.
- Alias or Variable Prefix — a short descriptor that identifies a table and is used to designate that table's fields in a Where Clause.
- Null — Empty field. Note that this is not the same as an empty string, which has to be checked for separately.
- Operators - &&, ||, AND, OR, !=, = - Are used to chain constraints together. Note that && and AND mean the same, as do || and OR.
Caveats
For all of the examples, I use the CMDB CI table structure. Of course you can use any database available for your views. Rather than showing how to use specific fields in the view output, I will demonstrate techniques for joining or union-ing multiple tables together.
Guidelines and Tips
Let's get started! First, I will describe some important tips and guidelines.
1. Out of the box, the Database View / View Tables list view does not contain the Active and Left Join fields. I always personalize my view, using the Update Personalize View (gear button), and add those two fields.
2. Dot and Underscore work the same for writing Variable Prefixes (Aliases) into the Where Clause.
For example:
ci_sys_id = comp_sys_id
is the same as:
ci.sys_id = comp.sys_id
3. You can have a one-record View, but it cannot contain a Where Clause. If you do happen to put a Where Clause into the single record, the view engine will ignore it when displaying the results.
For example if you create a very simple Database View like the following:
The results will contain ALL of the CMDB records regardless of Install Status:
This scenario defeats the purpose of having a View as it is nothing more than a wrapper for the table you are referencing. However, you can do something a regular list view cannot: You can add the sys_id to the view! More on that later.
To really get the power out of a Database View, you must join it to other tables!
4. Commenting your code is a best practice. To do this simply surround your comment text with /* … */.
5. Another good practice is to use short alias names. This will improve readability as some Where Clauses can get quite lengthy.
6. Like with all number ordering in ServiceNow, it's a good practice to order in the 100's. This allows you to go back and easily insert new records between others.
7. If no fields are specified with each View Tables record, then the default result includes all fields. Fields with the same names will be designated by their table name first, then by their field name in parenthesis. This helps with identification but will require some thought as to which fields you will actually want to display in your view and reports.
8. All fields from a view will be available to put in a report (even the record sys_ids). The fields are displayed in the picklist with their respective actual names in parenthesis prefixed with their table aliases.
9. If you include a choice field in a Where Clause, it will need to be against the "Value" field not the "Label" field.
NOTE: Check out this ServiceNow article for more information on choice list values and labels.
The following Where Clause will fail because it uses the install_status choice list Label instead of the Value:
Instead, the Where Clause should read:
computer.sys_id = ref.sys_id AND ref.install_status = 1
NOTE: I will give an example in the second part of this article on how to get around this.
10. Back when I originally wrote this article you used to have to stack up any tables where you wanted access to a particular field in the Where Clause. For example:
cmdb.install_status > cmdb_ci > cmdb_ci_hardware > cmdb_ci_computer.form_factor
cmdb.install_status
cmdb_ci_computer.form_factor
Would allow you access to both the install_status field and the form_factor fields in the where clause.
ServiceNow appears to have corrected this issue and it is no longer necessary. All fields appear to be available at the level you are working at. So the above example would actually be accessible thusly without the need for stacking:
cmdb_ci_computer.install_status
cmdb_ci_computer.form_factor
11. Here is another example of something that works differently since I wrote the original of this article. This time is is something ServiceNow has broken. You can no longer chain the Where Clause onto multiple lines for improved organization and readability.
EXAMPLE:
This:
computer_sys_id = ref_sys_id && (ref_form_factor = 'Laptop' || ref_form_factor = 'Desktop') && ref_install_status = 1
Use to be able to be written as this:
computer_sys_id = ref_sys_id
&& (ref_form_factor = 'Laptop' || ref_form_factor = 'Desktop')
&& ref_install_status = 1
Or this:
computer_sys_id = ref_sys_id
AND (ref_form_factor = 'Laptop' OR ref_form_factor = 'Desktop')
AND ref_install_status = 1
Not any longer. You are required to one-line your Where Clause. Otherwise, the extra lines are ignored. No error is thrown. You can get an error to occur if you try it differently though:
computer_sys_id = ref_sys_id AND
(ref_form_factor = 'Laptop' OR ref_form_factor = 'Desktop') AND
ref_install_status = 1
Will generate this:
NOTE: That I switched from dot (".") nomenclature to underscore ("_"). ServiceNow implemented this to reduce the possibility of problems with reserved field names. If you go to dot for this example it will fail with field not found! I get into this a bit more in my next article.
Learn More
In my next article I will cover interesting examples about tying in a reference field using Left Join, constraining a view using a choice label and more.
Enjoy!
Steven Bell.
If you find this article helps you, don't forget to log in and mark it as "Helpful"!
Originally published on: 05-01-2016 04:13 PM
I updated the code and brought the article into alignment with my new formatting standard.
NOTE: ON APRIL 1, 2016 ACCENTURE COMPLETED THE ACQUISITION PROCESS OF CLOUDSHERPAS. AT THAT TIME THE CLOUDSHERPAS BLOG SITES WERE DOWNED FOREVER.
THIS IS THE RE-PUBLICATION OF MY ARTICLE FROM July 14, 2014 ON THE CLOUDSHERPAS SERVICENOW ADMIN 101 BLOG.
7 Comments
