Wednesday, May 22, 2013

Create Auditing Reports in SharePoint using PowerShell

היי חברים,
בהמשך למאמר שפרסמתי החודש הלקוח שלי ביקש שדו"חות ה-Auditing יווצרו באופן אוטומטי כקובץ Excel/CSV  וישמרו בתיקיית שיתוף. (זוהי יכולת שניתנת לכל מנהל אוסף אתרים דרך ממשק המשתמש)
זהו אתגר לא פשוט מהסיבה הפשוטה שה-Script נבנה ב-PowerShell ולא ב-C# ויש שימוש באובייקטים .NETים ב-PowerShell.
אז איך מתחילים אתם שואלים... אני אספר לכם :) ..
ראשית לומדים את מבנה ה-Classים של SharePoint Auditing :
-          SPAudit
-          SPAuditEntry
-          SPAuditEntryCollection
-          SPAuditQuery
בנוסך כדאי לקרוא על  SPAuditMaskType .
מאמר מצוין בנושא Audit Object Model in SharePoint 2010.
אחרי שהבנתם מי נגד מי אפשר לעבור למימוש :

נתחיל ביצירת אובייקט טבלאי כדי לשמור את נתוני הדו"ח בתוך הטבלה.
לאחר יצירת הטבלה יוצרים את העמודות עם הכותרת והטיפוס של העמודה (Int,String,Date וכו' ). הפעולה הבאה היא להוסיף את העמודות לטבלה שיצרנו ומיד לאחר מכן מתחילים להגדיר את אובייקטי ה-SharePoint שרלוונטיים לדו"חות Auditing:
-           SPSite – אתר אב עליו אנו מעוניים לשלוף דו"ח
-          SPAuditQuery - מגדיר את ערכי השאילתה שישלפו ממסד הנתונים של SharePoint
-          auditCol – אוסף של כל הערכים בדו"חות
-          entry – ערך בודד מהאוסף.
לאחר מכן רצים על כל הערכים בלולאה וממלאים את השורות בטבלה בתוכן.
הפעולה האחרונה היא ליצוא קובץ עם תאריך בשם הקובץ ולייצא את הטבלה לקובץ CSV.




להלן ה-Script :

$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 http://'SiteCollectionName'
$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
#Export the CSV File to Folder Destination
$tabCsv = $table | export-csv C:\$FileName.csv -noType



בהצלחה !
רון נס.


 ============================================================================================================================================================================================= 



Hi Friends,
Further to the article  I published in ,my client asked the auditing report will be created automatically as Excel / CSV File and keep it sharing folder. (This ability is given to all site collection administrators through the user interface(
This is a challenge because the script was built on PowerShell and not in C # and I'm using   .NET Objects in PowerShell.
So how do you start ...
First learn the structure of the SharePoint Auditing Classes
- SPAudit
- SPAuditEntry
- SPAuditEntryCollection
- SPAuditQuery
*  you should read the SPAuditMaskType.
Great article on for learning about is Audit Object Model in SharePoint 2010.
After you understand who's who you can pass to the Code Work:

Let's start creating tabular object to save the report data in the table.
After you create a table with columns forming the title and type of the column (int, String, Date, etc.). The next step is to add the column to the table that we have created and then immediately begin to define the objects of the relevant SharePoint Auditing reports:
- SPSite - parent site which we pull their poverty report
- SPAuditQuery - defines the values ​​from the database query yank SharePoint
- AuditCol - a collection of all the values ​​in reports
- Entry - a single value from the collection.
Then we pass on all the values ​​in a loop and fill the rows in the table content.
The last step is to export the file with a date in the file name and export the table to a CSV file.
The following script:

$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 http://'SiteCollectionName'
$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
#Export the CSV File to Folder Destination
$tabCsv = $table | export-csv C:\$FileName.csv -noType


Good Luck :)
Ron Ness.

1 comment:

  1. I've edited the script to my specific site collection. I've ensure Reporting Feature was activated. Also changed the export location to E:\Temp. I get the file, but it's completely empty. Any ideas?

    ReplyDelete