Script to list out mostly used subsites and site collections in the farm

Accepted answer

RaytheonXie_MSFT 31,071 Reputation points Microsoft Vendor

2023-03-03T02:11:15.4733333+00:00

Hi @sns,

SPWeb.GetUsageData has been desperate since SharePoint 2013:

As a workaround is to directly query SharePoint WSS_logging DataBase to get the data:

View data in the logging database in SharePoint Server

And here is a similar issue with yours for reference

is SPWeb.GetUsageData still available

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
Please sign in to rate this answer.
sns 9,226 Reputation points

2023-03-03T04:29:27.6533333+00:00

Hi Raytheon Xue,

Thank you for the response.

Will I get mostly used site collection and sites list from below query?

SELECT FROM dbo.FeatureUsage_Partition1 INNER JOIN dbo.RequestUsage_Partition1 ON dbo.FeatureUsage_Partition1.PartitionId = dbo.RequestUsage_Partition1.PartitionId

could you also please suggest, what I need to amend in the above query to get the result in to CSV file?

and on which database I should run?

and also please suggest to which table I should navigate?

sns 9,226 Reputation points

2023-03-03T04:29:50.4433333+00:00

test123

sns 9,226 Reputation points

2023-03-03T05:51:24.86+00:00

test123

sns 9,226 Reputation points

2023-03-03T08:31:45.2533333+00:00

Hi Raytheon Xu,

I have not seen your latest commet, did you respond or am I missing something

RaytheonXie_MSFT 31,071 Reputation points Microsoft Vendor

2023-03-06T06:21:30.51+00:00

Hi @sns,

This sql will query the usage report of the sites. But the report is about items and pages in sites. Currently there is no most popular sites in usage report. We can only get most polular items or pages by the report.

You can refer to the document for more details

https://learn.microsoft.com/en-us/sharepoint/administration/view-and-configure-usage-analytics-reports

I feel regretful to inform you that it turns out to be a by-design one. Many features of our current products are designed and upgraded based on customers’ feedback. With requirements like this increase, the problem may well be released in the future. Thanks for your understanding.
Sign in to comment

Script to list out mostly used subsites and site collections in the farm

0 additional answers