Manhar Sharma

Sharepoint 2010 Web Analytics Troubleshooting (Reporting DB)

Manhar Sharma — Sat, 13 Oct 2012 01:47:00 GMT

We can divide the Troubleshooting of Web Analytics into 3 different multiple parts.

The data is moves from

1.)	.usage files are created on all the WFEs.
2.)	Data moves from .usage files to Staging Database and Logging Database with Import timer jobs.
3.)	Data moved from Staging Database to Reporting Database by Report Consolidation components.

Below I have included some basic steps which can be tried to confirm if Data is getting moved from Staging DB to Reporting DB

Troubleshooting Steps:

The Data which need to be written to the Reporting Database that is gathered in the fact tables in the Staging database.
Data is moved into the Reporting Database from staging database by the following Timer job "Microsoft SharePoint Foundation Usage Data Processing " which runs Daily once around 2.00 AM.

If you will check the ULS logs around 2.00 AM you can see the Web Analytics Service Component Controller, ReportConsolidatorComponent, WebAnalytics.DatabaseMaintenance and other jobs execute.
For fact table you can get in uls logs :-

------------------------------------------------------------------

10-09-2012 02:00:19.17 WebAnalyticsService.exe (0x1570) 0x1104 Web Analytics Services Data Consolidator c2af Verbose Copying the fact data from the source database using the connection string Data Source=spsqlext10;Initial Catalog=WebAnalyticsServiceApplicationStagingDB

10-09-2012 02:00:19.17 WebAnalyticsService.exe (0x1570) 0x1104 Web Analytics Services Data Consolidator c2ak Verbose Copying site inventory table
10-09-2012 02:00:19.17 WebAnalyticsService.exe (0x1570) 0x1104 Web Analytics Services Data Consolidator g14w Medium The last inventory copy time is 10/08/2012 00:00:00.
10-09-2012 02:00:19.18 WebAnalyticsService.exe (0x1570) 0x1104 Web Analytics Services Data Consolidator c2al Verbose Finished copying site inventory table
10-09-2012 02:00:19.18 WebAnalyticsService.exe (0x1570) 0x1104 Web Analytics Services Data Consolidator c2ag Verbose Copying the fact table dbo.WAQueryText
10-09-2012 02:00:19.20 WebAnalyticsService.exe (0x1570) 0x1104 Web Analytics Services Data Consolidator c2ag Verbose Copying the fact table dbo.WAScope
10-09-2012 02:00:19.22 WebAnalyticsService.exe (0x1570) 0x1104 Web Analytics Services Data Consolidator c2ag Verbose Copying the fact table dbo.WAUserTitle
10-09-2012 02:00:19.24 WebAnalyticsService.exe (0x1570) 0x1104 Web Analytics Services Data Consolidator c2ag Verbose Copying the fact table dbo.WAUserDepartment
10-09-2012 02:00:19.26 WebAnalyticsService.exe (0x1570) 0x1104 Web Analytics Services Data Consolidator d0t3 Verbose Copying the fact table dbo.WAAssetMetadata
10-09-2012 02:00:19.39 WebAnalyticsService.exe (0x1570) 0x1104 Web Analytics Services Data Consolidator c2ah Verbose Copying the fact table [dbo].[WAClickFact]
10-09-2012 02:00:19.44 WebAnalyticsService.exe (0x1570) 0x1104 Web Analytics Services Data Consolidator c2ah Verbose Copying the fact table [dbo].[WAResultFact]
10-09-2012 02:00:19.48 WebAnalyticsService.exe (0x1570) 0x1104 Web Analytics Services Data Consolidator c2ah Verbose Copying the fact table [dbo].[WAUserSearchFact]
10-09-2012 02:00:19.50 WebAnalyticsService.exe (0x1570) 0x1104 Web Analytics Services Data Consolidator dg6z Medium There is no new fact data to copy.
10-09-2012 02:00:19.50 WebAnalyticsService.exe (0x1570) 0x1104 Web Analytics Services Data Consolidator c2ai Verbose Finished copying the fact data
10-09-2012 02:00:19.52 WebAnalyticsService.exe (0x1570) 0x1104 Web Analytics Services Data Consolidator c2bg Medium Finished copying fact data to the database with server name MOSS2010.phm.com and database name WebAnalyticsServiceApplication_ReportingDB

-------------------------------------------------------------------------------------------

You can verify when was the last time Data was copied from Staging database to Reporting Database from the following Table=> Dbo.WASetting

You can also compare the data in both the database (staging and reporting) from the table dbo.WAAssetMetadata. This table is in both the databases and can be compared using simple queries..

Select * from dbo.WAAssetMetadata with (nolock)

If you want to check the result for any specific site collection or after specific date to cross check if data is moved or not in the reporting database you can use similar queries…

Select COUNT(*) from dbo.WAAssetMetadata WITH (NOLOCK)

select COUNT(*) from dbo.WAAssetMetadata where AssetId LIKE 'http://sitecollection%'

select COUNT(*) from dbo.WAAssetMetadata where AssetId LIKE 'http://sitecollection%' AND InsertDateTime > '2012-06-2202:00:59.330'

For the above dates you should generally see the web analytics report in site collection web analytics reports.

-----

Also the Reporting database in addition to MDF and LDF files you can see NDF files for this database. Each week reporting Database creates these NDF files.
These are the table partitions and not the database and we should never delete manually these NDF files.
They are deleted by a web analytics process which is ReportConsolidate Component once it cross the retention period. This Process runs daily.

You an create some queries with the help of functions(Reporting Database-> Programmability->Functinos) like To check Number of unique visits and run in reporting database and get the results.

SELECT * FROM [dbo].[WATrafficAggregationByDate] WITH (NOLOCK)
WHERE [AggregationId] = @aggregationid AND [IncludeSubSites] = 1 AND [MetricType] = 2 AND [DateId] BETWEEN @Dateid AND @Dateid

-----------------------------------------------------------------------

How to recreate a new 'Review problem and solutions' List in Central Admin which is corrupted.

Manhar Sharma — Tue, 03 Jul 2012 01:36:00 GMT

'Review problem and solution' list, of central administration is corrupted and throwing error.

Confirming the problem-

If there is corruption in sharepoint central administration lists and when you try to open the items in the list and you are getting errors. In such cases one of the action plan that can be tried is -to delete the corrupted list and create a new list using the same template ID in powershell.

*Always take the backup of admin content database before starting these steps.

Go on sharepoint Central administration -> Monitoring -> 'Review Problem and solution' -> and tried to open any items from different Category like security/configuration/availability others and we getting below error message:-
Of even if we try to edit, delete or try any task with these items we are getting the error message.

For recreation of the Review problem and solution list:-

Check the Template ID used for this list using the below query.
Run the following command on the Admin content database. (database of central admin)

select * from AllLists where tp_Title = 'review problems and solutions'

Note the server template id of this particular list it is =1221
Now need to delete this list forcefully.

Try below PowerShell commands:-
PS C:\Users\Administrator.DC268635> $web = Get-SPWeb http://Centraladminurl
PS C:\Users\Administrator.DC268635> $web.Lists | select Title

And you will get the below result:-

In the above list you can see the Review problems and solution list:-

Now we will delete the list using the PowerShell command

$list=$web.Lists["Review problems and solutions"]
$list.AllowDeletion
$list.AllowDeletion=$True
$list.Update()
$web.Update()
$list.Delete()
$web.Update

And again recreate it using the same template id 1221. A new list will be created with new Guid.

To add the list you need to provide below values
$web.Lists.Add("List Name", "List Discription", $template);

In my case I provided the name of the list as :- Health Reports and Description as "Review problems and solutions"

Once the above commands are completed go to the central administration -> Monitoring -> Review problem and solutions and check if page is loading fine.
Now we need to run the timer Jobs using 'Run Now' for all the health Analysis Jobs(like below).

This will instantly create the items in the Review problems and solutions list.

Getting 'Sample' when trying to 'email a link' - sharepoint 2010

Manhar Sharma — Sat, 09 Jun 2012 03:03:00 GMT

If you are facing this issue with 'Email a link' functionality in sharepoint server 2010 on claims based web authentication you can check below settings.

Problem:-

When we try 'Email a link' from 'Send To' for any document in the site collection

And In the new outlook email if we get the result with just 'sample' (no link) like below:-

Need to Check:-

1.) In IIS for that web applicatin check the anonymous authentication is set as enabled/disabled. if it is set as disabled then you can face this problem.

Save it to Enabled.

When a web application is configured to use claims authentication (windows claims, form-based authentication claims, or SAML claims) the IIS website is always configured to have anonymous access turned on.

2.) In IIS for that web application check the Windows Authentication -> advance setting -> Extended protection = check the value, if it is set to 'Accept' change it to 'Off' and this will fix the issue.

3.) In web.config of the web application check the settings for "serviceHostingEnvironment aspNetCompatibilityEnabled" it should be set as True.

---------------------------------------------------------------------------------------------

Kindly try these steps in testing farm before making below changes in production.

Stop/Start the Application pool in IIS 6.0 using PowerShell.

Manhar Sharma — Tue, 22 May 2012 01:24:00 GMT

Power Shell script to stop the application pool in IIS 6.0:-

--------------------------------------------------------------------------------

$appPool = get-wmiobject -namespace "root\MicrosoftIISv2" -class "IIsApplicationPool" | where-object {$_.Name -eq "W3SVC/AppPools/SharePoint - 80"}
$appPool.Stop()

---------------------------------------------------------------------------------

Power Shell script to start the application pool in IIS 6.0 :-

--------------------------------------------------------------------------------

$appPool = get-wmiobject -namespace "root\MicrosoftIISv2" -class "IIsApplicationPool" | where-object {$_.Name -eq "W3SVC/AppPools/SharePoint - 80"}
$appPool.Start()

---------------------------------------------------------------------------------

** need to change the name of the application pool (sharepoint - 80) as per in your farm.