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

Question

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

sns 9,246

is there any Script to list out mostly used subsites and site collections in the entire farm, If yes please share.

Accepted answer

0 additional answers

Your answer

Answer 1

RaytheonXie_MSFT 40,451 Microsoft External Staff

Hi @sns,

SPWeb.GetUsageData has been desperate since SharePoint 2013:

User's image

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.

sns 9,246 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,246 Reputation points

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

test123
sns 9,246 Reputation points

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

test123
sns 9,246 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 40,451 Reputation points Microsoft External Staff

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.

Share via

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

0 additional answers

Your answer