The fine tuning process is essential to ensure qualitative and accurate data. This section provides detailed instructions on how to perform the fine tuning process.

CardioLog collects usage data for various event types (views, visits, duration, search etc.). The Usage Data Processing service component matches events to their corresponding SharePoint tree item. An event which has a URL that does not match any tree item (for instance, a URL address which includes a list of custom parameters, or a URL which is not part of the monitored environments) is called a "Lost Event". Lost events are not displayed in the reports' results by default, they appear only after tuning the data.

Tuning the data includes of the following steps:

  1. Determining which data should not be collected and defining data collection filters.
  2. Identifying Lost Events and defining URL address modifications

It is recommended to perform this process a month after the initial installation of the production environment, 2-3 weeks after an upgrade and 2-3 weeks after adding a new monitored environment to CardioLog.

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

1.  Execute the following SQL script against the CardioLog database to get a list of the lost events URLs. This can be done for a specific date range (edit the date range in the SQL query):

Query results example:

/* 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 not, identify the reason for the missing URL:

3.  Create URL mappings based on Regular Expressions (See how to create URL Mappings). Examples:

Fixing Lost Events

1.  Make sure that there is a full backup of the CardioLog database before you continue to the next step.

2.  Fix history usage data according to the URL Mappings. The following example replaces the internal server name with the FQDN (edit the start date in the SQL query). 
     Create a script based on this example to fix history data according to the URL Mapping you have created and execute it against the CardioLog database.

/* Example: Replace the internal server name to the portal name - http://websrv01/ > 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 to their corresponding SharePoint tree item. This can be done for a specific date range (edit the date range 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'

Automatic Fine Tuning

The SharePoint Tree Auto Fine Tuning service is used in order to map SharePoint URLs to the corresponding SharePoint tree object in Object Explorer automatically. For instance, if users access a SharePoint website through different zones (public URL's), or to SharePoint pages with custom parameters, this service will map the different URLs into a single SharePoint object, bypassing the need to manually create an entry in the URL Mappings module.

Each SharePoint object in the SharePoint tree structure has a unique  SharePoint object ID. The Portal Tree Updates service component retrieves all lost events and sends them to the SPIDFinder web service (located on the SharePoint WFEs), and then maps them by their SPID in the tree structure.

 Starting version 2.0.6.0, the SharePoint Tree Auto Fine Tuning web service is installed with the CardioLog Analytics SharePoint feature by default.

Manual Installation

Perform the following steps for every SharePoint WFE:

  1. Create a CardioLogAgent folder under the SharePoint _layouts folder:

    For MOSS 2007 only:
    C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\12\TEMPLATE\LAYOUTS\

    For SharePoint 2010 only:
    C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\14\TEMPLATE\LAYOUTS\

    For SharePoint 2013 only:
    C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\15\TEMPLATE\LAYOUTS\
     
  2. Copy the following files into the new folder from [CardioLog Installation Folder]\CardioLogAgent\:
  3. In /_layouts/CardioLogAgent/Web.config (located on the SharePoint WFE), add the following key in the <assemblies> section:

    For MOSS 2007 only:
    <add assembly="Microsoft.SharePoint, Version=12.0.0.0, Culture=neutral, PublicKeyToken=71E9BCE111E9429C"/>

    For SharePoint 2010 only:
    <add assembly="Microsoft.SharePoint, Version=14.0.0.0, Culture=neutral, PublicKeyToken=71E9BCE111E9429C"/>

    For SharePoint 2013 only:
    <add assembly="Microsoft.SharePoint, Version=15.0.0.0, Culture=neutral, PublicKeyToken=71E9BCE111E9429C"/> 
  4. In /_layouts/CardioLogAgent/Web.config (located on the SharePoint WFE), edit the following key in the <appsettings> section. The key value can be set to true or false. When true (by default), the SharePoint Tree Auto Fine Tuning web service runs under the Farm Administrator account.


    <add key="SpidFinderRunWithElevatedPrivileges" value="true" />

     
  5. In [CardioLog Installation Folder]/CardioLog.Services.exe.config (located on the CardioLog application server), edit the following keys if needed:

    <add key="GetLostUrlsRegExExclude" value="_layouts|_vti_bin|editform\.aspx|newform\.aspx|aspx&amp;"/>
    A regular expression for excluding URLs to be fine-tuned.

    <add key="RunSpidFinder" value="true"/>
    Enable/disable the SharePoint Tree Auto Fine Tuning web service. Values - true/false
  6. In IIS, create a CardioLogAgent web application under _layouts
  7. Verify that the SharePoint web application pool user has full control access to the C:\Windows\Temp folder
  8. Test the Web Service by browsing SpidFinder.asmx and invoking the GetSPIDbyURL method. Submit the URL for your SharePoint website homepage, and verify that the guid value is not "00000000-0000-0000-0000-000000000000" 

Refreshing the Report's Data

  1. Clear the reports data cache: 
     Go to CardioLog > Administration > System Configuration > Reporting Data and click on Clear Cache OR execute the following SQL script against the CardioLog database:
  1. Delete history versions of the report: 
     Go to CardioLog > Report Center, click the report and click on Delete Historical Data.
  2. Regenerate reports: 
    CardioLog reports are generated automatically by the 
    Report Scheduling service. To regenerate a report on your own, click the report and select Open, then click Generate Report.