Analyze usage data

Using the built-in views provided in the logging database, you can review the different metrics that are captured in the SharePoint usage and health collection intervals. This information not only can be viewed in SSMS but can also be exported as a comma-separated value (*.csv) file to Microsoft Excel for further analysis.

SharePoint farm administrators are becoming more and more versatile. One of the key toolsets we are learning to master is the simple SSMS tool. Understand how to connect to a server, run a simple query, and view the result.

To begin viewing logging data in SSMS, do the following:

1. Open SSMS and connect to the SQL instance providing data services to your SharePoint farm (see Figure 1).

FIGURE 1 Connecting to a server.

2. Select the logging database and then select the plus (+) sign to show all its components (see Figure 2).

FIGURE 2 Expanded logging database.

3. Next, select a view for which you want to collect information (see Figure 3).

FIGURE 3 Choosing a view

4. Right-click the view and choose Select Top 1000 Rows (see Figure 4).

FIGURE 4 Selecting the top 1,000 rows.

5. The SQL Query appears in the top pane, and the lower pane shows the query results (see Figure 5).

FIGURE 5 T-SQL query and results.

6. These results can now be exported to a *.csv file for viewing and analysis in Microsoft Excel. To begin, right-click the results window and choose Save Results As (see Figure 6).

FIGURE 6 Saving results.

7. Choose a file name for the *.csv file and select Save (see Figure 7).

FIGURE 7 Choosing a name for the *.csv file.

8. Opening and importing the *.csv file in Excel enables you to display and interact with the data (see Figure 8).

FIGURE 8 Usage and health data in Excel.