locked
Powershell script to run fn_WA_GetTopVisitors RRS feed

  • Question

  • Hello.  I'm trying to put together a PowerShell script that can be scheduled to run monthly instead of going into Central Admin and running the Web Analytics Top Visitors report manually.  I've modified code from this article: https://gallery.technet.microsoft.com/office/Get-SharePoint-Web-19cd2137

    My version accepts the context, startdate, and enddate as parameters and is trying to call fn_WA_GetTopVisitors.  However, I am not getting any results from the script.  When I run the Top Visitors report in Central Admin for this web application and date range, I am getting results so I expect the script to have the same results.  Here is my code:

     
     
    Function Get-WASummaryReport($Context,$ReportStartDate, $ReportEndDate){ 
        Add-PSSnapin Microsoft.SharePoint.PowerShell -ea 0; 
        [System.Reflection.Assembly]::Load("Microsoft.Office.Server.WebAnalytics, Version=14.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c") | Out-Null; 
        [System.Reflection.Assembly]::Load("Microsoft.Office.Server.WebAnalytics.UI, Version=14.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c") | Out-Null; 
         
        Function DateTimeToDateId ([System.DateTime]$dt){ 
            if (![System.String]::IsNullOrEmpty($dt.ToString())){ 
                return [System.Int32]::Parse($dt.ToString("yyyyMMdd", [System.Globalization.CultureInfo]::InvariantCulture), [System.Globalization.CultureInfo]::InvariantCulture); 
            }else{ 
                return 0; 
            } 
        } 
         
        #Not used in this report but other report types require it. 
        Function GetSortOrder([String]$sortColumn,[Microsoft.Office.Server.WebAnalytics.ProcessedDataRetriever.OrderType]$order){ 
            $SortOrders = New-Object System.Collections.Generic.List[Microsoft.Office.Server.WebAnalytics.ProcessedDataRetriever.SortOrder]; 
            $sortOrders.Add((New-Object Microsoft.Office.Server.WebAnalytics.ProcessedDataRetriever.SortOrder($sortColumn, $order))); 
            return ,$SortOrders 
        } 
         
        $AggregationContext = [Microsoft.Office.Server.WebAnalytics.ProcessedDataRetriever.AggregationContext]::GetContext($Context); 
        if (!$?){throw "Cant get the Aggregation Context";}
        else{
            write-host "AggregationContext:  $AggregationContext"
        } 
    
        # Create parameters list     
        $viewParamsList = New-Object System.Collections.Generic.List[Microsoft.Office.Server.WebAnalytics.ProcessedDataRetriever.ViewParameterValue] 
        $viewParamsList.Add((New-Object Microsoft.Office.Server.WebAnalytics.ProcessedDataRetriever.ViewParameterValue("StartDateId", (DateTimeToDateId($ReportStartDate))))); 
        $viewParamsList.Add((New-Object Microsoft.Office.Server.WebAnalytics.ProcessedDataRetriever.ViewParameterValue("EndDateId", (DateTimeToDateId($ReportEndDate))))); 
     
        $dataPacket = [Microsoft.Office.Server.WebAnalytics.Reporting.FrontEndDataRetriever]::QueryData($AggregationContext, $null, "fn_WA_GetTopVisitors", $viewParamsList, $null, $null, 1, 25000, $False); 
        if (!$?){throw "Unable to get the Data. Try running the script as the Farm Account. If that doesnt work, make sure that the Web Analytics Service Application is connected to the Web Application and that the Site Web Analytics reports work through the browser.";}  
        return $dataPacket.DataTable
        
         
    } 
     
    $WebApp = Get-SPWebApplication https://mywebapp.com
    
    #Sets CSV Path
    $path="d:\Logs\WA_SummaryReport\$(get-date -f yyyy-MM)_MY_WebApp_TopVisitors.csv" 
    
    $startDate = 20150201
    $endDate = 20150228
    
    Get-WASummaryReport -Context $WebApp -ReportStartDate $startDate -ReportEndDate $endDate | Export-Csv $path -NoTypeInformation
    

    Tuesday, March 3, 2015 8:55 PM

Answers

  • Yes, I have one less parameter because the fn_WA_GetTopVisitors function has one less argument. Anyway, I gave up on this and found another solution that meets my needs. Here's a sample snippet from my code:

    $server = "CentralAdminUrl" 
    $webAppName = "MyWebApp" 
    $sd = 01/01/2015 
    $ed = 01/31/2015 
    
    $wc = New-Object System.Net.WebClient 
    $wc.Credentials =  [System.Net.CredentialCache]::DefaultCredentials 
    $url = $server+ "/_layouts/WebAnalytics/Report.aspx?t=TopVisitorsReport&l=wa&id=https%3a%2f%2f"+$id+"%2f&WebAppName="+$webAppName+"&CD=true&SD="+$sd+"&ED="+$ed+"&SHOWANALYZETAB=1&Export=1" 
    
    $file = "d:\Logs\$(get-date -f yyyy-MM)-WebApp-TopVisitors.csv" 
    
    $wc.DownloadFile($url, $file) 

    Go in Central Admin on your SharePoint server and look at the URL for the web analytics reports. You can just create a web client object in your code and use that url to get the report. All you need to do is enter the values for the variables in the snippet above to initialize the value of the $url variable.

    • Edited by esotto Thursday, March 5, 2015 7:37 PM
    • Marked as answer by esotto Thursday, March 5, 2015 7:37 PM
    Thursday, March 5, 2015 7:34 PM

All replies

  • Does the stock script work?

    You've got one less parameter than Ivan lists and your two are different to his. If you've gotten his working then you can narrow the possible causes of your issues down as you modify the code and see what breaks.

    Tuesday, March 3, 2015 9:54 PM
  • Have you put this in ISE and debugged it?

    If this is helpful please mark it so. Also if this solved your problem mark as answer.

    Wednesday, March 4, 2015 9:17 AM
  • Yes, I have one less parameter because the fn_WA_GetTopVisitors function has one less argument. Anyway, I gave up on this and found another solution that meets my needs. Here's a sample snippet from my code:

    $server = "CentralAdminUrl" 
    $webAppName = "MyWebApp" 
    $sd = 01/01/2015 
    $ed = 01/31/2015 
    
    $wc = New-Object System.Net.WebClient 
    $wc.Credentials =  [System.Net.CredentialCache]::DefaultCredentials 
    $url = $server+ "/_layouts/WebAnalytics/Report.aspx?t=TopVisitorsReport&l=wa&id=https%3a%2f%2f"+$id+"%2f&WebAppName="+$webAppName+"&CD=true&SD="+$sd+"&ED="+$ed+"&SHOWANALYZETAB=1&Export=1" 
    
    $file = "d:\Logs\$(get-date -f yyyy-MM)-WebApp-TopVisitors.csv" 
    
    $wc.DownloadFile($url, $file) 

    Go in Central Admin on your SharePoint server and look at the URL for the web analytics reports. You can just create a web client object in your code and use that url to get the report. All you need to do is enter the values for the variables in the snippet above to initialize the value of the $url variable.

    • Edited by esotto Thursday, March 5, 2015 7:37 PM
    • Marked as answer by esotto Thursday, March 5, 2015 7:37 PM
    Thursday, March 5, 2015 7:34 PM
  • Hi,

    Thank you for your detailed additional feedback on how you were successful in resolving this issue.

    Your solution will benefit many other users, and we really value having you as a Microsoft customer.

    Have a nice day!

    Best Regards,

    Lisa Chen


    TechNet Community Support
    Please remember to mark the replies as answers if they help, and unmark the answers if they provide no help. If you have feedback for TechNet Support, contact tnmff@microsoft.com.

    Thursday, March 19, 2015 5:11 AM
  • If you'd like to follow the same pattern as Ivan, calling the fn_WA_GetTopVisitors function, then I have provided a working sample below in Get-WAUserReport.ps1:

    #------------------------------------------------------------------------------------------- 
    # Name:            Get-WAUserReport.ps1
    # Description:     This script will get the Web Analytics Top Users Report 
    # Usage:         Run the function with the required parameters 
    #                Context can be SPWebApplication, SPSite or SPWeb 
    # Adapted By:    Colin Thornton
    # Adapted from:  Ivan Josipovic, softlanding.ca 
    #                https://gallery.technet.microsoft.com/office/Get-SharePoint-Web-19cd2137
    #------------------------------------------------------------------------------------------- 
      Add-PSSnapin "Microsoft.SharePoint.PowerShell" -erroraction SilentlyContinue;
      
    Function Get-WAUserReport($Context,$DaysToGoBack,$IncludeSubsites = 0){ 
        [System.Reflection.Assembly]::Load("Microsoft.Office.Server.WebAnalytics, Version=14.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c") | Out-Null; 
        [System.Reflection.Assembly]::Load("Microsoft.Office.Server.WebAnalytics.UI, Version=14.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c") | Out-Null; 
         
        Function DateTimeToDateId ([System.DateTime]$dt){ 
            if (![System.String]::IsNullOrEmpty($dt.ToString())){ 
                return [System.Int32]::Parse($dt.ToString("yyyyMMdd", [System.Globalization.CultureInfo]::InvariantCulture), [System.Globalization.CultureInfo]::InvariantCulture); 
            }else{ 
                return 0; 
            } 
        } 
         
        #Not used in this report but other report types require it. 
        Function GetSortOrder([String]$sortColumn,[Microsoft.Office.Server.WebAnalytics.ProcessedDataRetriever.OrderType]$order){ 
            $SortOrders = New-Object System.Collections.Generic.List[Microsoft.Office.Server.WebAnalytics.ProcessedDataRetriever.SortOrder]; 
            $sortOrders.Add((New-Object Microsoft.Office.Server.WebAnalytics.ProcessedDataRetriever.SortOrder($sortColumn, $order))); 
            return ,$SortOrders 
        } 
         
        $AggregationContext = [Microsoft.Office.Server.WebAnalytics.ProcessedDataRetriever.AggregationContext]::GetContext($Context); 
        if (!$?){throw "Cant get the Aggregation Context";} 
         
        $viewParamsList = New-Object System.Collections.Generic.List[Microsoft.Office.Server.WebAnalytics.ProcessedDataRetriever.ViewParameterValue] 
        $viewParamsList.Add((New-Object Microsoft.Office.Server.WebAnalytics.ProcessedDataRetriever.ViewParameterValue("StartDateId", (DateTimeToDateId([System.DateTime]::UtcNow.AddDays(-($DaysToGoBack))))))); 
        $viewParamsList.Add((New-Object Microsoft.Office.Server.WebAnalytics.ProcessedDataRetriever.ViewParameterValue("EndDateId", (DateTimeToDateId([System.DateTime]::UtcNow.AddDays(-1)))))); 
        $viewParamsList.Add((New-Object Microsoft.Office.Server.WebAnalytics.ProcessedDataRetriever.ViewParameterValue("IncludeSubSites", $IncludeSubsites))); 
     
        $dataPacket = [Microsoft.Office.Server.WebAnalytics.Reporting.FrontEndDataRetriever]::QueryData($AggregationContext, $null, "fn_WA_GetTopVisitors", $viewParamsList, $null, $null, 1, 25000, $False); 
        if (!$?){throw "Unable to get the Data. Try running the script as the Farm Account. If that doesnt work, make sure that the Web Analytics Service Application is connected to the Web Application and that the Site Web Analytics reports work through the browser.";} 
         
        return $dataPacket.DataTable 
    } 
    
      # IncludeSubsites = 1 to include sub sites, 0 to exclude them.
      
    $WebApps = Get-SPWebApplication 
     foreach($WebApp in $WebApps)
     {
       write-output $WebApp.Url
       Get-WAUserReport -Context $WebApp -DaysToGoBack 30 -IncludeSubsites 0
       $Sites = Get-SPSite -WebApplication $WebApp -Limit All
       
       foreach($Site in $Sites)
       {
         write-output ("Site: " + $Site.Url)
         Get-WAUserReport -Context $Site -DaysToGoBack 30 -IncludeSubsites 1
         foreach($web in $Site.AllWebs)
         {
           write-output("Web: " + $web.Url)
           Get-WAUserReport -Context $web -DaysToGoBack 30 -IncludeSubsites 0
         }
      }
    }


    I hope that this helps someone one day.


    BillyBob

    • Proposed as answer by rbsp2k2 Wednesday, May 9, 2018 3:50 PM
    Thursday, August 3, 2017 1:57 AM