Left Join on DB View isn't displaying null values on right table

[donaldanderson]

I'm attempting to gather data on the gaps in our coverage for a specific System Center Agent on our Windows servers.  We're running the latest patch on Istanbul.  SNOW has this data, but I can't expose it correctly.  I'm able to return where the agent is installed, but attempts to return where it isn't installed shows every Windows server and all of the other software installed on those servers.  I'm only interested in seeing hostnames of windows servers where the System Center Agent isn't installed.

Does anyone have an article or example of how this could be successfully performed on the latest patch of Istanbul?

I've created a DB View with the following tables:

• cmdb_ci  
  • LEFT JOIN
  • Variable: ci
  • Where: ci.sys_id = csi.installed_on
• cmdb_software_instance
  • Variable: csi
  • Where: blank
• cmdb_ci_spkg
  • Variable: spkg
  • Where:  (csi.software = spkg.sys_id) && (spkg.package_name LIKE 'Microsoft System Center Virtual Machine Manager Agent%')  

NOTEs:  

• I'm unable to show null fields in the right table even when I perform a left join on the left table.
• The example in this video shows null fields exposed on the right table:   https://youtu.be/usF7fjqUozc?t=2m6s
• I've checked the docs site, but I'm still stuck:  https://docs.servicenow.com/bundle/istanbul-performance-analytics-and-reporting/page/use/reporting/t...

Significance:

• I'm concerned about our inability to spot gaps in ServiceNow data using Left Outer Joins in the native toolset.   I would absolutely hate to have to export this data to SQL to have this capability, or be forced to export this data to Excel just to gain insight  of "differential" gaps in our SNOW data.

[shivanipatel]

Donald,

We are glad you took advantage of the ServiceNow Community to learn more and to get your questions answered. The Customer Experience Team is working hard to ensure that the Community experience is most optimal for our customers.

If you feel that your question was answered, we would greatly appreciate if you could mark the appropriate thread as "Correct Answer". This allows other customers to learn from your thread and improves the ServiceNow Community experience.

If you are viewing this from the Community inbox you will not see the correct answer button.   If so, please review How to Mark Answers Correct From Inbox View.

Thanks,

Shivani Patel

[donaldanderson]

Thanks for the responses everyone!   I truly appreciate your help.   I didn't want to respond until I tried all of the various suggestions (modifying order of the joined tables in the view and using a related list filter).

Unfortunately, I still can't find gaps using a left join in a database view or using the "Software Installed" fields in a related list filter.   I tried every order and combination of where clauses.   I also enabled List V3 and all other requirements to try and use related list filters.

Again, I truly appreciate the responses

cmdb_ci_spkg Where Clause: (csi.software = spkg.sys_id) && (spkg.package_name LIKE 'Microsoft System Center Virtual Machine Manager Agent%')

This Where Clause successfully shows all instances where SCVMM agent is installed, but I need the opposite (find the gaps): using "NOT LIKE" or "NULL" or "DISTINCT" or something, but these aren't accepted in ServiceNow DB Views

I plan on meeting with our DBAs to create a SQL database and import the data via the ODBC driver.

Results: Successful join, but can't display "Null" values (despite which table is the Left join, and despite order)

"Hostname" if from cmdb_ci and "Installed on" is from cmdb_software_instance

[Arnoud Kooi]

Strange, when I replicate it with a related list filter it works.

Could you post a screenshot how you set up your condition?