How to determine which MOSS sites/subsites are not used anymore?


For most of the SharePoint Admins this is a Challenge as analyzing usage logs if you have 1000+sites is almost impossible plus it gives data for only 30 days. What if we need to delete the sites which are not accessed by anyone in last 6 months/1 year?
Site auto deletion feature may not be feasible for everyone.

Below are some methods which can be utilized to solve the purpose

1. 1st Method- MSIT SharePoint Reporting Framework – This tool can be used to check last content modified date and other useful infomation to the site admins . Tool is available at codeplex-http://msitsrf.codeplex.com/ . But this should not be used on production database and the content database should be coiped to test farm if you need to execute the queries.

2. 2nd method- If your requiremnt is to get the last accessed date and you are not bothered if the content is modified recently or not then “Daylastaccessed” column in dbo.Webs is what you are looking for.

Daylastaccessed – column gives value that specifies the last day the site was accessed, which is expressed as the number of days since January 1, 1899 until the last day the site was accessed.
This column is currently used by the usage processing code, but it can also be used in ad-hoc queries to check which sites have been used most recently and which have been used least recently. For example, Select FullUrl,DayLastAccessed From Webs Order By DayLastAccessed.
Sites that have never been used or that have never had their usage data processed for some reason will have this field set to 0.

Use following sql query to extract the information from the content database. This query also converts the smallint type to datetime and gives exactly what you need :

SELECT FullUrl AS ‘Site URL’, TimeCreated, DATEADD(d,DayLastAccessed + 65536,
CONVERT(datetime, ‘1/1/1899’, 101)) AS
lastAccessDate
FROM [DATABSE_NAME].dbo.Webs
WHERE (DayLastAccessed 0)

Advertisements

About amritatalreja
I am working as a SharePoint Consultant in HCL America INC, Cary,NC.

One Response to How to determine which MOSS sites/subsites are not used anymore?

  1. If some one desires to be updated with hottest technologies then he must be pay
    a quick visit this web site and be up to date everyday.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: