A Favorite from Query Builder – Active User List

88 thoughts on “A Favorite from Query Builder – Active User List”

1. Hi,

   Thank you.. which is very useful, but i have an additional requirement on this, in case if i would like to check from past three months who all users are not using the Business objects. what all objects we need to consider. in case of Licence usage, we need to know this on priority basis.
   Kindly help me on this..

   Thank you..

2. Hey Rao, how about this?:
   
SELECT TOP 3000
si_id,
si_name
FROM
CI_SystemObjects
WHERE
si_kind = 'User' AND
si_lastlogontime <= '2010-01-01'

   Make sure that it returns less than 3000 records (if not, you need to increase the number). Be patient I don't think that the property "si_lastlogontime" is indexed. Make sure that the date is set to your requirements. Please note, there should be no space between the "<" and the "=" characters, thank WordPress and our WP theme for trying to beautify the code's appearance.

3. Can any one help me to fetch the list of users those who have Scheduled rights in BO XI r2.

4. Hi,

   Plz tell me how to fetch the list of users who have rights for scheduling Reports in the BO XI r2.
   Plz provide me the Query for the same so that i can run it in the Query Builder to get the results.

   Thanks.

5. Hi Madhu and Uday, I am not 100% certain if what you seek is possible with Query Builder. It would require a VERY complex query to traverse a user to all user groups to the rights on those groups to detect if anywhere along the line the right for “scheduling” is granted or denied. Unfortunately, I don’t have the time to build that query for you. In fact, you might want to you look at the free “Security Viewer” tool from BO to see if this meets your needs. Search this site for that term and you will find some guidance on getting the tool.

6. could you tell me that how to find the In-Activer user list.

7. Great question! This is not really possible with Query Builder alone. The reason is the BOE SDK query language does not support “minus” or “not exists” functionality. However, you could use Excel to get what you want with a little formatting and some Excel know how.

   1) Query all users
   2) Query all active users
   3) Format both lists into Excel and the difference between #1 and #2 is the list of inactive users!

8. is it possible to get company name of the user?

9. Hi Kumar, yes, if the company is stored in the description field of the user (si_description). Or perhaps it could be derived from the email address (si_email_address). In either case, both fields are not mandatory and flexible.

10. I have a requirement where I need to generate the list of users accessing a particular universe in BO XIR2 through Macros or Query Builder. Kindly help me on this.

11. Chaithanya, since you said “users accessing a particular universe” so I interpret that you want to know who has accessed a universe and not who has access to a universe. The statements seem similar, but one deals with historical usage of the system and the other deals with security access. For historical usage of the system you must have auditing enabled and you will have to query your systems audit tables.

12. Thanks Julian for the prompt reply! I was aware of retrieving this data by querying the audit tables. But the issue is we did not have our auditing enabled from the beginning; hence do not get the complete data. Is there any other way possible? Maybe through Query Builer or even a code in MACROS?

13. Sorry Chaithanya, if you are after data regarding usage of a universe then auditing data is your only option.

    If you want to know which users have access to a universe then this should be accessible through either Query Builder or the SDK.

14. Could you tell me how to find out the list of users who have access to a universe through Query Builder?

15. Requirement: List of all CReate license usage over last 3 ( or 6 months).

    How can we achieve this either using the Activity Universe or the Query Builder?

    Thanks for any help on this.

16. Hi Arnie, I have no idea what “create license usage” is? Please explain.

17. What I mean is how can I find the list of licenses being used in the past 6 months.Basically want to find how many licenses are being used currently, how many have been new licenses in say last 6 months etc.

    Hope I’m clearer now.

    Thanks a lot.

18. Perhaps my knowledge of BO licensing is outdated, but I don’t think that it is possible to track used licenses or new licenses as you are asking. BOE has either named user licenses or concurrent user licenses.

    If you have a named user license then you could track license consumption by looking at named user counts, you could also query users by creation date to see how many new users have been added in the last 6 months (or any date range).

    If you have concurrent licensing then things get more difficult to track. Adding new users to the system does not consume a license seat, only increasing user concurrency does. If you want to measure user concurrency trends than either you need to run this articles query at regular frequencies, or you need to mine your CMS auditing data (log on, log off audit events) and extract this information from the audit transactional data.

19. Thanks a ton Julian. You have been a great help.

20. How to get the list of active accounts in BOXi r2

21. Hi raghavendhar, I don’t understand the difference between the “active user” query described in the article (which should apply to Business Objects XI Release 2) and the “active accounts” that you desire. Please explain.

22. Hello

    I am trying to find out the refresh time of each report in BO ….

    Thanks for your help

    Guillaume

23. Hi Guillaume, as you may know refresh time will vary based on parameters and data source conditions. Refresh times can only be observed in auditing data on the Document/Report Refresh event (#19). Take a look at your “AUDIT_EVENT” table and look for event_type_id = 19. If you don’t have this data then you need to go and enable auditing on your servers through CMC.

24. Hi there,
    Do you know if there is anyway you can differenciate a SDK connection to the CMC from others using the Quey builder?

    Thanks
    Bob

25. Sorry BOB, I don’t think there is any way. Query Builder is using the SDK too and I don’t think that the CMS differentiates sources of connections… Do you see any differences when you change this query to “SELECT *“?

26. How to get list of universes and reports used by a particular user through querybuilder only.

27. Hi Krishna, Query Builder cannot tell you which reports a user is using. In Business Objects this is what the auditing data is for. Please try something like:
    SELECT * from Audit_Event WHERE user_name = 'Administrator'
    Just replace your desired user name for “Administrator”.

28. Hi,
    I need to export a all the scheduled reports of a particular user into a biar file.
    Can u pls help me with the query to find a solution for the same?

    Thanks,
    San

29. Hi Julian,

    In one of the responses above you have written:
    “If you have a named user license then you could track license consumption by looking at named user counts, you could also query users by creation date to see how many new users have been added in the last 6 months (or any date range).”
    1. How to find the named user count?

    2.SELECT SI_NAME, SI_USERFULLNAME, SI_CREATION_TIME, SI_LASTLOGONTIME
    FROM CI_SYSTEMOBJECTS
    WHERE SI_KIND = ‘User’ AND SI_CREATION_TIME>= ‘2010.01.01’
    Does the above query give me how many new users have been added since beginning of last year?

30. Hi,

    1)Can i get a list of users along with permission details at report level.

    2) Or can i get the report details along with user and permission details.

31. Hi sunayana, it may be possible but it would be a very complex set of queries. Perhaps a custom built SDK could do this for you, but honestly, you might try to see if this has already be developed by someone for purchase, because it ain’t easy.

32. Julain,

    – A list of all active BO users who use *** universe in R2
    – A list of all the reports, including the ones which are stored in the favorites folder. With the last update date and owner details.

    Could you please help me out of this by providing query to extract the data?

33. Hi cherry,
    1) “Users USING a universe” can be interpreted as an auditing query. If this is true, then you need to have auditing enabled and you need to query the auditing data for this information. I don’t have the query handy right now, unfortunately. Please take a look at your auditing tables and try to build a query for this. I encourage you to return and share what you develop.
    2) This can be taken from Query Builder. Using the Administrator information to login to QB execute the following query:
    SELECT TOP 3000
si_id,
si_cuid,
si_name,
si_owner,
si_update_ts
FROM
ci_InfoObjects
WHERE
si_kind = 'WebI'

34. I forgot to add that “TOP 3000” will restrict the query to the first 3000 records. If you get 3000 records returned then you know that you are not getting all of the records that you could get. You could increase the number, but beware that Query Builder output can become corrupt at higher numbers of records. You might need to look for other ways to batch the results by adding a “WHERE” clause.

35. here i’m not using any query builder.
    I’m trying this from auditing which is enabled.

    I’m not sure which objects to drag and which objects to drag into filters.

    I do have this query
    SELECT TOP 3000
    si_id,
    si_cuid,
    si_name,
    si_owner,
    si_update_ts
    FROM
    ci_InfoObjects

    but it is popping up the complete list of users.

    I’m looking for users updates on their favorites folders.

    I know this is hard to bring, that is why i’m asking experts help. 🙂

    Please took off me from this

36. Hi Cherry, it seems that my query got truncated before, I corrected it in the original comment above.

    I understand that your requirements are to generate a list of all documents owned by the users and stored in their “My Favorites” folders. There is no one query that can give you this information unless you can decidedly filter out all public reports based on owner. For example, in many systems I work with the following query returns all reports owned by users and stored in their “favorites” folders:
    
SELECT TOP 3000
si_id,
si_cuid,
si_name,
si_owner,
si_update_ts
FROM
ci_InfoObjects
WHERE
si_kind = 'WebI' AND
si_owner != 'Administrator'

    However, this only works because Administrator is the owner of EVERY public document, so process of elimination works in our favor.

37. Hi Juli,
    I want to query the users who belongs to two different groups. I used the below one but it does not return any data:
    select * FROM CI_systemObjects WHERE
    si_id= AND si_id=

    It gives me for single group but when I add another group it does not give any values. I have made sure that a user is existing in both the groups.

    Thanks in advance!

38. Hi Praba, you are crossing in the area of relationship queries. Without getting into the details of the syntax, let me offer an example taken from the Query Builder user guide that we sell on this site:
    SELECT si_name FROM CI_SystemObjects WHERE
Children("SI_NAME='UserGroup-User'","SI_NAME='Administrators'")

    All that you need to do is to add an additional filter for the second user group such as:
    SELECT si_name FROM CI_SystemObjects WHERE
Children(" SI_NAME='UserGroup-User' ", " SI_NAME='UserGroup1' ") AND
Children(" SI_NAME='UserGroup-User' ", " SI_NAME='UserGroup2' ")

    Please let me know if this worked for you. Good luck!

39. Hi Juli,
    I really thank you for this!
    However it works only for Enterprise, I apologies if I should have given here in my last post. My setup is AD users mapped from AD group (they are imported!), but still as we are querying CMS repository, I believe we should be able to get this info. Also I am not getting any error messages when I query for two AD groups but no hits are coming.

40. Hi Praba, I had not thought it would not work with AD users. I will have to find a system to test on to work with this. Does the relationship query work when you put just one user group name, removing the second clause?

41. Hi Juli,
    It worked fine and I am sure I should have missed something in the last time. Thanks a lot for the Query and Tip.

    Thanks,
    Praba

42. Hi Julian,

    We have a requirement to find number of users logged in/created in the system each year.

    I tried to shuffle the queries given above, but nothing worked.

    Could you please suggest something.

    Thanks.

43. Hi Chandra, you have just expressed two different and distinct requirements.

    1) Find the number of users logged in each year.
    You can merely query the user records and filter on the field “si_lastlogontime”.
    SELECT COUNT(SI_ID) FROM CI_SystemObjects WHERE si_kind = 'User' and si_lastlogontime > '2011-01-01-00-00'

    2) Find the number of users created each year.
    Again look at the “user” records and filter on the field “si_creationtime” this time.
    SELECT COUNT(SI_ID) FROM CI_SystemObjects WHERE si_kind = 'User' and si_creationtime > '2011-01-01-00-00'

    *Please note, this was written without double-checking the syntax, please let me know if there are an errors.

44. Thanks Julian, the query given by you really helped with slight modification in syntax.

    I used term created/logged in because in our system we have users from SAP system as well which we don’t create.

    These users get created automatically in the system with their first login.

    So the idea was to get count of users created(enterprise user) or logged in (SAP users)in a particular year.

    Now, here is the query I am using to get this number:

    SELECT COUNT(SI_ID) FROM CI_SYSTEMOBJECTS
    WHERE SI_KIND=’User’ and SI_CREATION_TIME >= ‘2010-01-01-00-00’ and SI_CREATION_TIME < '2011-01-01-00-00'

    Thanks again!

    -Chandra

45. Hi Julian,

    We have a requirement to validate the definition of schedules (in terms of their recurrence, target user-group etc.) against the definition submitted by business.

    Since there are too many (around 120) schedules in the system it would take lot of time to verify each schedule through instance manager. Moreover we have to generate a report from system which will serve as a proof that schedules have been defined as per the requests submitted.

    Could you please suggest any query that can provide the information about the schedules created in system.

    Thanks,
    Chandra

46. Hi Chandra, the following query is a good start, but you will probably want to refine its select clause so that it only returns the fields you care to see:

    
SELECT
*
FROM
ci_InfoObjects
WHERE
si_instance = 1 AND
si_recurring = 1


    You could also further refine this by appending the document type, such as “si_kind = ‘WebI’; however, your schedules may have various document types which could be excluded. Please share your final query when you can.

47. Hi Julian,

    Thanks for the reply!
    I already tried queries similar to suggested one, but the problem is in the output it gives everything in terms of ID instead of their description. For example if I have a schedule to be run for Grp1,Grp2 based on calendar Cal1 ,it will show me ids for Grp1 ,Grp2 and Cal1 under field “SI_MULTIPASS” and “SI_CALENDAR_TEMPLATE_ID” respectively.

    Information in this format is difficult to interpret.
    Is there a way to get description of groups or calendars instead of their ids.

    -Chandra

48. Sorry Chandra, I don’t think it is possible in this case. The only syntax/function that can provide this is relationship queries, but I don’t see a standard relationship that fits your data targets:
    Available Relationship (from the Query builder guide):
    Folder Hierarchy
    UserGroup-Folder
    ServerGroup-Server
    Category-Document
    DataConnection-Universe
    Profile-Principal
    Webi-Universe
    User-Favorites
    User-Inbox
    Universe-UserGroup
    UserGroup-User

49. Thanks Julian to clarify that required data targets can not be met with query builder in this case. Also thanks for sharing available relationships (from query builder guide).

    I’ll have to check now if I can get this info using audit database.

    -Chandra

50. Hi Julian,

    One question regarding Query Builder: Would it be possible to extract the WebI reports that users have access to?

    Cheers,
    John