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]

also one more point all tables have read acl on those tables?

[Goran]

Hi Balaji

They do yes.