DX Infrastructure Management

Expand all | Collapse all

Need help on MSSQL query to check if s_qos_data metrics is map to which device in the USM groups

Jump to Best Answer
  • 1.  Need help on MSSQL query to check if s_qos_data metrics is map to which device in the USM groups

    Posted 09-28-2018 10:11 PM

    Hi,

     

    Need help on MSSQL query to check if s_qos_data metrics is map to which device in the USM groups

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

     

    Tables    s_qos_data                
    Column    s_qos_data.source    s_qos_data.table_id    s_qos_data.qos    s_qos_data.target    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            



  • 2.  Re: Need help on MSSQL query to check if s_qos_data metrics is map to which device in the USM groups
    Best Answer

    Posted 09-30-2018 11:07 PM

    I hope it helps as an example.

     

    select ccs.name, sqd.qos, sqd.probe, sqd.robot, sqd.source, sqd.target from S_QOS_DATA sqd

    INNER JOIN CM_CONFIGURATION_ITEM_METRIC ccim on ccim.ci_metric_id = sqd.ci_metric_id

    INNER JOIN CM_CONFIGURATION_ITEM cci on cci.ci_id = ccim.ci_id

    INNER JOIN CM_DEVICE cmd on cmd.dev_id = cci.dev_id

    INNER JOIN CM_COMPUTER_SYSTEM ccs on ccs.cs_id = cmd.cs_id

    order by ccs.name

     



  • 3.  Re: Need help on MSSQL query to check if s_qos_data metrics is map to which device in the USM groups

    Posted 10-02-2018 02:32 PM

    Tweaked it so its more organized, sorted. Not sure of the column header names but you can adjust:

     

    select sqd.origin, sqd.robot, sqd.probe, sqd.source as QoS_Source, ccs.name as QoS_Device_Name, sqd.qos QoS_Name, sqd.target from S_QOS_DATA sqd

    INNER JOIN CM_CONFIGURATION_ITEM_METRIC ccim on ccim.ci_metric_id = sqd.ci_metric_id
    INNER JOIN CM_CONFIGURATION_ITEM cci on cci.ci_id = ccim.ci_id
    INNER JOIN CM_DEVICE cmd on cmd.dev_id = cci.dev_id
    INNER JOIN CM_COMPUTER_SYSTEM ccs on ccs.cs_id = cmd.cs_id

    order by sqd.origin,sqd.robot, sqd.probe, ccs.name, sqd.qos,sqd.target



  • 4.  Re: Need help on MSSQL query to check if s_qos_data metrics is map to which device in the USM groups

    Posted 10-02-2018 10:59 PM

    Thank you for getting this improved. Appreciate !



  • 5.  Re: Need help on MSSQL query to check if s_qos_data metrics is map to which device in the USM groups

    Posted 10-02-2018 11:09 PM

    Add the latest QOS values and its timestamp (join with S_QOS_SNAPSHOT table).

    It may help to track which QOS is active or decommissioned.

     

    select

    sqd.origin,

    sqd.robot,

    sqd.probe,

    sqd.source as QoS_Source,

    ccs.name as QoS_Device_Name,

    sqd.qos QoS_Name,

    sqd.target,

    sqs.samplevalue,

    sqs.sampletime

     

    from S_QOS_DATA sqd

     

    INNER JOIN CM_CONFIGURATION_ITEM_METRIC ccim on ccim.ci_metric_id = sqd.ci_metric_id

    INNER JOIN CM_CONFIGURATION_ITEM cci on cci.ci_id = ccim.ci_id

    INNER JOIN CM_DEVICE cmd on cmd.dev_id = cci.dev_id

    INNER JOIN CM_COMPUTER_SYSTEM ccs on ccs.cs_id = cmd.cs_id

    INNER JOIN S_QOS_SNAPSHOT sqs on sqs.table_id = sqd.table_id

     

    order by sqd.origin,sqd.robot, sqd.probe, ccs.name, sqd.qos,sqd.target