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.

[Jon Barnes]

I believe order matters here, so you would want to make sure cmdb_ci has a later order than cmdb_software_instance. cmdb_software_instance should have the lowest, then you could put cmdb_ci_spkg, then finally, cmdb_ci.

If I have understood your requirement correctly...

[Arnoud Kooi]

I think you should do the LEFT JOIN on the second and third table.

Be aware this is a SN implementation of joins, it might defer from SQL.

For example change the dots to underscores in the where clause csi.software to csi_software

And maybe keep the DB view generic and filter the result in the list condition builder.

Also if you have enabled list v3 you might wanna look into related list conditions

Maybe you don't even need a DB view using this feature.

[Arnoud Kooi]

I did some testing, I think you can achieve it with a related list filter.

Example query:

https://INSTANCE.service-now.com/cmdb_ci_list.do?sysparm_query=sys_class_name%3Dcmdb_ci_win_server%5...

[Mwatkins]

I agree with Arnoud, order definitely does matter and you might be able to get a Database view to work but I have had inconsistent results going that way, especially for complex use cases. Database View left joins are definitely not a 1:1 behavioral mapping to SQL left joins. If I were you I would go with a generic Database view and build the complex criteria in the filter builder or I would Add related list conditions, like Arnoud suggested. Here is the same related list URL that Arnoud posted (I think) without the first part so the community won't convert it to a   hyperlink, just copy/paste it on to the end of your instance URL:

/cmdb_ci_list.do?sysparm_query=sys_class_name%3Dcmdb_ci_win_server%5ERLQUERYcmdb_software_instance.installed_on%2C%3D00%2Cm2m%5EnameSTARTSWITHMicrosoft%20System%20Center%20Virtual%20Machine%20Manager%20Agent%5EENDRLQUERY&sysparm_list_mode=grid

That being said, here are a couple cases where Left Join was employed successfully for customers:

Database View not working after upgrade

Database View Unknown column error