Page History
The fine tuning process is essential In order to ensure qualitative and accurate data, it is ideal to go through the fine tuning process on your system. This section provides detailed instructions on how to perform the fine tuning processfine tune your system.
CardioLog collects usage data for various event types (, including views, visits, duration, search etc.). , and most other ways a user can interact with your site. The Usage Data Processing service component matches events to their the corresponding item in your SharePoint tree item. An Any event which has associated with a URL that does not match any item in your tree item (for instanceeg., a A URL address which includes a list of custom parameters, or a URL which is not part of the your monitored environments) is called known as a " Lost Event". Lost events are not displayed in the reports' results by default, they . Report results do not display lost events by default. They appear only after fine tuning the data.
Tuning Fine tuning the data includes of the following steps:
- Determining which data should not be collected and defining data collection filters.
- Identifying Lost Events and defining URL address modifications.
It is recommended to perform this process about a month after the initial installation of in the production environment, 2-3 weeks after an upgrade, and 2-3 weeks after adding a any new monitored environment to CardioLog.Fine Fine tuning should be performed by a user with a local administrator account on the CardioLog server and with a CardioLog Administrator role. Contact us for further assistance.
Contents
- Identifying Lost Events
- Fixing Lost Events
- Automatic Fine Tuning
- Refreshing the Report's Data
Anchor identifying lost identifying lost
Identifying Lost Events
| identifying lost | |
| identifying lost |
1. Execute the following SQL script against the CardioLog database to get a list of the lost events event URLs. This can be done for a specific date range (edit the date range by editing the timestamp in the SQL query):
Use CardioLog
GO
select SearchURL, count(SearchURL)
from tab_event_log (nolock)
where
timestamp >= '2010-09-01 00:00:00' /* Edit start date (date format: YYYY-MM-DD) */
and timestamp <= '2010-10-01 00:00:00' /* Edit end date (date format: YYYY-MM-DD) */
and entityid ='00000000-0000-0000-0000-000000000000'
and (eventtype not between 6 and 10)
and SearchURL not like '%/_layouts/%' /* Ignore SharePoint central administration pages */
group by SearchURL
order by count(SearchURL) desc
...
/* Example #1: URL with custom parameters */
http://www.intlock.com/Pages/default.aspx?department=sales
/* Example #2: Access from the internal WFE*/
http://websrv01/Pages/default.aspx
/* Example #3: Access from an insecure channel (for SSL monitored environments such as https://www.intlock.com) */
http://www.intlock.com/
/* Example #4: External events (non-monitored environments) */
http://www.amazon.com/
2. Check if the URL matches a SharePoint tree item. If it does not match, identify one of the reason reasons for the missing URL:
- URL with custom parameters
- URL with an internal server name (, instead of FQDN)
- URL with a non-secure channel (, instead of a secure channel and vice-versa)
- URL for from a non-monitored environment
3. Create URL mappings Mappings based on Regular Expressions (See how to create URL Mappings). Examples. Examples:
- URL with custom parameters:
pattern: "aspx\?department=.*"
action: "aspx"
- URL with an internal server name (, instead of FQDN):
pattern: "http://websrv01/(.*)"
action: "http://www.intlock.com/${1}"
- URL with a non-secure channel (, instead of a secure channel and viceor vice-versa):
pattern: "http://www.intlock.com/(.*)"
action: "https://www.intlock.com/${1}"
- Data for URLs for from a non-monitored environment (external) or administration pages (under /_layouts) can be seen in the "Page Views By URL" and "Unique Users by URL" reports.
...
1. Make sure that there is a full backup of the CardioLog database before you continue to the next step.
2. Fix your history usage data according to the your URL Mappings. The following example replaces the internal server name with the FQDN (edit Edit the start date timestamp in the SQL query to a relevant date range for you).
Create a script based on this example to fix history data according to for the URL Mapping you have created and . Then execute it against the CardioLog database.
/* Example: Replace the internal server name to the portal name - http http://websrv01/ > http > http://www.intlock.com/ */
Use CardioLog
GO
declare @top int
declare @startTime datetime
declare @endTime datetime
set @top = 10000
set @startTime = '2010-09-01' /* Edit the start date (date format: YYYY-MM-DD) */
set @endTime = GETDATE() + 1
select top 1 '1' from tab_event_log
while @@rowCOunt > 0
begin
print cast(@top as varchar(50))
;with a as (select top (@top) url, searchUrl, QueryString
from tab_event_log LG
where
eventtype in (0,1)
and Timestamp >= @startTime
and Timestamp < @endTime
and entityid = '00000000-0000-0000-0000-000000000000'
and SearchURL like 'http://websrv01/%'
)
update a
set QueryString = Url,
Url = substring(replace(url,'http://websrv01/','http://www.intlock.com/'), 0, 1000),
SearchURL = substring(replace(SearchURL,'http://websvr01/','http://www.intlock.com/'), 0, 400)
end
GO
3. Execute the following SQL script against the CardioLog database to map the Lost Events lost events to their corresponding SharePoint tree item. This can be done for a specific date range (edit editing the date range timestamp in the SQL query):
Use CardioLog
GO
/********************************************************************************/
/**** Fix Lost Events *****/
/********************************************************************************/
IF NOT EXISTS (SELECT name FROM sysindexes WHERE name = 'idx_tab_event_log_temp_for_update')
create index idx_tab_event_log_temp_for_update on tab_event_log(entityId, eventLogId) include (timestamp, eventType, entityType)
GO
declare @startTime datetime
declare @endTime datetime
declare @top int
declare @rowsupdated bigint
declare @continue smallint
declare @EventLogId bigint
set @top = 10000
set @rowsupdated = 0
set @startTime = '2010-09-01' /* Edit start date (date format: YYYY-MM-DD) */
set @endTime = GETDATE() + 1
if object_id('tempdb..#tmp_fix_url') IS NOT NULL
begin
drop table #tmp_fix_url
end
select top (@top) LG.eventlogid
into #tmp_fix_url
from
tab_Event_log LG
where
LG.[timestamp] >= @startTime
and LG.[timestamp] < @endTime
and LG.entityid = '00000000-0000-0000-0000-000000000000'
and (LG.eventtype in (0,1))
and LG.entitytype <> -2
order by LG.EventLogId asc
select @continue = @@ROWCOUNT
while @continue > 0 begin
update LG
set LG.entityid = TR.id, LG.splocation = TR.location,
LG.entitytype = TR.entitytype
from tab_event_log LG
join
tab_sharepoint_tree TR on LG.SearchURL = TR.url
join
#tmp_fix_url tmp on tmp.EventLogId = LG.EventLogId
where TR.isDeleted=0
select @rowsupdated = @rowsupdated + @@ROWCOUNT
begin try
print convert(varchar(30), getdate(), 121) + ' - ' + cast(@rowsupdated as varchar(10)) + ' rows were updated'
end try
begin catch
end catch
select @EventLogId=max(EventLogId) from #tmp_fix_url
truncate table #tmp_fix_url
insert into #tmp_fix_url
select top (@top) LG.eventlogid
from tab_Event_log LG
where
LG.EventLogId > @EventLogId and
LG.entityid = '00000000-0000-0000-0000-000000000000' and
LG.[timestamp] >= @startTime and
LG.[timestamp] < @endTime and
(LG.eventtype in (0,1)) and
LG.entitytype <> -2
order by EventLogId asc
select @continue = @@ROWCOUNT
end
drop index tab_event_log.idx_tab_event_log_temp_for_update
/* Delete reporting data cache */
delete from tab_controls_cache
print 'Done'
...