2 Replies Latest reply on Jul 6, 2016 5:17 PM by Paul Wong

    Remove old server smartgroups

    Paul Wong

      Hello,

      I am trying to remove a lot of old server smartgroups in our environment.

      i do not want to delete a server smartgroup and break the job using it.

      Is there a way to find out all jobs that has dependancies on each smartgroup?

       

      Thanks

      Paul

        • 1. Re: Remove old server smartgroups
          Bill Robinson

          this should get you all the smart group targets of any job:

           

          WITH temp1 (group_id, path) AS

          (

                      select group_id, cast(name as varchar(1000))

                         from blgroup bg1

                         where parent_group_id = 0 and group_type_id = 4

                         UNION ALL

                    select bg1.group_id,

                               cast(bg2.path || '/' || cast(bg1.name as varchar(100)) as varchar(1000))

                         from blgroup bg1

                         inner join temp1 bg2 on bg1.parent_group_id = bg2.group_id

          )

          ,temp2 (group_id, path) AS

          (

                      select group_id, cast(name as varchar(1000))

                         from blgroup bg1

                         where parent_group_id = 0 and group_type_id = 1

                         UNION ALL

                    select bg1.group_id,

                               cast(bg2.path || '/' || cast(bg1.name as varchar(100)) as varchar(1000))

                         from blgroup bg1

                         inner join temp2 bg2 on bg1.parent_group_id = bg2.group_id

          )

          select temp1.path,j.name,temp2.path,g.name from job j

                join temp1 on j.group_id = temp1.group_id

                join job_device_group jdg on j.job_id = jdg.job_id and j.job_version_id = jdg.job_version_id

                join blgroup g on jdg.group_id = g.group_id

                join temp2 on g.group_id = temp2.group_id

                where j.is_latest_version = 1

                and j.is_saved_explicitly = 1

                and j.is_deleted = 0

          ;

          • 2. Re: Remove old server smartgroups
            Paul Wong

            It works like a charm.

            Thank you very much Bill.