I recently had a client that wanted metrics on sharepoint usage in their farm. Of course I already knew about the out-of-box (OOB) usage reporting capabilities of SharePoint 2007. But the limitations with the OOB reports is that they only show a rolling 30 day picture of usage and show usage for one site collection at a time. My client wanted to see the metrics for the whole site, a whole year and quarterly reports. So I went and performed a Google search and found tons of articles on parsing Sharepoint’s log files. I even built a SSIS solution to parse the log files and place the usage data in a database I created. I kept thinking to myself, this solution isn’t very elegant and is problematic for implementing in a production environments.
If you have read any of my other blog entries, you know that I typically go “under the hood” of SharePoint to see what is really happening. So exactly what happens when you turn on Usage Analysis Processing under Operations in Central Administration? First, SharePoint creates several new database tables in the SSP database. The table names are:
• ANLDay
• ANLHistoricalSiteUsage
• ANLHistoricalWebUsage
• ANLHit
• ANLResource
• ANLUser
• ANLWeb
Then SharePoint starts to create usage log files in the default location C:/Program Files/Common Files/Microsoft Shared/Web Server Extensions/12/Logs (unless you changed that location when you started Usage Analysis Processing) with a name format of ex091102.log. The 091102 portion of the log name changes daily to reflect the date. Next, depending upon what time frame you entered into Processing Settings section on the Usage Analysis Processing page referenced above, SharePoint will parse through these logs on a daily basis at that time. SharePoint then takes this data and populates the new above mentioned tables in the SSP database.
Wow that’s great Julian, I’ll just query those tables and start creating my reports! Well not so fast… Remember that nasty 30 day rolling picture of usage we discussed earlier? SharePoint has a stored procedure that will delete table data every day while performing the Usage Analysis Processing job. The result is that it will delete the data from 31 days ago and add the new data from the day before to keep a rolling 30 days worth of data. Here are the actual SQL comments inside the stored procedure:
-- Check if the ANLHit table needs to be aggregated into historical data. We expect to have at most 30 days of daily detailed usage data and at most 335 days of daily summary data. The 30 days starts with "yesterday"'s (i.e. @TodayDayId-1) and goes back to @TodayDayId-30. The summary data starts at @TodayDayId-365 and goes to @TodayDayId-31.
Bummer… Now what? Let’s get further under the hood to make a few modifications to solve this final challenge.
Disclaimer:
In my solution you will be making a modification to a SharePoint 2007 database. It is erroneously reported all over the internet that if you make a change to a SharePoint database you will loose all support from Microsoft. That simply isn’t true – Here is the verbiage straight from Microsoft:
If an unsupported database modification is discovered during a support call, the customer must perform one of the following procedures at a minimum:
• Perform a database restoration from the last known good backup that did not include the database modifications
• Roll back all the database modifications
So, the first thing we are going to do is back up the stored procedure named proc_ANL_ProcessShadowTables (located in the SSP database) in safe location just in case we need to “Roll back database modifications”. Then we are going to modify the default 30 days in this stored procedure to whatever date range value you need for your reporting.
Open SQL Server Management Studio and drill down to your SSP database > Programmability >Stored Procedures. Find the stored procedure named proc_ANL_ProcessShadowTables and right click it, then select Modify. You will need to scroll down to lines I have highlighted in green below (lines 46 and 368) and replace the 30 with a numerical value equal to the amount time you want SharePoint to store the parsed log data. In the example below, I have replaced the 30 with 90. In this example, SharePoint will not delete any data until we reach that 90 day parameter. Once you have made the changes, hit F5 or mouse click Execute in the SQL Server Management Studio toolbar.
You are all set, just start writing your queries for reporting straight from the SSP ANL tables!
Modifications to proc_ANL_ProcessShadowTables
001.
set
ANSI_NULLS
ON
002.
set
QUOTED_IDENTIFIER
ON
003.
go
004.
005.
006.
-------------------------------------------------------------------------------
007.
-- Usage data processing stored procedures
008.
-------------------------------------------------------------------------------
009.
010.
--
011.
-- Imports the data from the shadow tables into the master tables.
012.
--
013.
-- Both the Shadow and Master locks must have been acquired before calling
014.
-- this procedure in order to guarantee that this procedure and the reporting
015.
-- procedures will behave predictably and reliably.
016.
--
017.
ALTER
PROCEDURE
[dbo].[proc_ANL_ProcessShadowTables]
018.
@TodayDayId
INT
019.
AS
020.
021.
-- 0. Check if any work actually needs to be done before we start the heavy
022.
-- lifting. Even though it is safe to process usage data multiple times per
023.
-- day, it can be very taxing to the database server.
024.
025.
-- The most common need for processing is newly-imported usage data. Check for
026.
-- that first.
027.
028.
DECLARE
@Hits
INT
, @Resources
INT
029.
SELECT
@Hits =
COUNT
(*)
FROM
ANLShadowHit
030.
SELECT
@Resources =
COUNT
(*)
FROM
ANLShadowResource
031.
032.
IF @Hits + @Resources = 0
033.
BEGIN
034.
-- Check if the ANLHit table needs to be aggregated into historical data.
035.
-- We expect to have at most 30 days of daily detailed usage data and at
036.
-- most 335 days of daily summary data. The 30 days starts with "yesterday"'s
037.
-- data (i.e. @TodayDayId-1) and goes back to @TodayDayId-30. The summary data
038.
-- starts at @TodayDayId-365 and goes to @TodayDayId-31.
039.
--
040.
-- Check if there is any old data in ANLHit (the detailed data) which needs to
041.
-- be aggregated into the historical tables.
042.
DECLARE
@MinDayId
INT
043.
SELECT
@MinDayId =
MIN
(DayId)
FROM
ANLHit
044.
045.-- CHANGED 30 TO 90 HERE
046. IF @MinDayId >= (@TodayDayId-90)
047.
BEGIN
048.
-- Next, check if old data in the historical tables need to be deleted. We
049.
-- only need to inspect ANLHistoricalWebUsage because ANLHistoricalSiteUsage
050.
-- won't have anything older.
051.
SELECT
@MinDayId =
MIN
(DayId)
FROM
ANLHistoricalWebUsage
052.
IF @MinDayId >= (@TodayDayId-365)
053.
BEGIN
054.
-- Lastly, check if ANLDay needs to be updated. If we got here then we know
055.
-- we have no usage data for 'yesterday' but may still need to create a new
056.
-- row for 'yesterday' in ANLDay so that we see it with 0 hits in our
057.
-- reports. Similarily, we know we don't have any historical usage data
058.
-- older than Today-365, but we may have a row in ANLDay for it which needs
059.
-- to be deleted.
060.
061.
-- Note: at this point we have the option of very cheaply removing or adding
062.
-- the appropriate rows in ANLDay. Consider this an optimization for sites
063.
-- that have a very uneven usage distribution and may receive no hits at all
064.
-- on some days but over the year accumulate a large amount of usage data.
065.
IF EXISTS (
SELECT
*
FROM
ANLDay
WHERE
DayId = (@TodayDayId-1))
AND
NOT
EXISTS (
SELECT
*
FROM
ANLDay
WHERE
DayId < (@TodayDayId-365))
066.
BEGIN
067.
-- Nothing to do; all usage data is up to date.
068.
RETURN
-- EARLY RETURN;
069.
END
070.
END
071.
END
072.
END
073.
074.
-- Take out table-level locks on all analytics tables to avoid row-level locking.
075.
SELECT
*
FROM
ANLDay
WITH
(TABLOCK, HOLDLOCK)
WHERE
1=0
076.
SELECT
*
FROM
ANLHistoricalSiteUsage
WITH
(TABLOCK, HOLDLOCK)
WHERE
1=0
077.
SELECT
*
FROM
ANLHistoricalWebUsage
WITH
(TABLOCK, HOLDLOCK)
WHERE
1=0
078.
SELECT
*
FROM
ANLHit
WITH
(TABLOCK, HOLDLOCK)
WHERE
1=0
079.
SELECT
*
FROM
ANLResource
WITH
(TABLOCK, HOLDLOCK)
WHERE
1=0
080.
SELECT
*
FROM
ANLWeb
WITH
(TABLOCK, HOLDLOCK)
WHERE
1=0
081.
SELECT
*
FROM
ANLShadowHit
WITH
(TABLOCK, HOLDLOCK)
WHERE
1=0
082.
SELECT
*
FROM
ANLShadowResource
WITH
(TABLOCK, HOLDLOCK)
WHERE
1=0
083.
SELECT
*
FROM
ANLShadowWeb
WITH
(TABLOCK, HOLDLOCK)
WHERE
1=0
084.
SELECT
*
FROM
ANLShadowUser
WITH
(TABLOCK, HOLDLOCK)
WHERE
1=0
085.
SELECT
*
FROM
ANLUser
WITH
(TABLOCK, HOLDLOCK)
WHERE
1=0
086.
087.
-- 1. Reconfigure the indexes and drop indexed views to optimize for bulk
088.
-- inserts and updates.
089.
090.
-- 2. Import users, webs and resources. WSS makes it impossible to track rename
091.
-- and move operations on resources so when that happens we'll just treat
092.
-- it as a new resource.
093.
094.
-- 2a. Import ANLShadowUser into ANLUser
095.
096.
-- Import all new users from ANLShadowUser
097.
INSERT
INTO
ANLUser (UserName)
098.
SELECT
DISTINCT
UserName
FROM
ANLShadowUser
099.
WHERE
NOT
EXISTS (
SELECT
*
FROM
ANLUser u
WHERE
u.UserName = ANLShadowUser.UserName)
100.
101.
-- Update ANLShadowHit to point to the correct ANLUser record
102.
UPDATE
ANLShadowHit
103.
SET
UserId = u.UserId
104.
FROM
ANLUser u, ANLShadowUser su
105.
WHERE
su.UserId = ShadowUserId
AND
u.UserName = su.UserName
106.
107.
-- Clear out ANLShadowUser
108.
TRUNCATE
TABLE
ANLShadowUser
109.
110.
-- 2b. Import ANLShadowWeb into ANLWeb
111.
112.
-- There is a remote chance that we'll have two different WebUrls for the same
113.
-- WebGuid. This could happen if a Web is renamed, and will only occur in the
114.
-- logs for that one day. To mitigate this we'll just drop an arbitrary
115.
-- duplicate Web and fix the broken URL on the next day's import.
116.
117.
-- Delete the duplicates (very rare)
118.
DELETE
FROM
ANLShadowWeb
119.
WHERE
EXISTS
120.
(
SELECT
*
FROM
ANLShadowWeb sw2
WHERE
sw2.WebGuid = ANLShadowWeb.WebGuid
AND
sw2.WebUrl < ANLShadowWeb.WebUrl)
121.
OPTION
(MAXDOP 4)
122.
123.
-- Update the names of any renamed webs
124.
UPDATE
ANLWeb
125.
SET
WebUrl = sw.WebURL
126.
FROM
ANLShadowWeb sw
127.
WHERE
ANLWeb.WebGuid = sw.WebGuid
128.
129.
-- Import all new webs from ANLShadowWeb
130.
INSERT
INTO
ANLWeb (WebGuid, WebUrl)
131.
SELECT
DISTINCT
WebGuid, WebUrl
FROM
ANLShadowWeb
132.
WHERE
NOT
WebGuid = '00000000-0000-0000-0000-000000000000
'
133.
AND NOT EXISTS (SELECT * FROM ANLWeb w WHERE w.WebGuid = ANLShadowWeb.WebGuid)
134.
135.
-- Clear out ANLShadowUser
136.
TRUNCATE TABLE ANLShadowWeb
137.
138.
-- 2c. Import ANLShadowResource into ANLResource
139.
140.
-- Update any non-resolved resources in ANLResource with resolved versions from
141.
-- ANLShadowResource.
142.
UPDATE ANLResource
143.
SET WebAppGuid = s.WebAppGuid, SiteGuid = s.SiteGuid, WebGuid = s.WebGuid, DocName = s.DocName
144.
FROM ANLShadowResource s
145.
WHERE ANLResource.WebAppGuid = '
00000000-0000-0000-0000-000000000000
' AND NOT s.WebAppGuid = '
00000000-0000-0000-0000-000000000000
'
146.
AND ANLResource.FullUrl = s.FullUrl
147.
148.
-- Insert any WSS resources which exist in ANLShadowResource but not ANLResource.
149.
INSERT INTO ANLResource
150.
SELECT DISTINCT WebAppGuid, SiteGuid, WebGuid, DocName, FullUrl, HostDns FROM ANLShadowResource a
151.
WHERE NOT EXISTS (SELECT * FROM ANLResource b WHERE a.FullUrl = b.FullUrl)
152.
153.
-- Try to resolve URLs which have no WSS reference info (such as webguid, etc)
154.
-- by looking them up in ANLResource.
155.
156.
-- Update ANLShadowHit to point to the correct ANLResource records.
157.
158.
UPDATE ANLShadowHit
159.
SET ResourceId = r.ResourceId
160.
FROM ANLResource r, ANLShadowResource sr
161.
WHERE ShadowResourceId = sr.ResourceId
162.
AND r.FullUrl = sr.FullUrl
163.
164.
-- Fix referrers. We'
re using 0
to
mark
null
referrers,
and
we know that
no
165.
-- resources have that ID so those ShadowReferrerResourceIds will become
166.
-- null ReferrerResourceIds.
167.
168.
-- Update referring resources which we know are internal (ones where WebAppGuid
169.
-- is not null).
170.
UPDATE
ANLShadowHit
171.
SET
ReferrerResourceId = r.ResourceId
172.
FROM
ANLResource r, ANLShadowResource sr
173.
WHERE
ShadowReferrerResourceId = sr.ResourceId
174.
AND
NOT
sr.WebAppGuid =
'00000000-0000-0000-0000-000000000000'
175.
AND
r.FullUrl = sr.FullUrl
176.
177.
-- Update resources which we suspect to be external but may just be unresolved
178.
-- internals (ones where WebAppGuid is null).
179.
UPDATE
ANLShadowHit
180.
SET
ReferrerResourceId = r.ResourceId
181.
FROM
ANLResource r, ANLShadowResource sr
182.
WHERE
ShadowReferrerResourceId = sr.ResourceId
183.
AND
sr.WebAppGuid =
'00000000-0000-0000-0000-000000000000'
184.
AND
r.FullUrl = sr.FullUrl
185.
186.
-- Clear out ANLShadowResource
187.
TRUNCATE
TABLE
ANLShadowResource
188.
189.
-- 3. Summarize and archive hits older than 30 days into the Historical tables.
190.
191.
-- #TempSummary is used as a temporary bucket of stats which makes it a little
192.
-- easier to store the aggregations before putting them into the Historical
193.
-- tables. SummaryGuid can refer to WebGuid or SiteGuid, depending on what type
194.
-- of object we're summarizing.
195.
CREATE
TABLE
#TempSummary (SummaryDayId
int
, SummaryGuid uniqueidentifier, Value
int
,
CONSTRAINT
PK_WebHitsSummary
PRIMARY
KEY
(SummaryDayId, SummaryGuid))
196.
197.
-- ANLHistoricalWebUsage.Hits
198.
insert
into
#TempSummary (SummaryDayId, SummaryGuid, Value)
199.
select
DayId, WebGuid, count_big(*)
200.
from
ANLShadowHit, ANLResource
201.
where
ANLShadowHit.ResourceId = ANLResource.ResourceId
202.
group
by
DayId, WebGuid
203.
204.
-- Update the the existing records, then insert any missing ones.
205.
206.
update
ANLHistoricalWebUsage
207.
set
Hits = Hits + Value
208.
from
#TempSummary
209.
where
SummaryGuid = WebGuid
and
SummaryDayId = DayId
210.
211.
-- The following INSERT statement will seed the other 3 aggregations
212.
-- (UniqueUsers, HomePageHits, HomePageUniqueUsers) with 0 values so that they
213.
-- do not need to be inserted anymore, just updated.
214.
215.
INSERT
INTO
ANLHistoricalWebUsage (DayId, WebGuid, Hits, UniqueUsers, HomePageHits, HomePageUniqueUsers)
216.
SELECT
SummaryDayId, SummaryGuid, Value, 0, 0, 0
from
#TempSummary
WHERE
NOT
EXISTS (
217.
SELECT
*
FROM
ANLHistoricalWebUsage
WHERE
SummaryGuid = WebGuid
AND
SummaryDayId = DayId)
218.
219.
TRUNCATE
TABLE
#TempSummary
220.
221.
-- ANLHistoricalWebUsage.UniqueUsers
222.
insert
into
#TempSummary (SummaryDayId, SummaryGuid, Value)
223.
select
DayId, WebGuid, count_big(
distinct
UserId)
224.
from
ANLShadowHit, ANLResource
225.
where
ANLShadowHit.ResourceId = ANLResource.ResourceId
226.
group
by
DayId, WebGuid
227.
228.
update
ANLHistoricalWebUsage
229.
set
UniqueUsers = UniqueUsers + Value
230.
from
#TempSummary
231.
where
SummaryGuid = WebGuid
and
SummaryDayId = DayId
232.
233.
truncate
table
#TempSummary
234.
235.
-- ANLHistoricalWebUsage.HomePageHits
236.
insert
into
#TempSummary (SummaryDayId, SummaryGuid, Value)
237.
select
DayId, WebGuid, count_big(*)
238.
from
ANLShadowHit, ANLResource
239.
where
ANLShadowHit.ResourceId = ANLResource.ResourceId
AND
(DocName = N'
' OR DocName = NULL OR LOWER(DocName) = N'
default
.aspx
' OR RIGHT(LOWER(DocName),13) = N'
/
default
.aspx
')
240.
group by DayId, WebGuid
241.
242.
update ANLHistoricalWebUsage
243.
set HomePageHits = HomePageHits + Value
244.
from #TempSummary
245.
where SummaryGuid = WebGuid and SummaryDayId = DayId
246.
247.
truncate table #TempSummary
248.
249.
-- ANLHistoricalWebUsage.HomePageUniqueUsers
250.
insert into #TempSummary (SummaryDayId, SummaryGuid, Value)
251.
select DayId, WebGuid, count_big(distinct UserId)
252.
from ANLShadowHit, ANLResource
253.
where ANLShadowHit.ResourceId = ANLResource.ResourceId AND (DocName = N'
' OR DocName = NULL OR LOWER(DocName) = N'
default
.aspx
' OR RIGHT(LOWER(DocName),13) = N'
/
default
.aspx
')
254.
group by DayId, WebGuid
255.
256.
update ANLHistoricalWebUsage
257.
set HomePageUniqueUsers = HomePageUniqueUsers + Value
258.
from #TempSummary
259.
where SummaryGuid = WebGuid and SummaryDayId = DayId
260.
261.
truncate table #TempSummary
262.
263.
DELETE FROM ANLHistoricalWebUsage WHERE DayId < (@TodayDayId - 365)
264.
OPTION (MAXDOP 4)
265.
266.
-- ANLHistoricalSiteUsage.Hits
267.
268.
INSERT INTO #TempSummary (SummaryDayId, SummaryGuid, Value)
269.
SELECT DayId, SiteGuid, COUNT_BIG(*)
270.
FROM ANLShadowHit, ANLResource
271.
WHERE ANLShadowHit.ResourceId = ANLResource.ResourceId
272.
GROUP BY DayId, SiteGuid
273.
274.
UPDATE ANLHistoricalSiteUsage
275.
SET Hits = Hits + Value
276.
FROM #TempSummary
277.
WHERE SummaryGuid = SiteGuid AND SummaryDayId = DayId
278.
279.
-- The following INSERT statement will seed the other aggregation (UniqueUsers)
280.
-- with a 0 values so that it does not need to be inserted anymore, just
281.
-- updated.
282.
283.
INSERT INTO ANLHistoricalSiteUsage (DayId, SiteGuid, Hits, UniqueUsers)
284.
SELECT SummaryDayId, SummaryGuid, Value, 0 FROM #TempSummary
285.
WHERE NOT EXISTS (SELECT * FROM ANLHistoricalSiteUsage WHERE SummaryGuid = SiteGuid AND SummaryDayId = DayId)
286.
287.
TRUNCATE TABLE #TempSummary
288.
289.
-- ANLHistoricalSiteUsage.UniqueUsers
290.
291.
INSERT INTO #TempSummary (SummaryDayId, SummaryGuid, Value)
292.
SELECT DayId, SiteGuid, COUNT_BIG(DISTINCT UserId)
293.
FROM ANLShadowHit, ANLResource
294.
WHERE ANLShadowHit.ResourceId = ANLResource.ResourceId
295.
GROUP BY DayId, SiteGuid
296.
297.
UPDATE ANLHistoricalSiteUsage
298.
SET UniqueUsers = UniqueUsers + Value
299.
FROM #TempSummary
300.
WHERE SummaryGuid = SiteGuid AND SummaryDayId = DayId
301.
302.
TRUNCATE TABLE #TempSummary
303.
304.
DELETE FROM ANLHistoricalSiteUsage WHERE DayId < (@TodayDayId - 365)
305.
OPTION (MAXDOP 4)
306.
307.
-- Note that the most recent hits in ANLHit are from yesterday, hence " < (today-30)" and not " <= (today-30)"
308.
-- CHANGED 30 TO 90 HERE
309.
DELETE FROM ANLHit WHERE DayId < (@TodayDayId - 90)
310.
OPTION (MAXDOP 4)
311.
312.
-- 4. Import ANLShadowHit
313.
314.
-- Fix the unknown referrers in ANLShadowHit. Do this in two passes: first try
315.
-- finding fully-resolved resources in ANLShadowResource, then go to
316.
-- ANLResource for anything left unresolved.
317.
--
318.
-- NOTE: we are not enforcing uniqueness on dbo.ANLHit.FullUrl so weirdness may
319.
-- occur with really long URLs that are truncated and result in
320.
-- duplicates.
321.
322.
-- Copy the shadow hits into the master table.
323.
INSERT INTO ANLHit (DayId, ResourceId, UserId, ReferrerResourceId)
324.
SELECT DayId, ResourceId, UserId, ReferrerResourceId FROM ANLShadowHit
325.
326.
-- We'
re now done
with
ANLShadowHit
327.
TRUNCATE
TABLE
ANLShadowHit
328.
329.
-- 5. Delete any resources that are no longer referenced by any hits. Also
330.
-- check that the summary tables don't use the resources. Note that we're not
331.
-- checking against ANLHistoricalSiteUsage because ANLHistoricalWebUsage is
332.
-- a superset.
333.
334.
-- Note that we're keeping just the home page of webs that have historical
335.
-- usage data.
336.
DELETE
FROM
ANLResource
337.
WHERE
NOT
EXISTS (
SELECT
*
FROM
ANLHit h
WHERE
h.ResourceId = ResourceId)
338.
AND
NOT
(DocName = N'
' OR DocName = NULL OR LOWER(DocName) = N'
default
.aspx
' OR RIGHT(LOWER(DocName),13) = N'
/
default
.aspx
') AND EXISTS (SELECT * FROM ANLHistoricalWebUsage s WHERE s.WebGuid = WebGuid)
339.
OPTION (MAXDOP 4)
340.
341.
-- Clean up the ANLWeb table.
342.
-- We'
ll
only
drop
webs that haven
't been accessed in the last year (no historical data).
343.
DELETE FROM ANLWeb
344.
WHERE NOT EXISTS (SELECT * FROM ANLWeb INNER JOIN ANLHistoricalWebUsage ON ANLWeb.WebGuid = ANLHistoricalWebUsage.WebGuid)
345.
OPTION (MAXDOP 4)
346.
347.
-- 6. Update ANLDay
348.
349.
-- Create any missing ANLDay entries by inserting all days into ANLDay between
350.
-- the oldest day day in ANLHistoricalWebUsage and yesterday.
351.
DECLARE @newDayId INT
352.
SELECT @newDayId = ISNULL(MIN(DayId), @TodayDayId-1) FROM ANLHistoricalSiteUsage
353.
WHILE @newDayId < @TodayDayId
354.
BEGIN
355.
IF (SELECT COUNT(*) FROM ANLDay WHERE DayId = @newDayId) = 0
356.
BEGIN
357.
INSERT INTO ANLDay (DayId, FullDate, IsHistorical) VALUES (@newDayId, DATEADD(DAY,@newDayId,'
20000101'), 0)
358.
END
359.
SELECT
@newDayId = @newDayId + 1
360.
END
361.
362.
-- Delete days older than 365 days from today
363.
DELETE
FROM
ANLDay
WHERE
DayId < (@TodayDayId - 365)
364.
OPTION
(MAXDOP 4)
365.
366.
-- Update the IsHistorical flag to 1 on all DayIds older than 30 days
367.-- CHANGED 30 TO 90 HERE
368.UPDATE ANLDay SET IsHistorical = CASE WHEN DayId < @TodayDayId - 90 THEN 1 ELSE 0 END
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
