BusinessObjects Board

Querybuilder: Find users who are only in "Everyone&quot

security CMC
#1

Querybuilder is my real achilles heel when it comes to XI R2 admin stuff so bear with me on this. I’ve done some google searching and such, but haven’t found this one. I want a list of users who are in the “Everyone” group, but who are not in any other groups. I know that we have some users like this and I need to identify them. Does anyone have a query that will give me this info? If not, can anyone confirm that it’s possible to formulate a query that would give me this result? I don’t want to spend a ton of time trying to create a query that isn’t possible.

dcartwright (BOB member since 2008-11-01)

#2

Hi,
What i know is, All users of BO Enterprise will be automatically added to the Everyone group by default.

If you want to really check who are they, You can make use of CMC and navigate to Everone group to see list of all users.

QueryBuilder:
i don’t know straight query but this can be achieved in 2 steps

  1. Get the group memembers SI_ID from query1
select SI_GROUP_MEMBERS from ci_Systemobjects where si_kind in ('UserGroup') and SI_NAME='Everyone'
  1. Get the user names by providing the SI_ID values from the above query
select SI_NAME from ci_Systemobjects where si_kind in ('User') and SI_ID in (12,1)

where 12, 1 are SI_ID’s of user’s from quer1

BOB Downloads :
There is a utility or code to extract complete list of users and the list of groups each user is member of.Check this Bob Downloads.

Thanks
-Satish

forgotUN (BOB member since 2006-12-13)

#3

Thanks for the reply.

I may not have been clear about what I was looking for. I realize all users are members of the Everyone group. What I’m trying to find are users who are members of the Everyone group and who are not members of any other group.

dcartwright (BOB member since 2008-11-01)

#4

Hi,
If that is the case, i would be inclined to go for Utility in BOB downloads which lists all users by Groups through Which you easily identify the users who are not part of any group.

Thanks
-Satish

forgotUN (BOB member since 2006-12-13)

#5

Or a simple trick. Run the macro to get user groups. Do not iterate through the group. there is a for loop to iterate through all the groups. Without for loop this query bring all users with the first available group. So for all user where you see group as everyone group will be the users who are not a part of any other group. Works fine i have tested it.

I used the below code( remove for loop) Call group name for first group ID to generate a txt file and then open it in xls.

or you can generate your own code just call group ID one in the SI_USERGROUPS.

'Build the query
    Query1 = "SELECT top 10 SI_ID,SI_NAME,SI_USERGROUPS,SI_LASTLOGONTIME,SI_EMAIL_ADDRESS FROM CI_SYSTEMOBJECTS WHERE SI_KIND = 'User' order by SI_ID"
    
    ' Execute the query
    Set retvals = iStore.Query(Query1)
  
   Call LogMessage(VbaLog, " Saving the query results into extract file ")
     ExtFileNum = FreeFile()
    Open DataExtract For Append As #ExtFileNum
     J = 0
     For Each retItem In retvals
         J = J + 1
             UserId = retvals.Item(J).Properties(1).Value
             UserName = retvals.Item(J).Properties(2).Value
             groupname = getGroupName(retItem.Properties(3).Properties.Item(1))
             Logon = retvals.Item(J).Properties(4).Value
             Email = retvals.Item(J).Properties(5).Value
             
         TableRec = UserId & "||" & UserName & "||" & groupname & "||" & Logon & "||" & Email
         Print #ExtFileNum, TableRec

     Next retItem
     
    Close #ExtFileNum
    esession.Logoff

Public Function getGroupName(GrpId) As String
    
   On Error Resume Next 'GoTo ErrHandler
   dbug = "Finding Universe"
        Dim Query4
   
        Query4 = "select si_name from CI_SYSTEMOBJECTS where si_id = " & GrpId
        Dim Result4 As InfoObjects
        
        Set Result4 = iStore.Query(Query4)
    
        Dim GrpName
        Dim oReport3
        For Each oReport3 In Result4
        GrpName = oReport3.Properties.Item("SI_NAME")
        Next
        
        getGroupName = GrpName
    
  Exit Function

XXX :madagascar: (BOB member since 2007-09-04)