Friday 4 April 2014

PowerShell - Get list of assemblies across servers, based on filters

This script can interrogate a list of servers for assemblies installed in the GAC, based on a filter or array of filters (using wildcards, if necessary). Returns as an array of objects, so output can be piped to csv using the | export-csv commandlet

NOTE: The script assumes that windows is installed in C:\Windows on each server - you may need to change this in the script i.e.
 
dir "\\$server\c$\windows\assembly\gac_msil"
 
replace with 

dir "\\$server\?$\windowsfolder?\assembly\gac_msil"


Script: check-assemblies.ps1



# Script to get assemblies from servers, and can be filtered for custom assemblies
# Params
# -ServerListFile : File containing a list of server names to check. One line per server. If not specified, local server will be checked
# -filters : filter or array of filters, to filter on dlls to be return. For example, I could use the filters "*chris*.dll","*Lawes*.dll"

param ($ServerListFile, $filters)

# Get server list, depending on params - if not used, just check the local server

if ($ServerListFile){
    $Servers = Get-Content $ServerListFile
}
else{

    $servers = $env:computername
}


foreach($server in $servers)
{
    foreach ($filter in $filters) { 
        dir "\\$server\c$\windows\assembly\gac_msil" -Recurse | where-object {$_.name -ilike $filter} | select @{n="Server";e={$server}}, Name, Length, LastwriteTime , @{n="Environment";e={$Environ}}
    }
} 

SharePoint - Get Office and SP Patches for multiple servers

This script can interrogate a list of servers for Office and SP updates and Patches. Either a comparison can be made from a patch list file, or all patches are returned. They are returns as an array of objects, so output can be piped to csv using the | export-csv commandlet



Script: check-spupdates.ps1


# Script to check registry on multiple servers for Office patches and security updates)
# Params
# -filter : fileter on the registry lookup - normally can be ignored
# -ServerListFile : File containing a list of server names to check. One line per server
# -PatchListFile : List of KB patches - i.e. KB2553345 - one per line. Will return if the patch has been applied or not. If this param is not specified, all SP patches installed on the 
# Server will be returned. This could be used to create a ServerListFile (using just the server name field) if comparison is required for other servers.
# -regpath : The root of the registry where update information is stored - can normally be ignored, as default is set for the param to the correct location
# -Servers : Can be used if a ServerListFile is not used, so we can specifity the servers in the comamnd line. If neither servers or ServerListFile specified, just check local server
# -all : retruns all patches within the S-1-5-18 registry, even if they may not be Office related.


Param($Filter = ".*",$ServerListFile, $PatchListFile, $regpath ="SOFTWARE\Microsoft\Windows\CurrentVersion\Installer\UserData\S-1-5-18\Products", $servers, [switch]$all)

function Check-RegKeys {
    Param($regkey,$Server)
    $ServerKey = [Microsoft.Win32.RegistryKey]::OpenRemoteBaseKey("LocalMachine", $Server)
    $SubKey = $ServerKey.OpenSubKey($regkey,$false)
    If(!($SubKey)){Return}
    if ($regkey -ilike "*Patch*"){
        $SubKeyValues = $SubKey.GetValueNames()
        if($SubKeyValues)
        {
            foreach($SubKeyValue in $SubKeyValues)
            {
                $Key = @{n="Key";e={$SubKey.Name -replace
                "HKEY_LOCAL_MACHINE\\",""}}
                $ValueName = @{n="ValueName";e={$SubKeyValue}}
                $Value = @{n="Value";e={$_}}
                $SubKey.GetValue($SubKeyValue) | ?{$_ -match $filter} | Select-Object $ValueName,$Value, @{n="path";e={$subkey.name}}
            }
        }
    }
    $SubKeyName = $SubKey.GetSubKeyNames()
    foreach($subkey in $SubKeyName)
    {
        $SubKeyName = "$regkey\$subkey"
        Check-RegKeys $SubKeyName $server
    }
}

# Get server list, depending on params - if not used, just check the local server
if (!$servers){
    if ($ServerListFile){
        $Servers = Get-Content $ServerListFile
    }
    else{
   
        $servers = $env:computername
    }
}

# Get Patches
if ($PatchListFile){
    try{$Patches = Get-Content $PatchListFile}catch {write-host " - Could not load Patch List File - will return all installed patches instead.."}
    $patches = $null
}

# Cycle through all servers
foreach ($server in $Servers){
    # Create array for results
    $allresults = @()
    write-host "Processing Server: $($server)"
    write-host "   - Getting Registry"
    # Get the registry entries for the patches
    $ServerPatches = Check-RegKeys $regPath $Server
    write-host "   - Obtained Registry"
    # Format the patches so we can get the relevent information    
    $patchraw = $ServerPatches | Where-Object {$_.ValueName -ne "AllPatches"} | group Path
    foreach ($group in $patchraw){
        # Cycle through each patch
        # Create object for results
        $results = "" | Select Server, DisplayName, KB, InstallDate, MoreInfoURL
        # Set data in object from the registry group
        $results.Server = $Server  
        $results.DisplayName = $($group.group | Where-Object{$_.ValueName -eq "DisplayName"}).Value
        $results.InstallDate = ([datetime]::ParseExact($($group.group | Where-Object{$_.ValueName -eq "Installed"}).Value,”yyyyMMdd”,$null)).ToShortDateString()
        $results.MoreInfoURL = $($group.group | Where-Object{$_.ValueName -eq "MoreInfoURL"}).Value
        if ($results.DisplayName -ilike "*kb*"){
            $results.KB =  "KB" + ([regex]::split(($results.DisplayName).ToUpper(), 'KB'))[1].split(")")[0]
        }
        elseif($results.MoreInfoURL -ilike "*/kb/*"){
               $results.KB = "KB" + (($results.MoreInfoURL).split("/"))[-1]
        }

        if ($all){
            $allresults += $results 
        }
        elseif (($results.MoreInfoURL).StartsWith("http://support.microsoft.com") ){
            # Filter out so only relevent patches are returned
            $allresults += $results  
        }
    } 
    #Remove deplicates and sort in KB order
    $allresults = $allresults | Sort-Object -Unique * | Sort-Object KB           
    # Patches specified, so we will test for these in the registry results
    if ($patches){
        foreach ($patch in $Patches){
            write-host "   - testing patch: $patch"
            $patchlookup = $allresults -match $patch
            if (!$Patchlookup){
                $patch | select @{Expression={$server};Name="Server"}, @{Expression={$patch};Name="KB"}, @{Expression={"False"};Name="Applied"}
                
            }
            else{
                $patchlookup | select *,@{Expression={"True"};Name="Applied"}
            }
        }
    }
    else{
        # No patches to test specified so retun all results
        $allresults
    }
}

Monday 17 March 2014

SharePoint - Export to Excel and removing # and numbers from lookup and people columns

When a list is exported to Excel from SharePoint you may see entries for lookup columns and People like this

myentry;#32;anotherentry;#43

We can remove these by creating a column next to the column with these entries - say that the above is in column C1

Use this formula in the new column in D1:

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(C1,"0",""),"1",""),"2",""),"3",""),"4",""),"5",""),"6",""),"7",""),"8",""),"9",""),";#;#","; "),";#","")

This will remove the #no bits. Copy this column and then paste it over the original column (C) as "Values". Then remove the column you used in the formula (D)

Thursday 13 March 2014

PowerShell - Write array of objects to a SQL table


This script srite array of objects to a SQL table. The input is via the pipeline, so usage could be

Get-Process | .\write-sqltable.ps1 -sqlserver SQLServer -dbname Mydatabase -dbtable Mytable

Note: the table MUST already exist with the same column names as the properties of the objects being passed to it. Use the -purgetable switch if you need to empty the table each time the script is run.


Script: write-sqltable.ps1

# Script to write array of objects, via Pipeline into a SQL table.
# Property names of objects being passed must match the column names in the destination table
# Returns select failedRowCount , SuccessRowCount , Errors 
# Params
# -sqlserver : Name or alias of SQL server instance
# -dbname : Name of the database
# -dbtable : Name of the database table
# -purgetable : [switch] Empty the table before writing back to it.

param ([string]$sqlServer = $(throw "SQL Server required"), [string]$dbName = $(throw "Database Required"),[string]$dbTable = $(throw "Database table Required"), [switch]$purgetable)
begin 
{
    $io = New-Object System.Collections.ArrayList;
}

process
{
    if ($_) {$io += $_}
}

end
{
    
    $conn = New-Object System.Data.SqlClient.SqlConnection("Data Source=$sqlServer;Initial Catalog=$dbName; Integrated Security=SSPI");
    $conn.Open();
    $cmd = $conn.CreateCommand();

    if ($purgetable){
        $cmd.CommandText = "DELETE from $dbtable"
        $cmd.ExecuteNonQuery() | out-null
        
    }
    if ($io -ne $null){
        $props = $io | select -First 1 | Get-Member -Membertype NoteProperty | foreach {$_.name}
        $successRowCount = 0
        $failedRowCount = 0
        $errors = @()
        foreach ($obj in $io)
        {
            $error.clear()
            $dbrowResult = $null
            $SQLInto = $null
            $SQLValues = $null
            $props | foreach {$SQLInto += $_ + ", "; $SQLValues += "'$($obj.$_)', "}
            $SQLInto = $SQLInto.trimEnd(", ")
            $SQLValues = $SQLValues.TrimEnd(", ")
            write-host "INSERT INTO $dbTable ($SQLInto) VALUES ($SQLValues)"
            $cmd.CommandText = "INSERT INTO $dbTable ($SQLInto) VALUES ($SQLValues)";
            try{$dbrowResult = $cmd.ExecuteNonQuery()}catch{}
            if ($dbrowResult -eq $null){
                $failedRowCount ++
                $errors += $error
                
            }
            else {
                $successRowCount ++
            }


        }
        
        $results = "" | select failedRowCount , SuccessRowCount , Errors 
        $results.failedRowCount = $failedRowCount
        $results.SuccessRowCount = $SuccessRowCount
        $flaterrors = ""
        $errors | Sort-Object -Unique | foreach {$results.Errors += ("{0}," -f $_)} | out-null; try{$results.Errors = ($results.Errors).TrimEnd(",")}catch{}
        $results
    }
    $conn.close();
}

Tuesday 11 March 2014

SharePoint 2007 \ 2010 - PowerShell script to get SharePoint audit information



This script outputs audit events as an array of objects, which can the be exported to csv using the | export-csv function:

.\get-spauditReport.ps1 -url <SiteCollection> -daysback 10 | export-csv <csv file>


It should work on SharePoint 2007 and 2010 - I haven't tested on 2013, but I think it should work.

The usage is in the comments at the start. Note that if there is heavy usage on the site collection, be mindful of the number of days the report should include, as memory may be an issue.


Script: get-spauditReport.ps1

# Script to output SharePoint audit information - can be exported to csv.
# Parameters:
# -url : URL of site collection
# -daysback : Number of days back from today that the audit information is to be collected from
# -startdate : As opposed to -daysback, use this as a start date for the audit report
# -enddate : Used with -startdate for the end date of the report
# -previousmonth : [switch] Previous full month for the report to use. Good if you want to run as a scheduled task every month
# -excludeusers : Can be an array of users that you don't want to appear on the report - i.e. monitoring accounts etc
# -excludeurls : excluded urls from the report - some system urls excluded by default, but will be overridden if used
# -includeRawevent : [switch] Included the raw event data from the SharePoitn audit
# -filterevent : Only returns events stated in the param
# -principle : If stated, only events for this account will be return.


param ($url, $daysback, $startdate, $enddate, [switch]$previousmonth, $excludeusers = ("SHAREPOINT\system"), $excludeurls = ("/Style Library/portal","/_catalogs","/_themes") , [switch]$includeRawEvent, $filterEvent, $principal)


[void][System.reflection.Assembly]::LoadWithPartialName("Microsoft.SharePoint")
[void][System.reflection.Assembly]::LoadWithPartialName("System")

# Funcions

# Function: To create new object for results output
function New-AuditObj(){

    $auditobj = new-object PSObject
    $auditobj | add-member -type NoteProperty -name LoginName -Value "" 
    $auditobj | add-member -type NoteProperty -name DisplayName -Value "" 
    $auditobj | add-member -type NoteProperty -name Email -Value "" 
    $auditobj | add-member -type NoteProperty -name SPUserID -Value "" 
    $auditobj | add-member -type NoteProperty -name Event -Value "" 
    $auditobj | add-member -type NoteProperty -name Item -Value "" 
    $auditobj | add-member -type NoteProperty -name ItemType -Value "" 
    $auditobj | add-member -type NoteProperty -name Version -Value "" 
    $auditobj | add-member -type NoteProperty -name EventData -Value ""
    $auditobj | add-member -type NoteProperty -name EventDataRaw -Value ""     
    $auditobj | add-member -type NoteProperty -name AuditTime -Value "" 

    return $auditobj
}

# Function: Return Values based on Permission Level Mask
function get-PermissionLevelMask($maskno)
{
    switch ($maskno)
    {
        "0" {
            Return "EmptyMask"
        }       
        "1" {
            Return "ViewListItems"
        }       
        "2" {
            Return "AddListItems"
        }       
        "4" {
            Return "EditListItems"
        }       
        "8" {
            Return "DeleteListItems"
        }       
        "16" {
            Return "ApproveItems"
        }       
        "32" {
            Return "OpenItems"
        }       
        "64" {
            Return "ViewVersions"
        }       
        "128" {
            Return "DeleteVersions"
        }       
        "256" {
            Return "CancelCheckout"
        }       
        "512" {
            Return "ManagePersonalViews"
        }       
        "2048" {
            Return "ManageLists"
        }       
        "4096" {
            Return "ViewFormPages"
        }       
        "65536" {
            Return "Open"
        }       
        "131072" {
            Return "ViewPages"
        }       
        "262144" {
            Return "AddAndCustomizePages"
        }       
        "524288" {
            Return "ApplyThemeAndBorder"
        }       
        "1048576" {
            Return "ApplyStyleSheets"
        }       
        "2097152" {
            Return "ViewUsageData"
        }       
        "4194304" {
            Return "CreateSSCSite"
        }       
        "8388608" {
            Return "ManageSubwebs"
        }       
        "16777216" {
            Return "CreateGroups"
        }       
        "33554432" {
            Return "ManagePermissions"
        }       
        "67108864" {
            Return "BrowseDirectories"
        }       
        "134217728" {
            Return "BrowseUserInfo"
        }       
        "268435456" {
            Return "AddDelPrivateWebParts"
        }       
        "536870912" {
            Return "UpdatePersonalWebParts"
        }       
        "1073741824" {
            Return "ManageWeb"
        }       
        "68719476736" {   
            Return "UseClientIntegration "
        }
        "137438953472" {
            Return "UseRemoteAPIs"
        }       
        "274877906944" {
            Return "ManageAlerts"
        }       
        "549755813888" {
            Return "CreateAlerts"
        }       
        "1099511627776" {
            Return "EditMyUserInfo"
        }       
        "4611686018427387904" {
            Return "EnumeratePermissions"
        }       
        "9223372036854775807" {
            Return "FullMask"
        }       
   
   
   
    }

}

# Function: Return Values based on audit level mask
function get-auditMask($maskno){
    switch ($maskno)
        {
            "-1" {
                #Return "All types of events and actions."
                Return "All"
            }
            "0" {
                #Return "No events or actions."
                Return "None"
            }
            "1" {
                #Return "Check out of the object."
                Return "CheckOut"
            }
            "2" {
                #Return "Check in of the object."
                Return "CheckIn"
            }
            "4" {
                #Return "Viewing of the object by a user."
                Return "View"
            }
            "8" {
                #Return "Deletion of the object."
                Return "Delete"
            }
            "16" {
                #Return "Update of the object."
                Return "Update"
            }
            "32" {
                #Return "Change of a profile."
                Return "ProfileChange"
            }
            "64" {
                #Return "Deletion of one of the object's child objects."
                Return "ChildDelete"
            }
            "128" {
                #Return "Change of the object's schema."
                Return "SchemaChange"
            }
            "256" {
                #Return "Change in a security configuration for the object."
                Return "SecurityChange"
            }
            "512" {
                #Return "Reversal of the deletion of the object."
                Return "Undelete"
            }
            "1024" {
                #Return "Use of the object in a workflow task."
                Return "Workflow"
            }
            "2048" {
                #Return "Copying the object."
                Return "Copy"
            }
            "4096" {
                #Return "Move of the object."
                Return "Move"
            }
            "8192" {
                #Return "Search of the object."
                Return "Search"
            }
                                                                                                                                                                                              
       
       
        }
       

}

## Start a loop that will run until both $sd (Start Date) and $ed (End Date) variables are assigned values
while (-not ($sd -and $ed))
{
    ## If the -daysback parameter was specified, set $ed to current timestamp and $sd to the specified number of days back
    if ($daysback)
    {
        ## The ToShortDateString() method is used to set the time value in the DateTime object to 00:00:00 (otherwise it will match current)
        $sd = (Get-Date (Get-Date).ToShortDateString()).AddDays(-$daysback);
        $ed = Get-Date;
       
        ## Stop processing the loop as the required values have been assigned
        break;
    }
   
    if ($previousmonth)
    {
        # get previous month days
        $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)
        ## Stop processing the loop as the required values have been assigned
        break;
    }
   
    ## If the -startdate parameter was specified, use it create a DateTime object and assign it to $sd variable
    if ($startdate)
    {
        $sd = Get-Date ($startdate);
    }
   
    ## As start date was not specified, set $sd to the earliest date possible
    else
    {
        $sd = [DateTime]::MinValue;
    }

    ## If the -enddate parameter was specified, use it create a DateTime object and assign it to $ed variable
    if ($enddate)
    {
        $ed = Get-Date ($enddate);
    }
   
    ## As end date was not specified, set $ed to today
    else
    {
        $ed = Get-Date;
    }
}

# Set type of start date and end date
$sd = Get-Date $sd
$ed = Get-Date $ed

# Get Site object
$site = New-Object Microsoft.SharePoint.SPSite($url)

# Get List of site users
$SPSiteUsers = $site.RootWeb.SiteUsers
# Get Site User Ids of excluded users (as defined in parameter)
$ExcludeUserIds = $SPSiteUsers | where-Object {$excludeusers -contains $_.LoginName} | foreach {$_.ID}
# Create gerneal query object
$query = New-Object Microsoft.SharePoint.SPAuditQuery($site)
$query.SetRangeStart($sd)
$query.SetRangeEnd($ed)

# Filter our events if defined in parametes
if ($filterEvent){
    Write-Host "  - Filtering Events:"
    $filterEvent | ForEach-Object {
        Write-Host "    - $_"
        $query.AddEventRestriction($_)
    }
}

# Get Prinicpal ID (from parameter) and set query to use it
if ($principal){
    $id = ($SPSiteUsers | Where-Object {$_.loginname -ilike "*$principal*"}).id
    if ($id){
        $query.RestrictToUser($id)
    }
    else{
        Write-Host "   - $principal does not exist on the site."
    }
}
# Get Audit Entries, excluding urls (as specified in parameters)
$auditEntries = $site.Audit.GetEntries($query) | Where-Object {$ExcludeUserIds -notcontains $_.userID } | ForEach-Object {$found = $false; if ($excludeurls){foreach ($exurl in $excludeurls){if($_.Doclocation -ilike "*$exurl*"){$found = $true}}; if ($found -eq $false){$_}}}
# reset Audit counter (for progress bar)
$auditcount = 0;

# Preloading objects from SharePoint - for efficency

# Get SecGroupCreate audit entries
$groupquery = New-Object Microsoft.SharePoint.SPAuditQuery($site)
$groupquery.AddEventRestriction("SecGroupCreate")
$SecGroupCreateEntries = $site.Audit.GetEntries($groupquery)

# Get SecRoleDefCreate and SecRoleDefModify audit entries
$rolequery = New-Object Microsoft.SharePoint.SPAuditQuery($site)
$rolequery.AddEventRestriction("SecRoleDefCreate")
$rolequery.AddEventRestriction("SecRoleDefModify")               
$SecRoleEntries = $site.Audit.GetEntries($rolequery)

# Loop through all audit entries, so we can process them
foreach ($AuditItem in $auditEntries){
    # Reset Principal variable
    $principal = $null
    # Increment counter
    $auditcount ++;
    # Progress bar
    if ($auditEntries.Count -gt 0){
        Write-Progress -activity "Processing Audit Logs for $url" -status "Processed: $auditCount of $($auditEntries.Count)" -PercentComplete (($auditcount / $auditEntries.Count)  * 100)
    }
    # Create new Audit output object
    $AuditObj = New-AuditObj
   
    # Get User Info
    try{
        $user = $SPSiteUsers.GetByID($audititem.UserId)
        $auditobj.LoginName = $user.LoginName
        $auditobj.DisplayName = $user.Name
        $auditobj.Email = $user.Email
        $auditobj.SPUserID = $user.ID
    }
    catch{
        $auditobj.LoginName = "User Removed"
        $auditobj.SPUserID = $audititem.UserId
    }
   
    # Get Item Info

    $auditobj.Event = $auditItem.Event
    $auditobj.Item = $auditItem.DocLocation
    $auditobj.ItemType = $auditItem.ItemType
    $auditobj.AuditTime = $auditItem.Occurred
    $auditobj.EventDataRaw = $AuditItem.EventData   
   
    if($auditobj.Item -ilike "*agenda.doc*")
    {
        Write-Host "Here"
    }
   
    if ((($auditItem.DocLocation).EndsWith(".aspx"))){
        $auditobj.ItemType = "Page"
    }
    # Calculate version info
    if ($AuditItem.EventData -eq ""){
        $auditobj.Version = "N/A"
    }
    if ($AuditItem.EventData -ilike "*<allversions/>*"){
        $auditobj.Version = "All"
    }   
    try {
        [xml]$eventData = "<root>$($AuditItem.EventData)</root>"
        if ($eventData.root.Version.Major -ne $null -and $eventData.Version.root.Minor -ne ""){
            if ($eventData.root.Version.Major -eq "0" -and $eventData.Version.root.Minor -eq "-1")
            {
                $auditobj.Version = "N/A"
            }
            else{
                $auditobj.Version = $eventData.root.Version.Major + "." + $eventData.root.Version.Minor
            }
        }
       
    }
    catch{}

    # Reset general EventData variable
    Remove-Variable eventData
    # Switch based on event type
    switch ($auditItem.Event)
        {
            "AuditMaskChange" {
                # Get mask from EventData
                $permissionMask = ([xml]($AuditItem.EventData)).NewAuditMask
                $auditobj.EventData = "Auditing Events: "
                # Go through each mask and bit and them - evaluation passed to "Get-AuditMask" function for actual values
                8192,4096,2048,1024,512,256,128,64,32,16,8,4,2,1 | foreach {if (($permissionMask -band $_) -gt 0){$auditobj.EventData += $(get-auditMask ($permissionMask -band $_)) + " "}}

            }
            "Move" {
                [xml]$eventData = $AuditItem.EventData
                $auditobj.EventData = "Moved to: {0}" -f $EventData.NewName
            }

            "ChildMove" {
                [xml]$eventData = $AuditItem.EventData
                $auditobj.EventData = "Moved to: {0}" -f $EventData.RelatedItem.NewName
            }            
            "Delete" {
           
                if ($($AuditItem.EventData) -ilike "*<Recycle>1<Recycle>*")           
                    {$auditobj.EventData = "Sent to the recycle bin"}
                if ($($AuditItem.EventData) -ilike "*<Recycle>0<Recycle>*")           
                    {$auditobj.EventData = "Permantently Deleted"}              
                   
                }    
            "UnDelete" {
                $auditobj.EventData = "Restored from the recycle bin"
                }                         
            "ChildDelete" {
                $auditobj.EventData = "Deleted {0}" -f $($eventData.RelatedItem.Location)
            }
            "EventsDeleted" {
                [xml]$eventData = $AuditItem.EventData              
                $auditobj.EventData = "{0} audit rows deleted before {1}" -f $eventdata.DeleteEntriesInfo.Rows, $(get-date ($eventdata.DeleteEntriesInfo.EndDate)).TosTring()
            }
            "View" {
                $auditobj.EventData = ""
            }
            "Search" {
                $eventData = $AuditItem.EventData
                $query = ($eventData.split(";")[0]).replace("query=","")
                $context = ($eventData.split(";")[1]).replace("constraint=' ","").replace("'","").replace("site:","").replace('"',"")
                $auditobj.EventData = "Searched for {0} in {1}" -f $query, $context

            }
                                        
            "SecGroupMemberAdd" {
                [xml]$eventdata = "<root>$($AuditItem.EventData)</root>"
                $GroupID = $eventData.root.groupid
                if ($GroupID -gt 0){
                    try {$GroupName = (($site.RootWeb.SiteGroups).GetByID($eventData.root.groupid)).Name}catch{}
                    if (!$groupName){
                        $SecGroupCreateEntries  | Where-Object {$_.EventData -ilike "*<groupid>$GroupID</groupid>*"} | Select -First 1 | foreach {[xml]$eventdata = ("<root>$($_.EventData)</root>").replace("&","&amp;"); $GroupName =  $eventData.root.title}

                    }
                    try {$principal = $SPSiteUsers.GetById($eventdata.root.user)}catch{}
                    if ($principal -ne $null){
                        $userinfo = "{0} ({1})" -f $principal.loginname, $principal.email
                    }
                    else
                    {
                        $userinfo = $eventdata.root.username
                    }
                    $auditobj.EventData = "Added {0} to Group: {1}" -f $userinfo, $GroupName
                }
            }                
           
            "SecGroupMemberDel" {
                [xml]$eventdata = "<root>$($AuditItem.EventData)</root>"
                try {$principal = $SPSiteUsers.GetById($eventdata.root.user)}catch{}
                if ($principal -ne $null){
                    $userinfo = "{0} ({1})" -f $principal.loginname, $principal.email
                }
                else{
                    $userinfo = "UserID: $($eventdata.root.user)"
                }  
                try {$GroupName = (($site.RootWeb.SiteGroups).GetByID($eventData.root.groupid)).Name}catch{}
                if (!$groupName){
                    $SecGroupCreateEntries  | Where-Object {$_.EventData -ilike "*<groupid>$GroupID</groupid>*"} | Select -First 1 | foreach {[xml]$eventdata = ("<root>$($_.EventData)</root>").replace("&","&amp;"); $GroupName =  $eventData.root.title}

                    }                           
                $auditobj.EventData = "Removed {0} from Group: {1}" -f $userinfo, $GroupName
            }               

            "SecGroupCreate" {
                [xml]$eventdata = ("<root>$($AuditItem.EventData)</root>").replace("&","&amp;")
                $auditobj.EventData = "Group Created: {0}" -f $eventData.root.title
            }             

            "SecGroupDelete" {
                [xml]$eventdata = "<root>$($AuditItem.EventData)</root>"
                $SecGroupCreateEntries | Where-Object {$_.EventData -ilike "*<groupid>$($eventData.root.groupid)</groupid>*"} | Select -First 1 | foreach {[xml]$eventdata = ("<root>$($_.EventData)</root>").replace("&","&amp;"); $auditobj.EventData = "Group Deleted: {0}" -f $eventData.root.title}
            }  
           
            "SecRoleBindUpdate" {
                [xml]$eventdata = "<root>$($AuditItem.EventData)</root>"
                try {$principal = $SPSiteUsers.GetById($eventdata.root.principalid)}catch{}
                if ($principal){
                    $principalInfo = "{0} ({1})" -f $principal.loginname, $principal.email
                }
                else{
                    # The Principle may be a group
                    try{$principal = (($site.RootWeb.SiteGroups).GetById($eventData.root.principalid)).Name}catch{}
                     $principalInfo = "$principal"
                }

                if ($eventdata.root.roleid -eq -1){
                    $permission = "All"
                }
                elseif ($eventdata.root.roleid -eq "0")
                {
                    $permission = "None"
                }
                else{
                    $permission = ($site.RootWeb.RoleDefinitions | Where-Object {$_.id -eq $eventData.root.roleid}).name
                }
                $operation = ($eventdata.root.operation).replace("ensure ","")
                $auditobj.EventData = "'{0}' permission {1} : {2}" -f $permission, $operation, $principalInfo
            }
            "SecRoleBindInherit" {
                [xml]$eventdata = "<root>$($AuditItem.EventData)</root>"
                $auditobj.EventData = "Item Url: {0}" -f $($eventdata.root.url)
            }                    
            "SecRoleBindBreakInherit" {
                [xml]$eventdata = "<root>$($AuditItem.EventData)</root>"
                $auditobj.EventData = "Item Url: {0}" -f $($eventdata.root.url)
            }
            "SecRoleDefCreate" {
                [xml]$eventdata = "<root>$($AuditItem.EventData)</root>"
                $permissionMask = $($eventdata.root.perm)
                $auditobj.EventData = "{0}: " -f $($eventdata.root.name)
                1,2,4,8,16,32,64,128,256,512,2048,4096,65536,131072,262144,524288,1048576,2097152,4194304,8388608,16777216,33554432,67108864,134217728,268435456,536870912,1073741824,68719476736,137438953472,274877906944,549755813888,1099511627776,4611686018427387904,9223372036854775807`
                 | foreach {if (($permissionMask -band $_) -gt 0){$auditobj.EventData += $(get-PermissionLevelMask ($permissionMask -band $_)) + " "}}
            }            
            "SecRoleDefModify" {
                [xml]$eventdata = "<root>$($AuditItem.EventData)</root>"
                $permissionMask = $($eventdata.root.perm)
                $auditobj.EventData = "{0}: " -f $($eventdata.root.name)
                1,2,4,8,16,32,64,128,256,512,2048,4096,65536,131072,262144,524288,1048576,2097152,4194304,8388608,16777216,33554432,67108864,134217728,268435456,536870912,1073741824,68719476736,137438953472,274877906944,549755813888,1099511627776,4611686018427387904,9223372036854775807`
                 | foreach {if (($permissionMask -band $_) -gt 0){$auditobj.EventData += $(get-PermissionLevelMask ($permissionMask -band $_)) + " "}}
            }   
            "SecRoleDefDelete" {
                [xml]$eventdata = $AuditItem.EventData
                $SecRoleEntries| Where-Object {$_.EventData -ilike "*<id>$($eventData.id)</id>*"} | Select -First 1 | foreach {[xml]$eventdata = "<root>$($_.EventData)</root>"; $auditobj.EventData = "Permission Level Deleted: {0}" -f $eventData.root.name}
            }                                                                                                                                                                                       
            default {}
        }


    if (-not $includeRawEvent){
        $auditobj | Select-Object * -exclude EventDataRaw
    }
    else{
        $auditobj
    }

}

# End Script