Whats the simplest way to find for a group of user all the group membership?

Question

I do have one domino group (Access Control List only), lets call them Main_Group. This group includes all employees, that I want to know on which other domino groups they are member of.

Members of Main_Group:

- John Smith/ORGANIZATION
- Peter Smith/ORGANIZATION
- Jeff Smith/ORGANIZATION

Of course this list is much longer then these 3 entries.

I would look for each member in this group, in which other domino group this user is member and put this information into a CSV. The CSV should have a format like this:

UserName;DominoGroups
John Smith;Domino_Group1,Domino_Group2,Domino_Group3
Peter Smith;Domino_Group2
Jeff Smith;Domino_Group1,Domino_Group3

Whats the best way to achieve to this information? Lotus Script, any View with formula? Or is there already a notes database is doing this?

Answer 1

This code builds a list of dynamic arrays to act as key/value pairs (where each value is an array). It's built from the Group view in names.nsf. Rather than taking each group name and loading up the members, it builds it the other way round so for each member, it has an array of groups. Groups can be in other groups so it runs through each group recursively. In order to prevent loops (e.g. where group A is in group B and vice-versa) it uses the visited array which terminates that part of the search if the group has already been visited. The visited array ends up, upon completion of the recursion, the list of groups the user is in.

Building the key/value List initially would be quicker than multiple full text searches, especially if, rather than looking up one name, you're looping all user names in names.nsf as once the key/value list is built there's no need to query the database again. I've not built the loop for each user but it could be added quite easily to the getGroupsForUser function.

Code below getGroupsForUser function. Returns a formatted string of each group that a user is in. The user name is the first item.

Function getGroupsForUser(userName As String) As String
    If userName="" Then Exit function
    
    Dim ns As New NotesSession, namesDatabase As NotesDatabase
    Dim visited As Variant, groupKeyValueStore List As Variant
    Dim returnString As String, separator As String, i As Integer
    
    Set namesDatabase = ns.getDatabase(ns.Currentdatabase.Server, "names.nsf", False)
    visited = Null
    
    Call getGroupKeyValues(groupKeyValueStore, namesDatabase)
    Call searchGroupsRecursive(userName, visited, groupKeyValueStore)

    i=0
    returnString = ""
    ForAll item In visited
        If i=0 Then separator = ""
        If i=1 Then separator = ";"
        If i>1 Then separator = "," 
        returnString = returnString + separator + item
        i = i + 1
    End forall

    getGroupsForUser = returnString
End Function

getGroupKeyValues loops through the Groups view in names.nsf and creates the key/value List.

Public Function getGroupKeyValues(groupKeyValueStore List As Variant , namesDatabase As NotesDatabase)
    Dim groupView As NotesView, doc As NotesDocument, members As Variant, groupName As String
    Dim separator As String, values As Variant, i As Integer, tempString(0) As String
    
    Set groupView = namesDatabase.getView("Groups")
    Set doc=groupView.Getfirstdocument()
    
    Do Until doc Is Nothing
        groupName = doc.ListName(0)
        members = doc.getItemValue("Members")
        ForAll member In members
            If IsElement(groupKeyValueStore(member)) Then
                If IsNull(ArrayGetIndex(groupKeyValueStore(member), groupName)) Then
                    values = groupKeyValueStore(member)
                    i = ubound(values) + 1
                    ReDim Preserve values(i)
                    values(i) = groupName
                    groupKeyValueStore(member) = values
                End If
            Else
                tempString(0) = groupName
                groupKeyValueStore(member) = tempString
            End If
        End ForAll
        Set doc=groupView.getNextDocument(doc)
    Loop
End Function

searchGroupsRecursive recursively searches each group, ensuring no group is visited twice.

Public Function searchGroupsRecursive(userName As String, visited As Variant, groupKeyValueStore List As Variant) As Variant
    Dim length As Integer, userNotesName As NotesName, fullUserName As String
    Dim tempArray(0) As String
    
    Set userNotesName = New NotesName(userName)
    fullUserName = userNotesName.Canonical
    
    If IsNull(visited) Then
        tempArray(0) = userName
        visited = tempArray
    Else
        length = UBound(visited)
        ReDim Preserve visited(length + 1)
        visited(length + 1) = userName  
    End If
    
    If Not isElement(groupKeyValueStore(fullUserName)) Then Exit function

    ForAll item In groupKeyValueStore(fullUserName)
        Call searchGroupsRecursive(CStr(item), visited, groupKeyValueStore)
    End ForAll
End Function