1

All of my analytic's are running in my farm, but when I open the Usage reports I am getting "we found a problem with some content ins usage.xlsx, Do you want us to try and recover..."

So I cant see any of my popularity trends and it is vexing me to why. If I go to popularity reports and look up top queries by day and by month... all of those excel files open fine. Number of queries opens fine as well. Only the Usage.xlsx file is not opening.

Is it the table that SharePoint creates that is causing the issue? How do I change this?

I've followed all of the suggested actions from technet

  • the logs show recent files
  • the event logs have recent files
  • Reports feature is activate
  • the timer jobs are running fine
  • i tried downloading the file and changing the '.xls' instead of '.xlsx' to no avail
  • the databases appear to have data.
  • I have restarted the timer jobs repeatedly to get them to attempt more
  • I actually downloaded a program that opens excel files that are corrupted and it was able to open the file and it showed there was data in the file but the charts did not work.

still when I open the file it says that it is corrupted. What interests me about the issue is that all of the other reports work but the usage report does not.

I think I am getting close. It must be something with the compression of the file, so I am trying to reset that compression by using the anaylitic's plug in using the following code:

#Set up the Anaytics powershell environment
Add-PSSnapin Microsoft.SharePoint.PowerShell
Add-PSSnapin hostcontrollerpssnapin
Add-pssnapin junopssnapin
Add-pssnapin searchcorepssnapin
Add-pssnapin enginepssnapin
Add-pssnapin analysisenginepssnapin
$env:CERES_REGISTRY_PRODUCT_NAME = "Office Server\15.0\Search\Ceres"
Connect-System -Uri (Get-      SPEnterpriseSearchServiceApplication).SystemManagerLocations[0] -ServiceIdentity     (Get-SPEnterpriseSearchService).ProcessIdentity
Connect-AnalysisEngine -NodeName AdminComponent1


Get-Command -Module analysisenginepssnapin

Start-Analysis UsageAnalytics

Start-Analysis SearchReports



Set-AnalysisConfiguration

this is where I run into trouble, there is disable binary format and disable compressed format that I both want to set to "true" from their current state "false". This may or may not fix the issue, but I am hoping. The trouble I run into is that we cant figure out how to set it to false using the set command above

update: So I tried deleting the service application than provisioning it, I then ran the reports again using the analytic's engine and still came up with no change. The search reports still work but when I click on the usage reports they come up with this excel file is corrupted. Now I did find the second part of the It Unity article which was short and did not cover the changing of thecompressed format but that is just a shot in the dark to wheter it works anyways.

Update:

So I deleted the usageapplication and turned off reporting on every single site in the portal. Still didnt work. The excel file still opens corrupted. I even changed the location of the log files from \usagelogs to \usagelogs2, so that I would have fresh data

A couple of interesting points from it though

  1. with the usageapplication deleted and reporting turned on the excel file is still corrupted 2.reinstated usagepplication with new DB and then enabled reporting. First attempt on excel... still corrupted.

I recently created a QA enviroment using the same databases and the excel file downloads fine and can be opened.

Below is the first bunch of lines from the hex to show what is going on with the excel file:

This is a picture of the hex from hexEd.it

Below is the configuration of usageanalytics that I got from using the analysis snapin:

Usage analytics information

Below is a image of the exiftool results:

Exiftool picture

enter image description here

  • What is the size of typical Usage.xlsx? Also would you try to rename it to Usage.zip and opening in explorer? – Aziz Kabyshev Mar 2 '16 at 22:02
  • The size of all of them are 12kb. Yes we put in a zip and renamed it and it still would not open correctly. It should open correctly from the download though. It should not download as a corrupted file – Renton Blackstone Mar 2 '16 at 22:06
  • Can you upload a file to hexed.it and post a screenshot of how the first dozen of lines is shown? This is a fileheader, I want to compare to normal xlsx. Like this: i.imgur.com/WHEU3Kv.png – Aziz Kabyshev Mar 2 '16 at 22:17
  • I did it, had to delete the other links because I need more reputation to put more links – Renton Blackstone Mar 2 '16 at 22:32
  • 1
    This is the only method available since you've provided no information about your farm in general, OS/SQL versions, ULS log fragments, and the problem is not reproducible in my environment. Structured approach, you know. I'm not asking you to dump owstimer process memory and run Windbg with cryptic keys. Just trying to help – Aziz Kabyshev Mar 2 '16 at 23:30
0

So.. fixed the problem. Metalogix was broken. I recently got a QA environment set up where we had the confidence to repair Metalogix. Magically, usage reports were working again.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.