How To: Create a Report of All Workgroup Pages that Have Not Been Hit - TSR (important)

This query will give you a report of which workgroup pages have not been accessed between the dates you specify.

1) First, edit the query below with your specific information.

Replace “Production_Portal_PortalFx” with the name of your portal framework database.

Enter the starting date of the range you want to report. For example, if you want information starting from March 1, 2017, enter 3/1/2017 in the single quote marks after @StartDate.

In the number section of the DATEADD function, enter the number of days, from the start date (and including the start date), that you want to report. For example, if you want to report data for an entire year from your start date, enter 365 for the middle number. If you want only one week, enter 7. In the example below, we are gathering data for an entire year, beginning with January 1, 2017.

2) Run the query.

3) OPTIONAL: Save your report in an Excel spreadsheet.

To save your report in an Excel spreadsheet, in your SQL Management Studio, select to send the results to a file. Go to Query>Results To>Results to File. Run the query again. You will be prompted to save the file (it will save in the .rpt format, by default).

Go into Excel and open the .rpt file you just saved. In the first window, click Next. In the next window, in the Delimiters section, click off the Tab box. Click the Other box, and enter the carat (^) in the empty box next to Other. Click Next. Click Finish.

To save this file as an Excel workbook file, go to File>Save As. In the “Save as type” box, choose Excel Workbook. Click Save.

/****** Setting Start Date and End Date  ******/

DECLARE @StartDate DateTime

DECLARE @EndDate DateTime

SET @StartDate = '1/1/2017'

SET @EndDate = DATEADD (day, 365, @StartDate)

/****  Report the workgroup pages that were not accessed during the specified date range  ****/

SELECT ViewName, DisplayText

FROM Production_Portal_PortalFx.dbo.PWViews, Production_Portal_PortalFx.dbo.PWPages

WHERE PWPages.ViewID = PWViews.ViewID AND

PWViews.ViewType = 2 AND

CAST (PWPages.PageID AS varchar (50)) NOT IN (

SELECT PageID

  FROM Production_Portal_PortalFx.dbo.PWTrafficMonitorEntries

  WHERE CategoryID IN (

          SELECT CategoryID FROM Production_Portal_PortalFx.dbo.PWTrafficMonitorCategories

          WHERE CategoryName= 'Pages')

  AND DateTime > @StartDate and DateTime < @EndDate)

ORDER BY ViewName, DisplayText