Search code examples
csvpowershellsharepoint-2010

How to return only one user and date for Created By and Created Date column using PowerShell

I have a script that outputs to CSV all items and files from Libraries and Lists in a Site shown below:

Add-PSSnapin microsoft.sharepoint.powershell
$excludeLists = @("Master Page Gallery",
                "Workflows",
                "Workflow History"
                                          )

function Get-DocInventory([string]$siteUrl) {
$web = Get-SPWeb "http://contoso.com/sites/depts/HBG"
foreach ($list in $web.Lists) {
if($excludeLists -notcontains $list.Title){

foreach ($item in $list.Items) {
foreach($version in $item.Versions){

$personField = $item.Fields.GetField("Author");
$authorObject = $personField.GetFieldValue($item["Author"]);
$authorName = $authorObject.LookupValue;

$userField = $version.Fields.GetField("Editor");
$editorObject = $userField.GetFieldValue($version["Editor"]);
$editorName = $editorObject.LookupValue;

$localOffset = +5;
$modified = $version["Modified"] -as [datetime];

if($modified.IsDaylightSavingTime()){$localOffset += 1;}
$modifiedLocal = $modified.addHours(-$localOffset);

$data = @{
"Version" = $version.VersionLabel
                        "List Name" = $list.Title
                        "Created By" = $authorName 
                        "Created Date" = ($item["Created"] -as [datetime]).DateTime
                        "Modified By" = $editorName
                        "Modified Date" = ($modifiedLocal -as[datetime]).DateTime
                        "Item Name" = $item.Name

}
New-Object PSObject -Property $data | Select "List Name", "Item Name", "Version", "Created By", "Created Date", "Modified By", "Modified Date"
}
}
$web.Dispose();
}
}
}




Get-DocInventory  | Export-Csv -NoTypeInformation -Path C:\AuditReport.csv

Below is a sample of what the script outputs:

enter image description here

The output above shows a file called Lions.pdf with 3 versions. In addition, this displays which user made the change at a specific version of the file. My question is, is there a way to return the user's name in the Created By column and the Created Date only once instead of it repeating for each version of the file? If so, can someone show me how as I have been having a hard time.

Below is the desired output that displays the user's name once in the Created By column and the Created Date shows only once as well:

enter image description here

Solution

  • This may be a bit dirty, but that would be my first approach to accomplish this.

    At each loop iteration, you compare the values of $authorName and $createdDate with their values during the previous iteration. If they're equal, you erase the values.

    Add-PSSnapin Microsoft.Sharepoint.Powershell
$excludeLists = @("Master Page Gallery",
                  "Workflows",
                  "Workflow History")

function Get-DocInventory([string]$siteUrl) {
    $web = Get-SPWeb "http://contoso.com/sites/depts/HBG"

    foreach ($list in $web.Lists) {

        if($excludeLists -notcontains $list.Title) {

            foreach ($item in $list.Items) {

                $tempCreatedBy = ""
                $tempCreatedDate = ""

                foreach($version in $item.Versions) {

                    $personField = $item.Fields.GetField("Author")
                    $authorObject = $personField.GetFieldValue($item["Author"])

                    $authorName = $authorObject.LookupValue
                    if($authorName -eq $tempCreatedBy) { $authorName = "" } else { $tempCreatedBy = $authorName }

                    $createdDate = ($item["Created"] -as [datetime]).DateTime
                    if($createdDate -eq $tempCreatedDate) { $createdDate = "" } else { $tempCreatedDate = $createdDate }

                    $userField = $version.Fields.GetField("Editor")
                    $editorObject = $userField.GetFieldValue($version["Editor"])
                    $editorName = $editorObject.LookupValue

                    $localOffset = +5
                    $modified = $version["Modified"] -as [datetime]

                    if($modified.IsDaylightSavingTime()){ $localOffset += 1 }
                    $modifiedLocal = $modified.addHours(-$localOffset)

                    $data = @{
                        "Version" = $version.VersionLabel
                        "List Name" = $list.Title
                        "Created By" = $authorName 
                        "Created Date" = $createdDate
                        "Modified By" = $editorName
                        "Modified Date" = ($modifiedLocal -as[datetime]).DateTime
                        "Item Name" = $item.Name
                    }

                    New-Object PSObject -Property $data | Select "List Name", "Item Name", "Version", "Created By", "Created Date", "Modified By", "Modified Date"
                }
            }
            $web.Dispose();
        }
    }
}

Get-DocInventory  | Export-Csv -NoTypeInformation -Path C:\AuditReport.csv