Need help on MSSQL query to check if s_qos_data source name is display in USM groups

[Guan Hua Chee]

Hi,

 

Need help on MSSQL query to check if s_qos_data source name is display in USM groups.

As there is too many device to check I hope use the database to query out a simple report to validate maybe with below column.

 

Tables    s_qos_data                
Column    s_qos_data.source   s_qos_data.ci_metric_id
                    
Tables    cm_configuration_item_metric                
Column    cm_configuration_item_metric.ci_metric_id    cm_configuration_item_metric.ci_id            
                    
Tables    cm_configuration_item                
Column    cm_configuration_item.ci_id     cm_configuration_item.dev_id             
                    
Tables    cm_device                
Column    cm_device.dev_id    cm_device.cs_id            
                    
Tables    cm_computer_system                
Column    cm_computer_system.cs_id    cm_computer_system.name            
                    
Tables    cm_group_member                
Column    cm_group_member.cs_id    cm_group_member.grp_id            
                    
Tables    cm_group_name                
Column    cm_group_name.grp_id    cm_group_name.name

[Gene Howard]

This is not exactly what you are looking for but it might help you get started:

 

SELECT        G.name AS GroupName, GM.cs_id, CS.cs_id AS CS_CS_ID, CS.name AS CS_NAME, qos.source, qos.target
FROM            S_QOS_DATA AS qos INNER JOIN
                         CM_CONFIGURATION_ITEM_METRIC AS CIM INNER JOIN
                         CM_CONFIGURATION_ITEM AS CI ON CIM.ci_id = CI.ci_id INNER JOIN
                         CM_DEVICE AS D ON CI.dev_id = D.dev_id INNER JOIN
                         CM_COMPUTER_SYSTEM AS CS ON D.cs_id = CS.cs_id ON qos.ci_metric_id = CIM.ci_metric_id LEFT OUTER JOIN
                         CM_GROUP_MEMBER AS GM INNER JOIN
                         CM_GROUP AS G ON GM.grp_id = G.grp_id ON CS.cs_id = GM.cs_id
ORDER BY g.name, CS_CS_ID