Based
on the research done, analyzed so many test cases, spend so many hours to
prepare a script, spend numerous days to explore the site columns in SQL
database and then finally got it that I wanted as per my requirements.
You
must be thinking that which path/solution I am talking about-
Let me
brief some details before sharing the actual data-
For
this requirement, you need two reports like-
Site
collection administrators across all site collections
Site
collections which are under-utilized / not been accessed from a long time.
I will
not talk about the first point as it’s very easy and you can prepare it in a
simple manner via PowerShell. In case of any queries then please let me know.
Regarding
second point which is our main agenda / goal – This can be extracted via SQL
Query!
Please
refer the following SQL Query which will accomplish this requirement.
SELECT
FullUrl AS 'Site URL', TimeCreated,
DATEADD(d,DayLastAccessed
+ 65536, CONVERT(datetime, '1/1/1899', 101))
AS
lastAccessDate FROM Webs WHERE
(DayLastAccessed
<> 0) AND (FullUrl LIKE N'sites/%') ORDER BY lastAccessDate
You
need to run this query against the specific content database and extract the
report.
1. Login to your SQL box
2. Open SQL Server
management studio
3. Click on “New Query” from
the top
4. Change the database from
the dropdown
5. Select your content
database
6. Paste the above query in
the query box
7. Select it (control A)
8. Execute it from the top.
9. You will get the report
in the bottom section. That’s it- You are done.
If you
have any queries/questions regarding the above mentioned information then
please let me know. I would be more than happy to help you as well as resolves
your issues, Thank you.
References:-
Product
Applies To:-
1. SharePoint Server 2007
2. SharePoint Server 2010
3. SharePoint Foundation
2010
4. SharePoint Foundation
2013
5. SharePoint Server 2013
No comments:
Post a Comment