How To: Bulk Export SQL queries of database groups based on specific keywords

Applies To:

GroupID 10 - GroupID Management Shell

Business Case:

We have a large number of Smart Groups in GroupID that use an SQL database as an external data source for membership update. We want to filter and bulk export groups with their queries to a CSV file based on some specific keywords in the query; for example, filter all groups if the query contains a specific department name.

Methodology:

We can use GroupID Management Shell to filter and bulk export database groups and their SQL queries to a CSV file.

Steps:

1. Launch GroupID Management Shell.

2. Let’s say that you want to filter all database groups with the Sales department in their query. For that, use the following cmdlet in GroupID Management Shell:

   Get-Group -LdapFilter "(imsggroupidconnectionstring=*)" | Where {$_.Criteria -like "*Sales*"} | Select DisplayName, Criteria

   
   This cmdlet will get all the database groups that have Sales in their query.
   
   

3. To export these results to a CSV file, simply use the export CSV switch in the pipeline with the above cmdlet, as shown below:

   Get-Group -LdapFilter "(imsggroupidconnectionstring=*)" | Where {$_.Criteria -like "*Sales*"} | Select DisplayName, Criteria | Export-CSV "C:/Smartgroup.csv" -NoTypeInformation

   
   You can change the attributes to export; for example, Name, sAMAccountName, etc. The above cmdlet will create a CSV file named Smartgroup in the root C drive. You can change the path of this file as needed.

Reference:

GroupID Management Shell Guide