CA Service Management

Expand all | Collapse all

Stored Query to find Groups with no Active Members

  • 1.  Stored Query to find Groups with no Active Members

    Posted 03-13-2019 11:08 AM

    I am looking to create a stored query to display all active groups that don't have any members in the group.



  • 2.  Re: Stored Query to find Groups with no Active Members

    Posted 03-13-2019 12:33 PM

    Hello,
    I don't think, that this is possible as a stored query.
    I would be able to provide you with the following solution:
    Adding a member count column to the grp object, and display this column in the group list form.
    This column would be sortable, that means , when sorting ascending, you would see all groups without members (member count  = 0 ) at the top of the list.
    A bit tricky, I know, but the only chance I am aware, to find these groups within the Gui.
    Using reporting capabilities would be another way to go.
    Let me know, if you're interested
    Regards
    .....Michael



  • 3.  Re: Stored Query to find Groups with no Active Members

    Posted 03-13-2019 12:56 PM

    Yeah our client has a report that can run this query, but we are having connection issues to our BI servers so they cannot access it. We were looking for a quick turn around for this, so we went into looking at a stored query but it was providing us some troubles. I will mention this to my client as an option though and see what they think.



  • 4.  Re: Stored Query to find Groups with no Active Members

    Broadcom Employee
    Posted 03-13-2019 12:38 PM

    DArceyTaylor.....

     

    This won't help with the scoreboard query, but you can run this command on the SDM server:

     

    bop_odump domsrvr cnt "type=2308" last_name member_list.length

     

    You will get a listing from which you can identify which groups have 0 members.



  • 5.  Re: Stored Query to find Groups with no Active Members

    Posted 03-13-2019 12:58 PM

    I was able to also query the solution in SQL manager, but our clients don't have the access to SQL manager or to the server. I don't think that they want to rely upon someone else to get these results.



  • 6.  Re: Stored Query to find Groups with no Active Members

    Posted 03-13-2019 02:22 PM

    Another idea: You might be able to achieve the same result( getting a list of groups and their member count, unfortunately not ordered, I assume) by adding the count of members to one of the legacy reports of the standard group/contact list,summary or detail...
    Advantage: no change in the object layer, just some addings to the rpt file, deployable on the fly.
    Regards
    ....Michael



  • 7.  Re: Stored Query to find Groups with no Active Members

    Posted 03-13-2019 02:46 PM

    If push comes to shove, you could also look at an option of getting the report via a ODBC connection that connects to SQL server and pulls the report into a CSV or XLS file.

     

    Hope this helps somewhat?

     

    ===

    Kind Regards,

    Brian