Database view on CMDB class tables

[Prudhvi Raj4]

Hi Team,

I have a requirement to create a database view on table where cmdb_ci are associated and wan to get the field values from 5 different cmdb_ci classes like (Business Application, Application Cluster, Business Application Module, etc) and we want to show the different table field values in the same columns on the database view for example Business application table has files like (Service Offering, Service Name, Business Justification fields) The same fields we have Application cluster as well but need to do a dot walk and get those values and new display in same column instead of adding new column from each ci class fields.

Please suggest

Thanks,

Prudhvi 

[Bert_c1]

Hi Prudhvi,

I was hoping for a screenshot, so I could see the prefix values. I see for the first entry "Impacted CI - impci_incident_alert = ia_sys_id" I guess the prefix for "Impacted CI" is 'impci', I don't know what table the prefix "ia" is for.  Anyway, the order specifies how those are processed and the 'ia' table must appear before the first where clause above so the processing knows what table join. I suggest you remove the first where clause, leave the others as they appear fine. if you have those tables, I don't find "Business Application Module" or "Application" (this one related to cmdb). So I can't test.

I searched for info on 'where clause' processing, without much luck, but there is a similar DB view described here, that supports what I propose.

https://www.servicenow.com/community/platform-analytics-forum/how-can-i-improve-database-view-perfor...

Notice the first table has no where clause.

Correction: I see your image and see the tables now. I'll try to test with the 4 tables I have.

[Bert_c1]

Hi @Prudhvi Raj4

Your where clause misspell "impci_imapcted_configuration_item", should be "impci_impacted_configuration_item" in each. Otherwise, it looks good. I see 'ia' is first  with no where clause.

[Prudhvi Raj4]

Hello,

Please find below screenshot of tables I am using ia is the OOB incident alert table, actually we want to report of all the cmdb_ci assets fields (few fields)attached to incident alert table for few of the cmdb_ci classes but from below where clause I am getting no results 

[Bert_c1]

Hi,

I suggest you perform a table query on each table using a filter from your where clause, to see if any records are returned. You can also remove entries order 300 and above, and test. Then add one back at a time and test and see what records are returned as you add more tables to the database view.