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.
In my previous article on Database Views, I shared some basic information on Database Views in ServiceNow. To recap, 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.
In the second part of this post, I'll present some examples of how to use the basics outlined in the previous post to your advantage.
Additional Notes and Observations
Reference Field using Left Join
Here is an example of how to use the Left Join to create a View that includes the user table but does not constrain on the related approved_by field.
The Database View:
Name: u_left_join_no_constraint
Label: Left Join No Constraint
View Tables:
Table: cmdb_ci (Configuration Item)
Order: 100
Variable Prefix: ci
Where clause: none.
Table: sys_user (User)
Order: 200
Variable Prefix: user
Where clause (I had to do a little research here to find the Assigned To field name to use here):
ci_assigned_to = user_sys_id
Left Join: true
Click on the Try It link.
Set up the fields so it looks similar to the screen shot:
Your results should look similar to this:
Note the results: assigned_to can be blank! This is because we are NOT constraining on the assigned_to field. If you uncheck the Left Join, you will see that those records with an empty assigned_to field are excluded from the View results.
Did you notice the Manager field to the right? That field is coming from the User table. Since this is a Join, all fields available from both tables are included in the view.
Unhiding a Hidden Table
Have you ever noticed that certain tables are not available for reporting? For example, tables like Field Labels and Dictionary. Well, if you wrap them in a View, you can then see them from a report!
As an example: From the report tool you will not be able to find the sys_documentation table:
So, let's create a database view that exposes the sys_documentaton table:
Name: u_hidden_tables
Label: Hidden Tables
View Tables:
Table: sys_documentation (Field Labels)
Order: 100
Variable Prefix: field
Where clause: none.
Results (after adjusting the columns that will be visible) should look something like this:
So, now if we go to the reporting tool what can we see?
At which point you have all the columns available.
Nothing really to add here. Just a neat trick to make the table available to the reporting application. Cool, huh?
Constraining a View Using a Choice Label
Follow these steps to create a view that constrains on the choice label rather than on the choice value.
The database view:
Name: u_constraining_on_choice_label
Label: Constraining On Choice Label
View Tables:
Table: cmdb_ci (Configuration Item)
Order: 100
Variable Prefix: ci
Where clause: none.
Table: cmdb_ci_computer (Computer)
Order: 200
Variable Prefix: comp
Where clause:
comp_sys_id = ci_sys_id
Table: sys_choice (Choice)
Order: 300
Variable Prefix: choice
Left Join: checked
Where clause:
choice_name = 'cmdb_ci' && choice_element = 'install_status' && choice_label = 'Installed' && choice_value = ci_install_status
NOTE: If && does not work then use AND. It seems a bit random, but one or the other should work. The system is finnicky!
After arranging what fields are displayed your results should look something like this:
As we want to constrain the View to the Label "Installed" and not the value "1," we have to join in the Choice table, then tell the View where to find the label. Once you do that, tell the View that you want only certain values from the Choice table: the cmdb_ci table (Name field), the Field name (Element). Next, tell it that you want only the label "Installed." Finally, tie it back into the cmdb_ci table by the install_status field value. A lot of stuff!
What would happen if you marked the sys_choice record with Left Join of true? Give it a try and see what happens to your record count!
Two Table Union
Is it possible to join together two unrelated tables? Sort of. This is known as a Union in the database world. The two tables must have a common root table for it to work. Here is an example that has cmdb_ci as that root.
The database view:
Name: u_union_of_two_tables
Label: Union of Two Tables
View Tables:
Table: cmdb_ci (Configuration Item)
Order: 100
Variable Prefix: ci
Where clause: none.
Table: cmdb_ci (Configuration Item)
Order: 200
Variable Prefix: ref
Where clause:
ref_sys_id = ci_sys_id && (ref_sys_class_name = 'cmdb_ci_ip_router' || ref_sys_class_name = 'cmdb_ci_computer')
Table: cmdb_ci_computer (Computer)
Order: 300
Variable Prefix: comp
Where clause:
ci.sys_id = comp.sys_id
Table: cmdb_ci_ip_router (Router)
Order: 400
Variable Prefix: router
Where clause:
ci.sys_id = router.sys_id
NOTE: The where clause order is extremely important. You must have ci.sys_id before the table you want to join:
Give me all ci's where it is a router
Give me all ci's where it is a computer
otherwise it will flip the "ask" to be:
Give me all computers that are a ci (returns nothing!?)
Give me all routers that are a ci (returns just the routers in the final list)
Try it. Sort-of makes sense.
After arranging what fields are displayed:
Your results should look something like this:
With this example you can see how to bring in two tables with the same root (cmdb_ci) and have the unique fields from both present in the View.
I started out with our root: cmdb_ci, then constrained the View to just routers and computers. To bring in fields unique to each table, I tied in the Computer and IP Router tables to cmdb_ci via their respective sys_ids. It can be visualized like this:
This is a Union query only in a matter of speaking. The router and computer tables do not share records, but they are related in the hierarchy to cmdb_ci. In the database world, you could bring in two totally unrelated tables, but you won't be able to do that with Database Views.
Some Additional Notes
1. It is possible to embed MySQL functions into the Where Clause, but you aren't guaranteed they will work.
Example: return all records from cmdb_ci where the updated date is less than the current date/time AND the Name field contains the letters 'HP.' Both will be ignored. It just does not see them. I have not done an exhaustive look to see what does and does not work. Maybe in another article.
ci.sys_updated_on < NOW() AND INSTR(ci.name, 'HP') > 0
2. Use parentheses to separate OR collections from AND statements. This DOES function, and is very useful.
Example:
(ci.name = 'Omniwobble Server' || ci.sys_class_name = 'cmdb_ci_server') && (ci.name = 'Guest Computer' || ci.sys_class_name = 'cmdb_ci_computer')
3. Not all MySQL commands are available for Database Views. For example, I found that the "IN" statement does not appear to work. These actually throw errors.
ci.sys_class_name IN ('Computer', 'Software')
or
FIND_IN_SET(ci.sys_class_name, 'Computer, Software')
4. You will need to get creative to check for a null field. IS NOT NULL does not work.
ci.ram IS NOT NULL
Nor:
ci.ram != NULL
Nor:
ci.ram <> NULL
Oddly this failed:
ci.ram > 0
There was no error returned. Just ignored.
Some Cool Stuff
Querying Database Views from a GlideRecord? All of the View fields need to be underscore-walked if you want to reference them!
Example which I ran from Scripts - Background:
var referenceExample = new GlideRecord('u_union_of_two_tables');
referenceExample.addQuery('ref_sys_class_name', 'cmdb_ci_computer');
referenceExample.addQuery('ref_name', 'JASONHWXP'); // I picked one I knew would return info
referenceExample.addNotNullQuery('comp_ram');
referenceExample.query();
gs.info(referenceExample.getRowCount());
while(referenceExample.next()) {
gs.info('---> \nName:{0}\nClass:{1}\nRAM:{2}',
[referenceExample.ref_name,
referenceExample.ref_sys_class_name,
referenceExample.comp_ram]);
}
Results should look something like this:
Some Final Thoughts
- Watch out when joining large tables together as this will have a performance impact when pulling up data.
- This article does a great job of describing MySQL WHERE clauses: MySQL WHERE Clause: AND, OR, IN, NOT IN Query Example (guru99.com)
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-03-2016 03:58 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 17, 2014 ON THE CLOUDSHERPAS SERVICENOW SCRIPTING 101 BLOG.
14 Comments
