Database View for SQL Server Reporting

[Goran]

Hi

A client of mine requires information on their SQL Server installations to be gathered and then reported on in a particular manner. Discovery was set up and is collecting all the information they need into various parts of the CMDB but they now need it to be all combined into one report. I've therefore begun setting up a database view in order to link all the relevant tables (six of them) together so that I can then report on it easily.

Here are all the tables involved, their given variable prefixes on the database view and the "where" clauses showing how I planned to link them:

• MSFT SQL Instances [cmdb_ci_db_mssql_instance]
  • mssqlinst
  • No "Where" clause
• CI Relationships [cmdb_rel_ci]
  • cirel
  • cirel_parent = mssqlinst_sys_id && cirel_child = winsvr_sys_id
• Windows Servers [cmdb_ci_win_server]
  • winsvr
  • No "Where" clause
• Software Installations [cmdb_sam_sw_install]
  • swinst
  • swinst_installed_on = winsvr_sys_id && swinst_discovery_model = swdiscmod_sys_id
• Software Discovery Models [cmdb_sam_sw_discovery_model]
  • swdiscmod
  • swdiscmod_model = swmod_sys_id
• Software Models [cmdb_software_product_model]
  • swmod
  • No "Where" clause

Unfortunately, after having set all of this up (and taken care of a few errors) I end up with a report returning no records. I've checked manually and we should definitely be getting results so there has to be something wrong with the database view setup.

I then went ahead and tested linking tables pairs (using the above "where" clauses) to make sure there were no issue with my joins and I succeeded; however, as soon as I tried linking three tables together, I encountered two sets that would also not return any records no matter how I set them up (by this I mean the order value on the table views). These sets were:

• cmdb_ci_db_mssql_instance, cmdb_rel_ci & cmdb_ci_win_server
• cmdb_ci_win_server, cmdb_sam_sw_install & cmdb_sam_sw_discovery_model

cmdb_ci_db_mssql_instance

cmdb_rel_ci

cmdb_ci_win_server

So my question is where am I going wrong? Logically I should definitely be able to link these six (6) tables together and have results returned. All this testing was performed using the System Administrator account so I doubt there would be any issues with ACLs but let me know if I'm overlooking anything on that front.

Thank you in advance for any feedback on this matter.

[BALAJI40]

After you configured all the tables, you are getting empty data?

[Goran]

Hi Balaji

At this point in time I cannot get the database view working without errors no matter the table order I use. The only way I had it working was by altering the "where" clauses to the below (although this did return no results/an empty data set):

• cirel_parent = mssqlinst_sys_id && cirel_child = 'cmdb_ci_win_server'
• swinst_installed_on = winsvr_sys_id && swinst_discovery_model = 'cmdb_sam_sw_discovery_model'
• swdiscmod_model = 'cmdb_software_product_model'

[BALAJI40]

Hi Goran,

If you select limited number of fields to populate, you have to be careful one point. The matching records to be select in list of needed columns

i need to create view b/w incident and problem.

In incident, caller, short description

problem: number, state

match criteria: inc_problem_id = pro_sys_id.

if you create view then it will show empty dataonly

so, in incident: caller, short description, problem_id (then only it will show the data).

please check your matching condition fields are there in table selected list.

also, every time you need to click try it in related lists in database view, if you create any relation between the table. it will populate error, if it has

[Goran]

Hi Balaji

All fields referenced in the "where" clauses (including sys_id) are selected in the view field lists for their respective tables and I've made sure to exclude superfluous fields so as to not over-burden the report. So from that point of view I think I've got this covered and that it shouldn't be the source of my problem. Thank you for checking on that however, it was something I had to fix at one point in the past.