WebCenter Administrators or Solution Architects may want to extract basic user information from WebCenter. For example, a list of all current users with their username, e-mail address, etc..
Naturally, WebCenter customers having a need for regular reports containing information from the WebCenter database can use the very flexible reporting framework. Using the WebCenter reporting framework has great advantages. For example, you can choose from an array of formats (PDF, XML, XLS, CSV, TXT, RTF, HTML, XHTML), add charts, calculate averages and compare actuals to targets, add your own images/logos and terminology and you do not need to access the database server to run reports. For more information, see the WebCenter User Documentation.
However, in case only a one-time simple Excel sheet containing information from a single WebCenter database table is needed, it is possible to run a basic database query and copy the results to an Excel file. This article describes how to do so, using a Microsoft SQL database. Of course, this procedure should not be promoted at customers. Whenever there is need for regular reports, WebCenter Reporting should be advocated.
Follow these steps to fetch the desired information from the WebCenter SQL database and add it to an Excel file:
Log on to the WebCenter database server and preferably connect with Remote Desktop Connection. If this is not possible, connect with TeamViewer.
Launch SQL Server Management Studio and log in.
In the top toolbar, click the New Query button. A new query window will open.
Type the following SQL query:
SELECT * from WebCenter4_0.webcenter.Users
In the top toolbar, click the Execute button.
The Results panel below the query window will display a table containing user information.
Now, you can export your results.
In case you are using Remote Desktop:
In the Results panel, press CTRL + A to select all records. Then press CTRL + C to copy them.
On your own machine, open a new blank Excel document and press CTRL + V (or Cmd + V for Mac) to paste the information.
In case you are using TeamViewer, you may need to create the Excel document on the server first and then copy it to your machine using File Transfer.
Alternatively, click anywhere in the Results table and choose Save Results As... to save the results to a CSV file. If so desired you convert the CSV file to Excel later by importing it into Microsoft Excel.
In your Excel file optionally delete the columns that are not very useful to you. You may also want to delete the rows where the value for Deleted is 1 as these contain deleted users.
The result is a very basic Excel sheet containing information such as: User Id, Username, Last Name, First Name, Email, Phone Number, Function and User Type.
If your goal is to later import the user accounts in the Excel file in another WebCenter system, have a look at the following article as this requires a specific format for you Excel sheet: