• Home

How to display most viewed documents from a Document Library

By peter.stilgoe

Came accross an interesting solution on Linkedin to display most viewed Documents from a Document Library in a webpart. I havnt tried it myself but sounds like it would work:

1) Install LogParser on Web App Server (Sharepoint Index Server)

2) Setup a batch file with the LogParse query of your choice.
For example:
“C:\Program Files\Log Parser 2.2\logparser” -i:IISW3C -o:XML “SELECT TOP 10 REPLACE_CHR(cs-uri-stem,’+',’ ‘) AS sUrl, EXTRACT_FILENAME(cs-uri-stem) AS sName, EXTRACT_EXTENSION(cs-uri-stem) AS sExt, COUNT(*) AS [Total Hits] INTO MYFILE.XML FROM \\WFE1\C$\Windows\system32\LogFiles\W3SVC1\*.log, \\WFE2\C$\Windows\system32\LogFiles\W3SVC1\*.log WHERE TO_UPPERCASE(sUrl) Like ‘/SITES/SPECIALSITE%%’ AND TO_UPPERCASE( sExt ) IN (‘DOCX’;'PPTX’;'PNG’;'GIF’;'JPG’;'BMP’) AND TO_UPPERCASE(sUrl) Not Like ‘%%/_T/%%’ AND TO_UPPERCASE(sUrl) Not Like ‘%%/_W/%%’ GROUP BY sUrl,sName,sExt ORDER BY [Total Hits] DESC

3) This query code search only for some documents (docx, pptx, etc.) on the site ‘/sites/specialsite’ on the default web application and site collection W3SVC1. The logs are located on WFE1 and WFE2. The log parser result file is an XML file named MYFILE.XML.

4) Daily Schedule the execute of the batch and the upload of the xml file to a sharepoint document library

5) Create an XSLT file for presentation and upload to the sharepoint document library.

6) Finally show the XML with the XML WebPart with the xml and xslt files as the parameters.

More From pstilgoe

• Import data from CSV and SQL to a SharePoint list
• How to embed a powerpoint presentation into a Sharepoint page
• How to make the filter choices display by default in the DVWP

Ask pstilgoe To Recommend Your Posts

Sharepoint How To , xml , XML Webpart

Sharepoint / MOSS / WSS, Sharepoint How To, Sharepoint Webparts, xml 4 Comments May 13th, 2010

About... peter.stilgoe

This author published 482 posts in this site.
Sharepoint, InfoPath, K2, Nintex, Business Process Mapping, Business Intelligence, Automation, ECM, Document Management, Document Imaging, Internet Marketing & Online Business Consultant Email / MSN: pstilgoe@hotmail.com LinkedIn: Pete Stilgoe - Sharepoint Consultant

Share

4 Comments

• At 2011.02.16 08:47, Fernando Nunes said:
  

  Will this work in a topology with multiple frontends and multiple IIS logs?

  [Reply]

  
  
  
  
  

  

  peter.stilgoe Reply:
  February 16th, 2011 at 8:53 am

  Yeah, if you look at the script you specify the location of the log files on each web front end

  [php]\\WFE1\C$\Windows\system32\LogFiles\W3SVC1\*.log, \\WFE2\C$\Windows\system32\LogFiles\W3SVC1\*.log[/php]

  [Reply]

  
  
  
  
  
  [Reply]
• At 2011.06.09 15:05, eabdo said:
  

  thank u for ur post but the code u supplies not worked for me it give me error i typed in cmd

  C:\Program Files (x86)\Log Parser 2.2>LogParser “SELECT TOP 10 REPLACE_CHR(cs-u
  ri-stem,’+',’ ‘) AS sUrl, EXTRACT_FILENAME(cs-uri-stem) AS sName, EXTRACT_EXTENS
  ION(cs-uri-stem) AS sExt, COUNT(*) AS [Total Hits] INTO MYFILE.XML FROM \\WFE1\C
  $\Windows\system32\LogFiles\W3SVC1\*.log, \\WFE2\C$\Windows\system32\LogFiles\W3
  SVC1\*.log WHERE TO_UPPERCASE(sUrl) Like ‘/SITES/SPECIALSITE%%’ AND TO_UPPERCASE
  ( sExt ) IN (‘DOCX’;'PPTX’;'PNG’;'GIF’;'JPG’;'BMP’) AND TO_UPPERCASE(sUrl) Not Like ‘%%/_T/%%’ AND TO_UPPERCASE(sUrl) Not Like ‘%%/_W/%%’ GROUP BY sUrl,sName,sExt ORDER BY [Total Hits] DESC”

  and it give me the following error

  WARNING: Input format not specified – using TEXTLINE input format.
  Error: SELECT clause: Syntax Error: unknown field ‘cs-uri-stem’

  To see valid fields for the TEXTLINE input format type:
  LogParser -h -i:TEXTLINE
  please if u have any idea contact me

  thanks for ur time

  [Reply]

  [Reply]
• At 2011.07.29 14:26, Brian said:
  

  The replace_chr function doesn’t work for me – if i just use ‘select top 10 cs-uri-stem’ it produces a list of ten documents, but if i use ‘select top 10 replace_chr(cs-uri-stem,’+',’ ‘) i get no list at all. Also, go you provide a link or go in to more detail on the creation of the XSLT file?

  [Reply]

  [Reply]