Skip to main content

Analyzing user data through User Reporting Views

Longview provides preconfigured User Reporting Views that you can query in Excel to view information for users, such as all users and groups in the system, along with their permissions, system activity, and information. You can use any method for querying database views; however, Longview recommends using Microsoft Excel in the manner described in this section.

To analyze user data through User Reporting Views, you must complete the following procedures:

  1. Add the relevant data source. For more information, see Adding a Data Source.
  2. Query the User Reporting Views in Microsoft Excel. For more information, see Querying User Reporting Views.

Adding a Data Source

Before you can view User Reporting views, you must add the required Data Source. Complete one of the following the procedures based on your database:

Adding a SQL Data Source

Before you can open User Reporting views in Microsoft Excel, you must add the related Data Source.

To add a SQL Data Source, complete the following steps:

  1. Open Microsoft Excel on the Client machine.
  2. Click the Data tab.
  3. In the ribbon, click From Other Sources, and then click From Microsoft Query. The Choose Data Source dialog opens, as shown below:

  4. Select <New Data Source> and click OK. The Create New Data Source dialog opens, shown below:

  5. Specify a name for the Data Source.
  6. Select the appropriate driver for the data source. For example, SQL Server Native Client 10.0.
  7. Click Connect. The SQL Server Login dialog opens, as shown below:

  8. Complete the following fields:

    1. Server: Specify the server on which the database is hosted.

    2. Login ID: Specify the login ID for the View User, as specified during installation.

    3. Password: Specify the password for the View User, as specified during installation.

  9. Click OK. The Create New Data Source dialog displays.
  10. Click OK. The Choose Data Source dialog appears with the new Data Source in the list.
  11. Do one of the following:
    1. If you want to query User Reporting Views immediately, click OK and continue with step 6 of Querying User Reporting Views.
    2. Click Cancel to close the Choose Data Source dialog and query User Reporting Views later.

Adding an Oracle Data Source

This procedure assumes you have installed and configured the relevant Oracle Client application.

Note: If you are using OS authentication, extra steps are required. Contact Longview Support Services.

To add an Oracle Data Source, complete the following steps:

  1. Open Microsoft Excel on the Client machine.
  2. Click the Data tab.
  3. In the ribbon, click From Other Sources, and then click From Microsoft Query. The Choose Data Source dialog displays, shown below:

  4. Select <New Data Source> and click OK. The Create New Data Source dialog displays, shown below:

  5. Specify a name for the Data Source.
  6. Select the appropriate driver for the data source. For example, Oracle in OraClient11g_home1.
  7. Click Connect. The Oracle ODBC Driver Connect dialog displays, like below:

  8. Complete the following fields:

    1. Service Name: Specify the Oracle Instance on which the database is hosted.

    2. User Name: Specify the user ID for the View User, as specified during installation.

    3. Password: Specify the password for the View User, as specified during installation.

  9. Click OK. The Create New Data Source dialog displays.
  10. Click OK. The Choose Data Source dialog appears with the new Data Source in the list.
  11. Do one of the following:
    1. If you want to query User Reporting Views immediately, click OK and continue with step 6 of Querying User Reporting Views.
    2. Click Cancel to close the Choose Data Source dialog and query User Reporting Views later.

Querying User Reporting Views

Once you have added the required Data Source, you can query User Reporting Views in Microsoft Excel.

For information on adding a Data Source, see Adding a Data Source.

To open User Reporting Views, complete the following steps:

  1. In Microsoft Excel, click the Data tab.
  2. In the ribbon, click From Other Sources, and then click From Microsoft Query. The Choose Data Source dialog opens.
  3. Select the Data Source that you created for your Longview database and click OK. The SQL Server Login dialog opens.
  4. Complete the following fields:

    1. Server: Specify the server on which the database is hosted.

    2. Login ID: Specify the login ID for the View User, as specified during installation.

    3. Password: Specify the password for the View User, as specified during installation.

  5. Click OK. The Query Wizard - Choose Columns dialog displays, as shown below:

  6. Click Options. The Table Options dialog displays, shown below:

  7. Select Views and clear all other options.
  8. For Owner, select the View User, as specified during installation.
  9. Click OK. The Query Wizard - Choose Columns dialog displays, populated with the User Reporting Views.

  10. Select one of the following User Reporting Views, and click the arrow:

    1. LV_CombinedUserGroupAccess: Select this View to retrieve a list of combined user and group access for each user for all users in the system.

      2. Note: This View does not list the resolved access for each user.

    2. LV_GroupAccessForAllUsers: Select this View to retrieve a list of Group Access for each user for all users in the system.

    3. LV_GroupAccessForGroups: Select this View to retrieve a list of Group Access for each group for all user groups in the system.

    4. LV_GroupActivity: Select this View to retrieve a list of login activity for each user group in the system.

    5. LV_GroupAuthorizations: Select this View to retrieve a list of all groups in the system along with their related authorizations.

    6. LV_Groups: Select this View to retrieve a list of user groups in the system.

    7. LV_GroupUserMembership: Select this View to retrieve a list of all groups with their associated user membership.

    8. LV_RoleAccess: Select this View to retrieve a list of role access for all roles in the system.

    9. LV_UserAccessForAllUsers: Select this View to retrieve a list of user access for all users in the system.

    10. LV_UserActivity: Select this View to retrieve a list of user login activity.

    11. LV_UserAttributes: Select this View to retrieve a list of user attributes in the system, along with the value of the attribute for users for which the attribute is set.

    12. LV_UserAuthorizations: Select this View to retrieve a list of all users in the system along with their authorizations.

    13. LV_UserGroupMembership: Select this View to retrieve a list of all users in the system with their associated domain and assigned groups.

    14. LV_UserInfo: Select this View to retrieve a list of all users and their information. For example, name, email, and phone numbers.

    15. LV_UserPasswordActivity: Select this View to retrieve a log of user password activity.

    16. LV_Users: Select this View to retrieve a list of all users in the system.

  11. Click Next. The Query Wizard - Filter Data dialog displays.
  12. Optionally, use the Filter options as required to filter the query data.

  13. Click Next. The Query Wizard - Sort Order dialog displays.
  14. Optionally, use the Sort options to sort the query data.

  15. Click Next. The Query Wizard - Finish dialog displays, as shown below:

  16. Select Return Data to Microsoft Excel and click Finish. The Import Data dialog displays, as shown below:

  17. Edit the settings as necessary and click OK.
  18. The selected View appears in Microsoft Excel, like below:

  19. Repeat steps step 2 to 17 for the required Views.

Was this article helpful?

We're sorry to hear that.