PowerShell Script to get the Usage Report

Hi,

We had a requirement of Usage Report of preceding month i.e.

The usage of files in a document library needs to be sent for customer as an attachment

For this we have fetched the data from audit logs as below

if ((Get-PSSnapin -Name Microsoft.SharePoint.Powershell -ErrorAction SilentlyContinue) -eq $null )

{

    Add-PSSnapin Microsoft.SharePoint.Powershell

}

#Global Variables

$site=Get-SPSite "siteurl"

$web=get-SPWeb "weburl"

$list=$web.Lists["doclibname"]

$fromaddress = "abc@domain.com"

$toaddress = "def@domain.com"

$ccaddress = "ghi@domain.com"

$auditloglib=$web.Lists["AuditLogReports"]

$reportid=Get-Random

$context = Get-SPServiceContext $site

  $date = get-date

        $numdays = $date.Day

        $ed = $date.AddDays(-$numdays)

        $numdays = $ed.Day

        $sd = $ed.AddDays( -$numdays + 1)

        $sd = ((($sd.AddHours(0-$sd.hour)).AddMinutes(0-$sd.Minute)).AddSeconds(0-$sd.Second)).AddMilliseconds(0-$sd.millisecond)

        $ed = $ed.AddDays(1)

        $ed = ((($ed.AddHours(0-$ed.hour)).AddMinutes(0-$ed.Minute)).AddSeconds(0-$ed.Second)).AddMilliseconds(0-$ed.millisecond)

#EndRegion Global Variables

#Region Nucleus Usage Report

$wssQuery = New-Object -TypeName Microsoft.SharePoint.SPAuditQuery($site)

try

   {

   

    

    $Headers = 'Section,Folder,    File,Noof Downloads,Noof ViewCounts,Document Path,Last Updated,Vertical / Service line related details1,Vertical / Service line related details2,Vertical / Service line related details3'

    $filename = "D:\UsageReports\UsageReport-" + $sd.Date.Date.ToString("MMM") + $sd.Date.Date.ToString("yy")+$reportid + ".csv"

   

    Add-Content $filename $Headers

    $Items = $list.items

    Write-Host  "Item Count" + $Items.Count

                $wssquery.SetRangeStart($sd)

        $wssquery.SetRangeEnd($ed)

    foreach($MItem in $Items)

   

    

    {

        $wssquery.RestrictToListItem($MItem)

        $auditCol = $site.Audit.GetEntries($wssQuery)

        Write-Host "Audit Count" + $auditCol.Count

         $viewcount = 0

         $DownloadCount = 0

        if($auditCol.Count -gt 0)

        {

            foreach($auditEntry in $auditCol)

                {

                    if($auditEntry.EventSource -eq 'SharePoint')

                        {

                            $DownloadCount++

                        }

                            elseif($auditEntry.EventSource -eq 'ObjectModel')

                                        {

                                            $viewcount++

                                        }

                }

$ItemDetails = $MItem.Url.Split('/')

#switch($ItemDetails.count)

#{

Write-Host "Item Count" + $ItemDetails.Count

if($ItemDetails.Count -eq 2) { $DocDetails = "Sales Tools" + "," + "NA" + "," + $ItemDetails[1] +  "," + $DownloadCount + "," + $viewcount + "," + $MItem.Url + "," +  $MItem["Modified"] + "," + "NA" + "," + "NA" + "," + "NA" + "," + $MItem.Url}

ElseIf($ItemDetails.Count -eq 3) { $DocDetails = "Sales Tools" + "," + $ItemDetails[1] + "," + $ItemDetails[2] +  "," + $DownloadCount + "," + $viewcount + "," + $MItem.Url + "," +  $MItem["Modified"] + "," + "NA" + "," + "NA" + "," + "NA" + "," + $MItem.Url}                                                             

ElseIf($ItemDetails.Count -eq 4) { $DocDetails = "Sales Tools" + "," + $ItemDetails[1] + "," + $ItemDetails[3] +  "," + $DownloadCount + "," + $viewcount + "," + $MItem.Url + "," +  $MItem["Modified"] + "," + $ItemDetails[2] + "," + "NA" + "," + "NA" + "," + $MItem.Url}

ElseIf($ItemDetails.Count -eq 5) { $DocDetails = "Sales Tools" + "," + $ItemDetails[1] + "," + $ItemDetails[4] +  "," + $DownloadCount + "," + $viewcount + "," + $MItem.Url + "," +  $MItem["Modified"] + "," + $ItemDetails[2] + "," + $ItemDetails[3] + "," + "NA" + "," + $MItem.Url}

ElseIf($ItemDetails.Count -eq 6) { $DocDetails = "Sales Tools" + "," + $ItemDetails[1] + "," + $ItemDetails[5] +  "," + $DownloadCount + "," + $viewcount + "," + $MItem.Url + "," +  $MItem["Modified"] + "," + $ItemDetails[2] + "," + $ItemDetails[3] + "," + $ItemDetails[4] + "," + $MItem.Url}

else

  { $DocDetails = "Sales Tools" + "," + $ItemDetails[1] + "," + $ItemDetails[5] +  "," + $DownloadCount + "," + $viewcount + "," + $MItem.Url + "," +  $MItem["Modified"] + "," + $ItemDetails[2] + "," + $ItemDetails[3] + "," + $ItemDetails[4] + "," + $MItem.Url}         

#default {}

#}

Write-Host $DocDetails

#$DocDetails = "Sales Tools" + ","  + $viewcount + "," + $DownloadCount + "," + $MItem.Url + "," +  $MItem["Modified"]

Add-Content $filename $DocDetails

}

               

    }

$files = Get-ChildItem -Path $filename -Force -Recurse

foreach ($file in $files)

        {

          

          $stream = $file.OpenRead()

          $done= $auditloglib.RootFolder.Files.Add($file.Name, $stream, $true)

          $stream.Close()

         

          Write-Host $done.Name  "Uploaded into the Document Library"$auditloglib -BackgroundColor Green   

               

        }

#############Email for Usage Report####################################

$Subject = "Usage Report-" + $sd.Date.Date.ToString("MMM")+$sd.Date.Date.ToString("yy")

$body = "Hi, Please find attached Usage report for the Month :" + $sd.Date.Date.ToString("MMM") +$sd.Date.Date.ToString("yy")

$attachment = $filename

#SMTP IP Address of Production

$smtpserver = "IP Address"

$message = new-object System.Net.Mail.MailMessage

$message.From = $fromaddress

$message.To.Add($toaddress)

$message.To.Add($ccaddress)

$message.IsBodyHtml = $True

$message.Subject = $Subject

$attach = new-object Net.Mail.Attachment($attachment)

$message.Attachments.Add($attach)

$message.body = $body

$smtp = new-object Net.Mail.SmtpClient($smtpserver)

$smtp.Send($message)

$message.Dispose()

$smtp.Dispose()

$file.Delete();

#######################################################

}

catch

    {

   Write-Host "Error for File : " $i."Name"

   Add-Content D:\Logs\UsageReport\UsageLogReport.txt $DocNameError

    }

#EndRegion Usage Report       

  

Finally we got the required email triggered as an attachment as below

Attachment