2

I am using SharePoint 2016, I am not sure that I am phrasing this question correctly, but I will do my best to explain the problem.

I need to check a certain website once a month. I then need to record when I checked it for auditing purposes.

I would like SharePoint to email me a monthly notice with standard verbiage (check this website), a link saying "Clicking this link confirms you checked.", and once clicked a list is updated on SharePoint with a new item including the date time and user (AD) who clicked it.

Is this possible only with sharepoint ? What SharePoint concepts should I be researching ?

**** EDIT ****

Using Rune's post I created a workflow that almost works. I copied everything exactly as a I could and the workflow kicks off automatically after I do a manual insert on the list but not for the item that the workflow makes. This post leads me to believe it may not be possible Sharepoint 2013 workflow to automatically trigger another workflow to start

enter image description here

I have saved and published the workflow. Here is the designer view.

enter image description here

2 Answers 2

1

For this requirement, I would prefer to go with generating the audit log report using the PowerShell script and configure this PowerShell script in the windows task job scheduler which should run once every month and that should send the audit log report automatically.

The PowerShell code should handle to send the audit log report as an email attachment.

Below is the PowerShell code to generate the audit log report for particular site collection:

## SharePoint Server: PowerShell Script to Create a CSV Audit Log Report For a Site Collection ##

<#
Overview: PowerShell Script that uses the 'SharePoint Auditing Classes' to produce a CSV Audit Log Report For a Site Collection
Environments: SharePoint Server 2010 / 2013 Farms
Usage: Edit the following variables to suit your environment and run the script: '$site'; '$FileName'; '$tabCsv'
Resource: http://shokochino-sharepointexperience.blogspot.ch/2013/05/create-auditing-reports-in-sharepoint.html
#>

Add-PSSnapin "Microsoft.SharePoint.Powershell" -ErrorAction SilentlyContinue

$tabName = "AuditLog"

#Create Table object
$table = New-Object system.Data.DataTable “$tabName”

#Define Columns
$col1 = New-Object system.Data.DataColumn SiteUrl,([string])
$col2 = New-Object system.Data.DataColumn SiteID,([string])
$col3 = New-Object system.Data.DataColumn ItemName,([string])
$col4 = New-Object system.Data.DataColumn ItemType,([string])
$col5 = New-Object system.Data.DataColumn UserID,([string])
$col6 = New-Object system.Data.DataColumn UserName,([string])
$col7 = New-Object system.Data.DataColumn Occurred,([DateTime])
$col8 = New-Object system.Data.DataColumn Event,([string])
$col9 = New-Object system.Data.DataColumn Description,([string])
$col10 = New-Object system.Data.DataColumn EventSource,([string])
$col11 = New-Object system.Data.DataColumn SourceName,([string])
$col12 = New-Object system.Data.DataColumn EventData,([string])
$col13 = New-Object system.Data.DataColumn MachineName,([string])
$col14 = New-Object system.Data.DataColumn MachineIP,([string])

#Add the Columns
$table.columns.add($col1)
$table.columns.add($col2)
$table.columns.add($col3)
$table.columns.add($col4)
$table.columns.add($col5)
$table.columns.add($col6)
$table.columns.add($col7)
$table.columns.add($col8)
$table.columns.add($col9)
$table.columns.add($col10)
$table.columns.add($col11)
$table.columns.add($col12)
$table.columns.add($col13)
$table.columns.add($col14)

#======================================================================================================================================================================================
#======================================================================================================================================================================================
#======================================================================================================================================================================================

$site = Get-SPSite -Identity "https://YourSiteCollection.com" #Change this to match your site collection name
$wssQuery = New-Object -TypeName Microsoft.SharePoint.SPAuditQuery($site)
$auditCol = $site.Audit.GetEntries($wssQuery)
$root = $site.RootWeb

for ($i=0; $i -le ($auditCol.Count)-1 ; $i++)
{
     #Get the Entry Item from the Collection
     $entry = $auditCol.item($i)

     #Create a row
     $row = $table.NewRow()

           #find the Current UserName 
           foreach($User in $root.SiteUsers)
           {
                if($entry.UserId -eq $User.Id)
                {
                     $UserName = $User.UserLogin
                }
           }   

           #find the Item Name
           foreach($List in $root.Lists)
           {
                if($entry.ItemId -eq $List.Id)
                {
                     $ItemName = $List.Title
                }
           }   

#Define Description for the Event Property
     switch ($entry.Event)
    {
           AuditMaskChange{$eventName = "The audit flags are changed for the audited object."}
           ChildDelete {$eventName = "A child of the audited object is deleted."}
           ChildMove {$eventName = "A child of the audited object is moved."}
           CheckIn {$eventName = " A document is checked in."}
           'Copy' {$eventName = "The audited item is copied."}
           Delete {$eventName = "The audited object is deleted."}
           EventsDeleted {$eventName = "Some audit entries are deleted from SharePoint database."}
           'Move' {$eventName = "The audited object is moved."}
           Search {$eventName = "The audited object is searched."}
           SecGroupCreate {$eventName = "A group is created for the site collection. (This action also generates an Update event.See below.)"}
           SecGroupDelete {$eventName = "A group on the site collection is deleted."}
           SecGroupMemberAdd {$eventName = "A user is added to a group."}
           SecGroupMemberDelete {$eventName = "A user is removed from a group."}
           SecRoleBindBreakInherit {$eventName = "A subsite's inheritance of permission level definitions (that is, role definitions) is severed."}
           SecRoleBindInherit {$eventName = "A subsite is set to inherit permission level definitions (that is, role definitions) from its parent."}
           SecRoleBindUpdate {$eventName = "The permissions of a user or group for the audited object are changed."}
           SecRoleDefCreate {$eventName = "A new permission level (a combination of permissions that are given to people holding a particular role for the site collection) is created."}
           SecRoleDefDelete {$eventName = "A permission level (a combination of permissions that are given to people holding a particular role for the site collection) is deleted."}
           SecRoleDefModify {$eventName = "A permission level (a combination of permissions that are given to people holding a particular role for the site collection) is modified."}
           Update {$eventName = "An existing object is updated."}
           CheckOut {$eventName = " A document is checked Out."}
           View {$eventName = "Viewing of the object by a user."}
           ProfileChange {$eventName = "Change in a profile that is associated with the object."}
           SchemaChange {$eventName = "Change in the schema of the object."}
           Undelete {$eventName = "Restoration of an object from the Recycle Bin."}
           Workflow {$eventName = "Access of the object as part of a workflow."}
           FileFragmentWrite {$eventName = "A File Fragment has been written for the file."}
           Custom {$eventName = "Custom action or event."}
        default {$eventName = "The Event could not be determined."}
    }

     #Enter data in the row
     $row.SiteUrl = $site.Url
     $row.SiteID = $entry.SiteID
     $row.ItemName = $ItemName
     $row.ItemType = $entry.ItemType
     $row.UserID = $entry.UserID
     $row.UserName = $UserName
     $row.Occurred = $entry.Occurred
     $row.Event = $entry.Event
     $row.Description = $eventName
     $row.EventSource = $entry.EventSource
     $row.SourceName = $entry.SourceName
     $row.EventData = $entry.EventData
     $row.MachineName = $entry.MachineName
     $row.MachineIP = $entry.MachineIP

     #Add the row to the table
     $table.Rows.Add($row)

}

#======================================================================================================================================================================================
#======================================================================================================================================================================================
#======================================================================================================================================================================================

     #Display the table (Optional)
     #$table | format-table -AutoSize

$date = get-date -format "d-M-yyyy"
$sDtae = [string]$date
$FileName = "AuditLogReport_For_" + $sDtae #Change this file name to match your environment
#Export the CSV File to Folder Destination
$tabCsv = $table | export-csv "C:\BoxBuild\Scripts\$FileName.csv" -noType #Change this file path to match your environment

Source of the above script:

chrisdee/Scripts

Note:

  • The above script can be customized as per your needs.
  • End of the script, you can add the code send the email along with the audit report CSV.

Example - how to send an email with attachment using PowerShell:

Add-PSSnapin Microsoft.Exchange.Management.Powershell.Admin -erroraction silentlyContinue
$file = "C:\folder\file.csv"


$mailboxdata = (Get-MailboxStatistics | select DisplayName, TotalItemSize,TotalDeletedItemSize, ItemCount, LastLoggedOnUserAccount, LastLogonTime)


$mailboxdata | export-csv "$file"


$smtpServer = "127.0.0.1"


$att = new-object Net.Mail.Attachment($file)


$msg = new-object Net.Mail.MailMessage


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


$msg.From = "[email protected]"


$msg.To.Add("[email protected]")


$msg.To.Add("[email protected]")


$msg.Subject = "Notification from email server"


$msg.Body = "Attached is the email server mailbox report"


$msg.Attachments.Add($att)


$smtp.Send($msg)


$att.Dispose()

Source:

Send an email with an attachment using PowerShell

2
  • I appreciate your effort, but this does not really address the problem. How can I update a SharePoint list item via an email ? Apr 20, 2020 at 19:21
  • Hi - I think you have misunderstood the above code, it will not update the list item from email. The above code once in a month will download a audit log report from a SharePoint site collection where all events details will be available and then the downloaded file will be sent to the configured user's email. Thank you!!!
    – SP 2022
    Apr 21, 2020 at 0:53
1

You could do this using a workflow in SharePoint 2016 using SharePoint Designer.

enter image description here

The steps:

  1. Add time to the Created date of the item (1 minute here for testing purposes) and output to a workflow variable.
  2. Pause until this date
  3. Assign a task to a user, with option "Wait for task completion" turned on
  4. Check the outcome of the task, if approved then
  5. Create a new item in the current list.

You will have to create the first item manually, but then the workflow should take care of creating next months reminder. Depending on your version of Outlook you might get buttons in the email to approve the task directly, else you will have to do this by following a link.

enter image description here

6
  • I copied what you had written down and it worked once after I manually kicked off the workflow. How do I get it to loop or send out every month ? Apr 30, 2020 at 19:03
  • 1
    You need to turn on that the workflow should start automatically when new items are created. Step 5 will ensure that the next months reminder starts by creating a new item... Apr 30, 2020 at 19:10
  • I have set the workflow to "Start workflow automatically when an item is created" in SharePoint designer, but it only kicks off when I do a manual install. The workflow creates an additional entry just as desired, but the automatically created workflow does not kick off a workflow. Are there any other spots I need to check ? Apr 30, 2020 at 20:29
  • Make sure that you do a new publish after setting it to start on item created Apr 30, 2020 at 20:53
  • I tried a brand new list and re-made the workflow from scratch. It only kicks off the workflow after I do a manual entry. I m editing the post to illustrate the problem. Can you think of anything else that needs checking? I am using an announcement list type if that matters. May 1, 2020 at 12:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.