Create Databaseview from 2 tables

[dominikgintsber]

Hello.

I need to create a database view from 2 tables to achieve a table where I see the "installed software", "computer" and "assigned to".

Table 1 = cmdb_ci_computer -> In this table I have the computers with the "name" and "assigned_to"

Table 2 = cmdb_sam_sw_install -> In this table I have the "display_name" of the software and the "installed_on" which is the name of the computer.

I have tried to create a view by adding these 2 tables and a where clause see screenshot. But the view remains empty.

The View:

[Brad Bowman · Accepted solution]

I don't have SAM in my instances, so I can't verify this, but if the installed_on field on the SAM table shows the name of the computer, then that is likely a reference to the cmdb_ci or cmdb_ci_computer table, which means that the value stored is the sys_id of the computer record.  You can verify this by looking at the Dictionary Entry for this field on a SAM record.  If you change your Where clause to swinstall_installed_on=Comp_sys_id the view should work.