3 Replies Latest reply on Jul 20, 2016 11:12 AM by Yanick Girouard

    How to find a server smartgroup with specific condition value ?

    Yanick Girouard

      I have hundreds of server smartgroups to search through in order to see if some have the wrong condition value specified. The condition in specific is evaluating a custom server property of string enumeration type. I need to find the ones that are set to the wrong enum value.


      I've looked for a blcli that can do this and I can't find one.


      I've poked in the db using the smart_group_query and object_overflow_details tables, but the query is referring to property instance values and I can't find the display value I'm looking for.


      Here is one of the smartgroups in example:


      2016-07-20 10-30-20 AM.png

      And here is the corresponding SQL query from the smart_group_query and object_overflow_details (concatenated) tables:


      SELECT mo.device_id, mo.guid, mo.bl_acl_id, mo.os_id, mo.device_type_id, mo.platform_id, mo.name, mo.description, mo.ip_address, mo.agent_major_version, mo.agent_minor_version, mo.agent_patch_version, mo.agent_build_version, mo.agent_platform_id, mo.is_repeater, mo.device_state_id, mo.agent_state_id, mo.created_by_role_id, mo.created_by_user_id, mo.modified_by_role_id, mo.modified_by_user_id, mo.date_created, mo.date_modified, mo.is_use_ip_address, mo.bl_value_id, mo.is_deleted, mo.os_release, mo.os_version,  mo.os_vendor, mo.reconciliation_identity, mo.build_environment, mo.pm_device_id, mo.staging_dir, mo.repeater_name, mo.repeater_staging_dir, mo.repeater_max_cache_size, mo.subnet_mask, mo.virtualization_manager_type, majoros.os_id major_os_id, majoros.name major_os, majoros.nsh_name FROM device mo INNER JOIN os majoros ON mo.os_id = majoros.os_id INNER JOIN os mo_OS ON (mo.os_id = mo_OS.os_id) LEFT JOIN bluser mo_UC ON (mo.created_by_user_id = mo_UC.user_id) LEFT JOIN blrole mo_RC ON (mo.created_by_role_id = mo_RC.role_id) LEFT JOIN bluser mo_UM ON (mo.modified_by_user_id = mo_UM.user_id) LEFT JOIN blrole mo_RM ON (mo.modified_by_role_id = mo_RM.role_id) LEFT JOIN prop_set_instance_prop_val cp0 ON (mo.bl_value_id = cp0.bl_value_id) AND (cp0.property_id = 2002300) LEFT JOIN prop_set_instance_prop_val cp1 ON (mo.bl_value_id = cp1.bl_value_id) AND (cp1.property_id = 2002500) LEFT JOIN prop_set_instance_prop_val cp2 ON (mo.bl_value_id = cp2.bl_value_id) AND (cp2.property_id = 2001500) LEFT JOIN prop_set_instance_prop_val cp3 ON (mo.bl_value_id = cp3.bl_value_id) AND (cp3.property_id = 2001600) LEFT JOIN prop_set_instance_prop_val cp4 ON (mo.bl_value_id = cp4.bl_value_id) AND (cp4.property_id = 2001401) INNER JOIN hrb_property_set_class cp0_hpsc ON ((cp0_hpsc.ancestor = 1002 AND cp0_hpsc.descendant = cp0.data_type_id)) INNER JOIN hrb_property_set_class cp1_hpsc ON ((cp1_hpsc.ancestor = 1002 AND cp1_hpsc.descendant = cp1.data_type_id)) INNER JOIN hrb_property_set_class cp2_hpsc ON ((cp2_hpsc.ancestor = 1002 AND cp2_hpsc.descendant = cp2.data_type_id)) INNER JOIN hrb_property_set_class cp3_hpsc ON ((cp3_hpsc.ancestor = 1002 AND cp3_hpsc.descendant = cp3.data_type_id)) INNER JOIN hrb_property_set_class cp4_hpsc ON ((cp4_hpsc.ancestor = 1002 AND cp4_hpsc.descendant = cp4.data_type_id)) LEFT JOIN primitive_bl_value lhsc_1002_5 ON (cp4.instance_value_id = lhsc_1002_5.bl_value_id) WHERE ((((cp0.instance_value_id = 2049715)) AND ((cp1.instance_value_id = 2143104)) AND ((cp2.instance_value_id = 2043506)) AND ((cp3.instance_value_id = 2043707)) AND ((upper(lhsc_1002_5.value) = upper('00:00'))) AND ((upper(mo.OS_VENDOR) = upper('Microsoft'))))) AND  mo.is_deleted = 0 AND mo.device_state_id = 50 AND mo.device_type_id IN (2, 1);


      I need to find all smartgroup contained within the same parent group (parent_group_id = 2006600) that have the value "Mensuel" instead of "Deux mois" for the ??Vid_Maint_Freq?? property condition (second line).


      Any chance a certain Bill or another fellow sql-fu master would know this ?