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 STEVE BELL’S ARTICLE FROM July 14, 2014 ON THE CLOUDSHERPAS SERVICENOW SCRIPTING 101 BLOG.

____________________________________________________________________________

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 wiki

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.

_________________________________________________________________________