none
How to get Usage Reports data programmatically. using Powershell or REST. RRS feed

  • Question

  • Hi all,

    I want to get Top 3 read / liked / visited articles from SharePoint site collection. 

    Also I want to have Total number of articles published in a month and Total number of Polls Published.

    How can i get this data using PowerShell or REST api. 

    Please share links or examples of this.

    Regards,

    Amol


    amol

    Tuesday, November 19, 2019 9:11 AM

Answers

  • Hi Amol,

    You can export the usage report data using PowerShell like this:

    Add-PSSnapin "Microsoft.SharePoint.PowerShell"
    
    function ExportRollupAnalyticsData {
        Param(
        [string]$RootSiteUrl,
        [string]$Scope,
        [switch]$IncludeSites,
        [switch]$IncludeWebs,
        [string]$OutputFilePath
        )
    
        # Delete CSV file if existing
        If (Test-Path $OutputFilePath) {
    	    Remove-Item $OutputFilePath
        }
    
        # Write header row to CSV File
        $OutputHeader = "Scope,Name,URL,Most Recent Day with Usage,Hits - All Time,Unique Users - All Time,Hits - Most Recent Day with Usage,Unique Users - Most Recent Day with Usage,Current Date"
        $OutputHeader | Out-File $OutputFilePath -Append 
    
        # Get Web Application for Root Site
        $RootSite = Get-SPSite $RootSiteUrl
        $WebApp = $rootSite.WebApplication 
    
        # Get Search Service Application
        $SearchApp = Get-SPEnterpriseSearchServiceApplication
    
        # Loop through all Site Collections in Web Application
        ForEach($Site in $WebApp.Sites) {    
            
            # Export Site analtyics if -IncludeSites flag is present
            If ($IncludeSites.IsPresent) {
                $Scope = "Site"
                $SiteTitle = $Site.RootWeb.Title.Replace(",", "")
                $SiteUrl = $Site.Url
                $UsageData = $SearchApp.GetRollupAnalyticsItemData(1,[System.Guid]::Empty,$Site.ID,[System.Guid]::Empty)
                $LastProcessingTime = $UsageData.LastProcessingTime
                $CurrentDate = $UsageData.CurrentDate
                $TotalHits = $UsageData.TotalHits
                $TotalUniqueUsers = $UsageData.TotalUniqueUsers
                $LastProcessingHits = $UsageData.LastProcessingHits
                $LastProcessingUniqueUsers = $UsageData.LastProcessingUniqueUsers
    
                # Write Web details to CSV File
                $OutputString = $Scope + "," + $SiteTitle + "," + $SiteUrl + "," + $LastProcessingTime + "," + $TotalHits + "," + $TotalUniqueUsers + "," + $LastProcessingHits + "," + $LastProcessingUniqueUsers + "," + $CurrentDate
                $OutputString | Out-File $OutputFilePath -Append
            }      
    
            # Export Web analtyics if -IncludeWebs flag is present
            If ($IncludeWebs.IsPresent) {
                
                # Loop through all Webs in Site Collection
                ForEach($Web in $Site.AllWebs) {
                    $Scope = "Web"
                    $WebTitle = $Web.Title.Replace(",", "")
                    $WebUrl = $Web.Url
                    $UsageData = $SearchApp.GetRollupAnalyticsItemData(1,[System.Guid]::Empty,$Site.ID,$Web.ID)
                    $LastProcessingTime = $UsageData.LastProcessingTime
                    $CurrentDate = $UsageData.CurrentDate
                    $TotalHits = $UsageData.TotalHits
                    $TotalUniqueUsers = $UsageData.TotalUniqueUsers
                    $LastProcessingHits = $UsageData.LastProcessingHits
                    $LastProcessingUniqueUsers = $UsageData.LastProcessingUniqueUsers
    
                    # Write Web details to CSV File
                    $OutputString = $Scope + "," + $WebTitle + "," + $WebUrl + "," + $LastProcessingTime + "," + $TotalHits + "," + $TotalUniqueUsers + "," + $LastProcessingHits + "," + $LastProcessingUniqueUsers + "," + $CurrentDate
                    $OutputString | Out-File $OutputFilePath -Append 
                }
            }
             
            # Dispose Site Collection Object
            $Site.Dispose()
        }
    }
    
    # Sample Usage: Export both SPSite and SPWeb analytics 
    ExportRollupAnalyticsData -RootSiteUrl "http://sitecollection" -OutputFilePath "C:\temp\analytics-export-sites-webs.csv" -IncludeSites -IncludeWebs
    
    # Sample Usage: Export only SPSite analytics
    ExportRollupAnalyticsData -RootSiteUrl "http://sitecollection" -OutputFilePath "C:\temp\analytics-export-sites-only.csv" -IncludeSites
    
    # Sample Usage: Export only SPWeb analytics
    ExportRollupAnalyticsData -RootSiteUrl "http://sitecollection" -OutputFilePath "C:\temp\analytics-export-webs-only.csv" -IncludeWebs

    Reference:

    Scripts/PowerShell/Working/SharePoint/SharePoint2013/SP2013GetWebAnalyticsReport.ps1

    Thanks

    Best Regards


    Please remember to mark the replies as answers if they helped. If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.

    SharePoint Server 2019 has been released, you can click here to download it.
    Click here to learn new features. Visit the dedicated forum to share, explore and talk to experts about SharePoint Server 2019.

    Wednesday, November 20, 2019 2:48 AM

All replies

  • Hi Amol,

    You can export the usage report data using PowerShell like this:

    Add-PSSnapin "Microsoft.SharePoint.PowerShell"
    
    function ExportRollupAnalyticsData {
        Param(
        [string]$RootSiteUrl,
        [string]$Scope,
        [switch]$IncludeSites,
        [switch]$IncludeWebs,
        [string]$OutputFilePath
        )
    
        # Delete CSV file if existing
        If (Test-Path $OutputFilePath) {
    	    Remove-Item $OutputFilePath
        }
    
        # Write header row to CSV File
        $OutputHeader = "Scope,Name,URL,Most Recent Day with Usage,Hits - All Time,Unique Users - All Time,Hits - Most Recent Day with Usage,Unique Users - Most Recent Day with Usage,Current Date"
        $OutputHeader | Out-File $OutputFilePath -Append 
    
        # Get Web Application for Root Site
        $RootSite = Get-SPSite $RootSiteUrl
        $WebApp = $rootSite.WebApplication 
    
        # Get Search Service Application
        $SearchApp = Get-SPEnterpriseSearchServiceApplication
    
        # Loop through all Site Collections in Web Application
        ForEach($Site in $WebApp.Sites) {    
            
            # Export Site analtyics if -IncludeSites flag is present
            If ($IncludeSites.IsPresent) {
                $Scope = "Site"
                $SiteTitle = $Site.RootWeb.Title.Replace(",", "")
                $SiteUrl = $Site.Url
                $UsageData = $SearchApp.GetRollupAnalyticsItemData(1,[System.Guid]::Empty,$Site.ID,[System.Guid]::Empty)
                $LastProcessingTime = $UsageData.LastProcessingTime
                $CurrentDate = $UsageData.CurrentDate
                $TotalHits = $UsageData.TotalHits
                $TotalUniqueUsers = $UsageData.TotalUniqueUsers
                $LastProcessingHits = $UsageData.LastProcessingHits
                $LastProcessingUniqueUsers = $UsageData.LastProcessingUniqueUsers
    
                # Write Web details to CSV File
                $OutputString = $Scope + "," + $SiteTitle + "," + $SiteUrl + "," + $LastProcessingTime + "," + $TotalHits + "," + $TotalUniqueUsers + "," + $LastProcessingHits + "," + $LastProcessingUniqueUsers + "," + $CurrentDate
                $OutputString | Out-File $OutputFilePath -Append
            }      
    
            # Export Web analtyics if -IncludeWebs flag is present
            If ($IncludeWebs.IsPresent) {
                
                # Loop through all Webs in Site Collection
                ForEach($Web in $Site.AllWebs) {
                    $Scope = "Web"
                    $WebTitle = $Web.Title.Replace(",", "")
                    $WebUrl = $Web.Url
                    $UsageData = $SearchApp.GetRollupAnalyticsItemData(1,[System.Guid]::Empty,$Site.ID,$Web.ID)
                    $LastProcessingTime = $UsageData.LastProcessingTime
                    $CurrentDate = $UsageData.CurrentDate
                    $TotalHits = $UsageData.TotalHits
                    $TotalUniqueUsers = $UsageData.TotalUniqueUsers
                    $LastProcessingHits = $UsageData.LastProcessingHits
                    $LastProcessingUniqueUsers = $UsageData.LastProcessingUniqueUsers
    
                    # Write Web details to CSV File
                    $OutputString = $Scope + "," + $WebTitle + "," + $WebUrl + "," + $LastProcessingTime + "," + $TotalHits + "," + $TotalUniqueUsers + "," + $LastProcessingHits + "," + $LastProcessingUniqueUsers + "," + $CurrentDate
                    $OutputString | Out-File $OutputFilePath -Append 
                }
            }
             
            # Dispose Site Collection Object
            $Site.Dispose()
        }
    }
    
    # Sample Usage: Export both SPSite and SPWeb analytics 
    ExportRollupAnalyticsData -RootSiteUrl "http://sitecollection" -OutputFilePath "C:\temp\analytics-export-sites-webs.csv" -IncludeSites -IncludeWebs
    
    # Sample Usage: Export only SPSite analytics
    ExportRollupAnalyticsData -RootSiteUrl "http://sitecollection" -OutputFilePath "C:\temp\analytics-export-sites-only.csv" -IncludeSites
    
    # Sample Usage: Export only SPWeb analytics
    ExportRollupAnalyticsData -RootSiteUrl "http://sitecollection" -OutputFilePath "C:\temp\analytics-export-webs-only.csv" -IncludeWebs

    Reference:

    Scripts/PowerShell/Working/SharePoint/SharePoint2013/SP2013GetWebAnalyticsReport.ps1

    Thanks

    Best Regards


    Please remember to mark the replies as answers if they helped. If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.

    SharePoint Server 2019 has been released, you can click here to download it.
    Click here to learn new features. Visit the dedicated forum to share, explore and talk to experts about SharePoint Server 2019.

    Wednesday, November 20, 2019 2:48 AM
  • Hi Team,

    I want recent hit count for pages in all site collection using powershell.

    Is there any ready script available for same?

    thanks in Advance.

    Amol


    amol

    Sunday, December 1, 2019 11:21 AM
  • Hi Amol,

    Please refer this snippet:

    [void][System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SharePoint")
      
    #Functions to Imitate SharePoint 2010 Cmdlets in MOSS 2007
    function global:Get-SPWeb($url)
    {
      $site= New-Object Microsoft.SharePoint.SPSite($url)
            if($site -ne $null)
            {
                  $web=$site.OpenWeb();      
            }
        return $web
    }
     
      
            #Method to Get Usage Data
            Function GetWebUsageData($Web)
            {
                try
                {
                    #DataTable for Hits result - Because GetUsageData returns DataTable!
                    $dtHits = New-Object System.Data.DataTable 
      
                    $dtHits = $Web.GetUsageData("url", "lastMonth")
     
                    if ($dtHits -ne $null)
                    {
                        return ($dtHits);
                    }
                    else
                    {
                        return ($null);
                    }
                }
                catch
                {
                    Write-Host $_.Exception.Message -ForegroundColor Red
                    return ($null);
                }
            }
      
        $WebURL = "http://sharepoint.crescent.com/operations/sfdc/"
         
        $CurrentPath = Split-Path -Parent -Path $MyInvocation.MyCommand.Definition
        $OutPutFile = Join-path $CurrentPath "SiteUsageReport.txt"
         
        #Get the Web
        $Web = Get-SPWeb $WebURL
      
     
        #create a CSV file
        "Page `t Total Hits `t Last Accessed" > $OutPutFile #Write the Headers in to a text file
     
        #Get the Hits Data
        $HitsDT = GetWebUsageData($Web)
        if ($HitsDT -ne $null)
        {
            foreach($dr in $HitsDT)
            {
               
              $result = $webURL +"/" + $dr["Folder"]+"/"+$dr["Page"] + "`t" + $dr["Total Hits"] + "`t" + $dr["Most Recent Day"]
              $result >> $OutPutFile  #append the data
            }
        }
    

    Site Traffic Hits Report on Every Item on a SharePoint Site

    Thanks

    Best Regards


    Please remember to mark the replies as answers if they helped. If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.

    SharePoint Server 2019 has been released, you can click here to download it.
    Click here to learn new features. Visit the dedicated forum to share, explore and talk to experts about SharePoint Server 2019.



    Monday, December 2, 2019 9:55 AM